返回基于Python中IF条件的Redshift数据库的结果

Question

我需要根据用Python编写的IF条件从redshift database中提取结果。

假设我有一个表，其中CustomerID, SHipment Number, Invoice Station, ect是Redshift table中的列，如果存在客户ID，我想从Redshift表中获取所有记录，该客户ID应该与用户输入一起检查。

• TABLE NAME=发货信息
• COLUMNS=客户ID、账单号码、开票站、费用等

Python

import psycopg2
con=psycopg2.connect(dbname= 'datamodel', host='123', 
port= '5439', user= 'bce', password= 'Ciz')
cur = con.cursor()
HWB = input("Enter the House Bill Number : ")
#if CustomerID = HWB:
cur.execute("SELECT source_system, file_nbr, file_date, CustomerID 
FROM public.shipment_info where CustomerID = $HWB")
results = cur.fetchall()
cur.close() 
con.close()
print(results)

Answer 1

考虑用户输入值的参数化(否则可能会出现臭名昭著的Bobby Tables(。

# PREPARED STATEMENT WITH PLACEHOLDER
sql = """SELECT source_system, file_nbr, file_date, CustomerID 
FROM public.shipment_info 
WHERE CustomerID = %s
"""
# BIND PARAM VALUES WITH TUPLE OF ONE-ITEM
cur.execute(sql, (HWB,))