正则替换sql为动态sql

版本一：

1. #coding:utf-8
3. import re
5. #把文档中的单引号替换为2个单引号
6. def replacestr(matched):
7. matchedstr = matched.group()
8. x = matchedstr.replace("'", "''")
9. return x
11. def main():
12. f_sql = open('fzt_dd.sql', 'r')
13. sql = f_sql.read()
14. f_sql.close()
16. for a, b, c in patterns:
17. sql = re.sub(a, b, sql, flags=c) #flags是匹配模式
19. f_sql = open('2.sql','w')
20. f_sql.write(sql)
21. f_sql.close()
23. patterns = \
24. (
25. (r"\bdelete\b", r"execute immediate 'delete", re.I), #替换delete等
26. (r"\bupdate\b", r"execute immediate 'update", re.I),
27. (r"\binsert\b", r"execute immediate 'insert", re.I),
28. (r"\balter\b", r"execute immediate 'alter", re.I),
29. (r"\bcreate\b", r"execute immediate 'create", re.I),
30. (r"\bdrop\s+TABLE\s+(\w+)\s*;", r"drop_table('\1');", re.I), #替换drop table为drop_table函数
31. (r"execute immediate(.*?);", r"execute immediate\1';", re.S), #在execute imediate尾加单引号
32. (r"(?<=execute immediate ').*?(?=';)", replacestr, re.S) #把内容中的单引号替换为2个单引号
33. )
36. if __name__ == '__main__':
37. main()

版本二：

2. #coding:utf-8
4. import re
5. f_sql = open('1.sql', 'r')
6. sql = f_sql.read()
7. f_sql.close()
9. drop_str = re.compile(r'\bdrop\s+TABLE\s+(\w+)\s*;', re.I)
10. start_str = re.compile(r'\bcreate\b', re.I)
11. end_str = re.compile(r'execute immediate(.*?);', re.S)
12. content_str = re.compile(r"(?<=execute immediate ').*?(?=';)", re.S)
15. #替换以下开头的语句为execute immediate开始
16. start_list = ['delete', 'update', 'insert', 'alter', 'create']
17. start_pattern = []
19. for i in start_list:
20. pa_str = r'\b%s\b' % i
21. result_str = r"execute immediate '%s " %i
22. pa = re.compile(pa_str, re.I)
23. sql = pa.sub(result_str, sql)
25. #用drop_table函数替换drop语句
26. sql = drop_str.sub(r"drop_table('\1');", sql)
28. #在execute immediate语句结尾加单引号
29. sql = end_str.sub(r"execute immediate\1';", sql)
31. #把文档中的单引号替换为2个单引号
32. def _replacestr(matched):
33. matchedstr = matched.group()
34. x = matchedstr.replace("'", "''")
35. return x
37. #用函数替换内容中的单引号为2个单引号
38. sql = content_str.sub(_replacestr, sql)
40. f_sql = open('2.sql','w')
41. f_sql.write(sql)
42. f_sql.close()

版本三:

1. import re
3. sql = '''DROP TABLE FZT_OL_NBR;
4. execute immediate 'CREATE TABLE FZT_OL_NBR as
5. 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 );
6. DROP TABLE FZT_OL_NBR;
7. execute immediate 'CREATE TABLE FZT_OL_NBR as
8. 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 );'''
10. a = re.compile(r'drop\s+TABLE\s+(\w+)\s*;', re.I)
11. b = re.compile(r'create\s+',re.I)
12. b1 = re.compile(r'execute immediate(.*?)(;)', re.S)
13. p = re.compile(r"(?<=execute immediate ').*?(?=;)", re.S)
15. def _replacestr(matched):
16. matchedstr = matched.group()
17. x = matchedstr.replace("'", "''")
18. return x
20. c = a.sub(r"drop_table('\1');", sql)
21. d = b.sub(r"execute immediate ' create ", sql)
22. e = b1.sub(r"execute immediate\1';", sql)
23. f = p.sub(_replacestr, sql)
28. print f

来自为知笔记(Wiz)

转载于:https://www.cnblogs.com/highroom/p/d63658b6b98f8852d3b10f867dd1f48f.html