[Python]将Excel文件中的数据导入MySQL

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

需求

  现有2000+文件夹,每个文件夹下有若干excel文件,现在要将这些excel文件中的数据导入mysql。

  每个excel文件的第一行是无效数据。

  除了excel文件中已有的数据,还要添加一列,名为“at_company”,值为821。

流程

  (1)获取excel文件列表,并根据excel文件名确定之后需要创建的table名;

  (2)连接mysql

  (3)创建table

  (4)插入数据

  (5)断开连接

依赖模块

1. xlrd # to read excel files
2. mysql-connector-python # to work with Mysql

源代码

复制代码
  1 #-*- coding: utf-8 -*-
  2 import os,sys,datetime
  3 import mysql.connector
  4 import xlrd
  5 
  6 '''
  7 the main function to import data
  8     username: username of mysql database
  9     password: password for username
 10     database: a specific database in mysql
 11     datapath: the absolute path or relative path of data folder
 12 '''
 13 def importDataHelper(username, password, database, datapath):
 14     '''import data helper'''
 15     '''
 16     Step 0: Validate input database parameters
 17     '''
 18     try:
 19         conn = mysql.connector.connect(user=username, password=password, database=database, use_unicode=True)
 20     except mysql.connector.errors.ProgrammingError as e:
 21         print e
 22         return -1
 23     '''
 24     Step 1: Traverse files in datapath, store file paths and corresponding table names in lists
 25     lists[0] is the list of files paths
 26     lists[1] is the list of table names
 27     '''
 28     lists = getFilesList(datapath)
 29     nfiles = len(lists[0])
 30     '''
 31     Step 2: Store data in mysql via a for-loop
 32     '''
 33     cursor = conn.cursor()
 34     for file_idx in xrange(0, nfiles):
 35         file_path = lists[0][file_idx]
 36         print "processing file(%d/%d):[ %s ]"%(file_idx+1, nfiles, file_path)
 37         table_name = lists[1][file_idx]
 38         num = storeData(file_path, table_name, cursor)
 39         if num >= 0:
 40             print "[ %d ] data have been stored in TABLE:[ %s ]"%(num, table_name)
 41         conn.commit()
 42     cursor.close()
 43     '''
 44     Step 3: Close connection
 45     '''
 46     conn.close()
 47 
 48 '''
 49 get files list in the dir, including the files in its sub-folders
 50 the return list contain two elements, the first element is a file names list
 51 and the second element is a table names list(will be used for creating tables in database),
 52 '''
 53 def getFilesList(dir):
 54     path_list = []
 55     table_list = []
 56     file_name_list = os.listdir(dir)
 57     for file_name in file_name_list:
 58         path = os.path.join(dir, file_name)
 59         if os.path.isdir(path):
 60             '''get the files in sub folder recursively'''
 61             tmp_lists = getFilesList(path)
 62             path_list.extend(tmp_lists[0])
 63             table_list.extend(tmp_lists[1])
 64         else:
 65             path_list.append(path)
 66             '''convert file name to mysql table name'''
 67             file_name = file_name.split('.')[0] #remove .xls
 68             # file_name = file_name.split('from')[0] #remove characters after 'from'
 69             file_name = file_name.strip()#remove redundant space at both ends
 70             file_name = file_name.replace(' ','_') #replace ' ' with '_'
 71             file_name = file_name.replace('-','_') #replace ' ' with '_'
 72             file_name = file_name.lower() #convert all characters to lowercase
 73             table_list.append(file_name)
 74     return [path_list, table_list]
 75 
 76 '''
 77 store the data of file file_path in table table_name
 78     file_path: file location
 79     table_name: name of the table that will be created in database
 80     cursor: a mysql cursor
 81 '''
 82 def storeData(file_path, table_name, cursor):
 83     ret = 0
 84     '''open an excel file'''
 85     file = xlrd.open_workbook(file_path)
 86     '''get the first sheet'''
 87     sheet = file.sheet_by_index(0)
 88     '''get the number of rows and columns'''
 89     nrows = sheet.nrows
 90     ncols = sheet.ncols
 91     '''get column names'''
 92     col_names = []
 93     for i in range(0, ncols):
 94         title = sheet.cell(1, i).value
 95         title = title.strip()
 96         title = title.replace(' ','_')
 97         title = title.lower()
 98         col_names.append(title)
 99     '''create table in mysql'''
