2019年5月自己在自己的知乎专栏写了一篇文章《弈心：网络工程师的Python之路----MySQL篇》，向网工读者们介绍了怎样使用MySQL来为网络设备建库的一些基本操作和用法，文末提到自己将会更新文章向大家讲解如何使用Python来为现网设备自动完成建库，结果这一拖就是一年半，期间有很多读者催更，奈何平时写书、学习、工作实在太忙，今天总算有时间把这个大坑给填上。
因为去年笔者用的虚拟机是CentOS 7 Python 2，而目前使用的版本为CentOS 8和Python 3，MySQL和MySQLdb模块的安装方法在这两个版本的操作系统和Python里有很大区别，因此本文分将分为四个部分，第一部分介绍MySQL在CentOS 8里的安装以及MySQLdb模块在Python3.6版本的下载和安装方法。第二部分将直接引用去年的文章再次讲解一次MySQL的基础内容，第三部分将给出MySQLdb的代码讲解MySQLdb模块的使用方法，第四部分则会结合一个实际案例来讲解如何让Python替我们自动登录、扫描现网里的所有交换机，并完成设备信息自动入库的方法。废话不多说，下面直接进入正题。
第一部分：MySQL 8.0安装步骤
- 在CentOS 8里，MySQL8.0版本已经可以在系统默认的repository下直接下载了，命令如下：
dnf install mysql-server
2. 安装完成后启用mysqld服务，并查看mysqld服务当前的状态。
systemctl start mysqld.service
systemctl status mysqld
3. 在确认mysqld的状态为active（running）后，即可输入命令mysql -u root进入mysql
mysql -u root
Python针对MySQL的第三方库叫做MySQLdb。在CENTOS 8中，Python3的MySQLdb模块的下载比较特别，不能直接用pip3.8 install MySQLdb或者pip3.8 install mysqlclient来下载。正确的下载方法如下：
- 首先安装EPEL源：
yum install epel-release
2. 然后再通过下面的dnf命令来安装Python3的MySQLdb
dnf --enablerepo=epel-testing install python3-mysqlclient
3. 这种方法安装出来的MySQLdb只能在Centos 8中内置的Python 3.6.8上运行，在Python 3.8.2中依然无法导入MySQLdb，目前笔者还没有找到在Python3.8中安装MySQLdb的方法，如果知道的读者欢迎在评论区留言告知。
第二部分：进入MySQL手动创建交换机管理数据库
注：为了更好的完善本文的内容，第二部分内容转载自我去年写的专栏文章《弈心：网络工程师的Python之路----MySQL篇》
登录MySQL
5.7以下版本的MySQL默认root账户不需要密码就能使用，因此如果你使用命令mysql -u root -p来登录的话，反而会收到ERROR 1045错误：
这时去掉参数-p，直接使用命令mysql -u root就能进入MySQL。
不过出于最基本的安全考虑，还是建议使用命令mysqladmin -u root password [password]来为root账户设置一个密码，设置好密码后，再用命令mysql -u root -p来登录mysql。
- 进入MySQL后，首先创建一个名为NetMon的数据库，方法为在mysql>提示符后输入命令create database NetMon; 注意末尾的分号‘;’不能省略
2. 使用命令show databases;来查看数据库列表（依然不要忘记命令末尾的分号）。这里可以看到除了我们创建的NetMon数据库外，还有information_schema、mysql、performance_schema等另外3个MySQL自带的数据库，了解即可，不用理会它们。
3. 使用命令use NetMon;来调用NetMon数据库，如果收到'Database changed'这个提示则说明数据库调用成功。
4. 调用数据库后，接下来需要在该数据库中创建数据表（table），在MySQL中创建数据表的基本命令如下：
CREATE TABLE table_name (column_name column_type);
其中CREATE TABLE为创建数据表的命令，这里将其写作大写是为了和后面用户自定义的数据表名(table_name)做区分，实际操作中CREATE TABLE这个命令用大小写都没问题（下同）。后面的column_name为数据列名字，column_type为数据列类型。
这里我们将要创建两个数据表，分别命名为Cisco_2960和Cisco_3750，每个数据表下面又各自含有Hostname, IPAddr, MACAddr, IOSVersion, SerialNo总共5个数据列(column)，实验中的数据表创建命令如下（初学者可以复制粘贴下面的命令）：
create table Cisco_2960(Hostname VARCHAR(30), IPAddr VARCHAR(15), MACAddr VARCHAR(20) PRIMARY KEY, IOSVersion VARCHAR(20), SerialNo VARCHAR(30));
数据表创建成功后会收到类似"Query OK, 0 rows affected (0.04 sec)"的提示
这里简单讲下VARCHAR和PRIMARY KEY这两个列类型，前者定义列长度，最大为65535， 比如Hostname VARCHAR(30)，表明支持的交换机名的长度最大为30个字符，IPAddr VARCHAR(15)，表明支持的IP地址最大为15个字符。有MySQL基础的应该知道CHAR, VARCHAR, Text三者间的差别，这里不做详细介绍，不清楚的网工读者可以自行延伸学习一下。
而PRIMARY KEY（主键）则是用来唯一标识数据表中某一列的属性或属性组。需要记住三点： 一个数据表中只能有一个主键，主键值不能为空 (NOT NULL)，主键必须包含唯一值，因为MAC地址的唯一性，因此这里我们将主键给了MACAddr这个列（MACAddr VARCHAR(20) PRIMARY KEY）。
5. 创建好数据表后，我们可以使用命令show tables;来查看NetMon数据库下现有的数据表
show tables;
6. 然后再用describe命令来查看数据表里具体包含的列名和列类型。
describe Cisco_2960;
这里可以看到我们在数据表Cisco_2960中创建了5个列，其中主键分配给了MACAddr这个列。
7. 创建好数据表和列后，下面我们可以使用insert into命令来手动向数据表中插入数据了。
insert into Cisco_2960(Hostname, IPAddr, MACAddr, IOSVersion, SerialNo) values('SW1', '10.10.10.1', '12:34:56:AB:CD:EF', '12.2(55)SE12', 'XXXXXXXXXXX');
这里我们向数据表Cisco_2960插入了一个Hostname为'SW1'， IPAddr为'10.10.10.1'，MACAddr为'12:34:56:AB:CD:EF', IOSVersion为'12.2(55)SE12'，SerialNo为'XXXXXXXXXXX'的数据。
接下来我们再次向数据表Cisco_2960中插入另外一组数据（SW2），这里我故意把SW2的MAC地址设为和SW1一样（12:34:56:AB:CD:EF），来看下会发生什么：
insert into Cisco_2960(Hostname, IPAddr, MACAddr, IOSVersion, SerialNo) values('SW2', '10.10.10.2', '12:34:56:AB:CD:EF', '12.2(55)SE12', 'YYYYYYYYYYY');
看到这里的Duplicate entry '12:34:56:AB:CD:EF' for key 'PRIMARY'的报错了吗？前面提到了，我们将主键给了MACAddr这个列，因此该列下的数据不可以有重复项！将SW2的MAC地址修改为11:22:33:AA:BB:CC重新插入数据成功。
insert into Cisco_2960(Hostname, IPAddr, MACAddr, IOSVersion, SerialNo) values('SW2', '10.10.10.2', '11:22:33:AA:BB:CC', '12.2(55)SE12', 'YYYYYYYYYYY');
8. 现在数据表Cisco_2960中已经有了两组数据了，我们可以使用下面的MySQL命令来查看：
SELECT * FROM [table name]；
这里我们的table name为Cisco_2960，因此我们可以用命令select * from Cisco_2960; 来查看
9. 除了SELECT * FROM [table name]之外，我们还可以使用命令SELECT * FROM [table name] WHERE来进一步筛选我们想要查找的数据
SELECT * FROM [table name] WHERE
比如我们只想从数据表Cisco_2960中查找Hostname为SW2的交换机的数据，那么可以使用命令SELECT * FROM Cisco_2960 WHERE Hostname = 'SW2';来实现
10. 回到本文开篇提到的“在思科设备数量上千台的大型网络中，如果高层领导问你我们的交换机有多少种IOS版本？每个IOS版本各自对应多少台交换机？"问题，为了演示如何用MySQL来快速回答这个问题，接下来我们再向数据表Cisco_2960中插入一组数据(SW3)，SW3和SW1,SW2的区别在于它的IOS版本为12.2(55)SE10，而后两者为SE12。
insert into Cisco_2960(Hostname, IPAddr, MACAddr, IOSVersion, SerialNo) values('SW3', '10.10.10.3', '44:55:66:DD:EE:FF', '12.2(55)SE10', 'ZZZZZZZZZZZ');
接下来我们可以使用MySQL中的count()函数和group by语法来实现这个要求，count()函数用来返回数据表中的行数，group by则是根据给定数据列的每个成员对查询结果进行分组统计，将两者结合起来使用即能回答“交换机有多少种IOS版本？每个IOS版本各自对应多少台交换机？”这个问题。命令如下：
select IOSVersion, COUNT(IOSVersion) from Cisco_2960 group by IOSVersion;
这里可以看到IOS版本为12.2(55)SE10的设备数有1台（即SW3），12.2(55)SE12的设备数有2台（即SW1和SW2）。
11. 如果领导又问你是哪两台交换机的IOS版本为12.2(55)SE12， 它们的Hostname和IP地址是多少？这时可以用下面的命令做查询：
select Hostname,IPAddr from Cisco_2960 where IOSVersion= '12.2(55)SE12';
是不是很方便？
12. 如果你想为数据表中加入新的数据列，比如我们想添加一个名为Location的列来记录交换机所在的物理位置，可以用下面的MySQL命令实现：
alter table Cisco_2960 add column Location VARCHAR(20);
添加了新的数据列后，该数据列的值均为NULL，
这时我们需要使用MySQL中的UPDATE, SET, WHERE几个语句来为新增列添加数据，这里假设SW1在Building 1，SW2在Builiding 2，SW3在Building 3，命令如下：
update Cisco_2960 set Location = 'Building 1' where Hostname = 'SW1';
update Cisco_2960 set Location = 'Building 2' where Hostname = 'SW2';
update Cisco_2960 set Location = 'Building 3' where Hostname = 'SW3';
再次查看数据表，新增的Location数据列里的数据已经添加完毕：
13 (optional). 有时也会碰到某台设备因各种因素不再使用，需要从数据库中删除的情况，这里我们可以使用MySQL中的DELETE, WHERE语句来完成，举例如下，比如这里我们想要删除SW3这台设备：
delete from Cisco_2960 where Hostname = 'SW3';
14 (optional). 如果想删除数据列，比如刚才添加的Location，可以使用MySQL中的ALTER,DROP语句来完成，举例如下：
alter table Devices drop column Location;
15. 最后所有操作完成后，记得使用commit;命令来保存配置：
commit;
第三部分：使用MySQLdb模块来管理数据库并为现网设备建库
如果你熟练掌握了第二部分的关于MySQL的一些基本操作，那么MySQLdb模块的使用就很简单、直观了，第三部分将直接给出使用MySQLdb的脚本然后配合代码讲解。
代码如下：
import MySQLdb as mdb
from getpass import getpass
sql_host = "localhost"
sql_username = input('Enter SQL username: ')
sql_password = getpass('Enter SQL password: ')
sql_connection = mdb.connect(sql_host,sql_username,sql_password)
cursor = sql_connection.cursor()
cursor.execute('drop database NetMon')
cursor.execute('create database test')
cursor.execute("use test")
cursor.execute("create table Devices (Hostname VARCHAR(30), MACAddr VARCHAR(20) PRIMARY KEY, Vendor VARCHAR(10), Model VARCHAR(20), IOSVersion VARCHAR(20), SerialNo VARCHAR(30))")
cursor.execute("insert into Devices(Hostname, MACAddr, Vendor, Model, IOSVersion, SerialNo) values('AS-2960-EC3-G-3630', '00:23:34:2A:F6:00', 'Cisco', ' WS-C2960-24PC-L', '12.2(55)SE12', 'FOC1236Z17C')")
cursor.execute("select * from Devices")
output = cursor.fetchone()
print (output)
output = cursor.fetchall()
print (output)
sql_connection.commit()
sql_connection.close()
代码分段讲解如下：
- 通过import MySQLdb as mdb导入MySQLdb模块并将它简写为mdb后，我们需要定义三个变量来作为登录MySQL的三个参数，分别为sql_host, sql_username, sql_password，因为MySQL是在我们主机上本地运行的，所以这里sql_host设为localhost，sql_username和sql_password的话则通过input()和getpass()交互式获取。随后调用MySQLdb的connect()函数，将三个变量作为参数放入，并赋值给变量sql_connection，至此已经登录MySQL的操作即告完成。
import MySQLdb as mdb
from getpass import getpass
sql_host = "localhost"
sql_username = input('Enter SQL username: ')
sql_password = getpass('Enter SQL password: ')
sql_connection = mdb.connect(sql_host,sql_username,sql_password)
- 随后我们调用MySQLdb下最重要的对象cursor()并将它赋值给变量cursor，有了它后我们就能通过cursor.execute()直接对MySQL发送命令，进行操作了。这里我首先用cursor.execute('drop database NetMon')将之前手动创建的数据库NetMon给删除掉，然后通过cursor.execute('create database test')创建一个新的名为test的数据库，后面添加数据表、数据列，并为数据列添加数据的方法和第二部分讲到的完全一样。最后我们通过cursor.execute("select * from Devices")来获取数据表Devices下面所有的内容。
cursor = sql_connection.cursor()
cursor.execute('drop database NetMon')
cursor.execute('create database test')
cursor.execute("use test")
cursor.execute("create table Devices (Hostname VARCHAR(30), MACAddr VARCHAR(20) PRIMARY KEY, Vendor VARCHAR(10), Model VARCHAR(20), IOSVersion VARCHAR(20), SerialNo VARCHAR(30))")
cursor.execute("insert into Devices(Hostname, MACAddr, Vendor, Model, IOSVersion, SerialNo) values('AS-2960-EC3-G-3630', '00:23:34:2A:F6:00', 'Cisco', ' WS-C2960-24PC-L', '12.2(55)SE12', 'FOC1236Z17C')")
cursor.execute("select * from Devices")
- MySQLdb下面有两种打印数据表内容的方法，分别为cursor.fetchone()和cursor.fetchall()。两者的区别顾名思义一个是只读取数据表里一排的内容，一个是读取数据表里全部的内容，因为这里我们通过cursor.execute("insert into Devices(Hostname, MACAddr, Vendor, Model, IOSVersion, SerialNo) values('AS-2960-EC3-G-3630', '00:23:34:2A:F6:00', 'Cisco', ' WS-C2960-24PC-L', '12.2(55)SE12', 'FOC1236Z17C')")只向数据表里添加了一排的内容，所以两者的输出结果看不出有什么区别，读者可以往数据表里自行添加另外一台交换机，然后做比较。
output = cursor.fetchone()
print (output)
output = cursor.fetchall()
print (output)
- MySQLdb使用后需要调用commit()才能让前面的所有命令生效，代码的最后通过close()退出MySQL程序。
sql_connection.commit()
sql_connection.close()
第四部分：MySQLdb实战案例
这部分将给出实战案例，演示如何使用MySQLdb Paramiko通过SSH登录现网的三台思科2960交换机，然后配合正则表达式做解析，抓取这三台交换机的hostname、IP地址、MAC地址、序列号、IOS版本、设备所在地的地址，然后通过MySQLdb为抓取到的这三台交换机的信息建库，三台交换机的IP地址已经提前写入名为ip_list.txt的文本文件中。
案例代码：
import re
import paramiko
import time
import MySQLdb as mdb
from getpass import getpass
sql_host = "localhost"
sql_username = input('Enter MySQL username: ')
sql_password = getpass('Enter MySQL password: ')
switch_username = input('Enter switch username: ')
switch_password = getpass('Enter switch password: ')
sql_connection = mdb.connect(sql_host,sql_username,sql_password)
cursor = sql_connection.cursor()
cursor.execute("create database if not exists Cisco_2960")
cursor.execute("use Cisco_2960")
cursor.execute("create table if not exists Devices (Hostname VARCHAR(30), IPAddr VARCHAR(20), MACAddr VARCHAR(20) PRIMARY KEY, IOSVersion VARCHAR(20), SerialNo VARCHAR(30), Location VARCHAR(20))")
f = open("ip_list.txt","r ")
for line in f.readlines():
ip_address = line.strip()
ssh_client = paramiko.SSHClient()
ssh_client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh_client.connect(hostname=ip_address,username=switch_username,password=switch_password)
print ("已经成功登录交换机 ", ip_address)
command = ssh_client.invoke_shell()
command.send("term len 0\n")
command.send("show ver\n")
time.sleep(1)
output = command.recv(65535).decode('ascii')
hostname = re.search(r"\w{2,3}-\w{2,5}-\w{3,4}-\w{1,5}-?\w{2,5}-?\w{1,4}-\w{1,4}", output)
macadd = re.search(r"\w{2}:\w{2}:\w{2}:\w{2}:\w{2}:\w{2}", output)
iosversion = re.search(r"\d{2}\.\d\(\d{1,2}\)\w{2,4}", output)
command.send("show ver | i System serial\n")
time.sleep(1)
output = command.recv(65535).decode('ascii')
serialno = re.search(r"\w{11}", output)
command.send("show run int vlan 3999\n")
time.sleep(1)
output = command.recv(65535).decode('ascii')
ipaddr = re.search(r"\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}", output)
command.send("show snmp location\n")
time.sleep(1)
output = command.recv(65535).decode('ascii')
location = re.search(r"\w{5,6}-\w{2}-\w{2}-\w{2}", output)
cursor.execute("insert into Devices (Hostname, IPAddr, MACAddr, IOSVersion, SerialNo, Location) values(%s,%s,%s,%s,%s,%s)",(hostname.group(), ipaddr.group(), macadd.group(), iosversion.group(), serialno.group(), location.group()))
sql_connection.commit()
f.close()
sql_connection.close()
ssh_client.close()
代码分段讲解如下：
- 这里要分别输入两套用户名和密码，一套登录MySQL，一套登录交换机。
import re
import paramiko
import time
import MySQLdb as mdb
from getpass import getpass
sql_host = "localhost"
sql_username = input('Enter MySQL username: ')
sql_password = getpass('Enter MySQL password: ')
switch_username = input('Enter switch username: ')
switch_password = getpass('Enter switch password: ')
- 这里注意在"create databse"和"create table"后面分别加入了"if not exists"，用来判断数据库和数据表是否存在，如果已存在，则不创建。这么做的好处是：因为你第一次运行脚本后已经创建了Cisco_2960这个数据库和Devices这个数据表，那么当你第二次，第三次。。。第N次运行这个脚本时，脚本不会因为“数据库或数据表已经存在的”之类的Python异常而被中断。这里我们在Devices数据表里插入了Hostname， IPAddr，MACAddr, IOSVersion，SerialNo以及Location总共6个数据列。
sql_connection = mdb.connect(sql_host,sql_username,sql_password)
cursor = sql_connection.cursor()
cursor.execute("create database if not exists Cisco_2960")
cursor.execute("use Cisco_2960")
cursor.execute("create table if not exists Devices (Hostname VARCHAR(30), IPAddr VARCHAR(20), MACAddr VARCHAR(20) PRIMARY KEY, IOSVersion VARCHAR(20), SerialNo VARCHAR(30), Location VARCHAR(20))")
- 通过for循环依次登录三台2960交换机，通过正则表达式去匹配我们需要的Hostname， IPAddr，MACAddr, IOSVersion，SerialNo以及Location等信息，最后通过cursor.execute("insert into Devices (Hostname, IPAddr, MACAddr, IOSVersion, SerialNo, Location) values(%s,%s,%s,%s,%s,%s)",(hostname.group(), ipaddr.group(), macadd.group(), iosversion.group(), serialno.group(), location.group()))将它们写入数据库，从而完成对三台交换机入库的操作。
f = open("ip_list.txt","r ")
for line in f.readlines():
ip_address = line.strip()
ssh_client = paramiko.SSHClient()
ssh_client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh_client.connect(hostname=ip_address,username=switch_username,password=switch_password)
print ("已经成功登录交换机 ", ip_address)
command = ssh_client.invoke_shell()
command.send("term len 0\n")
command.send("show ver\n")
time.sleep(1)
output = command.recv(65535).decode('ascii')
hostname = re.search(r"\w{2,3}-\w{2,5}-\w{3,4}-\w{1,5}-?\w{2,5}-?\w{1,4}-\w{1,4}", output)
macadd = re.search(r"\w{2}:\w{2}:\w{2}:\w{2}:\w{2}:\w{2}", output)
iosversion = re.search(r"\d{2}\.\d\(\d{1,2}\)\w{2,4}", output)
command.send("show ver | i System serial\n")
time.sleep(1)
output = command.recv(65535).decode('ascii')
serialno = re.search(r"\w{11}", output)
command.send("show run int vlan 3999\n")
time.sleep(1)
output = command.recv(65535).decode('ascii')
ipaddr = re.search(r"\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}", output)
command.send("show snmp location\n")
time.sleep(1)
output = command.recv(65535).decode('ascii')
location = re.search(r"\w{5,6}-\w{2}-\w{2}-\w{2}", output)
cursor.execute("insert into Devices (Hostname, IPAddr, MACAddr, IOSVersion, SerialNo, Location) values(%s,%s,%s,%s,%s,%s)",(hostname.group(), ipaddr.group(), macadd.group(), iosversion.group(), serialno.group(), location.group()))
sql_connection.commit()
f.close()
sql_connection.close()
ssh_client.close()
执行脚本：
然后登录MySQL进行验证：
至此，通过Paramiko MySQLdb re(正则表达式)为上面三个现网中的交换机建库的任务就完成了，用同样的脚本可以完成几千甚者上万台设备的入库工作，只需要在ip_list.txt文件中加入足够多的设备的IP地址即可，当然设备数量越多，某些设备不可达的状况发生的概率自然就越高，读者朋友需要根据自己的情况使用try/except来针对这个情况做异常处理，这个是我书里老生常谈的话题了，这里就不做讲解和演示了。
,
