关闭

将ipdata.dat 数据集导入 mysql 中并获取sql文件

标签: mysql数据库
1654人阅读 评论(0) 收藏 举报
分类:

1. 前言

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

2. 流程

2.1 网上一般流程

http://blog.csdn.net/yual365/article/details/9965711

Created with Raphaël 2.1.0start下载最新版本的QQWry.Dat下载IPLook, 提取IPDATA.txt使用Navicat for MySQL导入end
  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 我们的流程

Created with Raphaël 2.1.0start下载IPLook, 提取IPDATA.txt使用python编程导入mysqlend

2.3 提取文件数据

这里涉及python 的文件操作, 首先我们需要处理的IPData.txt 中的数据格式为:
这里写图片描述
很明显, 文件中的数据是非常有规律的, 这样就可以很方便的提取了

需要特别注意的是, 文件中涉及中文字符, 需要额外解码操作
文件读取操作, 参考了文章: 1. http://www.cnblogs.com/sysuoyj/archive/2012/03/14/2395789.html
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 中了

然后参考文章:http://blog.csdn.net/zolalad/article/details/11394829 我们可以导出相应的 sql 文件

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

效果图:
这里写图片描述

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:367786次
    • 积分:7045
    • 等级:
    • 排名:第3211名
    • 原创:338篇
    • 转载:120篇
    • 译文:0篇
    • 评论:48条
    最新评论