我正在嘗試在 python 中執行動態 sql 查詢?？雌饋硐裣旅孢@樣。import jsonfrom decimal import Decimalimport psycopg2def initial_execution(param_name): time = get_timestamp(param_name) query = 'SELECT name, account_mgr, addr1, addr2, ap_email, bill_to_nbr, billto_only, city, controlling_nbr, controlling_only, country, cust_contact, email, load_create_date, load_update_date, nbr, owner, sales_rep, source_system, state, station, zip FROM public.customers WHERE billto_only = 'Y' OR controlling_only = 'Y' AND load_create_date >= \''+time+'\' OR load_update_date >= \''+time+'\'' queryData = execute_db_query(query)execute_db_query -> 此函數使用 pscopg2 在 redshift 中運行 我通過從用戶輸入動態拉取“時間”來執行此腳本。我收到錯誤[ERROR] Runtime.UserCodeSyntaxError: Syntax error in module'src/customers': invalid syntax (customers.py, line 78) Traceback(most recent call last): File "/var/task/src/customers.py" Line 78 query = 'SELECT name,billto_only, controlling_only , load_create_date, load_update_date, nbr, owner, sales_rep, source_system, state, station, zip FROM public.test WHERE billto_only = 'Y' OR controlling_only = 'Y' AND load_create_date >= \''+time+'\' OR load_update_date >= \''+time+'\''sql 中的相同查詢如下編寫并正確執行SELECT name,billto_only, controlling_only,load_create_date, load_update_date, nbr, owner, sales_rep, source_system, state, station, zip FROM public.testWHERE (billto_only = 'Y' OR controlling_only = 'Y') AND (load_create_date >= '2020-10-09 07:04:51' OR load_update_date >= '2020-10-09 07:04:51') limit 5;
3 回答
慕仙森
TA貢獻1827條經驗 獲得超8個贊
可能不合時宜，但“Y”會拉斷你的琴弦嗎？
query = 'SELECT name,billto_only, controlling_only , load_create_date, load_update_date, nbr, owner, sales_rep, source_system, state, station, zip FROM public.test WHERE billto_only = \'Y\' OR controlling_only = \'Y\' AND load_create_date >= \''+time+'\' OR load_update_date >= \''+time+'\''
轉義撇號
長風秋雁
TA貢獻1757條經驗 獲得超7個贊
執行此操作的正確方法：
query = """SELECT
name, account_mgr, addr1, addr2, ap_email, bill_to_nbr, billto_only, city, controlling_nbr,
controlling_only, country, cust_contact, email, load_create_date, load_update_date, nbr,
owner, sales_rep, source_system, state, station, zip
FROM
public.customers
WHERE
billto_only = 'Y' OR controlling_only = 'Y'
AND
load_create_date >= %(time_val)s OR load_update_date >= %(time_val)s
"""
queryData = execute_db_query(query, {"time_val": time)
以上確保了傳入的數據被正確引用并防止 SQL 注入問題。我也會避免使用像time. time這是與模塊以及timePostgres 中的類型的潛在沖突。
不負相思意
TA貢獻1777條經驗 獲得超10個贊
發生錯誤的原因是'符號，
"SELECT name, account_mgr, addr1, addr2, ap_email, bill_to_nbr, billto_only, city, controlling_nbr, controlling_only, country, cust_contact, email, load_create_date, load_update_date, nbr, owner, sales_rep, source_system, state, station, zip FROM public.customers WHERE billto_only = 'Y' OR controlling_only = 'Y' AND load_create_date >= '{date}' OR load_update_date >= '{date}'".format(date=time)添加回答
舉報
0/150
提交
取消