python sql语句传参数_使用字典将参数传递给python中的postgresql语句

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'));

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??

解决方案

Please use a parameterized query as described in the docs

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.

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

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.

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.