PostgreSQL v14 Psycopg2 v2.9.3
我可以直接在PostgreSQL / pgsql中做到这一点，但我似乎不能在Psycopg 2中做到这一点。
给出一个示例表：

CREATE TABLE accounts (
       id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
       account_name TEXT,
       account_number TEXT NOT NULL,
       other_account_info TEXT
    )

    -- Enforces uniqueness for the pair, both non-null
    CREATE UNIQUE INDEX accounts_name_number_unique_idx ON accounts (account_name, account_number);

    -- Enforces uniqueness for the account_number for a non-null account_name
    CREATE UNIQUE INDEX accounts_number_unique_idx ON accounts (account_number) WHERE account_name IS NULL;

字符串
我想通过执行以下操作来匹配使用2个字段的帐户记录：

SELECT *
FROM accounts
WHERE (account_name, account_number) =ANY(VALUES('name', number'), ('name1', 'number2'))

型
当然，当我直接在psql中运行它时，这工作得很好，但是我不能让psycopg 2正确格式化SQL。

**注意：**我不想简单地执行WHERE account_name = %(account_name)s AND account_number = %(account_number)s，因为我需要查询未知数量的account_name / account_number对，并且不想动态生成SQL。

我试过以下方法：

template: str = f"""
SELECT *
FROM accounts
WHERE (account_name, account_number) = ANY(VALUES%(account_list)s)
"""

inserts: dict = {'account_list': ('account_name1', 'account_number1')}
execute(cur=_cursor, sql=template, argslist=inserts)

型
这很有效！但是，当我向参数添加第二个account_name和account_number并使其成为元组的元组时，它就会中断：UndefinedFunction: operator does not exist: text = record

template: str = f"""
SELECT *
FROM accounts
WHERE (account_name, account_number) = ANY(VALUES%(account_list)s)
"""

inserts: dict = { 'account_list': (('account_name1', 'account_number1',),('account_name2', 'account_number2',)) }
execute(cur=_cursor, sql=template, argslist=inserts)

型
我还尝试将参数设置为一个列表，并从SQL模板中删除“VALUES”，但它失败了，我得到了DatatypeMismatch: cannot compare dissimilar column types text and unknown at record column 1

template: str = f"""
SELECT *
FROM accounts
WHERE (account_name, account_number) = ANY(%(account_list)s)
"""

inserts: dict = { 'account_list': [('account_name1', 'account_number1'),('account_name2', 'account_number2')] }
execute(cur=_cursor, sql=template, argslist=inserts)

型
我认识到在某些情况下，我需要转换各个参数，但我不知道如何在psycopg 2中完成。
任何帮助将非常感谢！