从数据框创建逗号分隔列表以传递到 SQL 查询时出错

Question

我正在尝试创建一个逗号分隔的列表来传递 SQL 查询。我的代码sql1 = '''select carrier_name, carrier_account, invoice_number, invoice_amount, currency, invoice_datefrom invoice_summary where invoice_number in {}'''.format(tuple(data1['invoice_number'].values.tolist()))电流输出    "select carrier_name, carrier_account, invoice_number, invoice_amount, currency, invoice_date\nfrom invoice_summary where invoice_number in ('BHX3327983',)"预期产出 “select carrier_name, carrier_account, invoice_number,     invoice_amount, currency, invoice_date\nfrom invoice_summary where invoice_number in ('BHX3327983')"我正在寻找一种在有单个输入或要传递多个输入时有效的解决方案。我的代码有什么问题。

Answer 1

尝试使用join并将括号放在字符串中：

sql1 = '''select carrier_name, carrier_account, invoice_number, invoice_amount, currency, invoice_date

from invoice_summary where invoice_number in ({})'''.format(','.join(["'{}'".format(x) for x in data1['invoice_number']]))

更新

您可以使用该DataFrame.empty属性有条件地设置 sql 语句的值。如果data1为空，则将您的WHERE子句设置为 False，例如1 = 0：

if data1.empty:

    sql1 = '''select carrier_name, carrier_account, invoice_number, invoice_amount, currency, invoice_date

              from invoice_summary where 1 = 0'''

else:

    sql1 = ('''select carrier_name, carrier_account, invoice_number, invoice_amount, currency, invoice_date

              from invoice_summary where invoice_number in ({})'''

            .format(','.join(["'{}'".format(x) for x in data1['invoice_number']])))

Answer 2

为了避免 SQL 注入，你可以使用这个：

invoice_nums_list = data1['invoice_number'].values.tolist()

sql1 = '''select carrier_name, carrier_account, invoice_number, invoice_amount, currency, invoice_date from invoice_summary where invoice_number in ''' + 

       '(' + ','.join('%s' for i in range(len(invoice_nums_list))) + ')'

print(sql1)

cursor.execute(sql1, params=tuple(invoice_nums_list))