源码的名字是:psqlparse,是一个基于 libpg_query 来解析 sql 的,其中使用了 cython,
地址是https://github.com/alculquicondor/psqlparse,给个解决思路就行,这个程序不复杂,大约能看懂,已经看了一天,问题是:复杂的对象结构,其中包含json,list 的,还是嵌套的,如何遍历它,优雅的。
我已经试过了__dict__了
比如import psqlparse
query=r"select (b.script_name),'中' from (select * from temp.halfisolateworkjob20171221) a left join (select * from temp.script2table) b on a.schema_name||'.'||a.table_name=b.table_name where a.create_time
query2=r"insert into dis.td_bd_area_info_d SELECT A.DEAL_DATE,A.INT_ID,A.ZH_LABEL,A.COUNTY_ID, B.ZH_LABEL OUNTY_NAME,B.CITY_ID,case when B.city_id = '40' then '邢台市' when B.city_id = '33' then '秦皇岛市' when B.city_id = '41' then '邯郸市' when B.city_id = '34' then '沧州市' when B.city_id = '36' then '廊坊市' when B.city_id = '32' then '石家庄市' when B.city_id = '37' then '张家口市' when B.city_id = '38' then '保定市' when B.city_id = '42' then '唐山市' when B.city_id = '43' then '衡水市' when B.city_id = '39' then '承德市'ELSE '其他' END ,CASE WHEN A.CELL_SOURCE in ('铁通割接','无线宽带','新国标','自建','自建无线宽带') and A.COVER_TYPE IN ('0','1','2','3') THEN '自建有线' WHEN A.CELL_SOURCE in ('铁通割接','无线宽带','新国标','自建','自建无线宽带') and A.COVER_TYPE IN ('4') THEN '自建无线(WLAN)' WHEN A.CELL_SOURCE in ('铁通割接','无线宽带','新国标','自建','自建无线宽带') and A.COVER_TYPE IN ('6') THEN '自建无线( 4G )' WHEN A.CELL_SOURCE in ('铁通割接','无线宽带','新国标','自建','自建无线宽带') and A.COVER_TYPE IN ('6') THEN '自建无线( 4G )' WHEN A.CELL_SOURCE in ('第三方割接','第三方无线宽带') and A.COVER_TYPE IN ('0','1','2','3') THEN '三方有线' WHEN A.CELL_SOURCE in ('第三方割接','第三方无线宽带') and A.COVER_TYPE IN ('4') THEN '三方无线( WLAN )' else '其他' end,case when A.AREA_TYPE = '市区(含县城)' then '市区' when A.AREA_TYPE = '乡镇(含城乡结合部)' then '乡镇' when A.AREA_TYPE = '农村' then '农村' else '其他' end,A.CELL_SOURCE,A.COVER_TYPE,A.HOUSE_NUM,ROW_NUMBER() OVER (PARTITION BY A.INT_ID ORDER BY A.MODIFY_TIME DESC , B.MODIFY_TIME DESC ) RN FROM DW.TD_RMS_ADD_CELL_D A LEFT JOIN DW.TD_RMS_COUNTY_D B ON A.COUNTY_ID = B.INT_ID AND B.DEAL_DATE = 20170101 where A.DEAL_DATE = 20170101;"
statements = psqlparse.parse(query1)
used_tables = statements[0]
dir(used_tables.from_clause.items[0])
\每一个 sql 的内容不同,返回的结构也是不同的,下面这个在简单的 sql 中可以,所以我必须得到 statements 的全部内部,或者是遍历它,否则不知道结构,无法处理