当前位置：首页 > news >正文

sqlachemy orm create or delete table

news 2025/8/11 0:58:37

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来删除之间的关系

查看全文

http://www.lryc.cn/news/278740.html