我正在嘗試創(chuàng)建一個(gè)逗號(hào)分隔的列表來(lái)傳遞 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',)"預(yù)期產(chǎn)出 “select carrier_name, carrier_account, invoice_number, invoice_amount, currency, invoice_date\nfrom invoice_summary where invoice_number in ('BHX3327983')"我正在尋找一種在有單個(gè)輸入或要傳遞多個(gè)輸入時(shí)有效的解決方案。我的代碼有什么問(wèn)題。
2 回答
慕桂英546537
TA貢獻(xiàn)1848條經(jīng)驗(yàn) 獲得超10個(gè)贊
嘗試使用join并將括號(hào)放在字符串中：
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屬性有條件地設(shè)置 sql 語(yǔ)句的值。如果data1為空，則將您的WHERE子句設(shè)置為 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']])))
LEATH
TA貢獻(xiàn)1936條經(jīng)驗(yàn) 獲得超7個(gè)贊
為了避免 SQL 注入，你可以使用這個(gè)：
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))
添加回答
舉報(bào)
0/150
提交
取消