用代码:insertMediaSql = "INSERT INTO qa(id,question,answer,createTime,modifyTime,source) VALUES(%d,'%s','%s','%s','%s',%d)"
出错:Error (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'm free!','I'm free!','2018-05-30 11:22:20.487499','2018-05-30 11:22:20.487499',0' at line 1") for execute sql: INSERT INTO qa(id,question,answer,createTime,modifyTime,source) VALUES(16,'I'm free!','I'm free!','2018-05-30 11:22:20.487499','2018-05-30 11:22:20.487499',0)
很明显是:
sql语句中,值用撇号’包含着,但是内容本身:
I’m free!
却也包含着撇号
导致出错。
先去搞清楚英文的撇号的说法
英文 撇号
撇号( ‘ ,英语:apostrophe,又称上标点、略缩号、省字号、省年号、高撇号或缩写号)
所以再去搜:
mysql string contain apostrophe
可以用两个撇号
可以写成 ‘’
但是此处为了含义更明确,则去改为:#insertMediaSql = "INSERT INTO qa(id,question,answer,createTime,modifyTime,source) VALUES(%d,'%s','%s','%s','%s',%d)"
insertMediaSql = """
INSERT INTO qa(`id`,`question`,`answer`,`createTime`,`modifyTime`,`source`)
VALUES(%d,"%s","%s","%s","%s",%d)"""
且处理之前,要把双引号本身过滤掉:punctuation = [",", ".", "?", "!", "-", ";", '"']
试试结果:
就消除此问题了。
【总结】
注意在写sql时,要确保,如果外部用单引号’,比如:'x y'z'
时其中内部的单引号(撇号),要写成:'x y''z'
或者是换成双引号在外部:“x y'z"
才可以。
【后记】
在前面改为,最外部用双引号把字符串括起来,结果又遇到:Error (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'Let It Be?"","2018-05-31 12:04:52.240980","2018-05-31 12:04:52.240980",0)\' at line 2') for execute sql:
INSERT INTO qa(`id`,`question`,`answer`,`createTime`,`modifyTime`,`source`)
VALUES(54,"What a great song.","How about "Let It Be?"","2018-05-31 12:04:52.240980","2018-05-31 12:04:52.240980",0)
所以:
看起来这个方式不行。
可以考虑用:
单引号,要把值放到sql之前,可以把1个单引号替换为2个单引号
或者再去找找别的办法
mysql contain quote string
感觉是:
为了支持单引号,和双引号,则:
外部用单引号
内部有单引号的,写成:\’
内部有双引号的,直接用”
去改为:dialogA = dialogAB[0]
dialogB = dialogAB[1]
dialogA = dialogA.replace("'", "''")
dialogB = dialogB.replace("'", "''")
if len(dialogA) > 0 and len(dialogB) > 0:
#insertMediaSql = "INSERT INTO qa(id,question,answer,createTime,modifyTime,source) VALUES(%d,'%s','%s','%s','%s',%d)"
insertMediaSql = """
INSERT INTO qa(`id`,`question`,`answer`,`createTime`,`modifyTime`,`source`)
VALUES(%d,'%s','%s','%s','%s',%d)"""
qaid = curId
executeSql = insertMediaSql % (qaid, dialogA, dialogB, now, now, 0)
结果就可以了。