python数据库操作之sqlalchemy逆向工程

喜欢ヅ旅行 2022-09-16 11:24 127阅读 0赞

依赖安装

pip install sqlacodegen

数据库配置config.py

import os
    
    from sqlalchemy import create_engine
    from sqlalchemy.engine import Engine
    from sqlalchemy.orm import declarative_base, sessionmaker, Session
    
    HOST = 'localhost'
    PORT = 3306
    USERNAME = 'root'
    PASSWORD = 'root'
    DB = 'demo'
    
    DB_URI = f'mysql+pymysql://{USERNAME}:{PASSWORD}@{HOST}:{PORT}/{DB}'
    
    # 自动生成models
    os.system(f'sqlacodegen {DB_URI} > models.py')
    
    # 操作数据句柄
    engine: Engine = create_engine(DB_URI)
    Base = declarative_base(engine)
    session: Session = sessionmaker(engine)()

右键运行config.py,会在当前目录生成models.py模型文件如下，该文件最好不要改动，后面数据库表改动的话，直接重新生成就好。

# coding: utf-8
    from sqlalchemy import Column, DateTime, Integer, SmallInteger, String, TIMESTAMP, text
    from sqlalchemy.dialects.mysql import TINYINT, VARCHAR
    from sqlalchemy.ext.declarative import declarative_base
    
    Base = declarative_base()
    metadata = Base.metadata
    
    
    class Admin(Base):
        __tablename__ = 'admin'
    
        id = Column(Integer, primary_key=True)
        name = Column(String(50))
        password = Column(String(50))
        status = Column(Integer)
        create_time = Column(DateTime)
        update_time = Column(DateTime)
    
    
    class AdminRole(Base):
        __tablename__ = 'admin_role'
    
        id = Column(Integer, primary_key=True)
        user_id = Column(Integer, nullable=False, comment='用户id')
        role_id = Column(Integer, nullable=False, comment='角色id')
        create_time = Column(TIMESTAMP, nullable=False, server_default=text("CURRENT_TIMESTAMP"), comment='创建时间')

操作数据库

from orm.config import session
    from orm.models import Admin
    
    
    session.add(Admin(name='张三丰', password='123456', status=1))
    session.commit()
    # 查询数据
    admin__all = session.query(Admin.id).all()
    print(admin__all)