使用Python将按照某种特定格式的属性表excel文件动态生成建表SQL语句(使用Pandas库)

最新推荐文章于 2024-04-16 23:24:56 发布

Honyelchak

最新推荐文章于 2024-04-16 23:24:56 发布

阅读量2k

点赞数 2

分类专栏： python 文章标签： python pandas 数据库 sql 动态建表

本文链接：https://blog.csdn.net/m0_38072683/article/details/123753099

版权

python 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

问题描述

有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连接数据库建表。

Honyelchak

关注

2
点赞
踩
4

收藏

觉得还不错? 一键收藏
打赏
0
评论
使用Python将按照某种特定格式的属性表excel文件动态生成建表SQL语句(使用Pandas库)

问题描述有33个国标属性表excel文件，需要根据这些标准属性表来建数据库表。每个excel的格式大概如下：对于这样的繁重且基本无脑的活，我选择用Python来搞定：代码import pandasimport redef getStandardDataType(x): x = x.replace(" ", "") x = x.replace("，", ",") x = x.replace("（", "(") x = x.replace("）", ")")
复制链接

扫一扫