在MySQL/Python中创建一个带有转义字符的插入查询

Question

我正在编写一个脚本，将数据从Access数据库传输到MySQL数据库。我正试图生成一个类似于下面的查询:

INSERT into customers (firstname, lastname) value ('Charlie', "D'Amelio");

然而，MySQL不喜欢上面列出的双引号。我写了一个笨拙的函数，试图将"in D’amelio"替换为"a"。下面是创建SQL语句的整个函数:

def dictionary_output(dict):
output = "INSERT into lefm_customers "
fields = "(id, "
vl =  "('" + id_gen() + "', "
for key in dict.keys():
# print(dict[key])
if str(dict[key]) == 'None' or str(dict[key]) == "":
pass
elif "'" in str(dict[key]):
fields = fields + str(key) + ", "
string = ""
for character in string:
if character == "'":
string += r"'"
else:
string += character
vl = "'" + string + "', "
else:
fields = fields + str(key) + ", "
vl = vl + "'" + str(dict[key]) + "', "

fields = fields[:-2] + ")"
vl = vl[:-2] + ");"
return "INSERT into lefm_customers " + fields + " values " + vl

目前它只是完全忽略该值。有什么建议可以代替，或者如何提高我的功能吗?谢谢你！

Answer 1

修复:

def dictionary_output(dict):
lst = []
output = "INSERT into lefm_customers "
fields = "(id, "
vl =  "('" + id_gen() + "', "
for key in dict.keys():
# print(dict[key])
if str(dict[key]) == 'None' or str(dict[key]) == "":
pass

else:
fields = fields + str(key) + ", "
vl = vl + "%s, "
lst.append(dict[key])
fields = fields[:-2] + ")"
vl = vl[:-2] + ");"
return ("INSERT into lefm_customers " + fields + " values " + vl, lst)

for name in access_dict:
if str(name) not in mysql_dict.keys():
try:
statement = dictionary_output(access_dict[name])
mysql_cursor.execute(statement[0], statement[1]) 
print('attempting ' + str(name))
db_connection.commit()
print("Success!")
except:
print('something went wrong')

Answer 2

你可以直接调用Python的replace。终端示例:

>>> s = "D'Amelio"
>>> s.replace("'", "'")
"D'Amelio"

在这种情况下，第一个参数是单引号'，第二个参数是重音'。