Python 如何分批次导入 100W 条 mysql 数据

conn = MySQLdb.connect(host='localhost',port = 3306,user='admin',passwd='admin',db='google') cur = conn.cursor() try: create_tb_cmd=''' create table if not exists tmp.Video_2017bak (id int(10) unsigned, asset_id int(10) unsigned, company_id int(10), ); ''' cur.execute(create_tb_cmd) finally: insert_tb_cmd=''' insert ignore into tmp.Video_2017bak (select * from google.Video where created_at < date_sub(now(), interval 2 year));''' delete_tb_cmd=''' delete from google.Video where created_at < date_sub(now(), interval 2 year);''' cur.execute(insert_tb_cmd) cur.execute(delete_tb_cmd)

cur.close() conn.commit() conn.close()

cur

Python

execute

int

4 条回复 • 2017-04-27 15:53:42 +08:00

skydiver

2017-04-22 19:40:40 +08:00 via Android

直接用 MySQL 插入，没必要用 Python

wangyu8460958

2017-04-22 20:49:13 +08:00

@skydiver 我想使用脚本让它每周执行一次，所以想使用 python 脚本来做。

billlee

2017-04-22 23:27:47 +08:00

这代码没法看
我搞不清楚 autocommit 默认是不是开启的，所以我会显示地禁用掉。
禁用 autocommit 的情况下，就是写个循环执行 INSERT, 然后搞个计数器，每 10000 条执行一次 conn.commit() 就行了

wangyu8460958

2017-04-27 15:53:42 +08:00

下面是我自己写的脚本，下面的脚本还能够优化吗？

#coding=utf-8

import MySQLdb

import numpy as np

conn = MySQLdb.connect(host='localhost',port = 3306,user='root',passwd='123456',db='google')
cur = conn.cursor()
conn.autocommit(1)

query_sql = "select id from google.Video where created_at < date_sub(now(), interval 1 year);"
insert_sql = "insert ignore into tmp.Video_2017bak (select * from google.Video where id = %s);"
delete_sql = "delete from google.Video where id = %s;"
cur.execute(query_sql)
dataList = cur.fetchall()
aaa = np.array(dataList)
ids = []

for i in range(len(aaa)):
ids.append(aaa[i])
if (i+1)%100==0 :
cur.executemany(insert_sql,ids)
ids = []
cur.executemany(insert_sql,ids)
ids = []

for i in range(len(aaa)):
ids.append(aaa[i])
if (i+1)%100==0 :
cur.executemany(delete_sql,ids)
ids = []
cur.executemany(delete_sql,ids)
ids = []

cur.close()
conn.close()