Python 基于Python从mysql表读取千万数据实践

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: Python 基于Python从mysql表读取千万数据实践

基于Pythonmysql表读取千万数据实践

 

by:授客QQ1033553122

场景:

 

有以下两个表,两者都有一个表字段,名为waybill_no,我们需要从tl_waybill_bar_record表读取1000w条唯一的waybill_no,然后作为INSERT SQL语句的一部分,填充到ts_order_waybillwaybill_no字段中

 

tl_waybill_bar_record

 

ts_order_waybill

 

另外tl_waybill_bar_recordwaybill_no有部分重复

 

实现思路

思路1、利用MySqlLIMIT offset, length分页功能+ORDER BY primary_key按主键排序,循环读取数据,然后解析读取的数据,直到满足条件停止

 

例子:按5000条记录进行分页,循环2000000,从第0条记录开始,按seq_id主键升序排序,每次从不同的分页读取5000条记录

for i in range(0, 2000000):

query =  "SELECT waybill_no, seq_id FROM ts_order_waybill ORDER BY seq_id LIMIT'%s', 5000" % str(i*5000)

result = test_platform_db.select_many_record(query)

……(后续处理)

 

说明:这种方式,有一定的局限性,分页数量似乎会受限制,似乎只能8000多页,另外当offset达一定值(55w)的样子,似乎会变得很卡,数据量较少的情况下,可以考虑这么做

 

注意:这里如果不适用ORDER BY语句,可能在不同分页取数据时,会取到重复的数据

 

 

思路2、先SELECT MIN(primary_key)查询最小主键值key_min_value,然后每次查询时加WHERE primary_key>=key_min_value,并且加ORDER BY primary_key按主键升序排序,同时使用LIMIT length限制每次返回数据量大小

 

min_seq_id = '18e23705987643f9b84cd8c4c3efc47868944d88cb71480ea4b7659d2c9c6cba'

for i in range(0, 2000000):

query =  SELECT waybill_no, seq_id FROM ts_order_waybill WHERE seq_id>='%s' ORDER BY seq_id LIMIT 5000" % min_seq_id

result = test_platform_db.select_many_record(query)

……(后续处理)

 

 

问题

跑程序的时候,经常会突然卡死,可能是Python类库自身原因,也可能是数据库请求过于频繁,这样会导致获取的结果丢失,需要重头开始跑

 

解决方法:

一边跑,一边写入本地文件,同时输出上次读取的记录的位置(思路2来说,就是那个最小主键值),然后重新跑程序时,替换下初始值即可。

 

关键代码

 

test_db = MyDB('MYDB')

 

file_handler = open('./result/waybill_no.txt','r+', encoding='utf-8')

 

waybill_no_set = set()# 用于存储获取的waybill_no

 

# 读取上次获取的数据

line = file_handler.readline()

line = line.strip()# 去掉换行符

whileline:

   waybill_no_set.add(line)

   line = file_handler.readline()

   line = line.strip()

 

# 初始最小主键值

min_seq_id ='18e23705987643f9b84cd8c4c3efc47868944d88cb71480ea4b7659d2c9c6cba'

 

count = len(waybill_no_set)# 初始化以读取的waybill_no总数

foriinrange(0, 2000000):

   query =  "SELECT waybill_no,seq_id FROM ts_order_waybill WHERE seq_id>='%s' ORDER BY seq_id LIMIT 5000"% min_seq_id

   result = test_db.select_many_record(query)

   ifresult[0]andresult[1]:

       foriteminresult[1]:

           waybill_no = item[0]

           ifwaybill_nonot inwaybill_no_set:

               waybill_no_set.add(waybill_no)

               min_seq_id = item[1]

 

               count += 1

               print(count)

               print(min_seq_id)  # 记录上次读取的最后位置

 

               file_handler.write(waybill_no)

               file_handler.write('\n')

               file_handler.flush()

 

 

   print('已获取waybill_no总数:',  count)

   ifcount > 10000000:

       break

 

# 插入数据

for waybill_no in waybill_no_set:

time.sleep(0.1)

   insert_sql = "insert into `tl_waybill_bar_record` (`waybill_no`, `op_code`, `bar_Scan_Tm`, `ows_remark`, `stay_why_code`, `extend_attach1`, `extend_attach2`, `extend_attach3`, `extend_attach4`, `extend_attach5`, `op_name`, `dist_name`, `outside_name`, `old_waybill_no`, `courier_code`, `stay_why_name`, `create_time`) " \

      "values('%s','31','2018-05-09 20:02:33','广州市',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2018-05-05 15:56:09');" % waybill_no

