恩，mysql的模糊查询是大家常用的，今天在用python做域名统计的时候，遇到了相当的问题。。。。    主要是  fieldname like “%nima%” ,这语句在mysqldb的模块中会导致被转义。

文章的原文，http://xiaorui.cc/?p=1702  

下面的用法，我估计是大家都会用的…. 在python中，做%s的转义就是%%来解决。。。 如果你这么用了，那么会报错。。。

def select_mysql_domain(domain,start_date,end_date):
    conn = sqlstore._connect()
    cursor = conn.cursor()
    domain_str = "%"+domain+"%"
    sql = """select count(id) from article where url like '%%%s%%' and pub_date >%s and pub_date <
%s"""
    cursor.execute(sql, (domain,start_date, end_date))
    res = cursor.fetchone()
    return domain,res[0]

Traceback (most recent call last):
  File “buzz/lib/data_scan.py”, line 46, in <module>
    print select_mysql_domain(domain,start,end)
  File “buzz/lib/data_scan.py”, line 26, in select_mysql_domain
    cursor.execute(sql, (domain,start_date, end_date))
  File “/usr/local/lib/python2.7/dist-packages/MySQLdb/cursors.py”, line 201, in execute
    self.errorhandler(self, exc, value)
  File “/usr/local/lib/python2.7/dist-packages/MySQLdb/connections.py”, line 36, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.ProgrammingError: (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 ‘qq.com’%’ and pub_date >’2015-06-29 11:00:00′ and pub_date < ‘2015-06-29 12:00:0’ at line 1”)

这是在mysql中操作show processlist；，显示所有正在执行的sql语句。  我们会发现他被莫名的转义了。 

*************************** 271. row ***************************
     Id: 12807984
   User: buzz
   Host: 192.168.1.194:57323
     db: buzz_master
Command: Query
   Time: 3
  State: Sending data
   Info: select count(id) from article where url like “%’qq.com’%” and pub_date >’2015-06-29 11:00:00′ and pu
271 rows in set (0.00 sec)

下面是解决的办法…   因为在python的mysqldb中，他已经会做一些字符的转义. 比如你传进一个字符串，你不需要引起来，因为mysqldb会判断你的字符是否是整形或者是字符串，然后顺手帮你转义. 

def select_mysql_domain(domain,start_date,end_date):
    conn = sqlstore._connect()
    cursor = conn.cursor()
    domain_str = "%"+domain+"%"
    sql = """select count(id) from article where url like %s and pub_date >%s and pub_date < %s"""
    cursor.execute(sql, (domain_str,start_date, end_date))
    res = cursor.fetchone()
    return domain,res[0]

*************************** 268. row ***************************
     Id: 12807849
   User: buzz
   Host: 192.168.1.194:57218
     db: buzz_master
Command: Query
   Time: 48
  State: Sending data
   Info: select count(id) from article where url like ‘%qq.com%’ and pub_date >’2015-06-29 11:00:00′ and pub_…….

就这样搞定了….   其实关于mysqldb的模糊查询问题出过好几次，每次都去来来回回的测试才成功，总是记不住，年纪大了，今天就写个博客记录下。 

---

大家觉得文章对你有些作用！ 如果想赏钱，可以用微信扫描下面的二维码，感谢!
另外再次标注博客原地址  xiaorui.cc