CODEWITHSUNDEEP
pythonpandassqlalchemygoogle-bigquery
LoicM
LoicM

Reputation: 2203

SQLAlchemy ORM conversion to Pandas DataFrame with Bigquery

Extension of this question, which describes the process on how to use the pandas.read_sql function from an ORM to get the results of a query directly in a pandas DataFrame.

What I would like would be to be able to do the exact same thing while using SQLAlchemy and pybigquery

First off I define my session this way:

from sqlalchemy import
from sqlalchemy.orm import scoped_session, sessionmaker

db_uri = "bigquery://myproject/"
engine = create_engine(db_uri)
session = scoped_session(sessionmaker(bind=engine))

From there I can request from my ORM's Item

session.query(Item).first()
<Item...>

If I had to call a classical SQL database through pandas.read_sql I'd juste need to do:

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

Naively, I tried

pandas.read_bgq(query.statement)

But that returns TypeError: Object of type 'Select' is not JSON serializable

Is there a way to request bigquery directly into a pandas dataframe the same way as for classical SQL database ?

Upvotes: 1

Views: 5169

Answers (1)

LoicM
LoicM

Reputation: 2203

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)

Upvotes: 0

Related Questions