sqlalchemy多对多关系实现

男娘i 2022-07-24 05:20 148阅读 0赞

sqlalchemy多对多关系实现

## 情形一： ##

联系人—–分组

from sqlalchemy import create_engine,String,Integer,Column,ForeignKey,Table,Text,Sequence
    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy.orm import sessionmaker,relationship
    
    db_name = 'mysql+mysqlconnector://root:123456@localhost:3306/test'
    
    Base = declarative_base()
    con_gro = Table('asso',Base.metadata,
                    Column('con_id',Integer,ForeignKey('contact.id')),
                    Column('gro_id',Integer,ForeignKey('mygroup.id')))
    
    class Contact(Base):
        __tablename__ = 'contact'
        id =  Column(Integer,Sequence('contact_id'),primary_key=True)
        name = Column(String(20))
        home_tel = Column(String(20))
        memo = Column(Text)
        groups = relationship("Group",secondary=con_gro,backref="contacts")
    
    class Group(Base):
        __tablename__ = 'mygroup'
        id =  Column(Integer,Sequence('mygroup_id'),primary_key=True)
        name = Column(String(20))
        memo = Column(Text)

## 情形二： ##

用户—–好友

from sqlalchemy import create_engine,Column                 #sql语言构造器
    from sqlalchemy.orm import sessionmaker,relationship        #ORM
    from sqlalchemy.ext.declarative import declarative_base 
    
    from sqlalchemy.types import String,Integer
    from sqlalchemy.dialects.mysql import TINYINT
    from sqlalchemy.schema import ForeignKey
    
    DB_CONNECT_STR = 'mysql+mysqldb://root:123@localhost:3306/test?charset=utf8'
    engine = create_engine(DB_CONNECT_STR,echo=True)    #回显每条执行的语句
    
    BaseModel = declarative_base()
    
    def init_db():
        BaseModel.metadata.create_all(engine)
    
    def drop_db():
        BaseModel.metadata.drop_all(engine)
    
    
    class User(BaseModel):
        __tablename__ = 'users'
    
        id = Column(Integer,primary_key=True)
        password = Column(String(128))
        nick = Column(String(20),nullable=True)
        avatar = Column(Integer,nullable=True)
        gender = Column(TINYINT,nullable=True)
        age = Column(Integer,nullable=True)
        name = Column(String(20),nullable=True)
        time = Column(String(20),nullable=True)
        strategy = Column(Integer,nullable=True)
        friends = relationship('User',
                               secondary="friends",
                               primaryjoin="User.id==friends.c.left_id",
                               secondaryjoin="User.id==friends.c.right_id",
                               backref="friends"
                               )
    
    
    
    class Friends(BaseModel):
        __tablename__ = 'friends'
        left_id = Column(Integer,ForeignKey('users.id'),primary_key=True)
        right_id = Column(Integer,ForeignKey('users.id'),primary_key=True)