python模糊查询reslist_使用python的mysqldb做like模糊查询时遇到的问题

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

下面的用法，我估计是大家都会用的…. 在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

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