psycopg2 准备删除语句 [英] psycopg2 prepared delete statement

问题描述

我正在努力生成删除查询，其中查询的参数实际上是一组值.

I am struggling with generating the delete query where parameters for the query is actually a set of values.

所以我需要删除参数为一对值的行，例如:

So I need to delete rows where parameters are a pair values for example:

从表中删除，其中 col1 = %s 和 col2 = %s

可以在 Python 中执行，例如:

which can be executed in Python like:

cur = conn.cursor()
cur.execute(query, (col1_value, col2_value))

现在我想运行一个查询:

Now I would like to run a query:

delete from table where (col1, col2) in ( (col1_value1, col2_value1), (col1_value2, col2_value2) );

我可以生成查询和值并执行确切的 SQL，但我不能完全生成准备好的语句.

I can generate the queries and values and execute the exact SQL but I can't quite generate prepared statement.

我试过了:

delete from table where (col1, col2) in %s

和

delete from table where (col1, col2) in (%s)

但是当我尝试执行时:

cur.execute(query, list_of_col_value_tuples)

或

cur.execute(query, tuple_of_col_value_tuples)

我收到一个异常，表明 psycopg2 无法将参数转换为字符串.

I get an exception that indicates that psycopg2 cannot convert arguments to strings.

有没有办法使用psycopg2来执行这样的查询?

Is there any way to use psycopg2 to execute a query like this?

推荐答案

其实只要精心构建，解决方案很容易.

Actually the resolution is quite easy if carefully constructed.

在 psycopg2 的杂项 中有一个函数 execute_values.

In the miscellaneous goodies of psycopg2 there is a function execute_values.

虽然 psycopg2 给出的所有示例都处理插入，因为如果调用 delete 的格式如下，该函数基本上会将参数列表转换为 VALUES 列表:

While all the examples that are given by psycopg2 deal with inserts as the function basically converts the list of arguments into a VALUES list if the call to delete is formatted like so:

qry = "delete from table where (col1, col2) in (%s)"

电话:

execute_values(cur=cur, qry=qry, argslist=<list of value tuples>)

将使删除完全按照要求执行.

will make the delete perform exactly as required.

这篇关于psycopg2 准备删除语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！