Return value for sqlalchemy db.execute() in Flask?

Question

My Flask app is hooked up to my postgres database in Heroku like so:

from flask import Flask, render_template, session, request, url_for, redirect, flash
from flask_session import Session
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker
import os

app = Flask(__name__)

# Check for environment variable
if not os.getenv("DATABASE_URL"):
    raise RuntimeError("DATABASE_URL is not set")

# Configure session to use filesystem
app.config["SESSION_PERMANENT"] = False
app.config["SESSION_TYPE"] = "filesystem"
Session(app)

# Set up database
engine = create_engine(os.getenv("DATABASE_URL"))
db = scoped_session(sessionmaker(bind=engine))

My Flask methods run various SQL statements. The syntax I'm using is typically like bk = db.execute("SELECT * FROM books WHERE isbn=:isbn", {"isbn": isbn}).

I thought the return value of such a statement would be a list of dictionaries, however, when I coded a method to check for len(bk) it said the object had no length even when it should have.

So, what's the return value that I'm receiving, and why doesn't it seem to have a discernible length to Python? Couldn't find a straight answer anywhere.

Answer 1

It's a ResultProxy object, as explained in the documentation of Session.execute(). It is iterable, but does not have a length, because in general it is not known how many rows a query produces before fetching them all. You could pass it to list(), like in the other answer, or use its fetch methods, namely fetchall().

The individual rows are not represented by dict instances, but RowProxy instances. They do act as a mapping, though, so you can use them as they were a dictionary for most purposes (except serialization to JSON, for example).

Answer 2

sqlalchemy will return a lazy generator of records. You can't use len but you can iterate over it and it will yield the records one by one, to save memory. If you have the memory or the table is small and you want to load all records at once, you can call list() in it:

bk = list(bk)