3、浅浅浅使用pymysql模块

枫岚

最基层网工

本人看着群里的大佬纷纷分享自己的文章和一些运维思路，深知只有用出来，写出来，分享出来，跟大家一起交流，这个程序和学习到的新知识才算真正掌握了。在这里非常感谢王印王老师 @弈心、朱嘉盛老师 @朱嘉盛以及群里的各位大佬不遗余力地分享着自己的文章。针对python的学习主要源于他们的专栏。

看到群内大佬们的思路，把结构化数据写入数据库中，既方便用sql语句进行筛选，也方便后面结合Django做前端化的展示。而且即使目前并没有用Django做到前端化展示，但是用Navicat这样的数据库管理工具，也可以做到更加便捷的观察数据和提取数据，而且也可以直接导出成excle表格。

个人感觉把数据写入数据库这样的操作百利而无一害，所以本篇文章我先把对于sql语句的使用做一个学习和记录。

一、使用pymysql模块做增删改查

1、增删改查操作

#encoding=utf-8
#导入模块
import pymysql
#创建按数据库连接
connect_db=pymysql.connect(host='192.168.40.236',
                           port=3306,
                           user='root',
                           password='1999814',
                           charset='utf8',
                           database='db_device')
#获取游标对象，就是对数据的库的连接实例化对象
cursor=connect_db.cursor()
#编写sql语句 创建数据库
sql="create database if not exists db_device default character set utf8;"
#使用此数据库
cursor.execute("use db_device;")
#执行sql语句
cursor.execute(sql)
#提交sql语句
#编写sql语句 创建数据表
try:
    table = cursor.execute("show create table tb_property;")
except:
    sql = '''
    create table tb_property(
    ip char(15),
    manue char(20),
    type_role enum('核心','汇聚','接入'),
    name varchar(20),
    primary key(ip)  
    ) engine=innodb default charset=utf8;'''
    #执行sql语句
    cursor.execute(sql)
    #提交sql语句
#编写sql语句 增加
cursor.execute("insert into tb_property values ('192.168.1.1','h3c','汇聚','TJ-3F-DS');")
cursor.execute("insert into tb_property values ('192.168.1.2','h3c','汇聚','TJ-3F-DS');")
cursor.execute("insert into tb_property values ('192.168.1.3','huawei','接入','TJ-4F-AS');")
cursor.execute("insert into tb_property values ('192.168.1.4','juniper','核心','TJ-5F-CS');")
cursor.execute("insert into tb_property values ('192.168.1.5','cisco','汇聚','TJ-6F-DS');")
#提交sql语句
#编写sql语句 更改
sql="update tb_property set ip='192.168.1.5' where ip='192.168.1.6';"
#执行sql语句
cursor.execute(sql)
#提交sql语句
#编写sql语句 删除
sql="delete from tb_property where ip='192.168.1.2';"
cursor.execute(sql)
#提交sql语句
connect_db.commit()
#获取所有数据
sql="select * from tb_property;"
cursor.execute(sql)#返回值是查出记录的数字，比如查出来3条，就返回3
result_all=cursor.fetchall()
print(result_all)
for i in result_all:
    print(i)
#关闭游标对象
cursor.close()
#关闭数据库对象
connect_db.close()

我觉得上面的注释已经写的很详细了

思路就是先用判断是否存在的语句创建数据库，使用数据库，判断是否存在表，没有则创建表，然后做增删改查的操作，记得要提交sql语句，然后输出所有数据，最后关闭游标对象和数据库对象。

输出结果

(('192.168.1.1', 'h3c', '汇聚', 'TJ-3F-DS'), ('192.168.1.3', 'huawei', '接入', 'TJ-4F-AS'), ('192.168.1.4', 'juniper', '核心', 'TJ-5F-CS'), ('192.168.1.5', 'cisco', '汇聚', 'TJ-6F-DS'))
('192.168.1.1', 'h3c', '汇聚', 'TJ-3F-DS')
('192.168.1.3', 'huawei', '接入', 'TJ-4F-AS')
('192.168.1.4', 'juniper', '核心', 'TJ-5F-CS')
('192.168.1.5', 'cisco', '汇聚', 'TJ-6F-DS')

第一行是对result_all的输出，后面的是对result_all的遍历，发现python中对数据库数据的输出是以元组套元组的方式输出的。

2、游标查询

对刚才的数据表进行查询操作

1、获取一共几条记录

2、获取一条查询结果

3、获取指定条数的记录

4、获取所有数据

#encoding=utf-8
#导入模块
import pymysql
#创建按数据库连接
connect_db=pymysql.connect(host='192.168.40.236',
                           port=3306,user='root',
                           password='1999814',
                           charset='utf8',
                           database='db_device')
#获取游标对象，就是对数据的库的连接实例化对象
cursor=connect_db.cursor()
#编写sql语句
sql="select * from tb_property;"
#执行sql语句
#获取一共几条记录
row=cursor.execute(sql)#返回值是查出记录的数字，比如查出来3条，就返回3
print(f'查询到{row}条记录')
#获取一条查询结果
result1=cursor.fetchone()
print(result1)
#获取指定条数的记录
result_many=cursor.fetchmany(2)#这里获取三条
print(result_many)
#获取所有数据
result_all=cursor.fetchall()
print(result_all)#游标的概念，就跟读取文件每行的指针一样
#关闭游标对象
cursor.close()
#关闭数据库对象
connect_db.close()

结果

查询到4条记录
('192.168.1.1', 'h3c', '汇聚', 'TJ-3F-DS') #获取一条查询结果
(('192.168.1.3', 'huawei', '接入', 'TJ-4F-AS'), ('192.168.1.4', 'juniper', '核心', 'TJ-5F-CS'))#获取指定条数的记录
(('192.168.1.5', 'cisco', '汇聚', 'TJ-6F-DS'),)#获取所有数据

哎？这怎么跟想获得不一样呢？

这里我认为游标的概念跟文件里的指针一样，它每读一行，游标就会往下移动一行，然后顺着往下读。

如果想每次都从头获取数据怎么办呢？

cursor.scroll(0, 'absolute')
result_all=cursor.fetchall()
print(result_all)#游标的概念，就跟读取文件每行的指针一样

加上 cursor.scroll(0, 'absolute') 这一行，意思是游标重置，跟 file.seek(0) 一样。

还有骚操作

cursor.scroll(-3, 'relative') # relative相对偏移，游标在当前位置进行左右偏移
result_many=cursor.fetchmany(3)#这里获取三条

这里可以获得当前游标位置前三行的数据。

3、数据回滚

try:
   # 执行sql语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # 发生错误时回滚
   db.rollback()

.rollback()数据回滚的作用就是确保数据库操作的原子性问题，多次操作要么都执行，要么都不执行

（试了很多次都没有试出来使用db.rollback()的场景，还在探索，但是建议写代码的时候加上）

一些注意事项

cursor.execute("insert into tb_property values ('192.168.1.1','h3c','汇聚','TJ-3F-DS');")
cursor.execute("insert into tb_property values ('192.168.1.2','h3c','汇聚','TJ-3F-DS');")
cursor.execute("insert into tb_property values ('192.168.1.3','huawei','接入','TJ-4F-AS');")