1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
| # python
# -*- encoding: utf-8 -*-
# Useage: 将xlsx文件导入为mysql的databse
import os
import sys
import re
import datetime
import argparse
import openpyxl
import MySQLdb
Charset = "utf8"
def Xlsx2MySQLdb(Xlsx, User, Password):
Work_Book = openpyxl.load_workbook(Xlsx)
Database = re.search(".*\/(\S+)\.xlsx", Xlsx).group(1) + "_" + datetime.datetime.now().strftime("%Y%m%d_%H%M%S")
List_Sheet = Work_Book.sheetnames
# 提示信息
Time_Stamp = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
sys.stdout.write("*" + Time_Stamp + "* [运行信息]\n")
sys.stdout.write("[输入文件] " + Xlsx + "\n")
sys.stdout.write("[子表数量] " + str(len(List_Sheet)) + "\n")
sys.stdout.write("[子表名称] " + "、".join(List_Sheet) + "\n")
sys.stdout.write("[数据库名] " + Database + "\n")
sys.stdout.write("[字符编码] " + Charset + "\n")
sys.stdout.write("[表头字段] 每个子表的第1行会作为数据库的列名\n")
sys.stdout.write("[数据类型] 所有内容都保存为TEXT，如需修改，请在MySQL自行修改\n\n")
# 连接MySQL
SQL_Host = "localhost"
SQL_Connection = MySQLdb.connect(host = SQL_Host, user = User, passwd = Password, charset = Charset)
Cursor = SQL_Connection.cursor()
# 创建数据库
Cursor.execute("create database if not exists " + Database)
Cursor.execute("use " + Database)
# 逐个子表处理
for Sheet in List_Sheet:
List_Header = []
List_Table_Header = []
Data_Type = "TEXT"
Work_Sheet = Work_Book[Sheet]
for col_n in range(1, Work_Sheet.max_column+1):
Header = str(Work_Sheet.cell(row=1, column=col_n).value)
List_Header.append("`" + Header + "`")
List_Table_Header.append("`" + Header + "`" + " " + Data_Type)
# 创建数据表
Table_Header = ", ".join(List_Table_Header)
Cursor.execute("create table if not exists " + Sheet + " (" + Table_Header + ")")
# 逐行存入数据
Header = ", ".join(List_Header)
for row_n in range(2, Work_Sheet.max_row+1):
List_Value = []
for col_n in range(1, Work_Sheet.max_column+1):
Value = str(Work_Sheet.cell(row = row_n, column = col_n).value)
List_Value.append("\"" + Value + "\"")
Value = ", ".join(List_Value)
Cursor.execute("insert into " + Sheet + " (" + Header + ") values(" + Value + ")")
# 提交MyQSL操作
SQL_Connection.commit()
# 关闭连接
Cursor.close()
SQL_Connection.close()
# 关闭Xlsx
Work_Book.close()
|