python批量读取excel csv文件插入mysql数据库
2022/8/16 2:23:39
本文主要是介绍python批量读取excel csv文件插入mysql数据库,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
#python批量读取excel csv文件插入mysql数据库 import os 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() ''' 读取文件夹下的csv文件 ''' def readAllFiles(filePath): fileList = os.listdir(filePath) i = 0 for file in fileList: path = os.path.join(filePath, file) if os.path.isfile(path): file = open(path, 'r', encoding='utf-8') print(path) i += 1 print("插入第>>>>", i, ">>>>数据表") analysisWorkflowCsv(file) pass else: readAllFiles(path) def analysisWorkflowCsv(file): csvFile = csv.reader(file) # 读取一行,下面的reader中已经没有该行了 # head_row = next(csvFile) # print(head_row) __conn = ConnectionDatabase(ip="localhost", user_name="root", passwd="", db="mydb", char="utf8") counter = 0 for row in csvFile: data = {} # 获取excel内需要的数据,从0开始,根据导入的csv文件的列数来决定data的容量 data['a'] = row[0] data['b'] = row[1] data['c'] = row[2] # data['d'] = row[3] # data['e'] = row[4] # data['f'] = row[5] # data['g'] = row[6] # data['h'] = row[7] # data['i'] = row[8] # data['j'] = row[9] 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): # 在mysql建立表,字段名可以根据需要设置,也可以按a,b,c这样的简单记录也可以。跟data容量的一一对应。 # __sql = ''' # INSERT INTO `mydb`.`tbl_test` ( # `a`, # `b`, # `c`, # `d`, # `e`, # `f`, # `g`, # `h`, # `i`, # `j` # ) # VALUES # ( # %s, # %s, # %s, # %s, # %s, # %s, # %s, # %s, # %s, # %s # ) # ''' __params = ( data['a'], data['b'], data['c'], data['d'], data['e'],data['f'],data['g'],data['h'],data['i'],data['j'] ) # print(__sql % __params) return __conn.mysql_exe_sql(__sql, __params) if __name__ == "__main__": # 文件所在的文件夹父路径,按文件夹下面的文件批量导入 testFilePath = "F:/excel2Mysql/" readAllFiles(testFilePath)
这篇关于python批量读取excel csv文件插入mysql数据库的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-11-20部署MySQL集群入门:新手必读指南
- 2024-11-20部署MySQL集群教程:初学者指南
- 2024-11-20部署MySQL集群项目实战:新手教程
- 2024-11-20部署MySQL集群资料:新手入门教程
- 2024-11-20MySQL集群部署教程:入门级详解
- 2024-11-20MySQL集群教程:入门与实践指南
- 2024-11-20部署MySQL集群教程:新手入门指南
- 2024-11-20MySQL读写分离教程:轻松入门
- 2024-11-20部署MySQL集群入门:一步一步搭建你的数据库集群
- 2024-11-19部署MySQL集群学习:入门教程