dat文件导入到sqlite

1. 前言

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

2. 流程

2.1 网上一般流程

1. 下载地址： http://update.cz88.net/soft/setup.zip
2. 不过这个文件需要安装之后， 才能找到我们需要的QQWry.dat 文件， 还是比较费劲的
3. 不过后来我们找到一个 IPLook 文件的时候， 里面附带了这个我们所需要的 QQwry.dat 文件了 http://soft.duote.com.cn/iplook.zip
4. Navicat for mysql 的官网地址 https://www.navicat.com.cn/products/navicat-for-mysql， 但是， 为了一个数据文件， 需要特定安装一个软件还是太费劲了， 于是我们试图自己编程实现。

2.2 我们的流程

2.3 提取文件数据

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

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

需要特别注意的是， 文件中涉及中文字符， 需要额外解码操作 文件读取操作
2. https://docs.python.org/2/library/stdtypes.html?highlight=split#str.split

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 遇到的一些小坑

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

1. 数据表项长度不合理， 部分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 中了

然后参考文章： 我们可以导出相应的 sql 文件

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

效果图：