脚本工具---自动解析mysql建表语句,生成sqlalchemy表对象声明
常规建表语句:
CREATE TABLE `test_table` ( `id` int(11) NOT NULL, `name` char(64) NOT NULL, `password` char(64) NOT NULL, PRIMARY KEY (`name`,`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='test';
解析脚本代码:
# coding:utf-8
import re
def table_design_transfer(table_design): type_map = {"varchar":"String","datetime":"DateTime","bigint":"BigInteger","smallint":"SmallInteger","tinyint":"SmallInteger","text":"Text","int":"Integer","double":"Float","char":"String","set":"Enum"} l = table_design.split("\n") # 表设计行拆分 length = len(l) s = [] primary_key_l = [] for i in range(length): # 遍历表设计行 j = l[length-1-i].strip().split(" ") # 倒序遍历,并按空格切分 if len(j)>2: # 只关注行长度超过2的元素 column = j[0].replace("`","") i_type = j[1] if column == "PRIMARY": primary_key_l = re.sub(r'`|\(|\)','',j[2]).split(",") # 拿到主键key continue elif column == "CREATE": # 获取表名 table_name = j[2].replace("`","") s.append(" "+'__tablename__ = "%s"' % table_name) s.append("class %s(Base):" % table_name) continue elif column in ("UNIQUE",")","KEY"): # 非表列名,跳过 continue if i_type in type_map.keys(): # 类型存在映射表中 i_type = i_type.replace(i_type,type_map[i_type])+"()" elif "(" in i_type and i_type.split("(")[0] in type_map.keys(): # 类型有长度声明,提取类型字段,找到映射表映射value,并替换 old_type = i_type.split("(")[0] new_type = type_map[i_type.split("(")[0]] i_type = i_type.replace(old_type,new_type) else: print "Catch any case not in type_map:%s" % i_type if column in primary_key_l: # 列名存在主键数组中 i_type = i_type + ", primary_key=True" s.append(" "+column + " = Column(" + i_type + ")") for i in s[::-1]: # 反序输出 print i
输出结果:
class test_table(Base): __tablename__ = "test_table" id = Column(Integer(11), primary_key=True) name = Column(String(64), primary_key=True) password = Column(String(64))
sqlalchemy库官方文档:http://docs.sqlalchemy.org/en/latest/contents.html
sqlalchemy库官方文档(中文):http://www.cnblogs.com/iwangzc/p/4112078.html(感谢作者的分享)
faker库官方文档:https://faker.readthedocs.io/en/master/locales/zh_CN.html
faker库博客:https://www.jianshu.com/p/6bd6869631d9
posted on 2019-01-24 11:03 ExplorerMan 阅读(412) 评论(0) 编辑 收藏 举报