我正在嘗試創建一個逗號分隔的列表來傳遞 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')"我正在尋找一種在有單個輸入或要傳遞多個輸入時有效的解決方案。我的代碼有什么問題。
2 回答
慕桂英546537
TA貢獻1848條經驗 獲得超10個贊
嘗試使用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']])))
LEATH
TA貢獻1936條經驗 獲得超7個贊
為了避免 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))
添加回答
舉報
0/150
提交
取消