Python读取excel循环读取每行数据,并写入MySQL数据库

763 次查看 0 条评论

1、首先规划Excel表格的格式,以如下格式为例(读取Sheet1表格):

92052-5zhoigwenrb.png

2、规划MySQL数据库表结构,以sql展示:

create database test character set utf8 collate utf8_general_ci;
create table `apk` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `productId` varchar(255) NOT NULL DEFAULT '' COMMENT '产品ID',
  `url` varchar(4096) NOT NULL DEFAULT '' COMMENT 'URL',
  `status` int(1) NOT NULL DEFAULT '0' COMMENT '状态',
  `remark` varchar(2048) NOT NULL DEFAULT '' COMMENT '备注',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Apk';

3、实现代码,import.py代码如下:

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import warnings
warnings.filterwarnings("ignore")
import sys
import time
import xlrd # pip install xlrd 读excel的库
import xlwt # pip install xlwt 写excel的库
import MySQLdb # pip install MySQL-python 操作MySQL的库
## 配置MySQL相关参数
DB_HOST = '127.0.0.1'
DB_DATA = 'test'
DB_TABLE = 'apk'
DB_USER = 'root'
DB_PASS = ''
## 封装写MySQL函数
def writeMySQL(sql):
    try :
        db = MySQLdb.connect(DB_HOST,DB_USER,DB_PASS,DB_DATA,charset='utf8',connect_timeout=5)
        cursor = db.cursor()
        cursor.execute(sql)
        db.commit()
        return True
    except Exception , e :
        print '\033[0;35m' + str(sql) + '\033[0m\n\033[0;31mError: ' + str(e) + '\033[0m'
        if 'db' in locals().keys() :
            db.rollback()
        return False
    db.close()
## 封装从excel读取数据并写入MySQL数据库函数
def importData(path):
    try :
        workbook = xlrd.open_workbook(path)
        sheet = workbook.sheet_by_name('Sheet1')
        count = sheet.nrows # 表格行数
        for i in range(count-1):
            i = i + 1 # +1过滤掉表头
            rows = sheet.row_values(i)
            productId = str(int(rows[0]))
            downloadUrl = str(rows[1].strip())
            print 'Importing ' + str(i) + ' raw data , please watinig ...'
            sql = "insert into `%s`(productId,url) values ('%s','%s')" %(DB_TABLE,productId,downloadUrl)
            if writeMySQL(sql) :
                print '\033[0;32mData write succeeded.\033[0m'
            else:
                print '\033[0;31mWriting to the database failed.\033[0m'
    except KeyboardInterrupt:
        print '\033[0;31mUser exit , KeyboardInterrupt.\033[0m'
        quit()
    except Exception , e :
        print '\033[0;31mError : ' , str(e) , '\033[0m'
    print '\033[0;32mThe task was completed at ' + time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time())) + '.\033[0m'
## 程序入口
if __name__ == "__main__" :
    argv = sys.argv
    script = argv[0]
    if len(argv) < 2 :
        print '\033[0;31mError: Missing parameters, At least 1 parameters are required.\033[0m'
        print '\033[0;35mUsage: ' + script + ' "./data.xls"\033[0m'
    else :
        path = argv[1]
        importData(path)

下载import.zip源码

4、使用方法

./import.py "./data.xls"

导入成功结果展示:

71462-g8uqp30fe3p.png

Other Python

暂无评论,快来抢沙发。