LIKE子句.在python连接器mysql中

Question

我试图获得包含单词"；"；在它们的标题中，通过LIKE子句。在Python连接器MySQL中：

word='the'
query = """ SELECT COUNT(title) from movies WHERE title LIKE '%%%s%%' """ % (word,)
cursor.execute(query)

# error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT COUNT(title) from movies WHERE title LIKE '%the%'' at line 1

p = "the"
query = ("SELECT COUNT(title) from movies WHERE title LIKE", ("%" + p + "%",))
cursor.execute(query,(p,))

# AttributeError: 'tuple' object has no attribute 'encode'

Answer 1

您可以执行：

query = "SELECT COUNT(title) from movies WHERE title LIKE CONCAT('%', %s, '%')";
cursor.execute(query, (word,))

这使用了一个适当的准备语句来将变量传递给查询，因此代码不受SQL注入的影响，而且效率更高。

您也可以在应用程序端连接通配符：

query = "SELECT COUNT(title) from movies WHERE title LIKE %s";
cursor.execute(query, ('%' + word + '%',))