sqlachemy orm create or delete table

霸王龙不吃芹菜

已于 2024-01-17 15:32:18 修改

阅读量613

点赞数 9

文章标签：开发语言 python sqlachemy

于 2024-01-08 09:49:14 首次发布

本文链接：https://blog.csdn.net/Steven_yang_1/article/details/135449877

版权

sqlacehmy one to one ------detial to descript

关于uselist的使用。如果你使用orm直接创建表关系,实际上在数据库中是可以创建成多对多的关系,如果加上uselist=False 你会发现你的orm只能查询出来一个，如果不要这个参数orm查询的就是多个，一对多的关系。数据库级别如果也要限制可以自行建立唯一键进行约束。

总结就是：sqlacehmy One to One 是orm级别限制

sqlacehmy 简单创建实例展示:

from sqlalchemy import create_engine from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, DateTime  

Base = declarative_base()

engine = create_engine('mysql+pymysql://root:123456@localhost:3306/test?charset=utf8', echo=True)   class Worker(Base):   # 表名   __tablename__ = 'worker'   id = Column(Integer, primary_key=True)   name = Column(String(50), unique=True)   age = Column(Integer)   birth = Column(DateTime)   part_name = Column(String(50))   # 创建数据表

Base.metadata.create_all(engine)

该方法引入declarative_base模块，生成其对象Base,再创建一个类Worker。一般情况下，数据表名和类名是一致的。tablename用于定义数据表的名称，可以忽略，忽略时默认定义类名为数据表名。然后创建字段id、name、age、birth、part_name，最后使用Base.metadata.create_all(engine)在数据库中创建对应的数据表

数据表的删除

删除数据表的时候，一定要先删除设有外键的数据表，也就是先删除part,然后才能删除worker，两者之间涉及外键，这是在数据库中删除数据表的规则。对于两种不同方式创建的数据表，删除语句也不一样。

Base.metadata.drop_all(engine)

part.drop(bind=engine)

part.drop(bind=engine) Base.metadata.drop_all(engine)

sqlachemy +orm + create table代码


from sqlalchemy import Column, String, create_engine, Integer, Text
from sqlalchemy.orm import sessionmaker,declarative_base
import time


# 创建对象的基类:
Base = declarative_base()


# 定义User对象:
class User(Base):
    # 表的名字:
    __tablename__ = 'wokers'

    # 表的结构:
    id = Column(Integer, autoincrement=True, primary_key=True, unique=True, nullable=False)
    name = Column(String(50), nullable=False)
    sex = Column(String(4), nullable=False)
    nation = Column(String(20), nullable=False)
    birth = Column(String(8), nullable=False)
    id_address = Column(Text, nullable=False)
    id_number = Column(String(18), nullable=False)
    creater = Column(String(32))
    create_time = Column(String(20), nullable=False)
    updater = Column(String(32))
    update_time = Column(String(20), nullable=False, default=time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()),
                         onupdate=time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()))
    comment = Column(String(200))


# 初始化数据库连接:
engine = create_engine('postgresql://postgres:name@pwd:port/dbname')  # 用户名:密码@localhost:端口/数据库名

Base.metadata.create_all(bind=engine)

可级联删除的写法实例

class Parent(Base):
    __tablename__ = "parent"
    id = Column(Integer, primary_key=True)


class Child(Base):
    __tablename__ = "child"
    id = Column(Integer, primary_key=True)
    parentid = Column(Integer, ForeignKey(Parent.id, ondelete='cascade'))
    parent = relationship(Parent, backref="children")

sqlachemy 比较好用的orm介绍链接：https://www.cnblogs.com/DragonFire/p/10166527.html

sqlachemy的级联删除：

https://www.cnblogs.com/ShanCe/p/15381412.html

除了以上例子还列举一下创建多对多关系实例

class UserModel(BaseModel):
    __tablename__ = "system_user"
    __table_args__ = ({'comment': '用户表'})

    username = Column(String(150), nullable=False, comment="用户名")
    password = Column(String(128), nullable=False, comment="密码")
    name = Column(String(40), nullable=False, comment="姓名")
    mobile = Column(String(20), nullable=True, comment="手机号")
    email = Column(String(255), nullable=True, comment="邮箱")
    gender = Column(Integer, default=1, nullable=False, comment="性别")
    avatar = Column(String(255), nullable=True, comment="头像")
    available = Column(Boolean, default=True, nullable=False, comment="是否可用")
    is_superuser = Column(Boolean, default=False, nullable=False, comment="是否超管")
    last_login = Column(DateTime, nullable=True, comment="最近登录时间")
    dept_id = Column(
        BIGINT,
        ForeignKey('system_dept.id', ondelete="CASCADE", onupdate="RESTRICT"),
        nullable=True, index=True, comment="DeptID"
    )

    dept_part = relationship('DeptModel',back_populates='user_part')
    roles = relationship("RoleModel", back_populates='users', secondary=UserRolesModel.__tablename__, lazy="joined")
    positions = relationship("PositionModel", back_populates='users_obj', secondary=UserPositionModel.__tablename__, lazy="joined")


