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

Question

我正在尝试创建一个逗号分隔的列表来传递 SQL 查询。

我的代码

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

from 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_datefrom 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))