I am using SqlAlchemy to connect to a postgres database. My goal is to write a simple SELECT * query using the ORM and convert the result to a pandas dataframe.
Within my class, self._pg_client is a Session object, and Matches is a table defined in the data model.
from base import Matches
from pandas import pd
...
class GetData(object):
def __init__(self, pg_client: Session):
self._pg_client: Session = pg_client
def run(self):
print(type(self._pg_client))
query = self._pg_client.query(Matches).all()
print(query.statement)
After instantiating a GetData() object and running it, the first print statement returns:
<class 'sqlalchemy.orm.session.Session'>
as is expected. However the second returns an error:
AttributeError: 'list' object has no attribute 'statement'
query is actually a list object of results from the query, not the query itself. A few other posts have suggested passing the query statement (link, link) to read_sql(), but I cannot seem to get the statment itself. I try the following:
matches = pd.read_sql(self._pg_client.query(Matches).all().statement, self._pg_client.bind)
which fails because the list object does not have a statement attribute. What is the correct way to get the query statement from an ORM query in SQLalchemy? Is this all that is needed to pass to read_sql ?
