使用psycopg2执行SELECT查询时遭遇UndefinedTable错误

我编写的脚本读取格式为schema, table, column的.csv文件，执行SELECT查询获取这些列的所有记录值，目标是打印文件中列的所有值。

运行脚本时，cursor.execute()方法抛出psycopg2.errors.UndefinedTable错误：

print(f'SELECT "{column}" FROM {schema}."{table}";') # 打印查询用于调试
cursor.execute(f'SELECT "{column}" FROM {schema}."{table}";')

# 输出（已截断）
SELECT "CREATION_DATE" FROM abbotsley_271."AREA_BUILD_PHASE_BOUNDARIES";
psycopg2.errors.UndefinedTable: relation "abbotsley_271.AREA_BUILD_PHASE_BOUNDARIES" does not exist
LINE 1: SELECT "CREATION_DATE" FROM abbotsley_271."AREA_BUILD_PHASE...

尽管该schema和表确实存在于PostgreSQL数据库中，但报错称关系"abbotsley_271.AREA_BUILD_PHASE_BOUNDARIES"不存在。

在DBeaver等DBMS工具中运行print()输出的查询，执行正常；将execute()中的f-string替换为打印的查询字符串，也能正常执行。

import psycopg2
import csv
from tqdm import tqdm

conn = psycopg2.connect(
    host="IP",
    database="db",
    user="user",
    password="password")

cursor = conn.cursor()

with open("C:\Users\user\Downloads\excel_files\character varying to timestamp.csv", 'r', encoding='utf-8') as csv_file:
    csv_rows = csv.reader(csv_file, delimiter=',')
    columnLengths = []
    
    for value in tqdm(csv_rows, desc="CSV progress"):
        schema = value[0]
        table = value[1]
        column = value[2]
        
        print(f'SELECT "{column}" FROM {schema}."{table}";')
        cursor.execute(f'SELECT "{column}" FROM {schema}."{table}";') # 错误位置

        data = cursor.fetchall()
        print(data)

        for record in data:
            cvValue = record[0]

            columnLengths.append(f"{schema}, {table}, {column}, {cvValue}")

    for record in columnLengths:
        print(record)

• 切换不同引号包裹方式（' 和 "），无效
• 移除查询中的{schema}.，错误消失，但仅搜索一个schema

错误信息 #

SELECT "CREATION_DATE" FROM "abbotsley_271"."AREA_BUILD_PHASE_BOUNDARIES"

Traceback (most recent call last):
  File "g:\My Drive\Code Library\Python\Data - character varying to timestamp without time zone.py", line 30, in <module>
    cursor.execute(tbl_qry.as_string(conn))
psycopg2.errors.UndefinedTable: relation "abbotsley_271.AREA_BUILD_PHASE_BOUNDARIES" does not exist
LINE 1: SELECT "CREATION_DATE" FROM "abbotsley_271"."AREA_BUILD_PHA...

更新后的脚本 #

import psycopg2
import csv
from tqdm import tqdm
from psycopg2 import sql

conn = psycopg2.connect(
    host="host",
    database="db",
    user="user",
    password="password")

cursor = conn.cursor()

with open("C:\Users\alex.fletcher\Downloads\excel_files\character varying to timestamp.csv", 'r', encoding='utf-8') as csv_file:
    csv_rows = csv.reader(csv_file, delimiter=',')
    columnLengths = []
    
    for value in tqdm(csv_rows, desc="CSV progress"):
        schema = value[0]
        table = value[1]
        column = value[2]

        tbl_qry = sql.SQL("SELECT {} FROM {}").format(
            sql.Identifier(column),
            sql.Identifier(schema, table)
            )

        print(f'SELECT "{column}" FROM {schema}."{table}";')
        # cursor.execute(f'SELECT "{column}" FROM {schema}."{table}";')
        cursor.execute(tbl_qry.as_string(conn))

        data = cursor.fetchall()
        print(data)

        for record in data:
            cvValue = record[0]

            columnLengths.append(f"{schema}, {table}, {column}, {cvValue}")

    for record in columnLengths:
        print(record)

内容的提问来源于stack exchange，提问作者TangePRS