I am using pg8000 to connect to a PostgreSQL database via Python. I would like to be able to send in dates as parameters via the cursor.execute method:
def info_by_month(cursor, year, month):
query = """
SELECT *
FROM info
WHERE date_trunc('month', info.created_at) =
date_trunc('month', '%s-%s-01')
"""
cursor.execute(query, (year, month))
return cursor
This will raise the error: InterfaceError: '%s' not supported in a quoted string within the query string. It's possible to use Python's string formatting to insert the date in there. The use of the string formatting mini language provides a measure of data validation to prevent SQL injection attacks, but it's still pretty ugly.
def info_by_month(cursor, year, month):
query = """
SELECT *
FROM info
WHERE date_trunc('month', info.created_at) =
date_trunc('month', '{:04}-{:02}-01')
""".format(year, month)
cursor.execute(query)
return cursor
How do I sent a quoted string into the cursor.execute method?
Do the format ahead of time, and then pass the resulting string into execute. That way you avoid the SQL injection potential, but still get the formatting you want.
e.g. the query becomes:
query = """
SELECT *
FROM info
WHERE date_trunc('month', info.created_at) =
date_trunc('month', %s)"""
And then the format and execute becomes:
dateStr = "{:04}-{:02}-01".format(year, month)
cursor.execute(query, dateStr)
I use psycopg2, but it appears pg8000 adheres to the same DBI standard, so I would expect this to work in pg8000, too.