tablesqlconverter 1.0.1

Convert table schema between MySQL, Hive, and string formats

TableSQLConverter V1.0

TableSQLConverter是为了方便将数据库表的基本元信息 以表格形式如excel的批量转换成Mysql建表或HiveSQL的建表语句。也能够根据SQL的 创表语句，提取出基本字段信息形成表格，或者SQL创表语句向Hive创表语句的转换。

图1 TableSQlConverter V1架构图

pip install tablesqlconverter
from tablesqlconverter import Converter
myCvt=Converter()

示例

tbl数据间隔符号是”\t“,换行符号是"\n"

tbl="""表名   字段名    字段注释   字段类型   长度 约束 默认值
sys_user   user_id    用户主键 ID    bigint -  PK、自增  -
sys_user   username   登录账号（学号 / 工号）  varchar    50 UK、NOT NULL    -
sys_user   password   登录密码（加密存储） varchar    100    NOT NULL   -
sys_user   user_type  用户类型 1 = 管理员 2 = 教师 3 = 学生 tinyint    -  NOT NULL   3
sys_user   real_name  真实姓名   varchar    30 NOT NULL   -
sys_user   phone  联系电话   varchar    20 -  NULL
sys_user   email  邮箱 varchar    50 -  NULL
sys_user   status 状态 0 = 禁用 1 = 正常   tinyint    -  NOT NULL   1
sys_user   create_time    创建时间   datetime   -  NOT NULL   CURRENT_TIMESTAMP
sys_user   update_time    更新时间   datetime   -  NOT NULL   CURRENT_TIMESTAMP ON UPDATE
student    student_id 学生主键 ID    bigint -  PK、自增  -
student    user_id    关联系统用户 ID  bigint -  FK、NOT NULL    -
student    student_no 学号 varchar    30 UK、NOT NULL    -
student    dept_id    所属院系 ID    bigint -  FK、NOT NULL    -
student    class_id   所属班级 ID    bigint -  FK NULL
student    gender 性别 0 = 女 1 = 男 tinyint    -  -  NULL
student    id_card    身份证号   varchar    18 -  NULL
student    address    家庭住址   varchar    200    -  NULL
student    enrollment_date    入学日期   date   -  -  NULL
student    status 状态 1 = 在读 2 = 毕业 3 = 休学    tinyint    -  NOT NULL   1
"""
from tablesqlconverter import Converter
myCvt=Converter()
mysqlschema=myCvt.tblstr2schema(tbl,0,1,3,2,dataNo=1) #第0列是表名，DataNo=1是从第1行开始是主要数据
print(mysqlschema)
#中间数据形式结果是：
"""
{'sys_user': [{'field': 'user_id', 'type': 'bigint', 'comment': '用户主键 ID'}, {'field': 'username', 'type': 'varchar', 'comment': '登录账号（学号 / 工号）'}, {'field': 'password', 'type': 'varchar', 'comment': '登录密码（加密存储）'}, {'field': 'user_type', 'type': 'tinyint', 'comment': '用户类型 1 = 管理员 2 = 教师 3 = 学生'}, {'field': 'real_name', 'type': 'varchar', 'comment': '真实姓名'}, {'field': 'phone', 'type': 'varchar', 'comment': '联系电话'}, {'field': 'email', 'type': 'varchar', 'comment': '邮箱'}, {'field': 'status', 'type': 'tinyint', 'comment': '状态 0 = 禁用 1 = 正常'}, {'field': 'create_time', 'type': 'datetime', 'comment': '创建时间'}, {'field': 'update_time', 'type': 'datetime', 'comment': '更新时间'}], 'student': [{'field': 'student_id', 'type': 'bigint', 'comment': '学生主键 ID'}, {'field': 'user_id', 'type': 'bigint', 'comment': '关联系统用户 ID'}, {'field': 'student_no', 'type': 'varchar', 'comment': '学号'}, {'field': 'dept_id', 'type': 'bigint', 'comment': '所属院系 ID'}, {'field': 'class_id', 'type': 'bigint', 'comment': '所属班级 ID'}, {'field': 'gender', 'type': 'tinyint', 'comment': '性别 0 = 女 1 = 男'}, {'field': 'id_card', 'type': 'varchar', 'comment': '身份证号'}, {'field': 'address', 'type': 'varchar', 'comment': '家庭住址'}, {'field': 'enrollment_date', 'type': 'date', 'comment': '入学日期'}, {'field': 'status', 'type': 'tinyint', 'comment': '状态 1 = 在读 2 = 毕业 3 = 休学'}]}
"""

schema 结构

{"<tbname>":[{"colname":"","type":"","comment":""},]}

schema转mysql

