sqlacodegen 的使用

Python利用sqlacodegen自动生成ORM实体类

sqlacodegen 是命令级别,添加模型类以及构建拓展类可以结合 subprocess 或 os 模块进行脚本化操作

sqlacodegen --outfile project/models.py mysql://user_name:pwd@IP：PORT/db

常用配置，具体的配置 sqlacodegen--help 查看需要的配置

--schema SCHEMA load tables from an alternate schema
指定表，默认是全部的表 
--tables TABLES tables to process (comma-separated, default: all)
--noviews ignore views
--noclasses don't generate classes, only tables
指定生成模型类文件
--outfile OUTFILE file to write output to (default: stdout)

1 安装：

pip install sqlacodegen

2 测试数据库链接， postgresql 为例

from sqlalchemy import create_engine, MetaData
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.pool import QueuePool

HOSTNAME = '127.0.0.1'
PORT = '5432'
DATABASE = '**'  
USERNAME = '**'
PASSWORD = '**'
#mysql dialect+driver://username:password@host:port/database
#DB_URI = "mysql+pymysql://{username}:{password}@{host}:{port}/{db}?charset=utf8".format(username=USERNAME,password=PASSWORD,host=HOSTNAME,port=PORT,db=DATABASE)
#postgresql
DB_URI =postgresql://user:password@ip:port/db
# 1 创建引擎
engine = create_engine(DB_URI, echo=True, pool_size=100, pool_recycle=3600, poolclass=QueuePool, max_overflow=10)
# 2判断是否连接成功
conn = engine.connect()
result = conn.execute('select 1')
print(result.fetchone())
# 3创建会话 
　　pass

成功打印：

2020-02-22 21:45:21,353 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE ''
2020-02-22 21:45:21,353 INFO sqlalchemy.engine.base.Engine {}
2020-02-22 21:45:21,368 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
2020-02-22 21:45:21,368 INFO sqlalchemy.engine.base.Engine {}
2020-02-22 21:45:21,372 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2020-02-22 21:45:21,372 INFO sqlalchemy.engine.base.Engine {}
2020-02-22 21:45:21,374 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin'
2020-02-22 21:45:21,374 INFO sqlalchemy.engine.base.Engine {}
2020-02-22 21:45:21,386 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2020-02-22 21:45:21,386 INFO sqlalchemy.engine.base.Engine {}
2020-02-22 21:45:21,394 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2020-02-22 21:45:21,395 INFO sqlalchemy.engine.base.Engine {}
2020-02-22 21:45:21,395 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin AS anon_1
2020-02-22 21:45:21,395 INFO sqlalchemy.engine.base.Engine {}
2020-02-22 21:45:21,400 INFO sqlalchemy.engine.base.Engine select 1
2020-02-22 21:45:21,400 INFO sqlalchemy.engine.base.Engine {}
(1,)

链接失败报错

链接出现报错：

No module named 'psycopg2'

解决方案：

pip install psycopg2

出现报错：

Collecting psycopg2
  Using cached psycopg2-2.8.4.tar.gz (377 kB)
    ERROR: Command errored out with exit status 1:
     command: /home/wang/.pyenv/versions/3.6.4/envs/flask_zhiliao/bin/python3.6 -c 'import sys, setuptools, tokenize; sys.argv[0] = '"'"'/tmp/pip-install-9pbzr1ah/psycopg2/setup.py'"'"'; __file__='"'"'/tmp/pip-install-9pbzr1ah/psycopg2/setup.py'"'"';f=getattr(tokenize, '"'"'open'"'"', open)(__file__);code=f.read().replace('"'"'
'"'"', '"'"'
'"'"');f.close();exec(compile(code, __file__, '"'"'exec'"'"'))' egg_info --egg-base /tmp/pip-install-9pbzr1ah/psycopg2/pip-egg-info
         cwd: /tmp/pip-install-9pbzr1ah/psycopg2/
    Complete output (7 lines):
    running egg_info
    creating /tmp/pip-install-9pbzr1ah/psycopg2/pip-egg-info/psycopg2.egg-info
    writing /tmp/pip-install-9pbzr1ah/psycopg2/pip-egg-info/psycopg2.egg-info/PKG-INFO
    writing dependency_links to /tmp/pip-install-9pbzr1ah/psycopg2/pip-egg-info/psycopg2.egg-info/dependency_links.txt
    writing top-level names to /tmp/pip-install-9pbzr1ah/psycopg2/pip-egg-info/psycopg2.egg-info/top_level.txt
    writing manifest file '/tmp/pip-install-9pbzr1ah/psycopg2/pip-egg-info/psycopg2.egg-info/SOURCES.txt'
    Error: b'You need to install postgresql-server-dev-X.Y for building a server-side extension or libpq-dev for building a client-side application.
'
    ----------------------------------------
ERROR: Command errored out with exit status 1: python setup.py egg_info Check the logs for full command output.

解决方案:

sudo apt-get install libpq-dev python-dev
pip install psycopg2

生成表：

import os
import subprocess

def run():
    args = 'sqlacodegen --noviews --outfile ./models/model.py postgresql://postgres:postgres@192.168.1.1:5432/db_one'
    os.system(args)
    print('写入完成')
    # 拓展类定义 TODO

if __name__ == '__main__':
    run()

查看 model.py

# coding: utf-8
from sqlalchemy import Column, Date, ForeignKey, Integer, String, Text, text
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class Guide(Base):
    __tablename__ = 'guide'

    id = Column(UUID, primary_key=True, server_default=text("uuid_generate_v4()"))
    remark = Column(Text)
    status = Column(Integer, server_default=text("0"))
    addtime = Column(Date, server_default=text("now()"))
    enter_id = Column(UUID)
    filename = Column(String(255))

class GuideCount(Base):
    __tablename__ = 'guide_count'

    id = Column(UUID, primary_key=True, server_default=text("uuid_generate_v4()"))
    downloader_id = Column(UUID)
    down_time = Column(Date, server_default=text("now()"))
    guide_id = Column(ForeignKey('guide.id'))
    counts= Column(Integer, server_default=text("0"))

    guide = relationship('Guide')