将参数列表传递给psycopg2中的SQL [英] Passing list of parameters to SQL in psycopg2

问题描述

我有一个行ID列表，可从数据库中获取。我正在使用python和psycopg2，我的问题是如何有效地将这些ID传递给SQL？我的意思是，如果我知道该列表的长度，这很容易，因为我总是可以根据需要手动或自动将尽可能多的％s表达式添加到查询字符串中，但是在这里我不知道我需要多少。重要的是，我需要使用sql id IN（id1，id2，...）语句选择该行。我知道可以检查列表的长度并将适当数量的％s连接到查询字符串中，但是恐怕它会非常缓慢且难看。有谁知道如何解决它？而且，请不要问为什么我需要使用 IN语句来进行操作-这是基准测试，是我的课堂作业的一部分。

I have a list of ids of rows to fetch from database. I'm using python and psycopg2, and my problem is how to effectively pass those ids to SQL? I mean that if I know the length of that list, it is pretty easy because I can always manually or automatically add as many "%s" expressions into query string as needed, but here I don't know how much of them I need. It is important that I need to select that rows using sql "id IN (id1, id2, ...)" statement. I know that it is possible to check the length of the list and concatenate suitable number of "%s" into query string, but I'm afraid that it would be very slow and ugly. Does anyone have an idea on how to solve it? And please don't ask why I need to do it with "IN" statement - it is a benchmark which is a part of my class assignment. Thanks in advance!

推荐答案

Python元组在psycopg2中转换为sql列表：

Python tuples are converted to sql lists in psycopg2:

cur.mogrify("SELECT * FROM table WHERE column IN %s;", ((1,2,3),))

将输出

'SELECT * FROM table WHERE column IN (1,2,3);'

对于Python新手：这很重要使用元组，而不是这里的列表。这是第二个示例：

For Python newcomers: It is unfortunately important to use a tuple, not a list here. Here's a second example:

cur.mogrify("SELECT * FROM table WHERE column IN %s;", 
    tuple([row[0] for row in rows]))

这篇关于将参数列表传递给psycopg2中的SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！