mysqlstr=myCvt.schema2mysql(mysqlschema)
print(mysqlstr)
#输出结果：
"""
CREATE TABLE IF NOT EXISTS sys_user(`user_id` bigint comment '用户主键 ID',`username` varchar comment '登录账号（学号 / 工号）',`password` varchar comment '登录密码（加密存储）',`user_type` tinyint comment '用户类型 1 = 管理员 2 = 教师 3 = 学生',`real_name` varchar comment '真实姓名',`phone` varchar comment '联系电话',`email` varchar comment '邮箱',`status` tinyint comment '状态 0 = 禁用 1 = 正常',`create_time` datetime comment '创建时间',`update_time` datetime comment '更新时间')
CREATE TABLE IF NOT EXISTS student(`student_id` bigint comment '学生主键 ID',`user_id` bigint comment '关联系统用户 ID',`student_no` varchar comment '学号',`dept_id` bigint comment '所属院系 ID',`class_id` bigint comment '所属班级 ID',`gender` tinyint comment '性别 0 = 女 1 = 男',`id_card` varchar comment '身份证号',`address` varchar comment '家庭住址',`enrollment_date` date comment '入学日期',`status` tinyint comment '状态 1 = 在读 2 = 毕业 3 = 休学')
"""

schema转hiveschema ，然后转成hivesql

hiveschema=myCvt.mysqlSchema2hiveSchema(schema=mysqlschema)# 只做了类型转换
print(hiveschema)
"""
{'sys_user': [{'field': 'user_id', 'type': 'bigint', 'comment': '用户主键 ID'}, {'field': 'username', 'type': 'varchar', 'comment': '登录账号（学号 / 工号）'}, {'field': 'password', 'type': 'varchar', 'comment': '登录密码（加密存储）'}, {'field': 'user_type', 'type': 'tinyint', 'comment': '用户类型 1 = 管理员 2 = 教师 3 = 学生'}, {'field': 'real_name', 'type': 'varchar', 'comment': '真实姓名'}, {'field': 'phone', 'type': 'varchar', 'comment': '联系电话'}, {'field': 'email', 'type': 'varchar', 'comment': '邮箱'}, {'field': 'status', 'type': 'tinyint', 'comment': '状态 0 = 禁用 1 = 正常'}, {'field': 'create_time', 'type': 'datetime', 'comment': '创建时间'}, {'field': 'update_time', 'type': 'datetime', 'comment': '更新时间'}], 'student': [{'field': 'student_id', 'type': 'bigint', 'comment': '学生主键 ID'}, {'field': 'user_id', 'type': 'bigint', 'comment': '关联系统用户 ID'}, {'field': 'student_no', 'type': 'varchar', 'comment': '学号'}, {'field': 'dept_id', 'type': 'bigint', 'comment': '所属院系 ID'}, {'field': 'class_id', 'type': 'bigint', 'comment': '所属班级 ID'}, {'field': 'gender', 'type': 'tinyint', 'comment': '性别 0 = 女 1 = 男'}, {'field': 'id_card', 'type': 'varchar', 'comment': '身份证号'}, {'field': 'address', 'type': 'varchar', 'comment': '家庭住址'}, {'field': 'enrollment_date', 'type': 'date', 'comment': '入学日期'}, {'field': 'status', 'type': 'tinyint', 'comment': '状态 1 = 在读 2 = 毕业 3 = 休学'}]}
"""
hivesqlsr=myCvt.hiveSchema2HiveSql(hiveschema)
print(hivesqlsr)
"""
CREATE EXTERNAL TABLE IF NOT EXISTS sys_user(`user_id` bigint comment '用户主键 ID',`username` varchar comment '登录账号（学号 / 工号）',`password` varchar comment '登录密码（加密存储）',`user_type` tinyint comment '用户类型 1 = 管理员 2 = 教师 3 = 学生',`real_name` varchar comment '真实姓名',`phone` varchar comment '联系电话',`email` varchar comment '邮箱',`status` tinyint comment '状态 0 = 禁用 1 = 正常',`create_time` datetime comment '创建时间',`update_time` datetime comment '更新时间')
CREATE EXTERNAL TABLE IF NOT EXISTS student(`student_id` bigint comment '学生主键 ID',`user_id` bigint comment '关联系统用户 ID',`student_no` varchar comment '学号',`dept_id` bigint comment '所属院系 ID',`class_id` bigint comment '所属班级 ID',`gender` tinyint comment '性别 0 = 女 1 = 男',`id_card` varchar comment '身份证号',`address` varchar comment '家庭住址',`enrollment_date` date comment '入学日期',`status` tinyint comment '状态 1 = 在读 2 = 毕业 3 = 休学')
"""

schema 转dataxJsonlist

