對(duì)數(shù)據(jù)庫(kù)的增刪改查平時(shí)的工作中用的比較多，也比較熟悉奖地。忽然發(fā)現(xiàn)都沒(méi)有使用過(guò) insert 操作，因此在學(xué)習(xí) python 的過(guò)程中，通過(guò) mysql.connector 對(duì) 44w行 的 ip地址記錄進(jìn)行數(shù)據(jù)庫(kù)批量插入工作赞哗。
另：簡(jiǎn)書(shū)不支持圖片外鏈，我沒(méi)找到什么好方法來(lái)上傳圖片辆雾，如果有需要看圖片的肪笋，可以移步到我的博客中去看，本篇文章的博客地址在本文末乾颁。
步驟分為以下五步：
1. 數(shù)據(jù)格式整理
2. 創(chuàng)建表
3. 連接表
4. 讀取本地文件數(shù)據(jù)
5. 批量寫(xiě)入
數(shù)據(jù)格式整理
首先查看所拿到的數(shù)據(jù)涂乌，并整理：
分別在終端中使用 head ipdata.csv 和 tail ipdata.csv 來(lái)查看文件的頭部和尾部數(shù)據(jù)，對(duì)其規(guī)律進(jìn)行分析英岭。
:~/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服務(wù)器
7,1.0.32.0,1.0.63.255,廣東省,電信
8,1.0.64.0,1.0.127.255,日本,広島県中區(qū)大手町Energia通信公司
9,1.0.128.0,1.0.255.255,泰國(guó),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保留地址,用于多點(diǎn)傳送
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,純真網(wǎng)絡(luò),2014年10月25日IP數(shù)據(jù)
查看后我們可以大致將數(shù)據(jù)分為 5 列湾盒，「序號(hào)」，「起始IP」诅妹，「終止IP」罚勾，「所在地」毅人，「運(yùn)營(yíng)商」。
創(chuàng)建表
經(jīng)過(guò)格式的分析尖殃，我們就可以創(chuàng)建一個(gè)表的結(jié)構(gòu)了丈莺。
注：事實(shí)上分析數(shù)據(jù)規(guī)律比建表要重要的多，因?yàn)楸疚闹攸c(diǎn)不在此處送丰，所以概過(guò)缔俄。
首先通過(guò) navicat 創(chuàng)建本地的數(shù)據(jù)庫(kù)。數(shù)據(jù)庫(kù)格式如下圖：
[圖片上傳失敗...(image-e6819d-1518059726027)]
連接表
首先連接本地?cái)?shù)據(jù)庫(kù)
因?yàn)槲业谋硎緞?chuàng)建在本地?cái)?shù)據(jù)庫(kù)下的器躏，連接的庫(kù)的時(shí)候不需要添加 host 參數(shù)
連接本地?cái)?shù)據(jù)庫(kù)的代碼如下：
conn = mysql.connector.connect(user='root', password='admin', database='test')
連接遠(yuǎn)程數(shù)據(jù)庫(kù)的代碼如下：
conn = connection.MySQLConnection(host=host_d, user=user_d, password=password_d, database=database_d)
讀取 csv 數(shù)據(jù)文件
一般來(lái)說(shuō)俐载，讀取文件的方法有兩種；推薦以下方式來(lái)讀取文件登失，可以避免忘記關(guān)閉文件句柄的尷尬：
with open('./ipdata.csv', 'r') as f:
lines = f.readlines()
讀取文件格式采坑
在讀取文件的過(guò)程中發(fā)現(xiàn)了報(bào)錯(cuò)遏佣。
報(bào)錯(cuò)信息：
UnicodeDecodeError: 'ascii' codec can't decode byte 0xe4 in position 29: ordinal not in range(128)
原因分析：
出錯(cuò)的原因是因?yàn)?csv 內(nèi)的編碼方式與程序環(huán)境編碼方式不一致所致。csv 的編碼格式是 gbk揽浙， 只要能夠用 unicode 編碼格式處理讀取進(jìn)來(lái)的數(shù)據(jù)就解決了状婶。
搜索解決方案：
自從上次瞎捷豹連續(xù)操勞后，已經(jīng)很久沒(méi)有讓它出門(mén)了馅巷，這次又要有勞它了膛虫。
經(jīng)過(guò)搜索后，發(fā)現(xiàn)很多方案：
方案一 代碼頭部申明編碼
有兩種寫(xiě)法令杈，然而在試驗(yàn)后走敌，表示并沒(méi)有解決問(wèn)題ㄟ( ▔, ▔ )ㄏ。
# -*- coding: utf-8 -*-
# coding = utf-8
方案二 IDE 設(shè)置編碼格式
雖然在 「偏好設(shè)置 - Editor - File Encodings」 內(nèi)全都設(shè)置（默認(rèn)設(shè)置）了 UTF-8逗噩，但是在讀入數(shù)據(jù)文件 ipdata.csv 的時(shí)候掉丽，還是出現(xiàn)了錯(cuò)誤。我也很奇怪為什么沒(méi)有生效异雁。
方案三 修改默認(rèn) encoding 格式
- 在代碼頭部導(dǎo)入 sys 庫(kù)捶障；
- 重載 sys 庫(kù)，網(wǎng)上說(shuō) Python 文件運(yùn)行后纲刀，會(huì)刪除 setdefaultencoding 方法项炼？
- 設(shè)置默認(rèn) encoding 格式。
import sys
reload(sys)
sys.setdefaultencoding('utf-8')
運(yùn)行后顯示找不到 setdefaultending 方法.
<img src="https://lh3.googleusercontent.com/-WkiXCB9eWCM/WnqrJSZdnTI/AAAAAAAAACg/eoGC0oY1UhgkWctNBjVyOtX1VCRWAzDgwCHMYCw/I/15179886333472.png" width = "140" alt="MySQL"/>
瞎捷豹憤而暴走示绊，告訴我因?yàn)樵?py3 中默認(rèn)的編碼格式為 unicode 解碼锭部？所以取消了 setdefaultending 方法。
正解：引入 codecs 庫(kù)
codecs 庫(kù)中的 open 方法可以添加 encoding 參數(shù)面褐，完美解決拌禾。
import codecs
with open('./ipdata.csv', 'r', encoding='utf-8') as f:
lines = f.readlines()
后來(lái)發(fā)現(xiàn)其實(shí) 3.6 版本的 py 早就集成了 open 函數(shù)的 encoding 參數(shù)。所以并不用導(dǎo)入 codecs 庫(kù)展哭。
批量寫(xiě)入
插入操作的 sql 寫(xiě)法為 :
insert into tablename (key1, key2, key3...) values (value1, value2, value3...)
使用 cursor.executemany() 方法來(lái)執(zhí)行批量插入操作
方法為：
cursor = conn.cursor()
ret = cursor.executemany('insert into ipdata (id, startip, endip, country, carrier) values (%s, %s, %s, %s, %s)', nl_p_list)
失去連接報(bào)錯(cuò)
2055: Lost connection to MySQL server at '127.0.0.1:3306', system error: 32 Broken pipe
猜測(cè)是數(shù)據(jù)量太大所致湃窍，所以對(duì)讀入的文件進(jìn)行切片闻蛀，分批插入表中；
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)
數(shù)據(jù)庫(kù)插入中文錯(cuò)誤
出現(xiàn)了新的報(bào)錯(cuò)您市，查看報(bào)錯(cuò)信息：
1366 (HY000): Incorrect string value: '\xE4\xBF\x9D\xE7\x95\x99...' for column 'carrier' at row 1
對(duì)報(bào)錯(cuò)信息顯示 異常的 string 值觉痛，截取報(bào)錯(cuò)信息交給瞎捷豹，很快找到了解決方案茵休；還是編碼格式鍋薪棒，不過(guò)這次是表結(jié)構(gòu)編碼格式不符。
在 navicat 表設(shè)計(jì) - DDL 中查看 ipdata 的表結(jié)構(gòu)：
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;
發(fā)現(xiàn)表編碼為 latin1 泽篮。 果然不是 utf-8盗尸。通過(guò) navicat 查詢功能修改表編碼格式，輸入代碼并執(zhí)行：
alter table ipdata convert to character set utf8;
重新查看表結(jié)構(gòu)：
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;
重新運(yùn)行代碼帽撑，數(shù)據(jù)成功寫(xiě)入表中。
<img src="https://lh3.googleusercontent.com/-gm4UYcEpZz8/Wnu-RGDEn2I/AAAAAAAAAEg/IyM3nyyt4iAZLW9ukAUJwoGe7LsRhpbcQCHMYCw/I/15180590714358.png" width = "600" alt="MySQL3"/>
使用 navicat 的查詢功能： select count(*) from ipdata 得到記錄行數(shù) 444963鞍时，寫(xiě)入記錄正常亏拉。
計(jì)劃通！
參考鏈接：
『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
