如何在python中为Sql语句提供动态输入

Question

我正在尝试用python执行动态sql查询。它看起来像下面这样。

import json
from decimal import Decimal
import psycopg2

def 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在红移中运行我通过动态地从用户输入中拉取“时间”来执行这个脚本。我收到一个错误

[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.test
WHERE (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;

Answer 1

可能没有说中，但是“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+'\''

转义撇号

Answer 2

出现此错误是因为'符号，

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

Answer 3

这样做的正确方法：

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这样的变量名。这与Postgres中的time模块以及time类型存在潜在的冲突。