python-psycopg2:当列值包含单引号时，构建sql以插入多条记录时出错

Question

我正在尝试将一些记录插入PostgreSQL数据库。目前，我的代码生成了一个列表(recordLST(，我循环并生成一个元组，将其添加到一个不同的列表中，该列表被连接/转换为字符串，以便生成整个SQL字符串，看起来像：

'INSERT INTO table_name ("Column_1", "Column_2", ...) VALUES (...), (...) ON CONFLICT .....

也许有更好的方法来生成字符串？

我当前的代码如下：

#START THE CONNECTION
connection = psycopg2.connect(host = hostname, 
user = username, 
password = password, 
dbname = db_name, 
port = port,
connect_timeout=50)  
cursor = connection.cursor()
transaction_SQL = 'INSERT INTO app_banking ("id", "transactionBankDetail_id", "date", "text", "amount", "balance", "typeTransaction", "tags", "oldCategory", "modified_by_id", "exclude_loan" ) VALUES '
sub_value_LST = list()
for x in recordLST:
thistuple = (x["id"], x["transactionBankDetail_id"], x["date"], x["text"], x["amount"], x["balance"], x["typeTransaction"], x["tags"].replace("'", """), x["oldCategory"], x["modified_by_id"], x["exclude_loan"] )
sub_value_LST.append(str(thistuple))
sub_values = ','.join(sub_value_LST)
transaction_SQL = transaction_SQL + sub_values + ' ON CONFLICT ("id") DO UPDATE SET ("tags", "oldCategory", "modified_by_id", "exclude_loan") = (EXCLUDED."tags", EXCLUDED."oldCategory", EXCLUDED."modified_by_id", EXCLUDED."exclude_loan");' 
print('app_bankingn',transaction_SQL)
try:
transaction_SQL = transaction_SQL.replace("None", "null")
a = cursor.execute(transaction_SQL)
except Exception as error:
return str(error) 
......

上面的脚本生成下面的字符串，该字符串通常很好，除非列文本有一个引号：

INSERT INTO app_banking_transaction 
("id", "transactionBankDetail_id", "date", "text", "amount", "balance", "typeTransaction", "tags", "oldCategory", "modified_by_id", "exclude_loan" ) 
VALUES 
(2419, 2, '2018-11-05', "EFTPOS MANN'S BAKERY AU", '-16.3', -105.33, '', '....', None, None, False),
(2420, 2, '2018-11-05', 'VISA DEBIT PURCHASE CARD 5044 TT', '-28.15', -89.03, '', '...', None, None, False),

如果我删除"的记录；EFTPOS MANN’S BAKERY AU"；它工作得很好，或者如果我用***之类的东西替换单引号，但是，我想尽量让字符串接近

我必须以不同的方式构建查询吗？如果是，如何构建？或者我能对字符串做些什么来让它工作，就好像我用'转义字符串一样，仍然不会工作，得到错误：

列"；EFTPOS MANN’S BAKERY AU；不存在

Answer 1

使用cursor.execute(sql_string, tuple)插入/更新记录，例如，您可以尝试以下

transaction_SQL = (
'INSERT INTO app_banking '
'(id, transactionBankDetail_id, date, text, amount, balance, '
'typeTransaction, tags, oldCategory, modified_by_id, exclude_loan) '
'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) '
'ON CONFLICT (id) DO UPDATE SET '
'(tags, oldCategory, modified_by_id, exclude_loan) = (%s, %s, %s, %s)'
)
for x in recordLST:
thistuple = (
x["id"], x["transactionBankDetail_id"], x["date"], x["text"],
x["amount"], x["balance"], x["typeTransaction"], x["tags"], 
x["oldCategory"], x["modified_by_id"], x["exclude_loan"], 
x["tags"], x["oldCategory"], x["modified_by_id"], x["exclude_loan"]
)
cursor.execute(transaction_SQL, thistuple)
connection.commit()