从Python创建动态SQL查询

本文介绍了从Python创建动态SQL查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我使用下面的一组参数来创建动态SQL查询，并从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)

但是，这会导致数据库错误：

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'

有人能帮我更正一下吗？

推荐答案

问题是因为churn在query_params中不可用。将代码修改为

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)