psycopg2在我的字符串周围添加引号，这会产生问题

Question

我有以下脚本：

create_table_WAC = """
create table if not exists %s (
w_geocode text,
C000 text,
CFS04 text,
CFS05 text,
createdate text
)
"""
target_directory = Path(sys.argv[1]).resolve()
for file in target_directory.rglob('*.csv'):
table_name = 'opendata_uscensus_lodes_' + str(file.stem)
print(table_name)
# MAKE SURE THIS IS THE RIGHT TABLE FOR THE FILES
cur.execute(create_table_WAC, (table_name,))
with open(file,'r') as file_in:
# MAKE SURE THIS HAS THE RIGHT TABLE NAME IN THE COPY STATEMENT
cur.copy_expert("copy %s from stdin with csv header delimiter ','", table_name, file_in)
conn.commit()
conn.close()

当我运行它时，它会抛出与CREATE TABLE命令相关的错误。我不明白为什么添加了"，以及如何删除它们？

错误如下：

psycopg2.ProgrammingError: syntax error at or near "'opendata_uscensus_lodes_ca_wac_SA02_JT03_2003'"
LINE 2: create table if not exists 'opendata_uscensus_lodes_ca_wac_S...

Answer 1

使用SQL字符串组合：

from psycopg2 import sql
create_table_WAC = """
create table if not exists {} ( -- note changed placeholder
w_geocode text,
C000 text,
CFS04 text,
CFS05 text,
createdate text
)
"""
# ...
cur.execute(sql.SQL(create_table_WAC).format(sql.Identifier(table_name)))

阅读文档中的全面解释。