fieldlst=myCvt.schema2dataxjson(hiveschema,'field','type') 
#datax hdfswriter的json结构有column页就是选择,值得注意的是field要替换为name
print(fieldlst)
"""
{'sys_user': [{'field': 'user_id', 'type': 'bigint'}, {'field': 'username', 'type': 'varchar'}, {'field': 'password', 'type': 'varchar'}, {'field': 'user_type', 'type': 'tinyint'}, {'field': 'real_name', 'type': 'varchar'}, {'field': 'phone', 'type': 'varchar'}, {'field': 'email', 'type': 'varchar'}, {'field': 'status', 'type': 'tinyint'}, {'field': 'create_time', 'type': 'datetime'}, {'field': 'update_time', 'type': 'datetime'}], 'student': [{'field': 'student_id', 'type': 'bigint'}, {'field': 'user_id', 'type': 'bigint'}, {'field': 'student_no', 'type': 'varchar'}, {'field': 'dept_id', 'type': 'bigint'}, {'field': 'class_id', 'type': 'bigint'}, {'field': 'gender', 'type': 'tinyint'}, {'field': 'id_card', 'type': 'varchar'}, {'field': 'address', 'type': 'varchar'}, {'field': 'enrollment_date', 'type': 'date'}, {'field': 'status', 'type': 'tinyint'}]}
"""
sqlfields=myCvt.schema2SingleList(mysqlschema,'field')
#mysqlreader配置json时，column需要list列
print(sqlfields)
"""
{'sys_user': ['user_id', 'username', 'password', 'user_type', 'real_name', 'phone', 'email', 'status', 'create_time', 'update_time'], 'student': ['student_id', 'user_id', 'student_no', 'dept_id', 'class_id', 'gender', 'id_card', 'address', 'enrollment_date', 'status']}
"""

sql创提取到schema

raw_sqlstr="""CREATE external TABLE IF NOT EXISTS db_business.t_abnormal_enterprises(`id` int comment '自增id',`id_num` varchar(255) comment '备"(用"\'),字段',`area` varchar(255) comment '生产经营地址',`certificates_type` varchar(255) comment '证件种类',`judge_area` varchar(255) comment '认定单位的地址',`judge_date` varchar(255) comment '认定日期',`judge_department` varchar(255) comment '认定单位',`row_update_time` TIMESTAMP comment '数据库行更新时间',`date` date)
comment "abc(er),.()!";CREATE  TABLE IF NOT EXISTS db_business.t_creditimportexport_data(`id` bigint comment '自增id',`ename` varchar(255) comment '企业名称',`url` varchar(255) comment '数据源链接',`credit_no` varchar(50) comment '统一社会信用代码',`customs_num` varchar(100) comment '海关注册编码',`customs_reg` varchar(100) comment '注册海关',`business_category` varchar(100) comment '经营类别',`org_code` varchar(100) comment '组织机构代码',`reg_date` varchar(50) comment '注册日期',`reg_area` varchar(255) comment '工商注册地址',`administrative_divisions` varchar(100) comment '行政区划',`economic_regions` varchar(100) comment '经济区划',`industry_type` varchar(100) comment '行业种类')"""
newschema=myCvt.mysql2schema(raw_sqlstr)
print(newschema)
"""
{'db_business.t_abnormal_enterprises': [{'field': 'id', 'type': 'int', 'comment': '自增id'}, {'field': 'id_num', 'type': 'varchar(255)', 'comment': '备"(用"\'),字段'}, {'field': 'area', 'type': 'varchar(255)', 'comment': '生产经营地址'}, {'field': 'certificates_type', 'type': 'varchar(255)', 'comment': '证件种类'}, {'field': 'judge_area', 'type': 'varchar(255)', 'comment': '认定单位的地址'}, {'field': 'judge_date', 'type': 'varchar(255)', 'comment': '认定日期'}, {'field': 'judge_department', 'type': 'varchar(255)', 'comment': '认定单位'}, {'field': 'row_update_time', 'type': 'TIMESTAMP', 'comment': '数据库行更新时间'}, {'field': 'date', 'type': 'date', 'comment': ''}], 'db_business.t_creditimportexport_data': [{'field': 'id', 'type': 'bigint', 'comment': '自增id'}, {'field': 'ename', 'type': 'varchar(255)', 'comment': '企业名称'}, {'field': 'url', 'type': 'varchar(255)', 'comment': '数据源链接'}, {'field': 'credit_no', 'type': 'varchar(50)', 'comment': '统一社会信用代码'}, {'field': 'customs_num', 'type': 'varchar(100)', 'comment': '海关注册编码'}, {'field': 'customs_reg', 'type': 'varchar(100)', 'comment': '注册海关'}, {'field': 'business_category', 'type': 'varchar(100)', 'comment': '经营类别'}, {'field': 'org_code', 'type': 'varchar(100)', 'comment': '组织机构代码'}, {'field': 'reg_date', 'type': 'varchar(50)', 'comment': '注册日期'}, {'field': 'reg_area', 'type': 'varchar(255)', 'comment': '工商注册地址'}, {'field': 'administrative_divisions', 'type': 'varchar(100)', 'comment': '行政区划'}, {'field': 'economic_regions', 'type': 'varchar(100)', 'comment': '经济区划'}, {'field': 'industry_type', 'type': 'varchar(100)', 'comment': '行业种类'}]}
"""