用代码:
<code>insertMediaSql = "INSERT INTO qa(id,question,answer,createTime,modifyTime,source) VALUES(%d,'%s','%s','%s','%s',%d)" </code>
出错:
<code>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) </code>
很明显是:
sql语句中,值用撇号’包含着,但是内容本身:
I’m free!
却也包含着撇号
导致出错。
先去搞清楚英文的撇号的说法
英文 撇号
撇号( ‘ ,英语:apostrophe,又称上标点、略缩号、省字号、省年号、高撇号或缩写号)
所以再去搜:
mysql string contain apostrophe
escaping – How to escape apostrophe (‘) in MySql? – Stack Overflow
可以用两个撇号
mysql – SQL query fails with DB fields strings containing apostrophe – Stack Overflow
可以写成 ‘’
但是此处为了含义更明确,则去改为:
<code>#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)""" </code>
且处理之前,要把双引号本身过滤掉:
<code>punctuation = [",", ".", "?", "!", "-", ";", '"'] </code>
试试结果:
就消除此问题了。
【总结】
注意在写sql时,要确保,如果外部用单引号’,比如:
<code>'x y'z' </code>
时其中内部的单引号(撇号),要写成:
<code>'x y''z' </code>
或者是换成双引号在外部:
<code>“x y'z" </code>
才可以。
【后记】
在前面改为,最外部用双引号把字符串括起来,结果又遇到:
<code>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) </code>
所以:
看起来这个方式不行。
可以考虑用:
单引号,要把值放到sql之前,可以把1个单引号替换为2个单引号
或者再去找找别的办法
mysql contain quote string
string – How to escape single quotes in MySQL – Stack Overflow
When to use Single Quote, Double Quote, and Backticks when writing queries in MySQL
感觉是:
为了支持单引号,和双引号,则:
外部用单引号
内部有单引号的,写成:\’
内部有双引号的,直接用”
去改为:
<code>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) </code>
结果就可以了。
转载请注明:在路上 » 【已解决】mysql插入出错:Error 1064 You have an error in your SQL syntax