pymssql处理‘\’转义符

最近在使用mysql时经常要拼写sql语句，服务经常报错，下面是报错内容

  File "/Applications/PyCharm.app/Contents/plugins/python/helpers/pydev/pydevd.py", line 1434, in _exec
    pydev_imports.execfile(file, globals, locals)  # execute the script
  File "/Applications/PyCharm.app/Contents/plugins/python/helpers/pydev/_pydev_imps/_pydev_execfile.py", line 18, in execfile
    exec(compile(contents+"\n", file, 'exec'), glob, loc)

经查找是由于我拼写是导致的sql语句多了'\':'SELECT COUNT(*) from meituan_comment WHERE comment_detail_link = \'//www.meituan.com/feedback/f2399797301.html\''

注意上面语句在comment_detail_link参数里面多了两个'\'

代码

    sql_url = "SELECT COUNT(*) from meituan_comment WHERE comment_detail_link = '%s'"%(comment_link)
    return sql.execute_sql_find_comment(sql_url)

解决方法：

插入(查询)之前用 connection.escape(str)处理一下即可

    sql_url = "SELECT COUNT(*) from meituan_comment WHERE comment_detail_link = %s"%(pymsql.escape_string(comment_link))
    return sql.execute_sql_find_comment(sql_url)

或者

import pymongo

sql_pattern = "select * from my_collection where name = %s" #注意，这里直接用%s,不要给%s加引号，因为后面转移过后会自动加引号
name = "xxx\xxx"
name = connection.escape(name)
sql = sql_pattern%name
print(sql) # select * from my_collection where name = 'xxx\\xxx'

with connection.cursor() as cursor:
 try:
  cursor.execute(sql)
 except:
  print(sql)
  pass
 for r in cursor:
  print(r)

特别建议：

遇见这种带参数的数据库语句，比较建议使用cursor.execute(sql,comment_link)，sql是sql语句，后面是参数

from pymysql import connect

conn = connect(host='localhost', port=3306, user='root', password='wangying', bases= 'meituan', charset='utf8mb4')
cs1 = conn.cursor()
sql_url = "SELECT COUNT(*) from meituan_comment WHERE comment_detail_link = %s"
# 注意这里%s不用加‘’
cs1.execute(sql_url,comment_link)
count = ''
for temp in self.cs1.fetchall():
    count = temp[0]