1. Toy模板网首页
  2. > Toy博客

sqlachemy orm create or delete table

9月前 作者：霸王龙不吃芹菜 分类：Toy博客 阅读(42) 违法举报

这篇具有很好参考价值的文章主要介绍了sqlachemy orm create or delete table。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

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 orm create or delete table,开发语言,python,sqlachemy

关于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

即可！文章来源地址https://www.toymoban.com/news/detail-821845.html

到了这里，关于sqlachemy orm create or delete table的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处： 如若内容造成侵权/违法违规/事实不符，请点击违法举报进行投诉反馈，一经查实，立即删除！

分享到：
领支付宝红包 赞助服务器费用

相关文章

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包，优惠每天领

二维码1

领取红包

二维码2

领红包