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的级联删除:
除了以上例子还列举一下创建多对多关系实例
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
即可!