SQLAlchemy之一对多关系 1.创建单表class Test(Base): __tablename__ = 'user' nid = Colume(Integer,primary_key=True,autoincrement=True) name = Colume(String(32))2.创建一对多class Team(Base): __tablename__ = 'team' tid = Colume(Integer,primary_key=True,autoincrement=True) caption = Colume(String(32)) class user(Base): __tablename__ = 'user' nid = Colume(Integer,primary_key=True,autoincrement=True) name = Colume(String(32)) team_id = Colume(Integer,ForeignKey('team.gid'))写完类，接下来就是把类转化为数据库表了。3.生成表、删除表def init_db(): #根据Base去找它的子类，把所有的子类生成表。 Base.metadata.create_all(engine) def drop_db(): #把Base所有的子类对应表删除。 Base.metadata.drop_all(engine)from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index from sqlalchemy.orm import sessionmaker, relationship from sqlalchemy import create_engine engine = create_engine("mysql+pymysql://python:python@192.168.0.57:3306/python_mysql", max_overflow=5) Base = declarative_base() # 单表 class Test(Base): __tablename__ = 'test' nid = Column(Integer, primary_key=True, autoincrement=True) name = Column(String(32)) # 一对多 class Team(Base): __tablename__ = 'team' tid = Column(Integer, primary_key=True, autoincrement=True) caption = Column(String(32)) class user(Base): __tablename__ = 'user' nid = Column(Integer, primary_key=True, autoincrement=True) name = Column(String(32)) team_id = Column(Integer, ForeignKey('team.tid')) def init_db(): Base.metadata.create_all(engine) def drop_db(): Base.metadata.drop_all(engine) init_db()执行完上面代码后，就会在对应库生成test、user、group三张表，user表的group_id以group表的gid为外键。4.生成表后开始操作表，添加team表数据from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index from sqlalchemy.orm import sessionmaker, relationship from sqlalchemy import create_engine engine = create_engine("mysql+pymysql://python:python@192.168.0.57:3306/python_mysql", max_overflow=5) Base = declarative_base() # 单表 class Test(Base): __tablename__ = 'test' nid = Column(Integer, primary_key=True, autoincrement=True) name = Column(String(32)) # 一对多 class Team(Base): __tablename__ = 'team' tid = Column(Integer, primary_key=True, autoincrement=True) caption = Column(String(32)) class user(Base): __tablename__ = 'user' nid = Column(Integer, primary_key=True, autoincrement=True) name = Column(String(32)) team_id = Column(Integer, ForeignKey('team.tid')) def init_db(): Base.metadata.create_all(engine) def drop_db(): Base.metadata.drop_all(engine) # init_db() # drop_db() Session = sessionmaker(bind=engine) session = Session() #往team表里插入两条数据 session.add(Team(caption='dba')) session.add(Team(caption='ddd')) session.commit()5. 添加user表数据Session = sessionmaker(bind=engine) session = Session() #批量添加数据；user表的team_id与team表的tid是有外键的，按理来说要插入的team_id的值必须在team表里有对应的tid值，比如这里插入的tead_id是1、2、3，则team表里的tid至少要有1、2、3，不然会插入失败。 #但是，我发现插入没有对应键值的team_id也不会报错。 session.add_all([ User(name='zzz',team_id=1), User(name='sss',team_id=2), User(name='ccc',team_id=3), ]) session.commit()6.查询单表如果仅仅是查询user表的name值，那不需要联合别的表，直接查询单表即可ret = session.query(User).filter(User.name=='zzz').all() obj = ret[0] print(obj.name) #上面的代码等价于这个： ret = session.query(User.name).filter(User.name=='zzz').all() print(ret)7.通过__repr__()方法改变返回值from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index from sqlalchemy.orm import sessionmaker, relationship from sqlalchemy import create_engine engine = create_engine("mysql+pymysql://python:python@192.168.0.57:3306/python_mysql", max_overflow=5) Base = declarative_base() # 创建单表 class Users(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String(32)) extra = Column(String(16)) __table_args__ = ( UniqueConstraint('id', 'name', name='uix_id_name'), Index('ix_id_name', 'name', 'extra'), ) #__repr__方法是注释的，看print(ret)的输出 #def __repr__(self): # return "%s-%s" %(self.id, self.name) def init_db(): Base.metadata.create_all(engine) def drop_db(): Base.metadata.drop_all(engine) init_db() Session = sessionmaker(bind=engine) session = Session() session.add(Users(id=1,name='zsc')) session.commit() ret = session.query(Users).all() print(ret) #结果： [<__main__.Users object at 0x7f1836e80630>] 没有User类里没有__repr__方法时，session.query(Users).all()返回的是类的对象。from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index from sqlalchemy.orm import sessionmaker, relationship from sqlalchemy import create_engine engine = create_engine("mysql+pymysql://python:python@192.168.0.57:3306/python_mysql", max_overflow=5) Base = declarative_base() # 创建单表 class Users(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String(32)) extra = Column(String(16)) __table_args__ = ( UniqueConstraint('id', 'name', name='uix_id_name'), Index('ix_id_name', 'name', 'extra'), ) #__repr__方法取消注释 def __repr__(self): return "%s-%s" %(self.id, self.name) def init_db(): Base.metadata.create_all(engine) def drop_db(): Base.metadata.drop_all(engine) init_db() Session = sessionmaker(bind=engine) session = Session() session.add(Users(id=1,name='zsc')) session.commit() ret = session.query(Users).all() print(ret) #结果： [1-zsc] User类里定义了__repr__方法时，session.query(Users).all()返回的是定义的返回结果。8.联合查询#创建表时指定了外键 ret = session.query(User.name).join(Team).all()等价于SELECT user.name AS FROM user INNER JOIN team ON team.tid = user.team_id #用select的话需要用on指定约束条件，用SQLAlchemy就不用指定了。 #用“isouter=True”指定left join ret = session.query(User.name).join(Team,isouter=True).all()上面的查询，随便是依赖到了别的表，但是结果只是显示了user表的数据，如果想同时显示user和team表的数据，就得用下面的方法了，ret = session.query(User.name,Team.caption).join(Team).all() print(ret) #结果： [('zzz', 'dba'), ('sss', 'ddd')]虽然上面的联合查询已经比直接用select简单了，但是还是很麻烦，所以就有了下面的方法。9.relationship9.1 利用ralationship正向查询正向查询即是使用做外链的表来查询被外链里的数据from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index from sqlalchemy.orm import sessionmaker, relationship from sqlalchemy import create_engine engine = create_engine("mysql+pymysql://python:python@192.168.0.57:3306/python_mysql", max_overflow=5) Base = declarative_base() # 一对多 class Team(Base): __tablename__ = 'team' tid = Column(Integer, primary_key=True, autoincrement=True) caption = Column(String(32)) class User(Base): __tablename__ = 'user' nid = Column(Integer, primary_key=True, autoincrement=True) name = Column(String(32)) team_id = Column(Integer, ForeignKey('team.tid')) #加上底下这行后，不用使用.join()也可实现联表查询 #哪个表做外链，就把relationship加到哪个表 favor = relationship("Team", backref='uuu') def init_db(): Base.metadata.create_all(engine) def drop_db(): Base.metadata.drop_all(engine) # init_db() # drop_db() Session = sessionmaker(bind=engine) session = Session() ret = session.query(User).all() for obj in ret: print(obj.nid,obj.name,obj.favor,obj.favor.tid,obj.favor.caption) #结果： 1 zzz <__main__.Team object at 0x7f5c10d02a20> 1 dba 2 sss <__main__.Team object at 0x7f5c10d026a0> 2 ddd #可见，ret仅仅是User的query结果，而使用obj.favor就相当于是使用Team表，即可直接操作team表。9.2利用ralationship实现反向查询反向查询即是使用被外链的表查询到做外链的数据class Test(Base): __tablename__ = 'test' nid = Column(Integer, primary_key=True, autoincrement=True) name = Column(String(32)) # 一对多 class Team(Base): __tablename__ = 'team' tid = Column(Integer, primary_key=True, autoincrement=True) caption = Column(String(32)) class User(Base): __tablename__ = 'user' nid = Column(Integer, primary_key=True, autoincrement=True) name = Column(String(32)) team_id = Column(Integer, ForeignKey('team.tid')) favor = relationship("Team", backref='uuu') def init_db(): Base.metadata.create_all(engine) def drop_db(): Base.metadata.drop_all(engine) # init_db() # drop_db() Session = sessionmaker(bind=engine) session = Session() ret = session.query(Team).filter(Team.caption == 'dba').all() print(ret[0].tid) print(ret[0].caption) print(ret[0].uuu) #结果： 1 dba [<__main__.User object at 0x7f7d3fa5ba20>] #favor = relationship("Team", backref='uuu')里的uuu的作用就是存储着对应的做外链里的数据；比如user里有7个人是dba组的，这时候print(ret[0].uuu)就会返回7个用户的信息；user里有3个dbb组的，这时候print(ret[0].uuu)就会返回3个相关用户的信息。除了一对多还是多对多关系，多对多是专门建一个中间表来存储两张表的关联关系。SQLAlchemy看着麻烦，其实就是记语法而已，多用即可；先建表，再操作单表，再用连表，在整关系，一对多，多对多。原文转载：https://www.cnblogs.com/fuckily/p/6042743.html