import pymysql
# 把mysql查询写成函数
def get_ip():
# 准备连接所需要的信息
conn_info = {
"host":"127.0.0.1",
"port":3306,
"user":"root",
"passwd":"root123",
"database":"dh",
"charset":"utf8"
}
# 连接mysql数据库
conn = pymysql.connect(**conn_info)
# 获取游标
cursor = conn.cursor()
#预准备mysql语句
#
# sql = "select * from vsl_dev_copy"
# sql1 = "select `device_name`,`device_ip` from vsl_dev_copy " # where `device_ip` is null 192.168.1.2
# sql2 = "select `device_name`,`device_ip` from vsl_dev_copy where `device_ip` is null"
# sql3 = "select `device_name`,`device_ip` from vsl_dev_copy where `device_ip`= '192.168.1.2'" #这个是可以的,如果where 条件的右侧的值 192.168.1.2 如果用键盘的左上角的引号也报错,从而得出结论就是 字段 是需要 左上角的引号 ,值只能是单引号
sql4 = "select 'device_name','device_ip' from vsl_dev_copy where `device_ip` = '192.168.1.2'" # 这个是报错的,字段device_name 是使用的单引号报错
# 执行mysql语句
cursor.execute(sql4)
# 返回mysql的查询结果
result = cursor.fetchall()
return result
使用pysql语句操作时,在execute() 中的sql 语句经常报错,经过测试后找到原因如下
1 . 从而得出结论就是 字段 是需要 左上角的引号 ,值只能是单引号
sql4 = "select 'device_name','device_ip' from vsl_dev_copy where `device_ip` = '192.168.1.2'" cursor.execute(sql4)
2. 如果有变量传递
#通过格式化来准备的sql语句 sql6 ="select `device_name`,`device_ip` from vsl_dev_copy where `device_ip`=%s" # 执行mysql语句 cursor.execute(sql6,name)
其中%s 和 name 对应上了,name就是一个形参(变量),且使用其它字符串格式化好像不能用,会报错