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

我会尝试以下方法：

words = query.split(" ")

new_query = ' '.join([words[i].replace("'", "") if words[i + 1] == '=' else words[i] for i in range(len(words) - 1)] + [words[len(words) - 1]])

Answer 2

用逗号拆分查询怎么样。获取所有不同的字段

fields = query.split(", ")

然后用=号拆分每个字段

fields = [i.split("=") for i in fields]

然后删除单引号，但仅从赋值运算符的左侧删除

for f in fields:
    f[0] = f[0].replace("'", "")

然后倒转并组合字符串

fields = ["=".join(f) for f in fields]
query = ", ".join(fields)

Answer 3

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

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