python正则替换查询_用于提取和替换查询参数的python正则表达式

给你：import re

sql = """select name, count(1) from employees where status = 'Active' and role= 'Manager' group by name order by 1;"""

rx = re.compile(r"""\w+\s*=\s*'([^']+)'""")

params = rx.findall(sql)

print(params)

# ['Active', 'Manager']

主要部分是

^{pr2}$

分解后，这里说：\w+\s* # 1+ word characters, 0+ whitespace characters

=\s* # =, 0+ whitespace characters

'([^']+)' # '(...)' -> group 1

要同时拥有查询和参数，可以编写一个小函数：

import re

sql = """select name, count(1) from employees where status = 'Active' and role= 'Manager' group by name order by 1;"""

rx = re.compile(r"""(\w+\s*=\s*)'([^']+)'""")

def replacer(match):

replacer.params.append(match.group(2))

return '{}:{}'.format(match.group(1), len(replacer.params))

replacer.params = list()

query = rx.sub(replacer, sql)

params = replacer.params

print(query)

print(params)

# select name, count(1) from employees where status = :1 and role= :2 group by name order by 1;

# ['Active', 'Manager']

如注释中所述，您需要为要分析的每个查询重置params列表。在