StillLearningToCode StillLearningToCode - 1 year ago 103
Python Question

parameterizing a query in Postgres using Python

I am having a bit of trouble parameterizing a sql query with python. Don't exactly know why this error is happening... if the tuple has two members and i am using two parameters in the sql, how am i getting an off by one error?

error message:

File "...\", line 27, in main
rows = User.daily_users_by_pool_name('2016-08-01', '2016-08-02')
File "...\", line 48, in daily_users_by_pool_name
cursor.execute(query, (start_date, end_date))
IndexError: tuple index out of range

calling function in main:

rows = User.daily_users_by_pool_name('2016-08-01', '2016-08-02')

method in class User:

from database import ConnectionFromPool
from datetime import datetime
import pandas as pd
import numpy as np
import psycopg2

@classmethod #static
def daily_users_by_pool_name(cls, start_date, end_date):
'''returns a Pandas.DataFrame of results'''

query = """
Select foo.dos::date, foo.cust_id
from foo f
join customer c on =
where foo.dos >= %s::DATE
and foo.dos < %s::DATE
and c.cust_name ilike '%_bar'
and c.baz not ilike 'test%' """

with ConnectionFromPool() as cursor:
cursor.execute(query, (start_date, end_date))

return pd.DataFrame(cursor.fetchall(), columns=['foo', 'cust_id'])

Answer Source

Escape the % characters with one more %

and c.cust_name ilike '%%_bar'
and c.baz not ilike 'test%%' """
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download