How do I add a variable to a sqlalchemy query filter and convert it to a string query?

Question

I have a summarized query that I am trying to put into a pandas data frame in a Flask app. I have figured out how to convert the sqlalchemy query into a string query that pandas can read into a dataframe. The variable has a date value that has been converted to a string.

The issue I am having is I am trying to use a variable in the filter and, when I convert it to the query string, it leaves the variable out, like I didn't provide a value. This results in a sqlalchemy.exc.programmingerror.

Here's my date variable:

this_week = datetime.strftime(get_friday(datetime.now().date()),'%m/%d/%Y')

The get_friday() function just gets the Friday of the week from a given date. In this case, I am just looking at the current week.

Here's my query set to a variable:

grouped_entries = Entry.query \
  .with_entities(Entry.entry_date.label('entry_date'), \
    Entry.client.label('client'),Entry.day.label('day'), \
    func.sum(Entry.total_time).label('time_sum')) \
  .filter(Entry.week_end == this_week) \
  .group_by(Entry.client,Entry.entry_date,Entry.day) \
  .order_by(Entry.entry_date.asc())

Here's the pandas code converting the query to a string query to read into a dataframe:

pd.read_sql(str(grouped_entries.statement.compile(dialect=sqlite.dialect())), db.engine)

Here's the error I get when I try to run it:

sqlalchemy.exc.ProgrammingError: (sqlite3.ProgrammingError) Incorrect number of bindings supplied. The current statement uses 1, and there are 0 supplied.[SQL: 'SELECT entry.entry_date AS entry_date, entry.client AS client, entry.day AS day, sum(entry.total_time) AS time_sum \nFROM entry \nWHERE entry.week_end = ? GROUP BY entry.client, entry.entry_date, entry.day ORDER BY entry.entry_date ASC']

I know the query works on it's own as a sqlalchemy query but when it is converted to a string query, it adds a ? in place of the variable value in the filter/where clause. I imagine it is some sort of security measure for sql injection or something like that but I can't seem to figure out how to get the variable value in the string query.

Did you try `print grouped_entries`. This should print the raw SQL and you should see if the variable you are using is parsed correctly before the conversion. — shifloni, Dec 04 '15 at 03:42
@shifloni Yeah, I checked the string output of the query. It shows the following: SELECT entry.client AS client, entry.day AS day, sum(entry.total_time) AS time_sum FROM entry WHERE entry.week_end = :week_end_1 GROUP BY entry.client, entry.day ORDER BY entry.entry_date ASC — petey, Dec 04 '15 at 03:51
I found the params feature of the read_sql function in pandas. I was able to use that to add the `this_week` variable to the query but now I'm not getting any results even though there are results that should be showing up. — petey, Dec 04 '15 at 03:58
@shifloni I did check that after I got the variable to insert into the query properly. It is expecting a datetime object so I updated the `this_week` variable to make sure it is a datetime object as well. — petey, Dec 04 '15 at 04:55
Seems that pandas is not able to parse the SQL query returned by SQLAlchemy since it is expecting raw SQL? (not very familiar with pandas) so maybe you should find the location of the parameter where you want to insert the value using string.replace() or regex and then pass it to pandas? — shifloni, Dec 04 '15 at 04:59

score 0 · Accepted Answer · edited May 23 '17 at 11:44

Well, after several more hours of digging (and some rest), I managed to figure it out.

The easiest way I found to insert the variable into the statement was to utilize the read_sql function in pandas and set the variable as a parameter.

data = pd.read_sql(str(grouped_entries), db.engine, params=[this_week])

This will insert the variable(s) into the string query.

After solving the initial issue, I then ran into an issue with the filter. Turns out, when you try to cast a datetime as a date cast(Entry.week_end,Date) in sqlite (which is what I needed to do to make the filter work properly), it only returns the year and will throw a ValueError saying it can't parse the date string. To get around this, I found that you can apply a date function to the column in sqlalchemy, similar to this issue.

Here was my final code:

this_week = datetime.strftime(get_friday(datetime.now()),'%Y-%m-%d')
grouped_entries = Entry.query \
                       .with_entities(Entry.client.label('client'), \
                         Entry.day.label('day'), \
                         func.sum(Entry.total_time).label('time')) \
                       .filter(func.DATE(Entry.week_end) == this_week) \
                       .group_by(Entry.client,Entry.day) \
                       .order_by(Entry.day.asc())
data = pd.read_sql(str(grouped_entries), db.engine, params=[this_week])

How do I add a variable to a sqlalchemy query filter and convert it to a string query?

1 Answers1