test_db.execute_insert(insert_sql, '')

   count = count + 1

   print(waybill_no)

   print(count)

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
29天前
|
关系型数据库 MySQL 数据库
Python处理数据库:MySQL与SQLite详解 | python小知识
本文详细介绍了如何使用Python操作MySQL和SQLite数据库,包括安装必要的库、连接数据库、执行增删改查等基本操作,适合初学者快速上手。
196 15
|
23天前
|
缓存 关系型数据库 MySQL
MySQL 索引优化与慢查询优化:原理与实践
通过本文的介绍,希望您能够深入理解MySQL索引优化与慢查询优化的原理和实践方法,并在实际项目中灵活运用这些技术,提升数据库的整体性能。
66 5
|
24天前
|
存储 关系型数据库 MySQL
mysql怎么查询longblob类型数据的大小
通过本文的介绍,希望您能深入理解如何查询MySQL中 `LONG BLOB`类型数据的大小,并结合优化技术提升查询性能,以满足实际业务需求。
89 6
|
1月前
|
存储 关系型数据库 MySQL
PHP与MySQL动态网站开发:从基础到实践####
本文将深入探讨PHP与MySQL的结合使用,展示如何构建一个动态网站。通过一系列实例和代码片段,我们将逐步了解数据库连接、数据操作、用户输入处理及安全防护等关键技术点。无论您是初学者还是有经验的开发者,都能从中获益匪浅。 ####
|
1月前
|
SQL 关系型数据库 MySQL
mysql分页读取数据重复问题
在服务端开发中,与MySQL数据库进行数据交互时,常因数据量大、网络延迟等因素需分页读取数据。文章介绍了使用`limit`和`offset`参数实现分页的方法,并针对分页过程中可能出现的数据重复问题进行了详细分析,提出了利用时间戳或确保排序规则绝对性等解决方案。
|
2月前
|
关系型数据库 MySQL 数据库
GBase 数据库如何像MYSQL一样存放多行数据
GBase 数据库如何像MYSQL一样存放多行数据
|
2月前
|
关系型数据库 MySQL Java
MySQL索引优化与Java应用实践
【11月更文挑战第25天】在大数据量和高并发的业务场景下,MySQL数据库的索引优化是提升查询性能的关键。本文将深入探讨MySQL索引的多种类型、优化策略及其在Java应用中的实践,通过历史背景、业务场景、底层原理的介绍,并结合Java示例代码,帮助Java架构师更好地理解并应用这些技术。
60 2
|
2月前
|
存储 监控 关系型数据库
MySQL自增ID耗尽解决方案:应对策略与实践技巧
在MySQL数据库中,自增ID(AUTO_INCREMENT)是一种特殊的属性,用于自动为新插入的行生成唯一的标识符。然而,当自增ID达到其最大值时,会发生什么?又该如何解决?本文将探讨MySQL自增ID耗尽的问题,并提供一些实用的解决方案。
56 1
|
1月前
|
SQL 关系型数据库 MySQL
PHP与MySQL的高效交互:从基础到实践####
本文深入探讨了PHP与MySQL数据库之间的高效交互技术,涵盖了从基础连接到高级查询优化的全过程。不同于传统的摘要概述,这里我们直接以一段精简代码示例作为引子,展示如何在PHP中实现与MySQL的快速连接与简单查询,随后文章将围绕这一核心,逐步展开详细讲解,旨在为读者提供一个从入门到精通的实战指南。 ```php <?php // 数据库配置信息 $servername = "localhost"; $username = "root"; $password = "password"; $dbname = "test_db"; // 创建连接 $conn = new mysqli($se
30 0
|
15天前
|
存储 Oracle 关系型数据库
数据库传奇:MySQL创世之父的两千金My、Maria
《数据库传奇:MySQL创世之父的两千金My、Maria》介绍了MySQL的发展历程及其分支MariaDB。MySQL由Michael Widenius等人于1994年创建,现归Oracle所有,广泛应用于阿里巴巴、腾讯等企业。2009年,Widenius因担心Oracle收购影响MySQL的开源性,创建了MariaDB,提供额外功能和改进。维基百科、Google等已逐步替换为MariaDB,以确保更好的性能和社区支持。掌握MariaDB作为备用方案,对未来发展至关重要。
40 3