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

原创 2016年06月01日 00:20:47

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

效果图:
这里写图片描述

版权声明:本文为博主原创文章,未经博主允许不得转载。

Delphi7高级应用开发随书源码

  • 2003年04月30日 00:00
  • 676KB
  • 下载

dat文件如何导入mysql数据库

将movielens数据导入mysql数据库

数据库的.dat文件导入MySQL

在使用MySQL的过程中经常需要进行数据的导入导出,但通常操作的对象都是.sql类型的数据文件。数据库文件还有一种类型格式:.dat文件。这种文件可以使用记事本或写字板打开,打开后内容如图所示:通常一...
  • c1958
  • c1958
  • 2015年04月17日 16:11
  • 1636

用Derby数据库读取加密的DAT数据文件(一)

应某些需要,需要读取从Derby数据库中导出的dat加密文件。所以喽,现在手上有dat文件,要正确读取就要反过来。         后来发现需要开始做一些准备工作,比如:Derby的下载安装,环境的...

使用MYSQL命令直接导入导出SQL文件

1.MYSQL中将数据库导出成SQL文件 其实很简单的,就是一条语句就可以了,首先我们打开cmd,不用进mysql指令界面,直接按照下列格式将导出语句敲进去,然后再输入密码即可了: mysqldu...

java读取纯真IP数据库qqwry.dat的源代码

java读取纯真IP数据库QQwry.dat的源代码,要运行此程序必须有到网上下载QQwry.dat,下载地址 http://www.cz88.net/down/   由于太大,我这里就不提供了。 ...
  • syc001
  • syc001
  • 2017年06月02日 10:10
  • 1786

Delphi7高级应用开发随书源码

  • 2003年04月30日 00:00
  • 676KB
  • 下载

全国城市IP库-提供下载

项目需要江苏的城市ip库,找了几个都是QQ纯真级别的,太过于详细了,我没有必要去具体到哪个网吧,辗转,博客园也没有这种需求的库,功夫不负有心人,还是找到了。在这里把它上传到博客园。全国ip库下载:城市...

通过IP地址获取当前地理位置(省份)的接口

1.淘宝接口通过ip获取地址 /** * 通过淘宝IP地址库获取IP位置 *1. 请求接口(GET):http://ip.taobao.com/service/getIpInfo.ph...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:将ipdata.dat 数据集导入 mysql 中并获取sql文件
举报原因:
原因补充:

(最多只允许输入30个字)