需求:
1、遍历data目录下所有的压缩包,找到目录名符合要求的,提取内容合适的数据
2、提取的1000W条数据上传到mysql中的一张表中
环境
操作系统:CentOS
linux判断cpu有多少个核心 cat /proc/cpuinfo cat /proc/cpuinfo| grep processor| wc -l
MySQL版本:5.7
Python系统自带2.7
安装所需要的包
mysqldb、scandir
- Centos上安装MySQL-python会报错缺失配置,先安装两个工具包
yum install mysql-devel
yum install python-devel
如果还报错,显示gcc编译问题
再安装gcc yum install gcc
这时再安装
pip install MySQL-python
import MySQLdb就可以使用了
- 遍历文件的模块scandir
pip install scandir
在Windows下测试,安装MySQLdb的问题
Windows下安装python2连接数据库驱动
访问 http://www.lfd.uci.edu/~gohlke/pythonlibs/,找到(Ctrl + f找快多了)MySQL_python-1.2.5-cp27-none-win_amd64.whl下载(这里是64位)
将其拷贝到python安装目录下的Scripts目录下,在文件位置打开cmd,执行pip install MySQL_python-1.2.5-cp27-none-win_amd64.whl.
1、遍历文件模块
假设每个文件的数据有10条,需要1000W条数据,只需要读取100W个文件
def findAllFile(base,filenum):
'''
查找目录下所有文件
:param base: 目标目录
filenum: 读取的文件数
:return: 返回文件路径列表,以及列表的长度
'''
filelist = []
num = 0
for root, ds, fs in scandir.walk(base): # 在windows在速度提升了一倍
# 把每个文件拼接成绝对路径,100W文件读取的内容应该够1000W条数据
for f in fs:
if num >= filenum:
break
elif re.match(r".*-20\d{12}-.*.zip", f):
num += 1
fullname = os.path.join(root, f)
filelist.append(fullname)
return filelist, num
使用os.walk()模块遍历目录下文件夹,改用scandir.walk()模块,速度至少能提升一倍(看操作系统和文件存储形式)
2、循环解压缩包(多线程调用),并读取内容和写入数据库
# 对zip文件读操作https://www.jianshu.com/p/25c8f765efb7
import zipfile
g_counterdata = 0 # 记录读取数据的条数
lock = threading.Lock() # 共享变量锁
def unzipAndReadTXT(file_names, start, end):
'''
读取文件并插入数据中
:param file_names: 读取文件的文件路径,是一个大列表
:param start: 从哪个位置开始读,做分块操作
:param end: 读取列表停止的位置
:return: None
'''
global DATA_NUM
global g_counterdata
datas = []
# 循环解压缩包
for i in xrange(start, end):
# print("opening zip", file_names[i])
myzip = zipfile.ZipFile(file_names[i], 'r') # 创建一个zipfile,读形式
txtname = myzip.namelist() # 返回压缩包里面的文件个数
for file in txtname:
txtcontent = myzip.open(file).readlines() # 返回一个文件的内容
# print(txtcontent)
# print(file + "File content read and insert......")
# 将txt文件内容读取出来
for onelinedata in txtcontent:
data = division(onelinedata)
# 不符合读取条件的文件内容,就跳过
if data == None:
print(file)
continue
datas.append(data)
with lock:
if g_counterdata >= DATA_NUM:
break
g_counterdata += 1
# 正常循环完将一个文件内容插入数据到datas中,就执行一下语句
else:
# print(file + " insert success!!!")
continue # 继续下次循环文件读取
# 没有执行上面语句块,说明是通过break跳出的,再次跳出上层for
break
else:
continue
break
mydatabase = myMysql()
# 插入数据库
mydatabase.insert_sql(datas)
print("insert data number", g_counterdata)
xrange 用法与 range 完全相同,所不同的是生成的不是一个list对象,而是一个生成器。要生成很大的数字序列的时候,用xrange会比range性能优很多,因为不需要一上来就开辟一块很大的内存空间。
3、读取文件里的内容,并提取所需要的数据
文件的内容可能有些不统一,需要判断,但是分割线是一样的
from datetime import datetime
import _strptime
'''
多线程下会报 AttributeError: 'module' object has no attribute '_strptime' 这个错误
编译器没有显式地调用这个模块,但是在多线程下执行strptime()方法会引用这个模块
在调用 time.strptime(str,format) 这个方法的python文件中引用 '_strptime'模块
import _strptime
'''
def division(aline):
'''
处理每一行数据,做分离操作
:param aline: 一行数据
:return: 返回一条处理好的元组,出现异常则返回None
'''
temp = aline.split("|")
# 用来存储每一条数据的列表
res = []
# 只取指定位置的数据
for i in temp:
if i != '':
res.append(i)
# for i in range(0,5):
# if i == 0 or i == 1 or i==4:
# res.append(temp[i])
try:
data_type = (int(res[0])%100)/10
# 判断是否为2g数据,如果是则把时间数据覆盖前面的数据
if 2 == data_type:
res[2] = res[3]
# 格式化为时间字符串
date = datetime.strptime(res[2], "%Y%m%d%H%M%S")
res[2] = date.strftime("%Y-%m-%d %H:%M:%S")
except ValueError:
# 发现匹配值不是时间的时候,就返回None,跳过这个文件
print(res)
print("this file one data wrongful ", ValueError)
return None
return tuple(res[0:3])
4、连接数据库的类
import MySQLdb
import traceback
# 数据库线程池 https://www.jb51.net/article/214529.htm
class myMysql:
def __init__(self, ip=None, usr=None, pwd=None, database=None):
# 打开数据库连接 root(用户名) 123456(密码) java(数据库名称)
self.__db = MySQLdb.connect("192.168.0.194",
"root",
"@Aa123456",
"数据库名",
charset='utf8')
def insert_sql(self, datas):
# 使用cursor()方法获取操作游标
cursor = self.__db.cursor()
try:
# 使用execute方法执行SQL语句
# 一个列表,列表中的每一个元素必须是元组(一行数据)!!!
n = 100000 # 按每10万行数据为最小单位拆分成嵌套列表
data1 = [datas[i:i + n] for i in xrange(0, len(datas), n)] # 列表生成式
sql = "insert into xxx_table(xxx, xxx, xxx_time) values (%s, %s, %s)"
while data1:
data = data1.pop() # 弹出
cursor.executemany(sql, data)
self.__db.commit()
except:
# 发生异常回滚
self.__db.rollback()
traceback.print_exc()
finally:
cursor.close()
def __del__(self):
self.__db.close()
插入数据使用executemany()函数,可以批量提交多条数据,执行速度得到提升
数据参数时一个列表,列表中的每一个元素(一条数据)必须是元组。
5、创建多线程执行解压缩文件
def myrun(dirs):
'''
主运行程序,多线程执行解包程序
:param dirs: 需要遍历的文件根目录,写得越细遍历越快
:return:
'''
global DATA_NUM
filenum = DATA_NUM/10 # 假设每个文件有10条数据,需要遍历的文件数
# 定义6个线程
threadnum = 6
allfile, num = findAllFile(dirs, filenum)
# 将文件路径列表分割成线程数的份数
interval = int(num / threadnum)
start = time.time()
threads = []
for x in range(0, threadnum):
# 创建线程,传入分割文件的开始和结束位置,allfile是遍历出所有文件目录的列表
t = threading.Thread(target=unzipAndReadTXT, args=(allfile, x * interval, (x + 1) * interval))
threads.append(t)
# 启动线程
t.start()
print(t.name, "Theard start .......................")
# 等待线程结束
for tj in threads:
tj.join()
end = time.time()
print("spend time ", end - start, " sec")
传入需要遍历的文件根目录,全局变量DATA_NUM注明了需要插入的数据条数,这里可以自定义创建线程的数量
有可能遇到的坑
只用多线程创建数据库连接时,Thread传参问题
多线程创建数据库连接时会出错
t = threading.Thread(target=myrun, args=(datas1,))
使用threading创建线程时,传递参数args必须是元组,单个参数时要加,才表示元组
运行出现问题:
到程序最后提交数据时,出现
OperationalError: (2006, ‘MySQL server has gone away’)
原因是导入的数据超过单次的默认量。即超过 max_allowed_packet
通过命令查询,现数据库1000条数据占用:
Mysql默认数据一次提交的数据最大为4M
数据库中有了数据,查看已有数据占用的空间:
sql命令
select concat(truncate(sum(data_length)/1024/1024,2),'MB') as data_size,
concat(truncate(sum(max_data_length)/1024/1024,2),'MB') as max_data_size,
concat(truncate(sum(data_free)/1024/1024,2),'MB') as data_free,
concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size
from information_schema.tables
where TABLE_NAME = '你要查的数据表名';
1千多条数据,减去data_free(说是碎片啥的),那么大概占了1.5M,如果一次性插入10W条数据,那么要设置max_allowed_packet = 150M
通过进入数据库修改配置:设置大点也无所谓(这里设了300M)
set global max_allowed_packet = 3*1024*1024*100
最后程序的总运行时间:差不多14分钟