CODEWITHSUNDEEP
pythonsqlalchemy
Zero
Zero

Reputation: 12109

How do a pass a SQLAlchemy query to an execute(query_str, params) function

Many third party libraries support database connectivity with functions that mimic the PEP-249 .execute ( operation [, parameters ]) function.

An example is the pandas.read_sql(query, engine, params) function.

The problem is that the params spec varies depending on the engine. For example SQLite uses ?, MySQL uses %s etc in the query. The params needs to be either a list or a dict depending on the engine.

Using SQLAlchemy to handle the different engines, how can we get the appropriate query and params arguments to pass to execute.

(Note this is a different question to this question - I want to keep the query and parameters separate)

Upvotes: 1

Views: 1200

Answers (2)

sebastian
sebastian

Reputation: 9696

You're halfway there, checking the docs you'll see what compile returns:

http://docs.sqlalchemy.org/en/rel_1_0/core/internals.html#sqlalchemy.engine.interfaces.Compiled

Besides the string representation this object provides access to the bind parameters:

compiled = query.compile(engine)
query_str = str(compiled)
query_params = compiled.params

Upvotes: -1

Zero
Zero

Reputation: 12109

After posting the question, I found the following work-around:

import sqlalchemy
from sqlalchemy.sql import select
import pandas as pd

engine = sqlalchemy.create_engine("mysql+cymysql://root:tiger@localhost/test")

... snip ...

query = select(...)  # create a valid query.
df = pd.read_sql(query, engine)

So the magic of python saves the day. While this would be the preferred method, it doesn't exactly answer the question of getting the appropriate query string and parameters argument (if we needed them for a different library).

To get the query string, the following works. I don't know how to get the parameters in a way that works for all the different paramstyle

query_str = str(query.compile(engine))     # Gives the query string

Upvotes: 2

Related Questions