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 lw
import mysql.connector as mc
conn = 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:边学边写,伤不起...