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.
Here's the relevant documentation for the sqlmodule.