Reading from mysql tables using pandas and named parameters in query

user_2349589 0
Viewed 2906

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())}]
pandas sqlalchemy mysql python
3 Answers

As pointed out, your driver does not recognize named placeholders using the colon syntax. The query is passed as is to MySQL, which then complains about the placeholders, since they are syntax errors. A solution is to use the SQLAlchemy text() construct, which handles translating the named placeholders to a format understood by your driver:

from sqlalchemy import text

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