如何在python中使用sql查询生成文本文件？ Download

查询：

"SELECT trim(CONCAT_WS('|',mtd.Master_Job_Name,Seq,Table_Name,Source_File_Name_Lnd,landingdfname,Target_Path,query,mtd.Execution_Sequence  )) from imedical_master_metadata mtd inner join imedical_master_metadata_query mtq on (mtd.Master_Job_Name=mtq.Master_Job_Name) where mtd.Is_Active='Y' order by mtd.Execution_Sequence"

代码：

with db.cursor() as cur:
     query_meta1 = ("SELECT trim(CONCAT_WS('|',mtd.Master_Job_Name,Seq,Table_Name,Source_File_Name_Lnd,landingdfname,Target_Path,query,mtd.Execution_Sequence  )) from imedical_master_metadata mtd inner join imedical_master_metadata_query mtq on (mtd.Master_Job_Name=mtq.Master_Job_Name) where mtd.Is_Active='Y' order by mtd.Execution_Sequence")
     res1 = cur.execute(query_meta1)
     res1 = cur.fetchall()

获取输出：

[{u"trim(CONCAT_WS('|',mtd.Master_Job_Name,Seq,Table_Name,Source_File_Name_Lnd,landingdfname,Target_Path,query,mtd.Execution_Sequence  ))"": ""CATEGORY|1|ADDITIONAL_QUESTIONS|/medaff/eureka/RawLayer/AMPIL/ADDITIONAL_QUESTIONS/|vw_additional_questions|/medaff/eureka/Refined/CATEGORY/|SELECT (row_number() over(ORDER BY (SELECT NULL))) as category_id, cat.CTGRY, mdm.group_id as mdm_category_id, mdm.DESCRPTN, from_utc_timestamp(current_timestamp(), 'EST5EDT') as im_insrt_dt FROM (SELECT distinct NVL(CTGRY,'Unspecified') CTGRY FROM vw_additional_questions UNION SELECT DISTINCT NVL(CTGRY,'Unspecified') CTGRY FROM vw_case UNION SELECT DISTINCT NVL(CAST(QUICKCDE_CATGRY AS VARCHAR(4000)),'Unspecified') CTGRY FROM vw_case UNION SELECT DISTINCT NVL(CATG_MIR,'Unspecified') CTGRY FROM vw_case UNION SELECT DISTINCT  NVL(CATG,'Unspecified') CTGRY FROM vw_mir_slctd_respn UNION SELECT DISTINCT CTGRY FROM vw_call_dscssn UNION SELECT DISTINCT  vw_prod_strtgy.NM FROM vw_mdcl_dsscn INNER JOIN vw_prod_strtgy ON vw_mdcl_dsscn.PROD_STRTGY_VOD = vw_prod_strtgy.ID UNION SELECT DISTINCT GROUP_NAME AS CTGRY FROM vw_mdm_group where group_type = 'CATEGORY' ) cat LEFT JOIN (SELECT GROUP_ID, GROUP_NAME AS CATEGORY, GROUP_DESCRIPTION AS DESCRPTN FROM vw_mdm_group where group_type = 'CATEGORY') mdm ON cat.CTGRY = mdm.category|5""}","{u""trim(CONCAT_WS('|',mtd.Master_Job_Name,Seq,Table_Name,Source_File_Name_Lnd,landingdfname,Target_Path,query,mtd.Execution_Sequence  ))"": ""CATEGORY|2|CASE|/medaff/eureka/RawLayer/AMPIL/CASE/|vw_case|/medaff/eureka/Refined/CATEGORY/|SELECT (row_number() over(ORDER BY (SELECT NULL))) as category_id, cat.CTGRY, mdm.group_id as mdm_category_id, mdm.DESCRPTN, from_utc_timestamp(current_timestamp(), 'EST5EDT') as im_insrt_dt FROM (SELECT distinct NVL(CTGRY,'Unspecified') CTGRY FROM vw_additional_questions UNION SELECT DISTINCT NVL(CTGRY,'Unspecified') CTGRY FROM vw_case UNION SELECT DISTINCT NVL(CAST(QUICKCDE_CATGRY AS VARCHAR(4000)),'Unspecified') CTGRY FROM vw_case UNION SELECT DISTINCT NVL(CATG_MIR,'Unspecified') CTGRY FROM vw_case UNION SELECT DISTINCT  NVL(CATG,'Unspecified') CTGRY FROM vw_mir_slctd_respn UNION SELECT DISTINCT CTGRY FROM vw_call_dscssn UNION SELECT DISTINCT  vw_prod_strtgy.NM FROM vw_mdcl_dsscn INNER JOIN vw_prod_strtgy ON vw_mdcl_dsscn.PROD_STRTGY_VOD = vw_prod_strtgy.ID UNION SELECT DISTINCT GROUP_NAME AS CTGRY FROM vw_mdm_group where group_type = 'CATEGORY' ) cat LEFT JOIN (SELECT GROUP_ID, GROUP_NAME AS CATEGORY, GROUP_DESCRIPTION AS DESCRPTN FROM vw_mdm_group where group_type = 'CATEGORY') mdm ON cat.CTGRY = mdm.category|5'}]

所需的输出：

CATEGORY|1|ADDITIONAL_QUESTIONS|/medaff/eureka/RawLayer/AMPIL/ADDITIONAL_QUESTIONS/|vw_additional_questions|/medaff/eureka/Refined/CATEGORY/|SELECT (row_number() over(ORDER BY (SELECT NULL))) as category_id, cat.CTGRY, mdm.group_id as mdm_category_id, mdm.DESCRPTN, from_utc_timestamp(current_timestamp(), 'EST5EDT') as im_insrt_dt FROM (SELECT distinct NVL(CTGRY,'Unspecified') CTGRY FROM vw_additional_questions UNION SELECT DISTINCT NVL(CTGRY,'Unspecified') CTGRY FROM vw_case UNION SELECT DISTINCT NVL(CAST(QUICKCDE_CATGRY AS VARCHAR(4000)),'Unspecified') CTGRY FROM vw_case UNION SELECT DISTINCT NVL(CATG_MIR,'Unspecified') CTGRY FROM vw_case UNION SELECT DISTINCT  NVL(CATG,'Unspecified') CTGRY FROM vw_mir_slctd_respn UNION SELECT DISTINCT CTGRY FROM vw_call_dscssn UNION SELECT DISTINCT  vw_prod_strtgy.NM FROM vw_mdcl_dsscn INNER JOIN vw_prod_strtgy ON vw_mdcl_dsscn.PROD_STRTGY_VOD = vw_prod_strtgy.ID UNION SELECT DISTINCT GROUP_NAME AS CTGRY FROM vw_mdm_group where group_type = 'CATEGORY' ) cat LEFT JOIN (SELECT GROUP_ID, GROUP_NAME AS CATEGORY, GROUP_DESCRIPTION AS DESCRPTN FROM vw_mdm_group where group_type = 'CATEGORY') mdm ON cat.CTGRY = mdm.category|5

CATEGORY|2|CASE|/medaff/eureka/RawLayer/AMPIL/CASE/|vw_case|/medaff/eureka/Refined/CATEGORY/|SELECT (row_number() over(ORDER BY (SELECT NULL))) as category_id, cat.CTGRY, mdm.group_id as mdm_category_id, mdm.DESCRPTN, from_utc_timestamp(current_timestamp(), 'EST5EDT') as im_insrt_dt FROM (SELECT distinct NVL(CTGRY,'Unspecified') CTGRY FROM vw_additional_questions UNION SELECT DISTINCT NVL(CTGRY,'Unspecified') CTGRY FROM vw_case UNION SELECT DISTINCT NVL(CAST(QUICKCDE_CATGRY AS VARCHAR(4000)),'Unspecified') CTGRY FROM vw_case UNION SELECT DISTINCT NVL(CATG_MIR,'Unspecified') CTGRY FROM vw_case UNION SELECT DISTINCT  NVL(CATG,'Unspecified') CTGRY FROM vw_mir_slctd_respn UNION SELECT DISTINCT CTGRY FROM vw_call_dscssn UNION SELECT DISTINCT  vw_prod_strtgy.NM FROM vw_mdcl_dsscn INNER JOIN vw_prod_strtgy ON vw_mdcl_dsscn.PROD_STRTGY_VOD = vw_prod_strtgy.ID UNION SELECT DISTINCT GROUP_NAME AS CTGRY FROM vw_mdm_group where group_type = 'CATEGORY' ) cat LEFT JOIN (SELECT GROUP_ID, GROUP_NAME AS CATEGORY, GROUP_DESCRIPTION AS DESCRPTN FROM vw_mdm_group where group_type = 'CATEGORY') mdm ON cat.CTGRY = mdm.category|5

您能帮忙将其保存到txt文件中吗？

2 个答案:

答案 0 :(得分：0)

我认为输出错误是由于您在光标上查询了我们而导致的。 将 query_meta 构建为字符串形式（即不带括号），例如：

  

query_meta =“选择...”

这将解决从fetchall获得的结果。

如果要保存输出，可以将其转换为pandas数据框，然后使用pandas写功能。 下面是一个示例：

import pandas as pd

with db.cursor() as cur:
 query_meta1 = "SELECT ..."
 cur.execute(query_meta1)
 result = cur.fetchall()
 df = pd.Dataframe(result)
 df.to_csv("test.csv")

答案 1 :(得分：0)

这是您想要的答案。运行此代码，您将获得所需的输出：

d = [{u"trim(CONCAT_WS('|',mtd.Master_Job_Name,Seq,Table_Name,Source_File_Name_Lnd,landingdfname,Target_Path,query,mtd.Execution_Sequence  ))": "CATEGORY|1|ADDITIONAL_QUESTIONS|/medaff/eureka/RawLayer/AMPIL/ADDITIONAL_QUESTIONS/|vw_additional_questions|/medaff/eureka/Refined/CATEGORY/|SELECT (row_number() over(ORDER BY (SELECT NULL))) as category_id, cat.CTGRY, mdm.group_id as mdm_category_id, mdm.DESCRPTN, from_utc_timestamp(current_timestamp(), 'EST5EDT') as im_insrt_dt FROM (SELECT distinct NVL(CTGRY,'Unspecified') CTGRY FROM vw_additional_questions UNION SELECT DISTINCT NVL(CTGRY,'Unspecified') CTGRY FROM vw_case UNION SELECT DISTINCT NVL(CAST(QUICKCDE_CATGRY AS VARCHAR(4000)),'Unspecified') CTGRY FROM vw_case UNION SELECT DISTINCT NVL(CATG_MIR,'Unspecified') CTGRY FROM vw_case UNION SELECT DISTINCT  NVL(CATG,'Unspecified') CTGRY FROM vw_mir_slctd_respn UNION SELECT DISTINCT CTGRY FROM vw_call_dscssn UNION SELECT DISTINCT  vw_prod_strtgy.NM FROM vw_mdcl_dsscn INNER JOIN vw_prod_strtgy ON vw_mdcl_dsscn.PROD_STRTGY_VOD = vw_prod_strtgy.ID UNION SELECT DISTINCT GROUP_NAME AS CTGRY FROM vw_mdm_group where group_type = 'CATEGORY' ) cat LEFT JOIN (SELECT GROUP_ID, GROUP_NAME AS CATEGORY, GROUP_DESCRIPTION AS DESCRPTN FROM vw_mdm_group where group_type = 'CATEGORY') mdm ON cat.CTGRY = mdm.category|5"},{u"trim(CONCAT_WS('|',mtd.Master_Job_Name,Seq,Table_Name,Source_File_Name_Lnd,landingdfname,Target_Path,query,mtd.Execution_Sequence  ))": "CATEGORY|2|CASE|/medaff/eureka/RawLayer/AMPIL/CASE/|vw_case|/medaff/eureka/Refined/CATEGORY/|SELECT (row_number() over(ORDER BY (SELECT NULL))) as category_id, cat.CTGRY, mdm.group_id as mdm_category_id, mdm.DESCRPTN, from_utc_timestamp(current_timestamp(), 'EST5EDT') as im_insrt_dt FROM (SELECT distinct NVL(CTGRY,'Unspecified') CTGRY FROM vw_additional_questions UNION SELECT DISTINCT NVL(CTGRY,'Unspecified') CTGRY FROM vw_case UNION SELECT DISTINCT NVL(CAST(QUICKCDE_CATGRY AS VARCHAR(4000)),'Unspecified') CTGRY FROM vw_case UNION SELECT DISTINCT NVL(CATG_MIR,'Unspecified') CTGRY FROM vw_case UNION SELECT DISTINCT  NVL(CATG,'Unspecified') CTGRY FROM vw_mir_slctd_respn UNION SELECT DISTINCT CTGRY FROM vw_call_dscssn UNION SELECT DISTINCT  vw_prod_strtgy.NM FROM vw_mdcl_dsscn INNER JOIN vw_prod_strtgy ON vw_mdcl_dsscn.PROD_STRTGY_VOD = vw_prod_strtgy.ID UNION SELECT DISTINCT GROUP_NAME AS CTGRY FROM vw_mdm_group where group_type = 'CATEGORY' ) cat LEFT JOIN (SELECT GROUP_ID, GROUP_NAME AS CATEGORY, GROUP_DESCRIPTION AS DESCRPTN FROM vw_mdm_group where group_type = 'CATEGORY') mdm ON cat.CTGRY = mdm.category|5"}]
with open("output.txt", 'w') as file:
    for i in d:
        for j in i.values():
            print(j)
            file.writelines(j + "\n")