class PositionModel(BaseModel):
    __tablename__ = "system_position_management"
    __table_args__ = ({'comment': '岗位表'})
    postion_number = Column(String(50), nullable=False, comment="岗位编号")
    postion_name = Column(String(50), nullable=False, comment="岗位名称")
    remark = Column(String(100), nullable=True, default="", comment="备注")
    positon_status = Column(Integer, nullable=False, default=0, comment="岗位状态")
    create_user = Column(Integer, nullable=True, comment="创建人")
    update_user = Column(Integer, nullable=True, comment="修改人")

    users_obj = relationship("UserModel", back_populates='positions', secondary=UserPositionModel.__tablename__, lazy="joined")

class UserPositionModel(BaseModel):
    __tablename__ = "system_user_position"
    __table_args__ = ({'comment': '用户岗位关联表'})

    user_id = Column(
        BIGINT,
        ForeignKey("system_user.id", ondelete="CASCADE", onupdate="RESTRICT"),
        primary_key=True, comment="用户ID"
    )
    position_id = Column(
        BIGINT,
        ForeignKey("system_position_management.id", ondelete="CASCADE", onupdate="RESTRICT"),
        primary_key=True, comment="岗位ID"
    )

以上实例是多对多关系，主要是由PositionModel进行量表之间的多对多关系的关联

多对多关系查询

Session=sessionmaker(bind=engine)
sessions=Session()
Userobj=sessions.query(UserModel).filter(UserModel.id == 1).first()
# Positionobj=sessions.query(PositionModel).filter(PositionModel.id == 14).first()
# Userobj.positions.append(Positionobj)
for item in Userobj.positions:
    print(item.postion_name)
sessions.commit()
sessions.close()

2个对象之间是通过relationship 关联参数进行 append 来创建关系

还可以通过remove来删除之间的关系

关于基类外键关系搭建

1.关系中的Mixin

在SQLAlchemy的declarative体系里面，Mixin惯用法也可以用在relationship中。relationship()函数创建的关系，可以用declared_attr方法，消除在复制关系及其绑定字段时可能产生的歧义。如下的示例，将外键和relationship组合在一起，这样Foo和Bar两个派生类，都可以被配置为通过多对一的关系关联到Target类（译注：目前正在做单独使用Superset后端的定制开发，会新增很多张表，似乎本节的方法可以用在模型定义上）。

class RefTargetMixin(object):
    @declared_attr
    def target_id(cls):
        return Column('target_id', ForeignKey('target.id'))

    @declared_attr
    def target(cls):
        return relationship("Target")

class Foo(RefTargetMixin, Base):
    __tablename__ = 'foo'
    id = Column(Integer, primary_key=True)

class Bar(RefTargetMixin, Base):
    __tablename__ = 'bar'
    id = Column(Integer, primary_key=True)

class Target(Base):
    __tablename__ = 'target'
    id = Column(Integer, primary_key=True)

2.基类外键多表关系搭建（foreign_keys的使用）

class RefTargetMixin(object):

    @declared_attr
    def target_id(cls):
        return Column('target_id', ForeignKey('target.id'))

    @declared_attr
    def target(cls):
        return relationship("Target")

class Foo(RefTargetMixin, Base):
    __tablename__ = 'foo'
    id = Column(BIGINT, primary_key=True, autoincrement=True, unique=True, comment='主键ID', nullable=False)
    description = Column(Text, nullable=True, comment="备注")
    name = Column(String(40), nullable=False, comment="部门名称")
    order = Column(Integer, nullable=False, default=1, comment="显示排序")
    parent_id = Column(
        BIGINT,
        ForeignKey("foo.id", ondelete="CASCADE", onupdate="RESTRICT"),
        nullable=True, index=True, comment="父级部门ID"
    )
    available = Column(Boolean, nullable=False, default=True, comment="是否可用")
    # description = Column(Text, nullable=True, comment="备注")

    # user_part = relationship("UserModel", back_populates="dept_part")
    parent = relationship("Foo", foreign_keys=[parent_id], cascade='all, delete-orphan')


class Bar(RefTargetMixin, Base):
    __tablename__ = 'bar'
    id = Column(BIGINT, primary_key=True, autoincrement=True, unique=True, comment='主键ID', nullable=False)
    description = Column(Text, nullable=True, comment="备注")

    name = Column(String(50), nullable=False, comment="菜单名称")
    type = Column(Integer, nullable=False, comment="菜单类型")
    icon = Column(String(50), nullable=False, default="", comment="图标")
    order = Column(Integer, nullable=False, default=1, comment="显示排序")
    permission = Column(String(50), nullable=False, default="", comment="权限标识")
    route_name = Column(String(50), nullable=True, comment="路由名称")
    route_path = Column(String(50), nullable=True, comment="路由路径")
    component_path = Column(String(50), nullable=True, comment="组件路径")
    parent_id = Column(
        BIGINT,
        ForeignKey("bar.id", ondelete="CASCADE", onupdate="RESTRICT"),
        nullable=True, index=True, comment="父级菜单ID"
    )
    parent_name = Column(String(50), nullable=True, comment="父级菜单名称")
    available = Column(Boolean, nullable=False, default=True, comment="是否可用")
    cache = Column(Boolean, nullable=False, default=True, comment="是否缓存")
    hidden = Column(Boolean, nullable=False, default=False, comment="是否隐藏")
    # description = Column(Text, nullable=True, comment="备注")

    parent = relationship("Bar", cascade='all, delete-orphan')
    # roles = relationship("RoleModel", back_populates='menus', secondary=RoleMenusModel.__tablename__, lazy="joined")

