1

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)

Community
  • 1
  • 1
Zero
  • 11,593
  • 9
  • 52
  • 70

2 Answers2

2

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
Zero
  • 11,593
  • 9
  • 52
  • 70
-1

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
sebastian
  • 9,526
  • 26
  • 54
  • compiled.params appears contains a `dict` of params that does not correspond to the engine. In your example above you cannot call `engine.execute(query_str, query_params)`. The required format of params depends on the engines `paramstyle` attribute [PEP-249]. Is there a way to get the params in the correct format? – Zero May 15 '15 at 06:24