python+mysql:实现一千万条数据插入数据库_openpyxl 每10000行批量插入mysql

link之家

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

实现思路如下：

复制原数据库moviedb至newmoviedb
设置max_allowed_packet，以保证一条insert语句可以插入足够多的元组
用python生成一条一次插入10000个元组的insert语句
用python生成一个包含100条insert语句的事务，保存至sql文件
用navicat运行该sql文件

至此就可以实现一百万条记录的插入了（我的电脑耗时327s？好像很慢！？）

之后再套一层循环就可以继续完成1千万条记录的插入了。

下面给出可能用到的步骤（如未说明，代码默认为控制行或者mysql环境下的命令）：

复制数据库

创建新数据库newmoviedb

登录并创建数据库：

mysql -u root -p
CREATE DATABASE `newmoviedb` DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;
复制moviedb至newmoviedb 
复制数据库 
mysqldump moviedb -u root -pchouxianyu --add-drop-table | mysql newmoviedb -u root -pchouxianyu
上面chouxianyu是我的mysql密码 
进入newmoviedb 
use newmoviedb;
设置max_allowed_packet
 设置max_allowed_packet为100M 
set global max_allowed_packet = 100*1024*1024;
删除movies中所有元素（调试用） 
delete from movies;
生成一条insert语句
 下边是insert.py 
遇到问题没人解答？小编创建了一个Python学习交流QQ群：778463939
寻找有志同道合的小伙伴，互帮互助,群里还有不错的视频学习教程和PDF电子书！
insertStr = "INSERT INTO movies(title,year,length,movietype,studioname,producerC) VALUES"
value1_str = "('mymovietitle',"
value2_str = ",120,'sciFic','MGM',100)"
num_value = 10000
f = open(r'C:\Users\Cxy\Documents\Navicat\MySQL\Servers\MySQL\newmoviedb\insertRow.sql', 'w')  # 清空文件内容再写
f.write(insertStr)
for j in range(1, num_value):
    f.write(value1_str)
    f.write(str(j))
    f.write(value2_str)
    f.write(',')
f.write(value1_str)
f.write(str(num_value))
f.write(value2_str)
f.write(';')
f.close()
生成一个事务
 以下是transaction.py 
transaction_begin_str = "START TRANSACTION;\n"
transaction_end_str = "COMMIT;\n"
insertStr = "INSERT INTO movies(title,year,length,movietype,studioname,producerC) VALUES"
value1_str = "('mymovietitle',"
value2_str = ",120,'sciFic','MGM',100)"
num_value = 10000
num_sql = 100
# 打开文件
f = open(r'C:\Users\Cxy\Documents\Navicat\MySQL\Servers\MySQL\newmoviedb\transaction.sql', 'w')  # 清空文件内容再写
# 将SQL语句写入文件
f.write(transaction_begin_str)
for i in range(1, num_sql+1):
    f.write(insertStr)
    for j in range(1, num_value):
        f.write(value1_str)
        f.write(str(i*num_value*10+j))
        f.write(value2_str)
        f.write(',')
    f.write(value1_str)
    f.write(str(i*num_value*10+num_value))
    f.write(value2_str)
    f.write(';\n')
f.write(transaction_end_str)
# 关闭文件
f.close()
                        原文链接：https://www.jianshu.com/u/8f2987e2f9fb
大数据测试，有时候需要造大量数据（造数可以看我另外一篇文章），而且需要经常使用脚本快速插入数据，插入数据一般常用的两种方式，一种是接口，一种是数据库插入。按照正常的逻辑，使用接口造数是最好的方式，因为不需要去关注数据库字段关联的问题，但接口会有瓶颈，需要依赖接口的稳定型以及性能。如果接口的稳定性，性能不行，那就只能是使用数据库插入的方式进行了。
根据业务逻辑，一个流程当作一个事务，...
                                    网上关于python操作db2的文章很少，特此记录。
目录1. 最终解决方案2. 探索1：load & import3. 探索2：to_sql4. 探索3: excutemany(sql,param)5. 遇到的bug
Python 3.8.6
ibm_db_sa 0.3.7
ibm-db 3.0.4
sqlalchemy 1.4.7
1. 最终解决方案
采用拼接字符串的方法，把要批量插入的数据拼接成insert into table values (),...,(),()这种形式。
实现过程：
思路一 很快就被否定了，因为在写入txt的文件过程中，随着数据量越来越大，速度成线性下降，导致代码跑了一晚上也没写完，更别说插入了，即使用load data也没法补救前期写数据的时间。
思路二 在实现的过程中变量随着数据堆
                                    用python往mysql插入数据首先必须要安装pymysql库，安装命令：pip install Pymysql,注意Pymysql中的"P"要大写
插入数据格式为：fsy001到fsy100，总共字符数位6位，为了保持总字符数不变，需要用到if判断
import pymysql
'''连接数据库，包括数据库ip、端口、用户名、密码、数据库'''
conn =pymysql.connect(host='localhost',user='root',passwd='password',db='databas
                                    测试页面分页时，数据量不够，奈何SQL没学好，不会写存储过程批量插入数据，向易前辈请教了一下，他用的是excel表格转成insert语句，再插入，似乎用了转换工具，查了一下，按照网上的方法填入excel公式，结果还是没转换成insert into语句。感觉自己好菜。
回到宿舍，才想起来写一个python脚本插入数据不就行了，快被自己蠢死了。
import pymysql
conn = pymysql.connect(
    host="服务器IP",
    port=3306,
    user="t
批量添加数据时，数据格式必须list[tuple(),tuple(),tuple()] 或者tuple(tuple(),tuple(),tuple())
代码解析：
# -*- coding: utf-8 -*-...