python 正则批量替换

python 正则批量替换

使用dataX从上游不同的数据源抽数时，存在reader对querysql转换的问题，在writer中又需要去掉这个类型转换，只保留字段信息，我这边的话使用的是正则的替换。

import re

# 对sql中的case when 语句只需要字段名即可，这里需要做一个替换

sql = """
    SELECT ID, BONDISSUEID, BCODE, BISSUE_BDC1, BISSUE_BDC2
	, BISSUE_BDC3, BISSUE_BDC4, BISSUE_BDC5, BISSUE_BDC6, BISSUE_BDC7
	, BISSUE_BDC8
	, CASE BISSUE_BDC9
		WHEN 1 THEN 1
		WHEN 0 THEN 0
	END AS BISSUE_BDC9, BISSUE_BDC10, BISSUE_BDC11, BISSUE_BDC12, BISSUE_BDC13
	, BISSUE_BDC14, BISSUE_BDC15, BISSUE_BDC16, BISSUE_BDC17, BISSUE_BDC18
	, BISSUE_BDC19, BISSUE_BDC20, BISSUE_BDC21, BISSUE_BDC22, BISSUE_BDC23
	, BISSUE_BDC24
	, CASE BISSUE_BDC25
		WHEN 1 THEN 1
		WHEN 0 THEN 0
	END AS BISSUE_BDC25, BISSUE_BDC26, BISSUE_BDC27, BISSUE_BDC28, BISSUE_BDC29
	, BISSUE_BDC30, BISSUE_BDC31, BISSUE_BDC32, BISSUE_BDC33
	, CASE BISSUE_BDC34
		WHEN 1 THEN 1
		WHEN 0 THEN 0
	END AS BISSUE_BDC34, BISSUE_BDC35, BISSUE_BDC36, BISSUE_BDC37, BISSUE_BDC38
	, BISSUE_BDC39, BISSUE_BDC40, BISSUE_BDC41, BISSUE_BDC42
	, CASE BISSUE_BDC43
		WHEN 1 THEN 1
		WHEN 0 THEN 0
	END AS BISSUE_BDC43, BISSUE_BDC44, BISSUE_BDC45, BISSUE_BDC46, BISSUE_BDC47
	, BISSUE_BDC48, BISSUE_BDC49, BISSUE_BDC50, BISSUE_BDC51, BISSUE_BDC52
	, BISSUE_BDC53, EntryDate, EntryTime
	, convert(bigint, TMSTAMP, 112) AS TMSTAMP
	, BISSUE_BDC54, BISSUE_BDC55, SYNCHRONIZE, BISSUE_BDC56, BISSUE_BDC57
FROM BISSUE_BDC
"""
a = re.sub('case (.*?) when 1 then 1 when 0 then 0 end as ', lambda x: x[0][-1], sql)
print(a)
aa = re.sub(r'case (.*?) when 1 then 1 when 0 then 0 end as.*?\,', lambda x:x.group(1)+",", sql)
print(aa)