正则替换sql为动态sql

版本一：

#coding:utf-8
import re
#把文档中的单引号替换为2个单引号
def replacestr(matched):
    matchedstr = matched.group()
    x = matchedstr.replace("'", "''")
    return x
def main():
    f_sql = open('fzt_dd.sql', 'r')
    sql = f_sql.read()
    f_sql.close()
    for a, b, c in patterns:
        sql = re.sub(a, b, sql, flags=c)  #flags是匹配模式
    f_sql = open('2.sql','w')
    f_sql.write(sql)
    f_sql.close()
patterns = \
    (
        (r"\bdelete\b", r"execute immediate 'delete", re.I),    #替换delete等
        (r"\bupdate\b", r"execute immediate 'update", re.I),
        (r"\binsert\b", r"execute immediate 'insert", re.I),
        (r"\balter\b", r"execute immediate 'alter", re.I),
        (r"\bcreate\b", r"execute immediate 'create", re.I),
        (r"\bdrop\s+TABLE\s+(\w+)\s*;", r"drop_table('\1');", re.I),    #替换drop table为drop_table函数
        (r"execute immediate(.*?);", r"execute immediate\1';", re.S),   #在execute imediate尾加单引号
        (r"(?<=execute immediate ').*?(?=';)", replacestr, re.S)        #把内容中的单引号替换为2个单引号
    )
if __name__ == '__main__':
    main()

版本二：

#coding:utf-8
import re
f_sql = open('1.sql', 'r')
sql = f_sql.read()
f_sql.close()
drop_str = re.compile(r'\bdrop\s+TABLE\s+(\w+)\s*;', re.I)
start_str = re.compile(r'\bcreate\b', re.I)
end_str = re.compile(r'execute immediate(.*?);', re.S)
content_str = re.compile(r"(?<=execute immediate ').*?(?=';)", re.S)
#替换以下开头的语句为execute immediate开始
start_list = ['delete', 'update', 'insert', 'alter', 'create']
start_pattern = []
for i in start_list:
        pa_str = r'\b%s\b' % i
        result_str = r"execute immediate '%s " %i
        pa = re.compile(pa_str, re.I)
        sql = pa.sub(result_str, sql)
#用drop_table函数替换drop语句
sql = drop_str.sub(r"drop_table('\1');", sql)
#在execute immediate语句结尾加单引号
sql = end_str.sub(r"execute immediate\1';", sql)
#把文档中的单引号替换为2个单引号
def _replacestr(matched):
    matchedstr = matched.group()
    x = matchedstr.replace("'", "''")
    return x
#用函数替换内容中的单引号为2个单引号
sql = content_str.sub(_replacestr, sql)
f_sql = open('2.sql','w')
f_sql.write(sql)
f_sql.close()

版本三:

import re
sql = '''DROP TABLE FZT_OL_NBR;
execute immediate 'CREATE TABLE FZT_OL_NBR  as
SELECT OL_NBR FROM SP.CSIP_U2S_OL_RELA@OSS A WHERE A.CO_ID IN (SEL'ECT B'.CO_ID FROM FZT_CUST_ORDER B );
DROP TABLE FZT_OL_NBR;
execute immediate 'CREATE TABLE FZT_OL_NBR  as
SELECT OL_NBR FROM SP.CSIP_U2S_OL_RELA@OSS A WHERE A.CO_ID IN (SEL'ECT B'.CO_ID FROM FZT_CUST_ORDER B );'''
a = re.compile(r'drop\s+TABLE\s+(\w+)\s*;', re.I)
b = re.compile(r'create\s+',re.I)
b1 = re.compile(r'execute immediate(.*?)(;)', re.S)
p = re.compile(r"(?<=execute immediate ').*?(?=;)", re.S)
def _replacestr(matched):
    matchedstr = matched.group()
    x = matchedstr.replace("'", "''")
    return x
c = a.sub(r"drop_table('\1');", sql)
d = b.sub(r"execute immediate ' create ", sql)
e = b1.sub(r"execute immediate\1';", sql)
f = p.sub(_replacestr, sql)
print f

来自为知笔记(Wiz)