使用Python将Excel中的数据导入到MySQL

古城微笑少年丶 2022-03-09 00:22 393阅读 0赞

## 工具 ##

*  Python 2.7
 *  xlrd
 *  MySQLdb

***安装***

*  Python

对于不同的系统安装方式不同，Windows平台有exe安装包，Ubuntu自带。使用前请使用下面的命令确保是2.7.x版本：

`python --version`

*  xlrd :

这是一个扩Python包，可以使用pip包管理工具安装：`pip install xlrd`

*  MySQLdb

为MySQL 的Python驱动接口包，可以到http://sourceforge.net/projects/mysql-python/下载安装。在Ubuntu值哦你可以使用`sudo apt-get install python-mysql`安装

## 实现数据转移 ##

功能很简单，直接在代码中注释了

"""
    功能：将Excel数据导入到MySQL数据库
    """
    import xlrd
    import MySQLdb
    # Open the workbook and define the worksheet
    book = xlrd.open_workbook("pytest.xls")
    sheet = book.sheet_by_name("source")
    
    #建立一个MySQL连接
    database = MySQLdb.connect (host="localhost", user = "root", passwd = "", db = "mysqlPython")
    
    # 获得游标对象, 用于逐行遍历数据库数据
    cursor = database.cursor()
    
    # 创建插入SQL语句
    query = """INSERT INTO orders (product, customer_type, rep, date, actual, expected, open_opportunities, closed_opportunities, city, state, zip, population, region) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""
    
    # 创建一个for循环迭代读取xls文件每行数据的, 从第二行开始是要跳过标题
    for r in range(1, sheet.nrows):
          product      = sheet.cell(r,).value
          customer = sheet.cell(r,1).value
          rep          = sheet.cell(r,2).value
          date     = sheet.cell(r,3).value
          actual       = sheet.cell(r,4).value
          expected = sheet.cell(r,5).value
          open        = sheet.cell(r,6).value
          closed       = sheet.cell(r,7).value
          city     = sheet.cell(r,8).value
          state        = sheet.cell(r,9).value
          zip         = sheet.cell(r,10).value
          pop          = sheet.cell(r,11).value
          region   = sheet.cell(r,12).value
    
          values = (product, customer, rep, date, actual, expected, open, closed, city, state, zip, pop, region)
    
          # 执行sql语句
          cursor.execute(query, values)
    
    # 关闭游标
    cursor.close()
    
    # 提交
    database.commit()
    
    # 关闭数据库连接
    database.close()
    
    # 打印结果
    print ""
    print "Done! "
    print ""
    columns = str(sheet.ncols)
    rows = str(sheet.nrows)
    print "我刚导入了 " %2B columns %2B " 列 and " %2B rows %2B " 行数据到MySQL!"