sqlalchemy中relationship使用
sqlalchemy是python做orm管理的非常重要的工具,sqlalchemy2.0版本relationship与上个版本有所不同,具体如下:
from sqlalchemy import create_engine from sqlalchemy.orm import sessionmaker from sqlalchemy.orm import declarative_base SQLALCHEMY_DATABASE_URL='你的数据库配置信息' engine=create_engine(SQLALCHEMY_DATABASE_URL ,pool_pre_ping=True ) SessionLocal=sessionmaker( autoflush=False, bind=engine) Base=declarative_base()
from typing import Optional,List
from enum import Enum as pyEnum
from sqlalchemy import ForeignKey,SmallInteger,BigInteger,String,Text,Enum
from sqlalchemy.orm import relationship,Mapped,mapped_column
class ResultEnum(pyEnum):
SUCCESS='success'
ERROR='error'
class TestTableInfo(Base):
__tablename__='test_table_info'
id:Mapped[int]=mapped_column(BigInteger,autoincrement=True,primary_key=True)
num:Mapped[Optional[int]]=mapped_column(SmallInteger,default=None,comment='数字')
schema_table:Mapped[str]=mapped_column(String(100),default=None,comment='表名')
table_log_list:Mapped[List['TestTableLog']]=relationship(back_populates='test_table_info')
class TestTableLog(Base):
__tablename__='test_table_log'
id:Mapped[int]=mapped_column(BigInteger,autoincrement=True,primary_key=True)
table_id:Mapped[int]=mapped_column(BigInteger,ForeignKey('test_table_info.id'),index=True,comment='表id')
result:Mapped[Optional[str]]=mapped_column(Enum(ResultEnum),default=None,comment='结果')
test_table_info:Mapped['TestTableInfo']=relationship(back_populates='table_log_list')
from enum import Enum as pyEnum
from sqlalchemy import ForeignKey,SmallInteger,BigInteger,String,Text,Enum
from sqlalchemy.orm import relationship,Mapped,mapped_column
class ResultEnum(pyEnum):
SUCCESS='success'
ERROR='error'
class TestTableInfo(Base):
__tablename__='test_table_info'
id:Mapped[int]=mapped_column(BigInteger,autoincrement=True,primary_key=True)
num:Mapped[Optional[int]]=mapped_column(SmallInteger,default=None,comment='数字')
schema_table:Mapped[str]=mapped_column(String(100),default=None,comment='表名')
table_log_list:Mapped[List['TestTableLog']]=relationship(back_populates='test_table_info')
class TestTableLog(Base):
__tablename__='test_table_log'
id:Mapped[int]=mapped_column(BigInteger,autoincrement=True,primary_key=True)
table_id:Mapped[int]=mapped_column(BigInteger,ForeignKey('test_table_info.id'),index=True,comment='表id')
result:Mapped[Optional[str]]=mapped_column(Enum(ResultEnum),default=None,comment='结果')
test_table_info:Mapped['TestTableInfo']=relationship(back_populates='table_log_list')
TestTableInfo类和TestTableLog是一对多的关系,其中TestTableLog类中的table_id是外键,关联test_table_info表中的id。
两个类中的relationship必须是一一对应的,table_log_list必须是TestTableLog中relationship的back_populates。同理,test_table_info也一样。