Python - Psycopg2，如何在cur.execute（）中混合元组和字符串？

Question

我是新来的Python和Psycopg2 ...我'尝试这样做，使用IN SQL语句和其他WHERE子句的查询，但我发现这样的错误：

psycopg2.ProgrammingError: argument formats can't be mixed 

从我明白我的混合串Python的元组，这里是SELECT声明：

cur2.execute("SELECT hash FROM jobsads_text\ 
        WHERE\ 
        date_inserted::timestamp::date - now()::timestamp::date <= 0\ 
        AND date_inserted::timestamp::date - now()::timestamp::date >= -7\ 
        AND hash NOT IN %s \ 
        AND lower((%s)) LIKE '%(%s)%'\ 
        ORDER BY date_inserted asc;", ((not_in_sql,), search_field, search_string)) 

我在查询得到的错误之上。

这个查询波纹管运行正常：

cur2.execute("SELECT hash FROM jobsads_text\ 
        WHERE\ 
        date_inserted::timestamp::date - now()::timestamp::date <= 0\ 
        AND date_inserted::timestamp::date - now()::timestamp::date >= -7\ 
        AND hash NOT IN %s \ 
        ORDER BY date_inserted asc;", (not_in_sql,)) 

我的问题是...我怎么能与琴弦search_field和search_string混合元组not_in_sql？

任何线索？

最好的问候，

Answer 1

t = (1, 3) 
search_field = 'c' 
search_string = '%something%' 
print cursor.mogrify("""\ 
    select * 
    from p 
    where 
     c in %%s 
     and 
     lower (%s) like %%s 
    """ % search_field, (t, search_string)) 

将输出这样的：

select * 
from p 
where 
    c in (1, 3) 
    and 
    lower (c) like '%something%' 

psycopg2不会取代像列名标识，所以你必须传递查询作为方法的第一个参数之前替换即可。