Reading from mysql tables using pandas and named parameters in query

Question

I try to execute the following query

SELECT user_id, user_agent_id, requests 
FROM riskanalysis_user_http_ua_stats
WHERE since>= :since AND until< :until'

I try the following pandas code

sql = 'SELECT user_id, user_agent_id, requests ' \
      'FROM riskanalysis_user_http_ua_stats ' \
      'WHERE since>= :since AND until< :until'

dataframe_records = pd.read_sql_query(sql, engine,
                                      params={'since':datetime_object,
                                              'until':datetime_object}

and I get the following error

sqlalchemy.exc.ArgumentError: Could not parse rfc1738 URL from string 'SELECT user_id, user_agent_id, requests FROM riskanalysis_user_http_ua_stats WHERE since>= :since AND until< :until'

I am using pymysql as the driver and MySQL database. How do I pass named parameters in an sql query?

EDIT 1: Corrected the parameter order but now I get the following

sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, u"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':since AND until< :until' at line 1") [SQL: 'SELECT user_id, user_agent_id, requests FROM riskanalysis_user_http_ua_stats WHERE since>= :since AND until< :until'] [parameters: {'since': datetime.datetime(2015, 6, 18, 0, 0, tzinfo=tzutc()), 'until': datetime.datetime(2015, 6, 18, 0, 2, tzinfo=tzutc())}]

Answer 1

According to pandas.read_sql_query documentation,

params : list, tuple or dict, optional, default: None

List of parameters to pass to execute method. The syntax used to pass parameters is database driver dependent. Check your database driver documentation for which of the five syntax styles, described in PEP 249's paramstyle, is supported. Eg. for psycopg2, uses %(name)s so use params={‘name’ : ‘value’}

If you then look at PEP 249's paramstyle, you'll see many options. But the PyMySQL's execute documentation states that

If args is a list or tuple, %s can be used as a placeholder in the query.

If args is a dict, %(name)s can be used as a placeholder in the query.

So, applying to your case, that would be:

sql = 'SELECT user_id, user_agent_id, requests ' \
      'FROM riskanalysis_user_http_ua_stats ' \
      'WHERE since>= %s AND until< %s'

dataframe_records = pd.read_sql_query(sql, engine,
                                      params=(datetime_object, datetime_object))

or

sql = 'SELECT user_id, user_agent_id, requests ' \
      'FROM riskanalysis_user_http_ua_stats ' \
      'WHERE since>= %(since)s AND until< %(until)s'

dataframe_records = pd.read_sql_query(sql, engine,
                                      params={'since':datetime_object,
                                              'until':datetime_object})

Answer 2

pandas.read_sql_query expects sql query before the engine, and tries to parse your query as a database URI.
See https://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_sql_query.html

dataframe_records = pd.read_sql_query(sql, engine,
      params={'since':datetime_object,'until':datetime_object}
)