8

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

erogol
  • 12,388
  • 30
  • 95
  • 151
  • 1
    Have you considered using [pandas.read_sql](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_sql.html#pandas.read_sql)? – unutbu Aug 12 '14 at 12:47
  • Yes but SqlAlchemy has other use cases in my project as well. – erogol Aug 12 '14 at 13:17
  • 1
    pandas.read_sql can use an SqlAlchemy engine. – unutbu Aug 12 '14 at 13:21
  • For when you want to use another selectable than just the table (including working with the orm), take a look at: http://stackoverflow.com/a/29528804/1273938 – LeoRochael Jul 31 '15 at 18:39

2 Answers2

13

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()
Halee
  • 473
  • 8
  • 15
5

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.

jkmacc
  • 5,433
  • 3
  • 27
  • 27
  • 2
    just use `pandas.read_sql` with an SQLAlchemy engine. it's dead simple. – Paul H Aug 12 '14 at 17:52
  • 1
    How do you use `pandas.read_sql` on an ORM query, like: `session.query(MyORMTable).limit(100).all()` ? – jkmacc Aug 12 '14 at 17:57
  • 1
    `pandas.read_sql_table('MyTable', MySQLEngine)` see here http://pandas.pydata.org/pandas-docs/stable/whatsnew.html#whatsnew-0140-sql – Paul H Aug 12 '14 at 18:00
  • Very cool. It looks like it doesn't convert existing query results, though (or work with the ORM), which is how I was interpreting the original question. – jkmacc Aug 12 '14 at 18:23
  • 1
    What is `result_list` here? I get an error when trying to run this. I also have existing query results that I want to convert to a pandas data frame (as opposed to just loading up a straight table) – Vincent Mar 21 '17 at 18:23
  • In Python 3, you'll need to invoke `tuple` explicitly: `tuplefied_list = [tuple(getattr(item, col) for col in cols) for item in result_list]`. Otherwise `pd.DataFrame.from_records` does not parse the generator expressions as intended. – twolffpiggott Apr 30 '19 at 08:51
  • This answer and the comments are symptomatic of terrible database/ORM modeling. Please see @Halee's answer. – Ryan Ward Feb 24 '22 at 15:14