CODEWITHSUNDEEP
pythonmysqlpandassqlalchemy
PR102012
PR102012

Reputation: 878

Loop through list of dates in Python query string

I am trying to use Pandas and SQLAlchemy to run a query on a MySQL instance. In the actual query, there is a 'WHERE' statement referencing a specific date. I'd like to run this query separately for each date in a Python list, and append each date's dataframe iteratively to another Master dataframe. My code right now looks like this (excluding SQLAlchemy engine creation):

dates = ['2016-01-12','2016-01-13','2016-01-14']
for day in dates:
    query="""SELECT * from schema.table WHERE date = '%s' """
    df = pd.read_sql_query(query,engine)
    frame.append(df)

My error is 

/opt/rh/python27/root/usr/lib64/python2.7/site-packages/MySQLdb/cursors.pyc in execute(self, query, args)
157             query = query.encode(charset)
158         if args is not None:
--> 159             query = query % db.literal(args)
160         try:
161             r = self._query(query)
TypeError: not enough arguments for format string

I'm wondering what the best way to insert the string from the list into my query string is?

Upvotes: 0

Views: 1935

Answers (1)

alecxe
alecxe

Reputation: 474101

Use params to parameterize your query:

dates = ['2016-01-12', '2016-01-13', '2016-01-14']
query = """SELECT * from schema.table WHERE date = %s"""

for day in dates:
    df = pd.read_sql_query(query, engine, params=(day, ))
    frame.append(df)

Note that I've removed the quotes around the %s placeholder - the data type conversions would be handled by the database driver itself. It would put quotes implicitly if needed.

And, you can define the query before the loop once - no need to do it inside.

I also think that you may need to have a list of date or datetime objects instead of strings:

from datetime import date

dates = [
    date(year=2016, month=1, day=12), 
    date(year=2016, month=1, day=13),
    date(year=2016, month=1, day=14),
]

Upvotes: 3

Related Questions