100     sql = 'create table '\
101           +table_name+' (' \
102           +'id int NOT NULL AUTO_INCREMENT PRIMARY KEY, ' \
103           +'at_company varchar(10) DEFAULT \'821\', '
104 
105     for i in range(0, ncols):
106         sql = sql + col_names[i] + ' varchar(150)'
107         if i != ncols-1:
108             sql += ','
109     sql = sql + ')'
110     try:
111         cursor.execute(sql)
112     except mysql.connector.errors.ProgrammingError as e:
113         print e
114         # return -1
115 
116     '''insert data'''
117     #construct sql statement
118     sql = 'insert into '+table_name+'('
119     for i in range(0, ncols-1):
120         sql = sql + col_names[i] + ', '
121     sql = sql + col_names[ncols-1]
122     sql += ') values ('
123     sql = sql + '%s,'*(ncols-1)
124     sql += '%s)'
125     #get parameters
126     parameter_list = []
127     for row in xrange(2, nrows):
128         for col in range(0, ncols):
129             cell_type = sheet.cell_type(row, col)
130             cell_value = sheet.cell_value(row, col)
131             if cell_type == xlrd.XL_CELL_DATE:
132                 dt_tuple = xlrd.xldate_as_tuple(cell_value, file.datemode)
133                 meta_data = str(datetime.datetime(*dt_tuple))
134             else:
135                 meta_data = sheet.cell(row, col).value
136             parameter_list.append(meta_data)
137         # cursor.execute(sql, parameter_list)
138         try:
139             cursor.execute(sql, parameter_list)
140             parameter_list = []
141             ret += 1
142         except mysql.connector.errors.ProgrammingError as e:
143             print e
144             # return -1
145     return ret
146 
147 
148 
149 if __name__ == "__main__":
150     if len(sys.argv)<5:
151         print "Missing Parameters"
152         sys.exit()
153     elif len(sys.argv)>5:
154         print "Too Many Parameters"
155         sys.exit()
156     username = sys.argv[1]
157     password = sys.argv[2]
158     database = sys.argv[3]
159     datapath = sys.argv[4]
160     importDataHelper(username, password, database, datapath)
复制代码

Readme文件(帮导师做的,所以用英文写的文档)

复制代码
There are two dependency modules need to be installed.
    1. xlrd # to read excel files
    2. mysql-connector-python # to work with Mysql

Directory Structure:
    data_path: test files
    ImportDataProgram.py: the main program

Procedure:
    (1) Get all the paths and names of the files need to be stored
    (2) Connect MySQL
    (3) Create tables for each file
    (4) Insert data into each table

Usage:
    0. create a new database in mysql
        For example, after logging in mysql in terminal, you can use the the following command
        "create database test_database" to create a database named 'test_database',
        you can replace "test_database" with any other names you like.

    1. set username, password, database(created in step 0) and datapath in the tail of ImportDataProgram.py
    2. run ImportDataProgram.py with the following command
        python ImportDataProgram.py [username] [password] [database] [datapath]
    # username: your username in your mysql
    # password: the corresponding password
    # database: the database you specific
    # datapath: the directory of excel files
    e.g.
        python ImportDataProgram.py root root test_database data_path

PS:
    (1) The Length of Data In Table
    I am not sure the maximum length of data, so I set the
    length of data in mysql tables is 150 characters (you can find
    the code in function storeData(file_path, table_name, cursor), the code is
    " sql = sql + col_names[i] + ' varchar(150)' "), you can adjust it according
     to your requirements.
    (2)Table Name:
     You can set the rules of table name, the code is following the comment code:
    '''convert file name to mysql table name''' in function getFilesList(dir).
