使用字典将参数传递给python中的postgresql语句 [英] Using a dictionary to pass parameters to postgresql statement in python
问题描述
我定义了一个字典,其中包含几个参数及其值,这些参数及其值最终将用于构建SQL查询
I have defined a dictionary which contains several parameters and their values which will ultimately be used to build a SQL Query
query_params = collections.OrderedDict(
{'table_name':'publilc.churn_data',
'date_from':'201712',
'date_to':'201805',
'class_target':'NPA'
})
该参数将在以下查询中使用:
The parameters are to be used in the below query:
sql_data_sample = str("""select * from %s # get value of table_name
where dt = %s #get value of date_from
and target in ('ACTIVE')
----------------------------------------------------
union all
----------------------------------------------------
(select * from %s #get value of table_name
where dt = %s #get value of date_to
and target in (%s));""") #get value of class_target
%("'"+.join(str(list(query_params.values())[0])) + "'" +
"'"+.join(list(query_params.values())[1]) + "'" +
"'"+.join(list(query_params.values())[2]) + "'" +
"'"+.join(list(query_params.values())[3]) + "'" )
但这会给我一个缩进错误,如下所示:
However this gives me an indentation error as below:
get_ipython().run_line_magic('("\'"+.join(list(query_params.values())[0])', '+ "\'"')
^
IndentationError: unexpected indent
查询最终应该看起来像:
The query should ultimately look like:
select *from public.churn_data
where dt = '201712'
and target in ('ACTIVE')
----------------------------------------------------
union all
----------------------------------------------------
(select * from public.churn_data
where dt = '201805'
and target in ('NPA'));
由于公众的原因,我无法确定错误的出处在哪里.在table_name中? 有人可以帮我吗?
I am not being able to figure out where the source of the error is.Is it because of the public. in table_name? Can someone please help me with this??
推荐答案
请按照文档
既然已经有了字典,则可以执行以下操作:
Since you already have a dict, you could do:
sql_data_sample = """select * from %(table_name)s
where dt = %(date_from)s
and target in ('ACTIVE')
----------------------------------------------------
union all
----------------------------------------------------
(select * from %(table_name)s
where dt = %(date_to)s
and target in (%(class_target)s));"""
cur.execute(sql_data_sample, query_params)
我还没有测试过是否适用于赞美的字典,但我认为应该这样做.如果没有,您可以在将有序字典作为参数映射传递之前,将其作为常规字典.
I haven't tested if if works with an odered dict, but I think it should. If not, you could make your ordered dict a regular dict before passing it as parameters mapping.
编辑,除非以后需要将参数用作OrderedDict,否则请使用常规字典.据我所知,您只选择了OrderedDict来保留list(query_params.values())[0]
的值顺序.
EDIT Unless you need your parameters to be an OrderedDict later on, use a regular dict. As far as I can see, you only opted for an OrderedDict to preserve the value order for the list(query_params.values())[0]
.
EDIT2 不能使用绑定来传递表名和字段名. AntoineDusséaux在此答案中指出,从2.7版开始,psycopg2提供了一种或多或少的安全方式.
EDIT2 Table names and field names cannot be passed using bindings. Antoine Dusséaux pointed out in this answer that psycopg2 offers a more or less secure way to do that since version 2.7.
from psycopg2 import sql
sql_data_sample = """select * from {0}
where dt = %(date_from)s
and target in ('ACTIVE')
----------------------------------------------------
union all
----------------------------------------------------
(select * from {0}
where dt = %(date_to)s
and target in (%(class_target)s));"""
cur.execute(sql.SQL(sql_data_sample)
.format(sql.Identifier(query_params['table_name'])),
query_params)
您可能必须从字典中删除table_name
,我不确定psycopg2对参数dict中其他项目的反应,我现在无法对其进行测试.
You might have to remove the table_name
from your dict, I am not sure how psycopg2 reacts on additional items in the parameters dict and I cannot test it right now.
应该指出的是,这仍然构成SQL注入的风险,除非绝对必要,否则应避免.通常,表名和字段名是查询字符串中相当固定的部分.
It should be pointed out, that this still poses the risk of SQL injection and should be avoided unless absolutely necessary. Normally, table and field names are a rather fixed part of a query string.
以下是sql
模块的相关文档.
这篇关于使用字典将参数传递给python中的postgresql语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!