问题描述
有33个国标属性表excel
文件,需要根据这些标准属性表来建数据库表。
每个excel的格式大概如下:
对于这样的繁重且基本无脑的活,我选择用Python来搞定:
代码
import pandas
import re
def getStandardDataType(x):
x = x.replace(" ", "")
x = x.replace(",", ",")
x = x.replace("(", "(")
x = x.replace(")", ")")
if 'I' in x:
return "int"
elif 'C' in x:
print(x)
mm = re.match(r'C\((.*)\)$', x)
return "char({0})".format(int(mm.group(1)))
elif 'D' in x:
mm = re.match(r'D\((.*),(.*)\)$', x)
print(mm.group(1))
print(mm.group(2))
return "decimal({0},{1})".format(mm.group(1), mm.group(2))
elif 'L' in x:
return 'bigint'
elif 'T' in x:
return 'datetime'
else:
print(x)
def strConvert(x):
x = x.lower()
x = x.replace("-", "_")
x = x.replace(" ", "")
if x == "systemid":
return "system_id"
elif x == "datasource":
return "data_source"
elif x == "dataListid":
return "data_list_id"
elif x == "reportdept":
return "report_dept"
elif x == "reportdate":
return "report_date"
return x
nice = pandas.read_excel("C:\\Users\\Hades\\Desktop\\xxxx\\A08截流设施数据.xlsx")
nice
序号 | 中文字段名 | 英文字段名 | 推荐数据类型 | 说明 | |
---|---|---|---|---|---|
0 | 1 | 雨水口标识码 | Comb_ID | C(17) | 唯一编码 |
1 | 2 | 排水系统编码 | System_ID | C(17) | 所属排水系统编码 |
2 | 3 | 坐标x | X-Coor | D(11,3) | 合肥市坐标 |
3 | 4 | 坐标y | Y-Coor | D(11,3) | 合肥市坐标 |
4 | 5 | 所在道路名称 | Road_Name | C(20) | 设施所在道路 |
5 | 6 | 雨水口形式 | Inlet_Type | C(10) | 1-平篦式;2-立篦式;3-联合式4-偏沟式;5-道牙;6-其它,并 5 雨水口形式 In... |
6 | 7 | 雨水口形状 | Comb_Shape | I | 1矩形、2圆形、3其他 |
7 | 8 | 雨水口尺寸1 | Comb_Dimen1 | D(5,3) | 雨水口形状为矩形时填写长度,圆形时填写直径,单位:米 |
8 | 9 | 雨水口尺寸2 | Comb_Dimen2 | D(5,3) | 雨水口为矩形时填写宽度,单位:米 |
9 | 10 | 雨水篦材质 | Gra_Material | C(20) | 1-球墨铸铁;2-灰口铸铁;3-刚格板;4-其他,并注明材质。 |
10 | 11 | 雨水篦间距 | Gra_Spacing | D(5,3) | 格栅间距 单位:米 |
11 | 12 | 雨水篦厚度 | Gra_Thickness | D(5,3) | 厚度 单位:米 |
12 | 13 | 雨水口最大深度 | Max_Depth | D(6,3) | 雨水口的最大深度,单位:米 |
13 | 14 | 雨水口地面高程 | Surface_Ele | D(7,3) | 雨水口所处位置的地面高程,单位:米 |
14 | 15 | 数据来源 | Data_Source | C(50) | 1-设计图;2-竣工图;3-现场测4-人工估计;5-其它,并注明来源 |
15 | 16 | 数据获取时间 | Record_Date | T | 格式:yyyy-m-d |
16 | 17 | 填报单位 | Report_Dept | C(30) | 数据填报单位 |
17 | 18 | 填报日期 | Report_Date | T | 格式:yyyy-m-d |
18 | 19 | 设计能力 | Design_SluiCapa | D(6,2) | 设计泄水能力,单位:升/秒 |
19 | 20 | 是否装备垃圾拦截装置 | Anti_Trash | I | 0-否;1-是 |
20 | 21 | 是否装置防臭装置 | Anti_Odor | I | 0-否;2-是 |
21 | 22 | 是否装备初期雨水截流装置 | IniRain_Inter | I | 0-否;3-是 |
22 | 23 | 设施状态 | States | I | 0-否;4-是 |
23 | 24 | 备注 | Remark | C(100) | 相关事项描述 |
24 | 25 | 各阶段负责的单位 | unit_info_id | I | NaN |
nice["desc"] = nice["中文字段名"] + ": (" + nice["说明"] + " )"
nice["datatype1"] = nice["推荐数据类型"].apply(lambda x : getStandardDataType(x))
C(17)
C(17)
11
3
11
3
C(17)
C(50)
C(30)
C(17)
6
2
C(100)
nice["label"] = nice["英文字段名"].apply(lambda x : strConvert(x))
nice
序号 | 中文字段名 | 英文字段名 | 推荐数据类型 | 说明 | desc | datatype1 | label | |
---|---|---|---|---|---|---|---|---|
0 | 1 | 截流设施标识码 | Divider_ID | C(17) | 唯一编码 | 截流设施标识码: (唯一编码 ) | char(17) | divider_id |
1 | 2 | 排水系统编码 | System_ID | C(17) | 所属排水系统编码 | 排水系统编码: (所属排水系统编码 ) | char(17) | system_id |
2 | 3 | 坐标X | X-Coor | D(11,3) | 应与设施中心点的坐标一致 | 坐标X: (应与设施中心点的坐标一致 ) | decimal(11,3) | x-coor |
3 | 4 | 坐标Y | Y-Coor | D(11,3) | 应与设施中心点的坐标一致 | 坐标Y: (应与设施中心点的坐标一致 ) | decimal(11,3) | y-coor |
4 | 5 | 截流设施类型 | Divider_Type | I | 1一闸;2一泵;3一堰;4一阀;5一其他 | 截流设施类型: (1一闸;2一泵;3一堰;4一阀;5一其他 ) | int | divider_type |
5 | 6 | 截流设施连接管渠编码 | Diverted_ID | C(17) | 截留设施出口连接的排水管或排水渠编码 | 截流设施连接管渠编码: (截留设施出口连接的排水管或排水渠编码 ) | char(17) | diverted_id |
6 | 7 | 技术资料文件 | Data_List_ID | I | 技术文件编码,对应A.0.31表中的资料标识码 | 技术资料文件: (技术文件编码,对应A.0.31表中的资料标识码 ) | int | data_list_id |
7 | 8 | 数据来源 | Data_Source | C(50) | 1一现场探测;2一竣工图;3-设计图;4一人工估计;5-其他,并注明来源 | 数据来源: (1一现场探测;2一竣工图;3-设计图;4一人工估计;5-其他,并注明来源 ) | char(50) | data_source |
8 | 9 | 数据获取日期 | Record _Date | T | 格式:yyyy-MM-dd,数据来源的具体日期 | 数据获取日期: (格式:yyyy-MM-dd,数据来源的具体日期 ) | datetime | record_date |
9 | 10 | 填报单位 | Report_Unit | C(30) | 数据填报单位 | 填报单位: (数据填报单位 ) | char(30) | report_unit |
10 | 11 | 填报日期 | Report _Date | T | 格式:yyyy-MM-dd,数据填报日期 | 填报日期: (格式:yyyy-MM-dd,数据填报日期 ) | datetime | report_date |
11 | 12 | 截流内部设施编码 | Connect_ID | C(17) | 截流设施内部的闸门、阀门、溢流堰或泵的编码 | 截流内部设施编码: (截流设施内部的闸门、阀门、溢流堰或泵的编码 ) | char(17) | connect_id |
12 | 13 | 截流量 | Cutoff_Flow | D(6,2) | 如确定截流流量,直接设定流量,单位:升/秒 | 截流量: (如确定截流流量,直接设定流量,单位:升/秒 ) | decimal(6,2) | cutoff_flow |
13 | 14 | 截流曲线 | Diversion_Curve | I | 截流量变化曲线,与XY曲线表关联,X代表入流量,Y代表出流量,单位:升/秒 | 截流曲线: (截流量变化曲线,与XY曲线表关联,X代表入流量,Y代表出流量,单位:升/秒 ) | int | diversion_curve |
14 | 15 | 设施状态 | Status | I | 1-已建;2-在建;3-待废;4-已废;5-其他 | 设施状态: (1-已建;2-在建;3-待废;4-已废;5-其他 ) | int | status |
15 | 16 | 备注 | Remark | C(100) | 相关事项说明 | 备注: (相关事项说明 ) | char(100) | remark |
16 | 17 | 各阶段负责的单位 | unit_info_id | I | NaN | NaN | int | unit_info_id |
table_name = "closure_facility_data "
sql = "SET FOREIGN_KEY_CHECKS=0;\nDROP TABLE IF EXISTS `{0}`;\nCREATE TABLE `{0}` (\n `id` int NOT NULL,\n".format(table_name)
def addLabel(x):
global sql
#x = nice[x]
if "char" in x["datatype1"]:
sql += "`{0}` {1} CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '{2}',\n".format(x["label"], x["datatype1"], x["desc"])
else:
sql += "`{0}` {1} DEFAULT NULL COMMENT '{2}',\n".format(x["label"], x["datatype1"], x["desc"])
nice.apply(addLabel, axis=1)
sql += "`gmt_create` datetime NULL DEFAULT current_timestamp,\n`gmt_modified` datetime NULL default current_timestamp on update current_timestamp,\nPRIMARY KEY (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;"
print(sql)
最后生成的sql文件!
SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `closure_facility_data `;
CREATE TABLE `closure_facility_data ` (
`id` int NOT NULL,
`divider_id` char(17) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '截流设施标识码: (唯一编码 )',
`system_id` char(17) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '排水系统编码: (所属排水系统编码 )',
`x-coor` decimal(11,3) DEFAULT NULL COMMENT '坐标X: (应与设施中心点的坐标一致 )',
`y-coor` decimal(11,3) DEFAULT NULL COMMENT '坐标Y: (应与设施中心点的坐标一致 )',
`divider_type` int DEFAULT NULL COMMENT '截流设施类型: (1一闸;2一泵;3一堰;4一阀;5一其他 )',
`diverted_id` char(17) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '截流设施连接管渠编码: (截留设施出口连接的排水管或排水渠编码 )',
`data_list_id` int DEFAULT NULL COMMENT '技术资料文件: (技术文件编码,对应A.0.31表中的资料标识码 )',
`data_source` char(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '数据来源: (1一现场探测;2一竣工图;3-设计图;4一人工估计;5-其他,并注明来源 )',
`record_date` datetime DEFAULT NULL COMMENT '数据获取日期: (格式:yyyy-MM-dd,数据来源的具体日期 )',
`report_unit` char(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '填报单位: (数据填报单位 )',
`report_date` datetime DEFAULT NULL COMMENT '填报日期: (格式:yyyy-MM-dd,数据填报日期 )',
`connect_id` char(17) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '截流内部设施编码: (截流设施内部的闸门、阀门、溢流堰或泵的编码 )',
`cutoff_flow` decimal(6,2) DEFAULT NULL COMMENT '截流量: (如确定截流流量,直接设定流量,单位:升/秒 )',
`diversion_curve` int DEFAULT NULL COMMENT '截流曲线: (截流量变化曲线,与XY曲线表关联,X代表入流量,Y代表出流量,单位:升/秒 )',
`status` int DEFAULT NULL COMMENT '设施状态: (1-已建;2-在建;3-待废;4-已废;5-其他 )',
`remark` char(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '备注: (相关事项说明 )',
`unit_info_id` int DEFAULT NULL COMMENT 'nan',
`gmt_create` datetime NULL DEFAULT current_timestamp,
`gmt_modified` datetime NULL default current_timestamp on update current_timestamp,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
最后:
感觉这样还是不够自动化,应该直接用python连接数据库建表。