复制代码

 遇到的坑以及填坑方法:

(1)Python中argv参数用法

(2)Python使用xlrd读取excel文件的方法[1(比较简要)][2(比较详细)

(3)Python使用xlrd读取excel文件中日期类型变为浮点数[stack overflow][2中文博客

(4)Python遍历目录下的文件[1

(5)Python连接MySQL[1

(6)Python中print格式化输出(%),取消默认换行(,)[print用法

(7)Python字符串连接[字符串操作]

(8)Python连接list[连接list]

(9)Python字符串替换[字符串替换]



本文转自ZH奶酪博客园博客,原文链接:http://www.cnblogs.com/CheeseZH/p/5050394.html,如需转载请自行联系原作者

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
16小时前
|
数据采集 数据可视化 Python
Python分析香港26281套在售二手房数据
Python分析香港26281套在售二手房数据
|
1天前
|
数据采集 存储 数据挖掘
Python DataFrame初学者指南:轻松上手构建数据表格
【5月更文挑战第19天】本文是针对初学者的Pandas DataFrame指南,介绍如何安装Pandas、创建DataFrame(从字典或CSV文件)、查看数据(`head()`, `info()`, `describe()`)、选择与操作数据(列、行、缺失值处理、数据类型转换、排序、分组聚合)以及保存DataFrame到CSV文件。通过学习这些基础,你将能轻松开始数据科学之旅。
|
1天前
|
数据挖掘 数据处理 Python
【Python DataFrame 专栏】Python DataFrame 入门指南:从零开始构建数据表格
【5月更文挑战第19天】本文介绍了Python数据分析中的核心概念——DataFrame,通过导入`pandas`库创建并操作DataFrame。示例展示了如何构建数据字典并转换为DataFrame,以及进行数据选择、添加修改列、计算统计量、筛选和排序等操作。DataFrame适用于处理各种规模的表格数据,是数据分析的得力工具。掌握其基础和应用是数据分析之旅的重要起点。
【Python DataFrame 专栏】Python DataFrame 入门指南:从零开始构建数据表格
|
1天前
|
机器学习/深度学习 数据处理 Python
如何利用Python实现高效的数据清理与预处理
数据清理和预处理是数据科学家和分析师工作中不可或缺的一环,而Python作为一门强大的编程语言,可以使这个过程变得更加高效和便捷。本文将介绍一些常见的数据清理和预处理技术,并演示如何使用Python来实现这些技术。
|
3天前
|
机器学习/深度学习 关系型数据库 MySQL
MySQL 到 Greenplum 实时数据同步实操分享,2024年最新【Python面试题
MySQL 到 Greenplum 实时数据同步实操分享,2024年最新【Python面试题
|
3天前
|
数据采集 数据挖掘 关系型数据库
Excel计算函数(计算机二级)(1),2024年最新2024Python架构面试指南
Excel计算函数(计算机二级)(1),2024年最新2024Python架构面试指南
|
4天前
|
JSON JavaScript 数据格式
利用 python 分析基金,合理分析数据让赚钱赢在起跑线!(1)
利用 python 分析基金,合理分析数据让赚钱赢在起跑线!(1)
|
4天前
|
Java Apache 索引
POI操作大全(动态合并单元格,为单元格生成一个自定义的数据显示格式,自定义公式计算结果生成,读取excel,word文件在生成图片,word指定位置生成图片)
POI操作大全(动态合并单元格,为单元格生成一个自定义的数据显示格式,自定义公式计算结果生成,读取excel,word文件在生成图片,word指定位置生成图片)
|
5天前
|
存储 JSON 数据格式
Python知识点——高维数据的格式化
Python知识点——高维数据的格式化
6 0
|
5天前
|
Python
Python知识点——文件和数据格式化
Python知识点——文件和数据格式化
8 0