对数据库的增删改查平时的工作中用的比较多，也比较熟悉。忽然发现都没有使用过 insert 操作，因此在学习 python 的过程中，通过 mysql.connector 对 44w行 的 ip地址记录进行数据库批量插入工作。
另：简书不支持图片外链，我没找到什么好方法来上传图片，如果有需要看图片的，可以移步到我的博客中去看，本篇文章的博客地址在本文末。
步骤分为以下五步：
1. 数据格式整理
2. 创建表
3. 连接表
4. 读取本地文件数据
5. 批量写入
数据格式整理
首先查看所拿到的数据，并整理：
分别在终端中使用 head ipdata.csv 和 tail ipdata.csv 来查看文件的头部和尾部数据，对其规律进行分析。
:~/Documents/study/NiuCodeLesson/insertSQL% head ipdata.csv
1,0.0.0.0,0.255.255.255,IANA,保留地址
2,1.0.0.0,1.0.0.255,澳大利亚,CZ88.NET
3,1.0.1.0,1.0.3.255,福建省,电信
4,1.0.4.0,1.0.7.255,澳大利亚,CZ88.NET
5,1.0.8.0,1.0.15.255,广东省,电信
6,1.0.16.0,1.0.31.255,日本,Beacon服务器
7,1.0.32.0,1.0.63.255,广东省,电信
8,1.0.64.0,1.0.127.255,日本,広島県中区大手町Energia通信公司
9,1.0.128.0,1.0.255.255,泰国,CZ88.NET
10,1.1.0.0,1.1.0.255,福建省,电信
:~/Documents/study/NiuCodeLesson/insertSQL% tail ipdata.csv
444954,223.255.252.0,223.255.253.255,福建省,电信
444955,223.255.254.0,223.255.254.255,新加坡,滨海湾金沙私人有限公司
444956,223.255.255.0,223.255.255.255,澳大利亚,CZ88.NET
444957,224.0.0.0,224.255.255.255,IANA,CZ88.NET
444958,225.0.0.0,239.255.255.255,IANA保留地址,用于多点传送
444959,240.0.0.0,247.255.255.255,IANA保留地址,CZ88.NET
444960,248.0.0.0,248.255.255.255,IANA保留地址,CZ88.NET
444961,249.0.0.0,254.255.255.255,IANA保留地址,CZ88.NET
444962,255.0.0.0,255.255.254.255,CZ88.NET,
444963,255.255.255.0,255.255.255.255,纯真网络,2014年10月25日IP数据
查看后我们可以大致将数据分为 5 列，「序号」，「起始IP」，「终止IP」，「所在地」，「运营商」。
创建表
经过格式的分析，我们就可以创建一个表的结构了。
注：事实上分析数据规律比建表要重要的多，因为本文重点不在此处，所以概过。
首先通过 navicat 创建本地的数据库。数据库格式如下图：
[图片上传失败...(image-e6819d-1518059726027)]
连接表
首先连接本地数据库
因为我的表示创建在本地数据库下的，连接的库的时候不需要添加 host 参数
连接本地数据库的代码如下：
conn = mysql.connector.connect(user='root', password='admin', database='test')
连接远程数据库的代码如下：
conn = connection.MySQLConnection(host=host_d, user=user_d, password=password_d, database=database_d)
读取 csv 数据文件
一般来说，读取文件的方法有两种；推荐以下方式来读取文件，可以避免忘记关闭文件句柄的尴尬：
with open('./ipdata.csv', 'r') as f:
lines = f.readlines()
读取文件格式采坑
在读取文件的过程中发现了报错。
报错信息：
UnicodeDecodeError: 'ascii' codec can't decode byte 0xe4 in position 29: ordinal not in range(128)
原因分析：
出错的原因是因为 csv 内的编码方式与程序环境编码方式不一致所致。csv 的编码格式是 gbk， 只要能够用 unicode 编码格式处理读取进来的数据就解决了。
搜索解决方案：
自从上次瞎捷豹连续操劳后，已经很久没有让它出门了，这次又要有劳它了。
经过搜索后，发现很多方案：
方案一 代码头部申明编码
有两种写法，然而在试验后，表示并没有解决问题ㄟ( ▔, ▔ )ㄏ。
# -*- coding: utf-8 -*-
# coding = utf-8
方案二 IDE 设置编码格式
虽然在 「偏好设置 - Editor - File Encodings」 内全都设置（默认设置）了 UTF-8，但是在读入数据文件 ipdata.csv 的时候，还是出现了错误。我也很奇怪为什么没有生效。
方案三 修改默认 encoding 格式
- 在代码头部导入 sys 库；
- 重载 sys 库，网上说 Python 文件运行后，会删除 setdefaultencoding 方法？
- 设置默认 encoding 格式。
import sys
reload(sys)
sys.setdefaultencoding('utf-8')
运行后显示找不到 setdefaultending 方法.
<img src="https://lh3.googleusercontent.com/-WkiXCB9eWCM/WnqrJSZdnTI/AAAAAAAAACg/eoGC0oY1UhgkWctNBjVyOtX1VCRWAzDgwCHMYCw/I/15179886333472.png" width = "140" alt="MySQL"/>
瞎捷豹愤而暴走，告诉我因为在 py3 中默认的编码格式为 unicode 解码？所以取消了 setdefaultending 方法。
正解：引入 codecs 库
codecs 库中的 open 方法可以添加 encoding 参数，完美解决。
import codecs
with open('./ipdata.csv', 'r', encoding='utf-8') as f:
lines = f.readlines()
后来发现其实 3.6 版本的 py 早就集成了 open 函数的 encoding 参数。所以并不用导入 codecs 库。
批量写入
插入操作的 sql 写法为 :
insert into tablename (key1, key2, key3...) values (value1, value2, value3...)
使用 cursor.executemany() 方法来执行批量插入操作
方法为：
cursor = conn.cursor()
ret = cursor.executemany('insert into ipdata (id, startip, endip, country, carrier) values (%s, %s, %s, %s, %s)', nl_p_list)
失去连接报错
2055: Lost connection to MySQL server at '127.0.0.1:3306', system error: 32 Broken pipe
猜测是数据量太大所致，所以对读入的文件进行切片，分批插入表中；
for i in range(int(len(nl_p_list)/1000 +1)):
tmp_nl_p_list = nl_p_list[i*1000: (i+1)*1000]
# 批量插入表中
ret = cursor.executemany('insert into ipdata (id, startip, endip, country, carrier) values (%s, %s, %s, %s, %s)',
tmp_nl_p_list)
数据库插入中文错误
出现了新的报错，查看报错信息：
1366 (HY000): Incorrect string value: '\xE4\xBF\x9D\xE7\x95\x99...' for column 'carrier' at row 1
对报错信息显示 异常的 string 值，截取报错信息交给瞎捷豹，很快找到了解决方案；还是编码格式锅，不过这次是表结构编码格式不符。
在 navicat 表设计 - DDL 中查看 ipdata 的表结构：
CREATE TABLE `ipdata` (
`id` int(11) NOT NULL,
`startip` bigint(20) DEFAULT NULL,
`endip` bigint(20) DEFAULT NULL,
`country` text,
`carrier` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
发现表编码为 latin1 。 果然不是 utf-8。通过 navicat 查询功能修改表编码格式，输入代码并执行：
alter table ipdata convert to character set utf8;
重新查看表结构：
CREATE TABLE `ipdata` (
`id` int(11) NOT NULL,
`startip` bigint(20) DEFAULT NULL,
`endip` bigint(20) DEFAULT NULL,
`country` text,
`carrier` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
重新运行代码，数据成功写入表中。
<img src="https://lh3.googleusercontent.com/-gm4UYcEpZz8/Wnu-RGDEn2I/AAAAAAAAAEg/IyM3nyyt4iAZLW9ukAUJwoGe7LsRhpbcQCHMYCw/I/15180590714358.png" width = "600" alt="MySQL3"/>
使用 navicat 的查询功能： select count(*) from ipdata 得到记录行数 444963，写入记录正常。
计划通！
参考链接：
『mysql.connector 批量插入』：https://my.oschina.net/hhdys412/blog/182762
源码地址:
『NiuCodeLesson/insertSQL/』：https://github.com/wengfe/NiuCodeLesson/tree/master/insertSQL
博客地址:
『wengfe.win』：http://www.wengfe.win/2018/02/07/2018-02-07/#more
