Convert SQL database table to a DataFrame with SQLAlchemy’s ORM

Question

In a Flask app, I would like to convert an SQL Lite database table (User here) to a DataFrame. I have tried this

df = pd.DataFrame(dict(r) for r in User.query.all())

but it returns the error 'User' object is not iterable.

I'm not very familiar with SQLAlchemy. What would be a correct statement?

Answer 1

Try this

from your_flask_app import db
from your_flask_app.models import User
import pandas as pd

users = User.query.all()

user_dicts = [user.__dict__ for user in users]

for user_dict in user_dicts:
    user_dict.pop('_sa_instance_state', None)

df = pd.DataFrame(user_dicts)

print(df.head())