使用字典将参数传递给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屋！