class Target(Base):
    __tablename__ = 'target'
    id = Column(BIGINT, primary_key=True, autoincrement=True, unique=True, comment='主键ID', nullable=False)
    description = Column(Text, nullable=True, comment="备注")

    username = Column(String(150), nullable=False, comment="用户名")
    password = Column(String(128), nullable=False, comment="密码")
    name = Column(String(40), nullable=False, comment="姓名")
    mobile = Column(String(20), nullable=True, comment="手机号")
    email = Column(String(255), nullable=True, comment="邮箱")
    gender = Column(Integer, default=1, nullable=False, comment="性别")
    avatar = Column(String(255), nullable=True, comment="头像")
    available = Column(Boolean, default=True, nullable=False, comment="是否可用")
    is_superuser = Column(Boolean, default=False, nullable=False, comment="是否超管")
    last_login = Column(DateTime, nullable=True, comment="最近登录时间")
    dept_id = Column(
        BIGINT,
        ForeignKey('system_dept.id', ondelete="SET NULL", onupdate="RESTRICT"),
        nullable=True, index=True, comment="部门ID"
    )

    dept = relationship('Foo')
    # roles = relationship("RoleModel", secondary=UserRolesModel.__tablename__, lazy="joined")
    # positions = relationship("PositionModel", secondary=UserPositionsModel.__tablename__, lazy="joined")

说明

关于sqlachemy 主键继承问题

class CustomMixin(TimestampMixin):
    """
    自定义公共 ORM 模型
    """

    id = Column(BIGINT, primary_key=True, autoincrement=True, unique=True, comment='主键ID', nullable=False)
    description = Column(Text, nullable=True, comment="备注")

    @declared_attr
    def creator_id(cls):
        return Column(
            BIGINT,
            ForeignKey("system_user.id", ondelete="SET NULL", onupdate="RESTRICT"),
            nullable=True, index=True, comment="创建人"
        )

    @declared_attr
    def creator(cls):
        return relationship("UserModel", foreign_keys=cls.creator_id)

class UserModel(CustomMixin, Model):
    __tablename__ = "system_user"
    __table_args__ = ({'comment': '用户表'})

    username = Column(String(150), nullable=False, comment="用户名")
    password = Column(String(128), nullable=False, comment="密码")
    name = Column(String(40), nullable=False, comment="姓名")
    mobile = Column(String(20), nullable=True, comment="手机号")
    email = Column(String(255), nullable=True, comment="邮箱")
    gender = Column(Integer, default=1, nullable=False, comment="性别")
    avatar = Column(String(255), nullable=True, comment="头像")
    available = Column(Boolean, default=True, nullable=False, comment="是否可用")
    is_superuser = Column(Boolean, default=False, nullable=False, comment="是否超管")
    last_login = Column(DateTime, nullable=True, comment="最近登录时间")
    dept_id = Column(
        BIGINT,
        ForeignKey('system_dept.id', ondelete="SET NULL", onupdate="RESTRICT"),
        nullable=True, index=True, comment="部门ID"
    )

    dept = relationship('DeptModel', primaryjoin="UserModel.dept_id == DeptModel.id")
    roles = relationship("RoleModel", secondary=UserRolesModel.__tablename__, lazy="joined")
    positions = relationship("PositionModel", secondary=UserPositionsModel.__tablename__, lazy="joined")

关于sqlachemy 模型外键 relationship 配置scheme问题解决

仅仅主要配置relationship relationship 关联值

class PositionOut(Basemodel):

        id : int

        name: str

positions = PositionOut

即可！

霸王龙不吃芹菜

关注

9
点赞
踩
8

收藏

觉得还不错? 一键收藏
0
评论
sqlachemy orm create or delete table

用于定义数据表的名称，可以忽略，忽略时默认定义类名为数据表名。然后创建字段id、name、age、birth、part_name，最后使用Base.metadata.create_all(engine)在数据库中创建对应的数据表。删除数据表的时候，一定要先删除设有外键的数据表，也就是先删除part,然后才能删除worker，两者之间涉及外键，这是在数据库中删除数据表的规则。对于两种不同方式创建的数据表，删除语句也不一样。一般情况下，数据表名和类名是一致的。
复制链接

扫一扫