How can I convert Sqlalchemy table object to Pandas DataFrame?

Question

Is it possible to convert retrieved SqlAlchemy table object into Pandas DataFrame or do I need to write a particular function for that aim ?

Answer 1

This might not be the most efficient way, but it has worked for me to reflect a database table using automap_base and then convert it to a Pandas DataFrame.

import pandas as pd
from sqlalchemy.ext.automap import automap_base
from sqlalchemy import create_engine
from sqlalchemy.orm import Session

connection_string = "your:db:connection:string:here"
engine = create_engine(connection_string, echo=False)
session = Session(engine)

# sqlalchemy: Reflect the tables
Base = automap_base()
Base.prepare(engine, reflect=True)

# Mapped classes are now created with names by default matching that of the table name.
Table_Name = Base.classes.table_name

# Example query with filtering
query = session.query(Table_Name).filter(Table_Name.language != 'english')

# Convert to DataFrame
df = pd.read_sql(query.statement, engine)
df.head()

Answer 2

I think I've tried this before. It's hacky, but for whole-table ORM query results, this should work:

import pandas as pd

cols = [c.name for c in SQLA_Table.__table__.columns]
pk = [c.name for c in SQLA_Table.__table__.primary_key]
tuplefied_list = [(getattr(item, col) for col in cols) for item in result_list]

df = pd.DataFrame.from_records(tuplefied_list, index=pk, columns=cols)

Partial query results (NamedTuples) will also work, but you have to construct the DataFrame columns and index to match your query.