sqlacodegen自动生成sqlalchemy表对象模型
使用ORM(Object-Relational Mapping)技术,把关系数据库的表结构映射到对象上,从而面向对象编程,就不需要再去和复杂的SQL语句打交道,只需简单的操作实体对象的属性和方法 。Python中较好用的ORM框架是SQLAlchemy。
为了使用SQLAlchemy,需要将数据库表按照其识别的格式创建成Model,但一般情况下都是已经创建并定义好了数据库表,再创建Model。如果人工根据表结构写Model,不仅费时费力,还难免会出错。
而使用sqlacodegen,则可自动生成sqlalchemy表对象模型。
1. 环境配置
pip install sqlacodegen
pip install pymysql (此前已安装,可以省略)
pip install sqlalchemy (此前已安装,可以省略)
2. 使用方式
sqlacodegen 官方文档:https://pypi.org/project/sqlacodegen/
更具体的用法可看help(sqlacodegen --help)
–tables 指定数据表名称
–outfile 指定输出文件名称(可使用路径)
mysql+pymysql://数据库账号:密码@ip:端口/库名?charset=utf8
命令行运行命令示例如下:
sqlacodegen --tables sqlacodegen_test --outfile sqlacodegen_test.py mysql+pymysql://root:123456@127.0.0.1:3306/test?charset=utf8
建表语句:
CREATE TABLE `sqlacodegen_test` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT ,
`city_id` int(11) NOT NULL DEFAULT 0 ,
`site` smallint(6) NOT NULL DEFAULT 1 ,
`code` varchar(20) NOT NULL ,
`type` int(11) NOT NULL DEFAULT 0 ,
`quality` double NOT NULL DEFAULT 0 ,
`rank` int(11) NOT NULL DEFAULT 1 ,
`detail` varchar(500) NOT NULL DEFAULT '0' ,
`status` tinyint(4) NOT NULL DEFAULT 0 ,
`ctime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ,
`mtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARACTER SET=utf8 ROW_FORMAT=DYNAMIC;
生成的model:
# coding: utf-8
from sqlalchemy import BIGINT, Column, Float, INTEGER, Index, SMALLINT, TIMESTAMP, VARCHAR, text
from sqlalchemy.dialects.mysql.types import TINYINT
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
metadata = Base.metadata
class SqlacodegenTest(Base):
__tablename__ = 'sqlacodegen_test'
__table_args__ = (
Index('idx_house_agent_site', 'code', 'site', unique=True),
)
id = Column(BIGINT(20), primary_key=True)
city_id = Column(INTEGER(11), nullable=False, server_default=text("'0'"))
site = Column(SMALLINT(6), nullable=False, server_default=text("'1'"))
code = Column(VARCHAR(20), nullable=False, index=True)
type = Column(INTEGER(11), nullable=False, server_default=text("'0'"))
quality = Column(Float(asdecimal=True), nullable=False, server_default=text("'0'"))
rank = Column(INTEGER(11), nullable=False, server_default=text("'1'"))
detail = Column(VARCHAR(500), nullable=False, server_default=text("'0'"))
status = Column(TINYINT(4), nullable=False, server_default=text("'0'"))
ctime = Column(TIMESTAMP, nullable=False, server_default=text("'0000-00-00 00:00:00'"))
mtime = Column(TIMESTAMP, nullable=False, server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"))
注意:
- 生成后要把整型后的字符大小去掉,否则会报错,如:INTEGER(11) 改为 INTEGER
- table必须要有主键,否则转化成的是Table类型而不是class。
# coding: utf-8
from sqlalchemy import BIGINT, Column, Float, INTEGER, Index, MetaData, SMALLINT, TIMESTAMP, Table, VARCHAR, text
from sqlalchemy.dialects.mysql.types import TINYINT
metadata = MetaData()
t_sqlacodegen_test = Table(
'sqlacodegen_test', metadata,
Column('id', BIGINT(20), nullable=False),
Column('city_id', INTEGER(11), nullable=False, server_default=text("'0'")),
Column('site', SMALLINT(6), nullable=False, server_default=text("'1'")),
Column('code', VARCHAR(20), nullable=False, index=True),
Column('type', INTEGER(11), nullable=False, server_default=text("'0'")),
Column('quality', Float(asdecimal=True), nullable=False, server_default=text("'0'")),
Column('rank', INTEGER(11), nullable=False, server_default=text("'1'")),
Column('detail', VARCHAR(500), nullable=False, server_default=text("'0'")),
Column('status', TINYINT(4), nullable=False, server_default=text("'0'")),
Column('ctime', TIMESTAMP, nullable=False, server_default=text("'0000-00-00 00:00:00'")),
Column('mtime', TIMESTAMP, nullable=False, server_default=text("CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP")),
Index('idx_house_agent_site', 'code', 'site', unique=True)
)
--------------------------文档信息--------------------------
版权声明:本文为博主原创文章,未经博主允许不得转载
署名(BY) :dkjkls(dkj卡洛斯)
文章出处:http://blog.csdn.net/dkjkls