自动获取mysql建表语句_脚本工具---自动解析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

deftable_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_typeif 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

输出结果：

classtest_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