python - 使用 Python 在 Postgres 中参数化查询

Question

我在用 python 参数化 sql 查询时遇到了一些麻烦。不完全知道为什么会发生此错误...如果元组有两个成员并且我在 sql 中使用两个参数，我如何因一个错误而关闭？

错误信息:

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

在main中调用函数:

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

用户类中的方法:

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 f.id = c.id 
            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 1

用一个 % 转义 % 字符>

and c.cust_name ilike '%%_bar'
and c.baz not ilike 'test%%' """