Pandas: read_sql from SQLAlchemy Models

pandas-read_sql-from-sqlalchemy-models.py

# Example of read_sql loading dataframe from database, using SQLAlchemy Models (possibily from flask app)

# import pandas lib
import pandas as pd

# import sqlalchemy libs
from flask_app.db import express_session
from flask_app.models import SalesRegistry
from flask_app.app import create_app, db

# instantiate flask app to load db settings
app = create_app()
app.app_context().push()
db.init_app(app)

with express_session() as db_session:
    # build query object from express_session
    query = db_session.query(SalesRegistry.id,
                             SalesRegistry.client,
                             SalesRegistry.value,
                             SalesRegistry.date
                            ).filter(SalesRegistry.sales_status == 'COMPLETED').limit(50)
    
    # execute query on database from pandas
    df = pd.read_sql(query.statement, db_session.bind)
    
# show loaded data on dataframe memory
df.head()