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

Github Link

需求

  现有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字符串替换[字符串替换]

时间: 12-15

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

springMVC从上传的Excel文件中读取数据

示例:导入客户文件(Excle文件) 一.编辑customer.xlsx 二.编辑jsp(addCustomer3.jsp) <%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefi

详解用Navicat工具将Excel中的数据导入Mysql中

第一步:首先需要准备好有数据的excel: 第二步:选择"文件"->"另存为",保存为"CSV(逗号分隔)(*.csv)",将excel表另存为csv文档  第三步:(很重要):如果你的数据中有中文,那么需要将CSV文件处理一下,负责会导入失败:用editplus或者其他编辑器(另存时可以选择保存编码的编辑器)打开CSV文件,然后另存为,选择utf-8(你的数据库也是utf-8哦),点击保存. 第四步:开始导入了,使用mysql图形化工具(

SpringMVC框架简单实现上传Excel文件,并将Excel中的数据导入mySQL数据库

第一步 配置DispathcherServlet文件 第二步 配置applicationContext文件 第三步 在index.jsp中 第四步 在HelloSpringmvc.java中写入方法 第五步:与数据库进行连接 第六步 mySQL实体类 第七步 操作excel表 第八步 Dao文件 第九步 测试

通过Navicat将Excel表中的数据导入到数据库

Navicat.Excel 1)首先创建测试表“student”,表结构为: 2)然后准备编写好的Excel数据表: 3)在Navicat选择student表,右键“导入向导”,弹出如下窗口,我们选择“Excel”: 4)点击“下一步”,“导入从”选择刚刚建好的Excel表,点击“下一步”,弹出如下窗口,这里注意选择好对应的“Sheet”: 5)点击“下一步”,进行一些设置: 6)点击“下一步”,选择已有的表还是新建表: 7)点击“下一步”,对应数据库中student表和excel表的栏位: 8

Java实现Excel导入数据库,数据库中的数据导入到Excel

实现的功能: Java实现Excel导入数据库,如果存在就更新 数据库中的数据导入到Excel 1.添加jxl.jar mysql-connector-java.1.7-bin.jar包到项目的lib目录下­ 2.Excel文件目录:D://book.xls 3.数据库名:javenforexcel 4.表名:stu 5.编写类:连接mysql的字符串方法.插入的方法.实体类­­ 表结构如下 : 连接数据库的工具类 package com.javen.db; import java.sql.Co

利用java反射机制实现读取excel表格中的数据

如果直接把excel表格中的数据导入数据库,首先应该将excel中的数据读取出来. 为了实现代码重用,所以使用了Object,而最终的结果是要获取一个list如List<User>.List<Book>等,所以需要使用泛型机制去实现.下面会给出代码,可能会稍微复杂一点,但注释很清晰,希望大家耐心阅读. 在上代码之前简单说一下思路: 1.excel表格必须有表头,且表头中各列的值要与实体类的属性相同: 2.先读取表头信息,然后获取表头列数,接着确定需要使用的set方法的名称,并存到数

效率最高的Excel数据导入---(c#调用SSIS Package将数据库数据导入到Excel文件中【附源代码下载】) 转

效率最高的Excel数据导入---(c#调用SSIS Package将数据库数据导入到Excel文件中[附源代码下载])  本文目录: (一)背景 (二)数据库数据导入到Excel的方法比较   (三)SSIS的简介   (四)数据库中存储过程示例(SSIS应用需要) (五)Excel模板的制作(这步这么简单,稍微介绍一下)   (六)SSIS操作过程(生成Package,用来调用)(下一篇随笔将详细讲解制作Package包的过程,图片太多,篇幅过长,因此本文将直接采用生成的Package包进行

winfrom_导入Excel文件加载数据到datagridview并将数据保存到数据库

1.效果图: Excel表: winfrom界面: 主要先 选择Excel文件--->显示文件路径--->选择是那一个Excel表--->加载显示在下面的datagridview--->导入到数据库保存 2.点击 '选择Excel' 按钮事件: 1 private void btn_excel_Click(object sender, EventArgs e) 2 { 3 try 4 { 5 //获取Excel文件路径和名称   6 OpenFileDialog odXls = n

如何把Excel中的数据导入到数据库

NPOI: using NPOI.HSSF.UserModel; using NPOI.SS.Formula.Eval; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using System; using System.Data; using System.IO; namespace ZZAS.HNYZ.GPSInstallManage.Common { public class ExcelHelper : IDisposable {

(2) 如何用Apache POI操作Excel文件-----如何在已有的Excel文件中插入一行新的数据?

在POI的第一节入门中,我们提供了两个简单的例子,一个是如何用Apache POI新建一个工作薄,另外一个例子是,如果用Apache POI新建一个工作表.那么在这个章节里面,我将会给大家演示一下,如何用Apache POI在已有的Excel文件中插入一行新的数据.具体代码,请看下面的例子. import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.