How to read / query SQL table after creating an 'Engine' object in Python?

user_10581944 0
Viewed 267

I'm using Python 3.7, I'm not very familiar with SQL with Python, I have the code below to create a SQL engine:

mysql_engine = sqlalchemy.create_engine('mysql://xxxxxx')

Then I have a pre-defined SQL query, here's a simplified version: (this query I have tested in MySQL Workbench and it works fine)

example_query = '''
select
    a.date
from
    abc.xyz a
'''

I want to be able to read/query from the table, I tried:

cursor = mysql_engine.cursor()
cursor.execute(example_query)

but I got error AttributeError: 'Engine' object has no attribute 'cursor', perhaps I can use pandas? Can someone help with this, thanks.

sqlclient pandas database-cursor mysql python
1 Answers

pd.read_sql

The second argument can accept a sqlalchemy engine.

From the Docs

con: SQLAlchemy connectable, str, or sqlite3 connection Using SQLAlchemy makes it possible to use any DB supported by that library. If a DBAPI2 object, only sqlite3 is supported. The user is responsible for engine disposal and connection closure for the SQLAlchemy connectable; str connections are closed automatically.

pd.read_sql(example_query, mysql_engine)
user_23366540
Related