pymysql连接mysql数据库时怎么使用模糊查询
我们都知道mysql使用模糊查询的语句是select * from 表名 where 列名 like “%查询字段%”;
我们使用pymsql连接mysql数据库的时候使用参数来进行模糊查询,我们的语句写成:
sql = "SELECT * FROM energy WHERE foodname LIKE %s; ('%' + params +'%')"
cur.execute(sql)
此时报错:
pymysql.err.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 '%s; ('%' + params +'%')' at line 1")
如果使用
params=['%' + data1 + '%']
sql = "SELECT * FROM energy WHERE foodname LIKE '%s'" %params
cur.execute(sql)
则会报错:
pymysql.err.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 '%苹果%']'' at line 1")
正确解决步骤:
1、创建连接对象:
conn = pymysql.connect(
host='localhost' # 如果要连接远程对象就填写远程主机地址
port=3306 # mysql的默认端口
user='root' # 用户名
password='root' # 密码
database='food' # 连接数据库名
charset='utf8' # 数据库编码设置
)
2、获取游标对象
cur = conn.cursor()
# sql查询语句,%s是需要模糊查询的内容
sql = "SELECT * FROM energy WHERE foodname LIKE %s;"
# data是需要查询的内容,%与与data字符串拼接,得到模糊查询的参数
params = ['%' + data + '%']
# 执行sql语句
cur.execute(sql, params)