首頁(yè) 猿問(wèn) 如何在python中為Sql語(yǔ)句提...

如何在python中為Sql語(yǔ)句提供動(dòng)態(tài)輸入

Python

蠱毒傳說(shuō) 2023-10-26 10:28:35

我正在嘗試在 python 中執(zhí)行動(dòng)態(tài) sql 查詢?？雌饋?lái)像下面這樣。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 -> 此函數(shù)使用 pscopg2 在 redshift 中運(yùn)行我通過(guò)從用戶輸入動(dòng)態(tài)拉取“時(shí)間”來(lái)執(zhí)行此腳本。我收到錯(cuò)誤[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 中的相同查詢?nèi)缦戮帉?xiě)并正確執(zhí)行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貢獻(xiàn)1827條經(jīng)驗(yàn) 獲得超8個(gè)贊

可能不合時(shí)宜，但“Y”會(huì)拉斷你的琴弦嗎？

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+'\''

轉(zhuǎn)義撇號(hào)

反對(duì) 回復(fù) 2023-10-26

長(zhǎng)風(fēng)秋雁

TA貢獻(xiàn)1757條經(jīng)驗(yàn) 獲得超7個(gè)贊

執(zhí)行此操作的正確方法：

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)

以上確保了傳入的數(shù)據(jù)被正確引用并防止 SQL 注入問(wèn)題。我也會(huì)避免使用像time. time這是與模塊以及timePostgres 中的類型的潛在沖突。

反對(duì) 回復(fù) 2023-10-26

不負(fù)相思意

TA貢獻(xiàn)1777條經(jīng)驗(yàn) 獲得超10個(gè)贊

發(fā)生錯(cuò)誤的原因是'符號(hào)，

"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)

反對(duì) 回復(fù) 2023-10-26