#!/usr/bin/env python # -*- coding:utf-8 -*- from sqlalchemy import create_engine,and_,or_,func,Table from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column, Integer, String,ForeignKey from sqlalchemy.orm import sessionmaker,relationship engine = create_engine('sqlite:///dbyuanabcd4970.db', echo=True) Base = declarative_base() #生成一个SqlORM 基类 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 Host(Base): __tablename__ = 'host' id = Column(Integer,primary_key=True,autoincrement=True) hostname = Column(String(64),unique=True,nullable=False) ip_addr = Column(String(128),unique=True,nullable=False) port = Column(Integer,default=22) group = relationship('Group', secondary=HostToGroup.__table__, backref='host_list') #group =relationship("Group",back_populates='host_list') def __repr__(self): return "<id=%s,hostname=%s, ip_addr=%s>" %(self.id, self.hostname, self.ip_addr) class Group(Base): __tablename__ = 'group' id = Column(Integer,primary_key=True) name = Column(String(64),unique=True,nullable=False) def __repr__(self): return "<id=%s,name=%s>" %(self.id,self.name) Base.metadata.create_all(engine) #创建所有表结构 if __name__ == '__main__': SessionCls = sessionmaker(bind=engine) session = SessionCls() # g1 = Group(name='g1') # g2 = Group(name='g2') # g3 = Group(name='g3') # g4 = Group(name='g4') # session.add_all([g1,g2,g3,g4]) # h1 = Host(hostname='h1',ip_addr='192.168.1.56') # h2 = Host(hostname='h2',ip_addr='192.168.1.57',port=10000) # h3 = Host(hostname='ubuntu',ip_addr='192.168.1.58',port=10000) # # 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] # print("===========>",h2.group) # g4 = session.query(Group).filter(Group.name=='g4').first() # print(g4) # obj1 = session.query(Host).filter(Host.hostname=='h1').update({'port':444}) # # obj2= session.query(Host).filter(Host.hostname=='h1').first() # print("h1:",obj2.group) # print("g:",g4.host_list ) session.commit()
#!/usr/bin/env python # -*- coding:utf-8 -*- 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('sqlite:///dbyuan67.db', echo=True) Base = declarative_base() class Men(Base): __tablename__ = 'men' id = Column(Integer, primary_key=True) name = Column(String(32)) age= Column(String(16)) # __table_args__ = ( # UniqueConstraint('id', 'name', name='uix_id_name'), # Index('ix_id_name', 'name', 'extra'), # ) class Women(Base): __tablename__ ='women' id = Column(Integer, primary_key=True) name = Column(String(32)) age= Column(String(16)) men_id=Column(String(20), ForeignKey('men.id'),unique=True) bf=relationship("Men",backref="gf") Base.metadata.create_all(engine) Session = sessionmaker(bind=engine) session = Session() m1=Men(name='alex',age=18) w1=Women(name='如花',age=40) w2=Women(name='铁锤',age=45) m1.gf=[Women(name='如花',age=40),Women(name='铁锤',age=45)] session.add(m1) session.commit()
#!/usr/bin/env python # -*- coding:utf-8 -*- 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('sqlite:///dbyuan691.db', echo=True) Base = declarative_base() class Son(Base): __tablename__ = 'son' id = Column(Integer, primary_key=True) name = Column(String(32)) age= Column(String(16)) father_name=Column(String(20),ForeignKey('father.name')) father=relationship('Father') __table_args__ = ( UniqueConstraint('id', 'name', name='uix_id_name'), Index('ix_id_name', 'name', 'extra'), ) class Father(Base): __tablename__ ='father' id = Column(Integer, primary_key=True) name = Column(String(32)) age= Column(String(16)) son=relationship('Son') Base.metadata.create_all(engine) Session = sessionmaker(bind=engine) session = Session() f1=Father(name='alvin',age=50) w1=Son(name='little alvin1',age=4) w2=Son(name='little alvin2',age=5) f1.son=[w1,w2] session.add(f1) session.commit()
#!/usr/bin/env python # -*- coding:utf-8 -*- 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('sqlite:///dbyuan674uu.db', echo=True) Base = declarative_base() class Men_to_Wemon(Base): __tablename__ = 'men_to_wemon' 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(32)) age= Column(String(16)) class Women(Base): __tablename__ ='women' id = Column(Integer, primary_key=True) name = Column(String(32)) age= Column(String(16)) bf=relationship("Men",secondary=Men_to_Wemon.__table__,backref='gf') Base.metadata.create_all(engine) Session = sessionmaker(bind=engine) session = Session() m1=Men(name='alex',age=18) m2=Men(name='wusir',age=18) w1=Women(name='如花',age=40) w2=Women(name='铁锤',age=45) # t1=Men_to_Wemon(men_id=1,women_id=2) m1.gf=[w1,w2] w1.bf=[m1,m2] session.add_all([m1,m2,w1,w2]) session.commit()
#!/usr/bin/env python # -*- coding:utf-8 -*- 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('sqlite:///dbyuan67.db?') Base=declarative_base() class Men(Base): __tablename__ = 'men' id = Column(Integer, primary_key=True) name = Column(String(32)) age= Column(String(16)) # __table_args__ = ( # UniqueConstraint('id', 'name', name='uix_id_name'), # Index('ix_id_name', 'name', 'extra'), # ) def __repr__(self): return self.name class Women(Base): __tablename__ ='women' id = Column(Integer, primary_key=True) name = Column(String(32)) age= Column(String(16)) men_id=Column(String(20), ForeignKey('men.id')) # def __repr__(self): # return self.age # Base.metadata.create_all(engine) # Base.metadata.drop_all(engine) Session = sessionmaker(bind=engine) session = Session() # select * from # select id,name from women # sql=session.query(Women).all() # select * from women inner join men on women.men_id = men.id sql = session.query(Women.age,Men.name).join(Men).all() print(sql) # print(sql) # r = session.query(session.query(Women.name.label('t1'), Men.name.label('t2')).join(Men).all()).all() # print(r) # r = session.query(Women).all() # print(r) # m1=Men(name='alex',age=18) # w1=Women(name='如花',age=40) # w2=Women(name='铁锤',age=45) # m1.gf=[Women(name='如花',age=40),Women(name='铁锤',age=45)] # m1=Men(name='alex',age=18) # w1=Women(name='如花',age=40,men_id = 1) # w2=Women(name='铁锤',age=45,men_id = 1) # session.add_all([w1,w2]) # session.commit()
扫一扫
6360
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
