python操作mysql百万行表数据处理后写进另一表

1、代码如下：

import os
import pandas as pd
import csv
import argparse
import pymysql
import sys
class ConnectionDatabase(object):
# 连接mysql数据库
def __init__(self, ip, user_name, passwd, db, char='utf8'):
self.ip = ip
# self.port = port
self.username = user_name
self.passwd = passwd
self.mysqldb = db
self.char = char
self.MySQL_db = pymysql.connect(
host=self.ip,
user=self.username,
password=self.passwd,
database=self.mysqldb,
charset=self.char)
def mysql_findList(self, sql):
cursor = self.MySQL_db.cursor()
MySQL_sql = sql
results = None
if not cursor:
raise (NameError, "数据库连接失败")
try:
# 执行SQL语句
cursor.execute(MySQL_sql)
# 获取所有记录列表
results = cursor.fetchall()
except Exception as e:
print(e)
self.MySQL_db.close()
if results:
return results
else:
return None
# 数据增删改查（sqlserver）
def mysql_exe_sql(self, sql, params):
cursor = self.MySQL_db.cursor()
MySQL_sql = sql
result = 0
if not cursor:
raise (NameError, "数据库连接失败")
try:
# 执行SQL语句
self.MySQL_db.ping(True)
cursor.execute(MySQL_sql, params)
result = cursor.rowcount
except Exception as e:
print(e)
self.MySQL_db.rollback()
self.MySQL_db.close()
return result > 0
'''
提交数据
'''
def commitData(self):
try:
self.MySQL_db.commit()
except Exception as e:
print(e)
'''
关闭数据库连接
'''
def closeConn(self):
if self.MySQL_db:
self.MySQL_db.close()
'''
读取数据库表1下文件
'''
def read_table1_Files(table_name):
__conn1 = pymysql.connect(host='', port=, user='', password='', db='', charset='utf8') #连接需要处理的数据库
print("连接数据库成功")
cur1 = __conn1.cursor() #游标
cur1.execute("select count(1) from {0};".format(table_name))
count = cur1.fetchall() #获取表行数，fetchall返回的是一个元组
print("##########", count[0][0])
i = 0
while (i < count[0][0]):
'''
一次性读取5000行
'''
if count[0][0] - i > 5000:
lines = 5000
else:
lines = count[0][0] - i
sql = "SELECT * from %s ORDER BY plate_c limit %s , %s;" % (table_name, i, lines)
# print(sql)
cur1.execute(sql)
result = cur1.fetchall() #result是一个长度为5000的元组
i = i + lines
# print("result: ", result)
analysisWorkflow(result)
print("insert success")
def findNumValue(list=[]):
for i in range(len(list) - 1):
for j in range(len(list) - 1 - i):
if list[j] > list[j + 1]:
temp = list[j]
list[j] = list[j + 1]
list[j + 1] = temp
print("最小值：{0}".format(list[0]))
print("最大值：{0}".format(list[len(list) - 1]))
def analysisWorkflow(result):
__conn = ConnectionDatabase(host='', user='', password='', db='', charset='utf8') #连接需要插入数据的数据库
counter = 0
for i in range(len(result)):
#len1 = len(result[i])
data = {}
deal_list = list(result[i]) #由于result是元组类型，强制转换为list处理
'''
此处插入你的处理代码,获取需要插入表的对应字段内容
'''
data['a'] =
data['b'] =
data['c'] =
if insert_data(__conn, data):
counter += 1
if counter % 1000 == 0:
__conn.commitData()
print("已经插入工作流数据： %d 条。"%counter)
__conn.commitData()
__conn.closeConn()
'''
插入工作流程数据
'''
def insert_data(__conn, data):
__sql = '''
INSERT INTO data (
a, b, c
) VALUES (%s, %s, %s)
'''
__params = (
data['a'], data['b'], data['c'])
# print(__sql % __params)
# data为需要插入表的表名
return __conn.mysql_exe_sql(__sql, __params)
if __name__ == "__main__":
read_table1_Files('表名')

最后

以上就是缥缈手套为你收集整理的python操作mysql百万行表数据处理后写进另一表的全部内容，希望文章能够帮你解决python操作mysql百万行表数据处理后写进另一表所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。