Flask requests are stateless: re-run SQL instead of passing pandas DataFrames between views to plot with Plotly

Learn why Flask requests are stateless, why storing a pandas DataFrame in session fails, and how to re-run SQL and render Plotly charts from query results.

Flask views feel like a straight line until you try to carry a pandas DataFrame from one request to the next. A common workflow looks simple on paper: accept a SQL query, show the result, let the user pick two columns, and render a Plotly chart. The snag arrives when you attempt to reuse the DataFrame produced in the “result” step inside the “plot” step. You render a template, the request ends, and the object is gone. Stashing a serialized DataFrame in session may work for tiny datasets, but quickly turns into a dead end.

Minimal example that exhibits the issue

The flow below collects a SQL query, executes it, builds a pandas DataFrame, renders an HTML table, and then tries to reuse that same DataFrame via Flask session in the plotting route.

from flask import render_template as render, request as http, session as web_session
from sqlalchemy import text
import pandas as pd
@app.route("/", methods=["GET", "POST"])
def home():
    return render('base.html')
@app.route('/submit', methods=['POST'])
def run_query():
    sql_text = http.form.get('sqlr')  # e.g., SELECT * FROM Soils
    with engine.connect() as db:
        res = db.execute(text(sql_text))
        cols = res.keys()
        rows = res.fetchall()
    frame = pd.DataFrame(rows, columns=cols)
    # Works only for small frames
    web_session['frame'] = frame.to_json()
    return render(
        "result.html",
        request_recap=sql_text,
        request_result=frame.to_html(index=False),
        cols=frame.columns,
    )
@app.route('/plot', methods=["GET", "POST"])
def make_chart():
    # Pulling from session; large frames won't scale
    frame = pd.read_json(web_session.get('frame'))
    x_name = http.form.get('var1')
    y_name = http.form.get('var2')
    fig = makefig(table=frame, x=x_name, y=y_name)
    return render("plot.html", fig=fig.to_html())

What actually goes wrong and why

render_template returns a response just like any other Python function returns a value. The moment a response is sent, the request context is gone and local variables disappear with it. HTTP and HTML are stateless, which means a later request cannot see variables from an earlier one. Each view runs in isolation. If you must share data across requests, you need persistence beyond local scope. Frameworks offer session mechanisms that map data to a specific user, but storing large, serialized DataFrames there is only practical for very small payloads and will not scale.

In other words, the problem is not with pandas or Plotly. The problem is expecting a variable created during one request to still be in memory during another request. That is not how request lifecycles work.

A practical fix: carry the query, not the DataFrame

A simple and effective pattern is to re-run the same SQL query in the plotting route. Instead of moving a DataFrame between views, keep the original SQL text in a hidden field in the form. When the user submits the form with the selected columns, the plotting route receives the SQL, executes it again, and builds the Plotly figure directly from the raw rows. No pandas required, no JSON payloads stuffed in session, no JavaScript.

from flask import Flask as WebApp
from flask import render_template as show
from flask import request as req
from sqlalchemy import text
import plotly.graph_objects as pgo
app = WebApp(__name__)
app.config['SECRET_KEY'] = 'your-secret-key'
@app.route("/", methods=["GET", "POST"])
def landing():
    return show('base.html')
@app.route('/submit', methods=['POST'])
def handle_submit():
    incoming_sql = req.form.get('sqlr')
    with engine.connect() as link:
        outcome = link.execute(text(incoming_sql))
        field_names = list(outcome.keys())
        data_rows = outcome.fetchall()
    # Convert rows for safe templating
    dict_rows = [dict(zip(field_names, r)) for r in data_rows]
    return show(
        "result.html",
        request_recap=incoming_sql,
        request_result=dict_rows,
        cols=field_names,
    )
@app.route('/plot', methods=["POST"])
def render_plot():
    sql_again = req.form.get('sql_query')
    x_field = req.form.get('var1')
    y_field = req.form.get('var2')
    with engine.connect() as link:
        outcome = link.execute(text(sql_again))
        field_names = list(outcome.keys())
        data_rows = outcome.fetchall()
    chart = build_figure(data_rows, field_names, x_field, y_field)
    return show("plot.html", fig=chart.to_html())
def build_figure(rows, cols, x_name, y_name):
    x_idx = cols.index(x_name)
    y_idx = cols.index(y_name)
    xs = [r[x_idx] for r in rows]
    ys = [r[y_idx] for r in rows]
    fig = pgo.Figure()
    fig.add_trace(
        pgo.Scatter(
            x=xs,
            y=ys,
            mode='markers',
            marker=dict(size=8, color='blue', opacity=0.7),
            name=f'{y_name} vs {x_name}',
        )
    )
    fig.update_layout(
        title=f'{y_name} vs {x_name}',
        xaxis_title=x_name,
        yaxis_title=y_name,
        template='plotly_white',
    )
    return fig

The accompanying templates accept plain dicts and send the original SQL back with the plotting request. This renders the rows as a table and provides the UI for choosing axes, all server-side.

<!-- result.html -->
<p>Your Query: {{ request_recap }}</p>
<table>
  <thead>
    <tr>
      {% for col in cols %}
      <th>{{ col }}</th>
      {% endfor %}
    </tr>
  </thead>
  <tbody>
    {% for row in request_result %}
    <tr>
      {% for col in cols %}
      <td>{{ row[col] }}</td>
      {% endfor %}
    </tr>
    {% endfor %}
  </tbody>
</table>
<form action="/plot" method="POST">
  <input type="hidden" name="sql_query" value="{{ request_recap }}" />
  <label for="var1">X-axis:
    <select name="var1" id="var1" required>
      <option value="">Select X column</option>
      {% for col in cols %}
      <option value="{{ col }}">{{ col }}</option>
      {% endfor %}
    </select>
  </label>
  <label for="var2">Y-axis:
    <select name="var2" id="var2" required>
      <option value="">Select Y column</option>
      {% for col in cols %}
      <option value="{{ col }}">{{ col }}</option>
      {% endfor %}
    </select>
  </label>
  <br /><br />
  <button type="submit">Generate Plot</button>
</form>

<!-- plot.html -->
<div>
  {{ fig|safe }}
</div>

Why this is worth understanding

Each view in a web framework runs like a separate program. Without server-side session data, files, or a database, one request cannot see the in-memory variables of another. Sessions exist precisely to tie request state to a specific user, but they are not a good place for large payloads such as full DataFrames. Passing the SQL and re-executing it when needed preserves correctness, avoids oversized session objects, and fits the stateless nature of HTTP. It also eliminates the need for pandas in this path, which removes unnecessary complexity when you only need to select columns and plot points with Plotly.

Takeaways

Do not rely on local variables to persist across requests. If you need continuity between views, either keep small data in a session or, as shown here, send the original SQL forward and re-run it to build the Plotly figure from raw rows. This keeps the templates lean, avoids bloating the session, and respects how Flask and HTTP actually work. When all you need is to plot two columns, skipping pandas in favor of direct row handling is simpler and effective. If multiple users are involved, remember that the mechanism tying data to a specific user is the session, which is designed for that purpose, while large datasets are better kept out of it.