9

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
  • 13,156
  • 33
  • 101
  • 155
  • 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

4 Answers4

15

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
  • 492
  • 9
  • 15
6

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
  • 6,125
  • 3
  • 30
  • 27
  • 3
    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 Valverde Feb 24 '22 at 15:14
0

Pandas database functions such as read_sql_query accept SQLAlchemy connection objects (so-called SQLAlchemy connectables, see pandas docs and sqlalchemy docs). Here's one example of using such object called my_connection:

import pandas as pd
import sqlalchemy

# create SQLAlchemy Engine object instance 
my_engine = sqlalchemy.create_engine(f"{dialect}+{driver}://{login}:{password}@{host}/{db_name}")

# connect to the database using the newly created Engine instance
my_connection = my_engine.connect()

# run SQL query
my_df = pd.read_sql_query(sql=my_sql_query, con=my_connection)
mirekphd
  • 4,799
  • 3
  • 38
  • 59
0

I have a simpler way:

# Step1: import
import pandas as pd
from sqlalchemy import create_engine

# Step2: create_engine
connection_string = "sqlite:////absolute/path/to/database.db"
engine = create_engine(connection_string)

# Step3: select table
print (engine.table_names())

# Step4: read table
table_df = pd.read_sql_table('table_name', engine)
table_df.head()

For other types of connection_string, SQLAlchemy 1.4 Documentation.

showteth
  • 340
  • 3
  • 10