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.servicesystemctl 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的方法，如果知道的读者欢迎在评论区留言告知。
注：为了更好的完善本文的内容，第二部分内容转载自我去年写的专栏文章《弈心：网络工程师的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;
如果你熟练掌握了第二部分的关于MySQL的一些基本操作，那么MySQLdb模块的使用就很简单、直观了，第三部分将直接给出使用MySQLdb的脚本然后配合代码讲解。
代码如下：
import MySQLdb as mdbfrom getpass import getpasssql_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 mdbfrom getpass import getpasssql_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()
这部分将给出实战案例，演示如何使用MySQLdb Paramiko通过SSH登录现网的三台思科2960交换机，然后配合正则表达式做解析，抓取这三台交换机的hostname、IP地址、MAC地址、序列号、IOS版本、设备所在地的地址，然后通过MySQLdb为抓取到的这三台交换机的信息建库，三台交换机的IP地址已经提前写入名为ip_list.txt的文本文件中。
案例代码：
import reimport paramikoimport timeimport MySQLdb as mdbfrom getpass import getpasssql_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 reimport paramikoimport timeimport MySQLdb as mdbfrom getpass import getpasssql_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进行验证：
至此，通过Paramiko MySQLdb re(正则表达式)为上面三个现网中的交换机建库的任务就完成了，用同样的脚本可以完成几千甚者上万台设备的入库工作，只需要在ip_list.txt文件中加入足够多的设备的IP地址即可，当然设备数量越多，某些设备不可达的状况发生的概率自然就越高，读者朋友需要根据自己的情况使用try/except来针对这个情况做异常处理，这个是我书里老生常谈的话题了，这里就不做讲解和演示了。
