如何在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 在 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.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

执行此操作的正确方法：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 中的类型的潜在冲突。

Answer 3

发生错误的原因是'符号，"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)