CODEWITHSUNDEEP
pythonpandassqlalchemy
erogol
erogol

Reputation: 13604

How can I convert Sqlalchemy table object to Pandas DataFrame?

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

Upvotes: 11

Views: 14174

Answers (4)

showteth
showteth

Reputation: 418

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.

Upvotes: -1

mirekphd
mirekphd

Reputation: 6743

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)

Upvotes: 0

Halee
Halee

Reputation: 502

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()

Upvotes: 16

jkmacc
jkmacc

Reputation: 6427

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.

Upvotes: 6

Related Questions