用Python把EXCEL中的数据写入到MySQL中

来源：互联网发布：音乐节知乎编辑：程序博客网时间：2024/05/18 22:42

EXCEL 和 MySQL 大体上来说都可以算是"数据库"，MySQL貌似有EXCEL的接口，但是最近在自学Python，用Python实现了一下

暂时效果还凑合，后续的学习过程中可能会优化...

背景：

Python: 3.3

EXCEL: 2007

MySQL: 5.6

一. MySQL表部署

1、创建表

mysql> create table cc(    -> id int unsigned not null auto_increment primary key,    -> telno varchar(20) not null,    -> address varchar(10) not null,    -> status varchar(30) not null,    -> enter_time varchar(30) not null,    -> get_time varchar(30),    -> hotline varchar(20) not null,    -> hold_time varchar(10) not null,    -> total_time varchar(10) not null,    -> name varchar(20));Query OK, 0 rows affected (0.35 sec)

2、添加列和索引

mysql> alter table cc add index in_enter_time(enter_time);mysql> alter table cc add index in_name(name);mysql> alter table cc add channel varchar(20) not null;

看一下表结构

mysql> desc cc;+------------+------------------+------+-----+---------+----------------+| Field      | Type             | Null | Key | Default | Extra          |+------------+------------------+------+-----+---------+----------------+| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment || telno      | varchar(20)      | NO   |     | NULL    |                || address    | varchar(10)      | NO   |     | NULL    |                || status     | varchar(30)      | NO   |     | NULL    |                || enter_time | varchar(30)      | NO   | MUL | NULL    |                || get_time   | varchar(30)      | YES  |     | NULL    |                || hotline    | varchar(20)      | NO   |     | NULL    |                || hold_time  | varchar(10)      | NO   |     | NULL    |                || total_time | varchar(10)      | NO   |     | NULL    |                || name       | varchar(20)      | YES  | MUL | NULL    |                || channel    | varchar(20)      | NO   | MUL | NULL    |                |+------------+------------------+------+-----+---------+----------------+11 rows in set (0.00 sec)

默认的是InnoDB引擎，修改成MyISAM引擎

mysql> alter table cc engine=myisam    -> ;Query OK, 0 rows affected (0.16 sec)

至此MySQL部分已经完成了。

二. Python读取EXCEL写入MySQL

模块：mysql connector/Python # 连接MySQL

openpyxl # 读取EXCEL

1、代码是这样式儿的

from openpyxl.reader.excel import load_workbook as lwimport mysql.connector as mcconn = mc.connect(user='root', password='password', host='127.0.0.1', database='test')cur = conn.cursor()insert_sql = 'insert into cc (telno, address, channel, status, enter_time, get_time, hotline, hold_time, total_time, name) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'wb = lw(filename = 'c:\\users\\administrator\\desktop\\excel.xlsx')ws = wb.get_sheet_by_name(wb.get_sheet_names()[0])    # <worksheet "data">rows = ws.get_highest_row()         # 最大行数columns = ws.get_highest_column()   # 最大列数data = []for rx in range(2, rows+1):for cx in range(1, columns+1):data.append(str(ws.cell(row=rx, column=cx).value))cur.execute(insert_sql, (data[0], data[1], data[2], data[3], data[4], data[5], data[6], data[7], data[8], data[9]))data = []conn.commit()  # 提交# 关闭两个连接cur.close()    conn.close()

2、检查

mysql> select address, count(enter_time) from cc where address in ('北京', '天津', '重庆', '上海') group by address;+---------+-------------------+| address | count(enter_time) |+---------+-------------------+| 上海    |                 5 || 北京    |                38 || 天津    |                13 || 重庆    |                14 |+---------+-------------------+4 rows in set (0.08 sec)

ps：边学边写，伤不起...

0 0