练习一:朋友
import sqlalchemy from sqlalchemy import create_engine from sqlalchemy import Column, String, Integer, ForeignKey from sqlalchemy.orm import sessionmaker, relationship from sqlalchemy.ext.declarative import declarative_base engine = create_engine("mysql+pymysql://root:root@127.0.0.1/t2?charset=utf8") Base = declarative_base() class Men_to_Wemon(Base): __tablename__ = "men_to_women" nid = Column(Integer, primary_key=True) men_id = Column(Integer, ForeignKey('men.id')) women_id = Column(Integer, ForeignKey('women.id')) class Men(Base): __tablename__ = 'men' id = Column(Integer, primary_key=True) name = Column(String(40)) age = Column(String(10)) #gf = relationship("Women", secondary=Men_to_Wemon.__table__) class Women(Base): __tablename__ = "women" id = Column(Integer, primary_key=True) name = Column(String(40)) age = Column(String(10)) bf = relationship("Men",secondary=Men_to_Wemon.__table__,backref='gf')
#backref相当于在men表中加了一个关于women的对象叫gf
#secondary是第三张表,用于多对多 #bf = relationship("Men",secondary=Men_to_Wemon.__table__) Base.metadata.create_all(engine) MySession = sessionmaker(bind=engine) session = MySession() m1 = session.query(Men).filter_by(id=2).first() #所有女 w1 = session.query(Women).all() m1.gf = w1 session.add(m1) session.commit()
练习二:主机和分组
import sqlalchemy from sqlalchemy import create_engine from sqlalchemy import Column,String,ForeignKey,Integer from sqlalchemy.orm import relationship,sessionmaker from sqlalchemy.ext.declarative import declarative_base engine = create_engine("mysql+pymysql://root:root@127.0.0.1/t2?charset=utf8") Base = declarative_base() class HostToGroup(Base): __tablename__ = 'host_2_group' nid = Column(Integer,primary_key=True) host_id = Column(Integer,ForeignKey('host.id')) group_id = Column(Integer,ForeignKey('group.id')) class Group(Base): __tablename__ = 'group' id = Column(Integer,primary_key=True) name = Column(String(40),unique=True) class Host(Base): __tablename__ = 'host' id = Column(Integer,primary_key=True) hostname = Column(String(40),unique=True) ip_addr = Column(String(40)) port = Column(String(10),default=22) group = relationship('Group',secondary=HostToGroup.__table__,backref="host_list") Base.metadata.create_all(engine) MySession = sessionmaker(bind=engine) session = MySession() # g1 = Group(name='g1') # g2 = Group(name='g2') # g3 = Group(name='g3') # g4 = Group(name='g4') # # session.add_all([g1,g2,g3,g4]) # session.commit() # # h1 = Host(hostname='h1',ip_addr='192.168.0.0.1') # h2 = Host(hostname='h2',ip_addr='192.168.0.0.2',port=10) # h3 = Host(hostname='h.',ip_addr='192.168.0.0.3',port=1000) # h1.group=[g2,g4] # session.add_all([h1,h2,h3]) # session.commit() # groups = session.query(Group).all() # h2 = session.query(Host).filter(Host.hostname=='h2').first() # h2.group=groups[:-1] # # session.add(h2) # session.commit() g4 = session.query(Group).filter(Group.name=='g4').first() obj1 = session.query(Host).filter(Host.hostname=='h2').update({'port':4214}) obj2 = session.query(Host).filter(Host.hostname=='h2').first() g4.host_list.append(obj2) session.add(g4) session.commit()