Mysqldb的sql语句转义问题(1064)
遇到的问题
如代码所示：
import MySQLdb
db_connector = MySQLdb.Connect(host="127.0.0.1",port=3306,user="*",passwd="*",db="test",charset="utf8")
name = "ww','\n中.北'京 文w"
score = 94
sqlcmd = "INSERT INTO student (name, score) VALUES "
sqlcmd += "('%s', %d)" % (name, score)
cursor.execute(sqlcmd) //报错
db_connector.commit()
db_connector.close()
报错如下：
(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 '\xe4\xba\xac \xe6\x96\x87w', 94)' at line 1")
解决方法(下述三种均可行)
自己写转义函数
def handle_mysql(mstr):
mstr = mstr.replace("\'","\\\'")
mstr = mstr.replace('\"','\\\"')
return mstr
name = handle_mysql(name)
利用MySQLdb提供的转义功能
name = MySQLdb.escape_string(name)
利用execute()接受多参数，避开转义
cursor.execute("INSERT INTO student (name, score) VALUES (%s, %s)" , (name,score) );
