Sqlalchemy Orm Conversion to Pandas Dataframe

SQLAlchemy ORM conversion to pandas DataFrame

Below should work in most cases:

df = pd.read_sql(query.statement, query.session.bind)

See pandas.read_sql documentation for more information on the parameters.

SQLAlchemy ORM conversion to Pandas DataFrame with Bigquery

From @TimSwast's comment:

Using regular query statement with the pandas.read_sql actually works ! So this would look like this:

query = session.query(...)
df = pandas.read_sql(query.statement, session.query.bind)

How to convert SQL Query result to PANDAS Data Structure?

Here's the shortest code that will do the job:

from pandas import DataFrame
df = DataFrame(resoverall.fetchall())
df.columns = resoverall.keys()

You can go fancier and parse the types as in Paul's answer.

Moving data from sqlalchemy to a pandas DataFrame

Simply add an __init__ method in your model and call the Class object before dataframe build. Specifically below creates an iterable of tuples binded into columns with pandas.DataFrame().

class LPRRank(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    candid = db.Column(db.String(40), index=True, unique=False)
    rank = db.Column(db.Integer, index=True, unique=False) 
    user_id = db.Column(db.Integer, db.ForeignKey('lprvote.id'))

    def __init__(self, candid=None, rank=None, user_id=None):
        self.data = (candid, rank, user_id)

    def __repr__(self):
        return (self.candid, self.rank, self.user_id) 

data = db.session.query(LPRRank).all()
df = pd.DataFrame([(d.candid, d.rank, d.user_id) for d in data], 
                  columns=['candid', 'rank', 'user_id'])

Alternatively, use the SQLAlchemy ORM based on your defined Model class, LPRRank, to run read_sql:

df = pd.read_sql(sql = db.session.query(LPRRank)\
                         .with_entities(LPRRank.candid,
                                        LPRRank.rank,
                                        LPRRank.user_id).statement, 
                 con = db.session.bind)

Import an SQLAlchemy table to a pandas dataframe without Flask

Similar to your conclusion, here's how I read databases into pandas:

# Create your query.
# This can be as complex or simple as you'd like
query = session_remote.query(Game)

df = pd.read_sql(query.statement, session_remote.bind)

The key difference here is the utilization of the ORM to perform (or rather, write) the query itself.

Masking SQL behind an ORM has many advantages -- I strongly recommend against utilizing raw SQL in production backends.

How to perform a SQL query with SQLAlchemy to later pass it into a pandas dataframe

It is solved, I had to upgrade SQLAlchemy

sudo pip install sqlalchemy  --upgrade

---

Related Topics