Python:尝试形成 SQL 语句时出现字符串操作问题

Question

假设我有以下字符串:

query = '''UPDATE table1 SET 'variable1' = 'value1', 'something' = "['bla', 'yada']", location = 'chicago' WHERE id = 9'''

这是一个字符串，我将使用它作为自定义库的参数来更新 SQLite 表。我需要做的是删除每个等号之前单词周围的单引号(如果该单词确实有单引号)。

期望的输出如下:

query = '''UPDATE table1 SET variable1 = 'value1', something = "['bla', 'yada']", location = 'chicago' WHERE id = 9'''

请注意，单词“variable1”和“something”周围的单引号已被删除。我该如何对此表单中的所有查询执行此操作？

Answer 1

您可以使用正则表达式来替换它们。如果单引号内后跟空格和等号，则正则表达式会捕获单引号内的值，然后仅用值替换引号和值。

import re
query = '''UPDATE table1 SET 'variable1' = 'value1', 'something' = "['bla', 'yada']", location = 'chicago' WHERE id = 9'''

updated_query = re.sub(r"'([^']+)'(?=\s=)", r"\1", query)
print(query)
print(updated_query)

输出

UPDATE table1 SET 'variable1' = 'value1', 'something' = "['bla', 'yada']", location = 'chicago' WHERE id = 9
UPDATE table1 SET variable1 = 'value1', something = "['bla', 'yada']", location = 'chicago' WHERE id = 9