1. 前言

最近在看极客学院的视频， 看到有关数据库部分， 然后里面的老师给了一个 ipdata.sql 的数据库指令， 可以用来向mysql中导入ipdata数据， 可是， 我们没有拿到这个 sql 数据库的指令， 因而没有办法构建出我们自己的数据库， 这个对于有些强迫症的我来说， 有些费劲

2. 流程

2.1 网上一般流程

Created with Raphaël 2.1.0

start

下载最新版本的QQWry.Dat

下载IPLook， 提取IPDATA.txt

使用Navicat for MySQL导入

end

不过这个文件需要安装之后， 才能找到我们需要的QQWry.dat 文件， 还是比较费劲的

不过后来我们找到一个 IPLook 文件的时候， 里面附带了这个我们所需要的 QQwry.dat 文件了 http://soft.duote.com.cn/iplook.zip

Navicat for mysql 的官网地址 https://www.navicat.com.cn/products/navicat-for-mysql， 但是， 为了一个数据文件， 需要特定安装一个软件还是太费劲了， 于是我们试图自己编程实现。

2.2 我们的流程

Created with Raphaël 2.1.0

start

下载IPLook， 提取IPDATA.txt

使用python编程导入mysql

end

2.3 提取文件数据

这里涉及python 的文件操作， 首先我们需要处理的IPData.txt 中的数据格式为：

很明显， 文件中的数据是非常有规律的， 这样就可以很方便的提取了

def LoadData(self):

# load data from ipdata.txt

fd = open(self.filename, "r")

data = fd.readlines()

for line in data:

line = line.decode(self.filecode, "ignore")

items = line.split('\t')

items_new = []

for item in items:

items_new.append(item)

value = tuple(items_new)

#print(line, *value)

self.values.append(value)

self.values = self.values[1:]

2.4 发送数据给mysql

通过2.3 中的LoadData 我们将所需要的数据提取到了一个列表中， 下面就需要考虑如何将数据发送到 mysql 中了。这里我们采用 mysql connector 进行连接。

2.4.1 遇到的一些小坑

提示 数据包太大

同时在 mysql 官方文档中也提示了 B.5.2.3 Lost connection to MySQL server 的原因

以及关键参数的解释 max_allowed_packet

于是， 我们可以采用 将max_allowed_packet 增大到 64M 来处理这个问题， 而默认值为 1M

mysqld --console --max_allowed_packet=67108864

数据表项长度不合理， 部分local 数据字段长度太长， 无法插入， 修改数据表字段长度即可

2.4.2 实现代码

def InsertIntoDB(self):

# connect to the db and insert data

self.LoadData()

db = connector.Connect(host = "127.0.0.1",

user="root",

passwd="zhyh2010",

charset="utf8",

database="pythontest")

cursor = db.cursor()

print cursor.execute(self.DROP_TABLE)

print cursor.execute(self.CREATE_TABLE)

print cursor.executemany(self.sql_model, self.values)

db.commit()

db.close()

3. 最终代码

# coding:utf8

''' 主要目的： 将 ipdata.txt 中的数据导入到我们的数据库中 author ; zhyh2010 create in 2016.05.30 '''

#from __future__ import print_function

from mysql import connector

class Ipdata:

''' 将 ipdata.txt 中的数据导入到我们的数据库中 主要分为两个模块， 数据库操作， 文件操作 '''

def __init__(self):

self.DROP_TABLE = 'drop table if exists ipdata2;'

self.CREATE_TABLE = ' create table ipdata2( \ id bigint(20) not null auto_increment primary key, \ startip varchar(45) default null, \ endip varchar(45) default null, \ country varchar(45) default null, \ local varchar(300) default null \ ) charset = utf8;'

self.sql_model = 'insert into `ipdata2` (`startip`, `endip`, `country`, `local`) values(%s, %s, %s, %s)'

self.values = []

self.filename = "IPData.txt"

self.filecode = "gb2312"

def LoadData(self):

# load data from ipdata.txt

fd = open(self.filename, "r")

data = fd.readlines()

for line in data:

line = line.decode(self.filecode, "ignore")

items = line.split('\t')

items_new = []

for item in items:

items_new.append(item)

value = tuple(items_new)

#print(line, *value)

self.values.append(value)

self.values = self.values[1:]

def InsertIntoDB(self):

# connect to the db and insert data

self.LoadData()

db = connector.Connect(host = "127.0.0.1",

user="root",

passwd="zhyh2010",

charset="utf8",

database="pythontest")

cursor = db.cursor()

print cursor.execute(self.DROP_TABLE)

print cursor.execute(self.CREATE_TABLE)

print cursor.executemany(self.sql_model, self.values)

db.commit()

db.close()

if __name__ == "__main__":

ipdata = Ipdata()

ipdata.InsertIntoDB()

4. 插入效果

自此 IPData.dat 数据已经完全插入到了mysql 中了

mysqldump -hlocalhost -uroot -p pythontest ipdata2 > ipdata2.sql

效果图：