python-3.x - 如何使用 psycopg2 以点符号方式使用它们的别名在两个连接表中的许多字段上正确构建 SELECT 查询

Question

有没有办法使用 psycopg2 将这些标识符转换为 PostgreSQL 查询：

total_query_fields = (
    'p.id',
    'p.name',
    'p.type',
    'p.price',
    'o.date',        # please, notice the 'o' alias here
    'o.transaction', # please, notice the 'o' alias here
    'p.warehouse',
    'p.location',
)

# they may get split into their own tables if necessary:
product_query_fields = ('id', 'name', 'type', 'price', 'warehouse', 'location',)
order_query_fields = ('date', 'transaction',)

变成这样的东西：

import psycopg2
from psycopg2 import sql

myid = 100

sql_query = sql.SQL("""
    SELECT {fields} FROM product p
    INNER JOIN owner o ON p.id = o.product_id
    WHERE p.id = {jidx} AND (o.dateof_purchase IS NOT NULL
    OR o.state = 'checked_out' );"""
).format(
    fields = # there should be the readable ***magic***
    jidx = sql.Literal(myid)
)

?

即使通过阅读此线程了解了我的问题，我也无法找到一种使用点分符号获取我的字段列表的好方法。我猜可能必须使用两个，并且都使用and 。 或者也许更优雅的东西，例如使用？map()sql.SQL('.').join(...)sql.SQL(', ').join(...)
SELECT {}.{}...

因为目前我遇到了麻烦：

fields = sql.SQL(', ').join(map(sql.Identifier, total_query_fields)),

因为它将用双引号转义所有序列“table.fields”，这在 SQL 中绝对无效：

# /!\ INVALID SQL QUERY /!\:
print(sql_query.as_string(conn))
# will print:
# SELECT "p.id", "p.name", "p.type", "p.price", "o.date", "o.transaction", "p.warehouse", "p.location" FROM product p
#    INNER JOIN owner o ON p.id = o.product_id
#    WHERE p.id = 100 AND (o.dateof_purchase IS NOT NULL
#    OR o.state = 'checked_out' );

事实上，如果我直接在我最喜欢的 PostgreSQL 查询工具中复制/粘贴最后一个查询：

ERROR:  column "p.id" does not exist
LINE 1: SELECT "p.id", "p.name", "p.type", "p.price", "o.date",...
               ^
HINT:  Perhaps you meant to reference the column "p.id" or the column "o.id".
SQL state: 42703
Character: 8

psycopg2 显然引发了同样的错误：

UndefinedColumn: column "p.id" does not exist
LINE 1: SELECT "p.id", "p.name", "p.type", "p.price", "o.date...
               ^
HINT:  Perhaps you meant to reference the column "p.id" or the column "o.id".

文档也很清楚：

版本控制：

psycopg2.__version__
 '2.9.1 (dt dec pq3 ext lo64)'

标签： python-3.xpostgresqlpsycopg2

Answer 1

如果我正确理解了这个问题，我认为这里的技巧是sql.Identifier接受一个或多个字符串 ( *strings)，因此您可以拆分点上的列并将两个部分都传递给sql.Identifier这将构成所需"alias"."column"结果。

>>> i = sql.Identifier('a', 'col')
>>> i.strings
('a', 'col')
>>> conn = psycopg2.connect(database='test')
>>> cur = conn.cursor()
>>> i.as_string(cur)
'"a"."col"'

引用所有字段可以这样完成：

fields = sql.SQL(', ').join(sql.Identifier(*f.split('.')) for f in total_query_fields)

sql_query = sql.SQL(
    """
    SELECT {fields} FROM product p
    INNER JOIN owner o ON p.id = o.product_id
    WHERE p.id = {jidx} AND (o.dateof_purchase IS NOT NULL
    OR o.state = 'checked_out' );"""
).format(fields=fields, jidx=sql.Literal(myid))

结果查询（来自cursor.mogrify）是

b'\n    SELECT "p"."id", "p"."name", "p"."type", "p"."price", "o"."date", "o"."transaction", "p"."warehouse", "p"."location" FROM product p\n    INNER JOIN owner o ON p.id = o.product_id\n    WHERE p.id = 100 AND (o.dateof_purchase IS NOT NULL\n    OR o.state = \'checked_out\' );'

如果您更喜欢使用map而不是生成器表达式，则可以使用itertools.starmap

from itertools import starmap

fields = sql.SQL(', ').join(
    starmap(sql.Identifier, map(lambda f: f.split('.'), total_query_fields)))