Creating a dynamic SQL Query from Python

Question

I am using the below set of parameters to create a dynamic sql query and running it from python.

target_column = 'connect_status'
table_name = 'public.main_table'
query_params = {'month_date' : '201801',
 'churn_date' : ['201802','201803','201804'],
 'disco_target' : '1-3 Month Disco'
 }
for churn in query_params['churn_date']:
 sql_data_sample = str("""select * from {1}
 where dt = %(month_date)s 
 and churn_date = %(churn)s
 and {0} IN (%(disco_target)s,'No Disco') order by random() limit 5000
 ;""").format(target_column,table_name)
 print (sql_data_sample)
 df_data_sample = pd.read_sql(sql_data_sample,con = cnxn,params = query_params)

However this gives an DatabaseError:

Execution failed on sql 'select * from public.main_table
 where dt = %(month_date)s 
 and churn_date = %(churn)s
 and connect_status IN (%(disco_target)s,'No Disco') order by random() limit 5000
 ;': 'churn'

Can someone please help me to correct this?

Answer 1

The problem is because churn is not available in query_params. Modify your code to

for churn in query_params['churn_date']:
 try:
 sql_data_sample = str("""select * from {1}
 where dt = %(month_date)s 
 and churn_date = %(churn)s
 and {0} IN (%(disco_target)s,'No Disco') order by random() limit 5000
 ;""").format(target_column,table_name)
 print (sql_data_sample)
 query_params['churn'] = churn
 df_data_sample = pd.read_sql(sql_data_sample,con = cnxn,params = query_params)
 except (Exception, psycopg2.DatabaseError) as error:
 print(error)

Answer 2

Your string format expression references data in your query_params but query_params isn't referenced in the format function's parameters. Have a look at your month_date and disco_target usage.

Answer 3

#Use this python to write the dynamic update query for postgreSql

def updateQuery(self,tableName,setFields,setValues,whereFields,whereValues):
 querySetfields = None
 queryWhereFields = None
 # Loop for set fields
 for i in range(len(setFields)):
 if querySetfields is None:
 querySetfields=setFields[i]+"='"+setValues[i]+"'"
 else:
 querySetfields=querySetfields+","+setFields[i]+"='"+setValues[i]+"'"
 
 # Loop for whereFields
 for i in range(len(whereFields)):
 if queryWhereFields is None:
 queryWhereFields=whereFields[i]+"='"+whereValues[i]+"'"
 else:
 queryWhereFields=queryWhereFields+","+whereFields[i]+"='"+whereValues[i]+"'"
 
 #Form the complete update query
 query="UPDATE "+tableName+" SET "+querySetfields+" WHERE "+queryWhereFields
 
 return query