DAY13 SQLAlchemy with table operation and fortress machine

1. Creating tables, inserting data, and one-to-many queries

#！ /usr/bin/env python#-*-coding:utf-8-*-# author:wanghuafengfrom sqlalchemy.ext.declarative Import declarative_ Basefrom sqlalchemy Import Column, Integer, String, ForeignKey, UniqueConstraint, Indexfrom sqlalchemy.orm Import session Maker, Relationshipfrom sqlalchemy Import create_engineengine = Create_engine ("mysql+pymysql://root:[email  Protected]:3306/s13 ", Max_overflow =5) Base = Declarative_base () #单表class Test (base): __tablename__ = ' Test ' nid = Col Umn (Integer, Primary_key=true, autoincrement=true) name = Column (String) #一对多class Group (Base): __tablename__ = '    Group ' nid = Column (Integer, Primary_key=true, autoincrement=true) caption = column (String ()) class User (Base):    __tablename__ = ' user ' nid = Column (Integer, primary_key=true, autoincrement=true) Username = column (String (32)) #外键 group_id = Column (Integer, ForeignKey ("Group.nid")) # is only used for query #uuu代表虚拟列: [1 Wang, 3 xiaoming, 4 Xiaoxiao] #re Lationship and ForeignKey are generally together group = relAtionship ("Group", backref= ' UUU ') #只是对print对象的时候有用 def __repr__ (self): #return "<nid=%s, username=%s, Grou P_id=%s> "% (Self.nid, self.username, self.group_id) temp ="%s-%s-%s "% (Self.nid, Self.username, Self.grou P_ID) return temp# CREATE Table Def init_db (): Base.metadata.create_all (Engine) def drop_db (): Base.metadata.drop_all (Eng INE) init_db () #创建组Session = Sessionmaker (bind=engine) session = Session () Session.add (Group (caption = ' DBA ')) Session.add (Group (caption = ' dbd ')) session.commit () #只获取用户ret = Session.query (User). Filter (user.username== ' Wang '). All () print (ret) ret = Session.query (User). All () obj = Ret[0]print (obj) print (obj.nid) print (obj.username) print ( OBJ.GROUP_ID) ret = Session.query (user.username). All () print (ret) #左连接isouter =true# simultaneously fetch two tables session.query (User, Group) sql = Session.query (User, group). Join (Group, isouter=true) sql = Session.query (user.username, group.caption). Join ( Group, isouter=true) print (SQL) ret = session.query (User.username, group.caption). JoiN (Group, isouter=true). All () #select * from the user left join Group on user.group_id = Group.nidprint (ret) #新方式 (forward query): relation    The ship is in this table and queries the data for the tables ret = Session.query (User). All () for obj in ret: # Obj.group:obj represents each row of data for the User table # Obj.group as a group object Print (Obj.nid, Obj.username, obj.group_id, Obj.group, Obj.group.nid, obj.group.caption) #列出组中的所有人 # ret = Session.query ( User.username, group.caption). Join (Group, isouter=true). Filter (Group.caption = = ' dba '). All () # print (ret) # New Way (reverse query): relationship is not in this table and queries the data of other tables obj = Session.query (Group). Filter (Group.caption = = ' dba '). First () print ( Obj.nid, obj.caption) print (OBJ.UUU)

2, many-to-many associations

#！ /usr/bin/env python#-*-coding:utf-8-*-# author:wanghuafengfrom sqlalchemy.ext.declarative Import declarative_ Basefrom sqlalchemy Import Column, Integer, String, ForeignKey, UniqueConstraint, Indexfrom sqlalchemy.orm Import session Maker, Relationshipfrom sqlalchemy Import create_engineengine = Create_engine ("mysql+pymysql://root:[email  Protected]:3306/s13 ", Max_overflow =5) Base = Declarative_base () #多对多class Host (base): __tablename__ = ' host ' nid = Co Lumn (Integer, primary_key=true, autoincrement=true) hostname = column (string ()) port = column (string ()) IP = Column (String) class Hostuser (Base): __tablename__ = ' host_user ' nid = Column (Integer, Primary_key=true, AUTOINCR Ement=true) Username = Column (String ()) class Hosttohostuser (Base): __tablename__ = ' Host_to_hostuser ' #增加nid, convenient    Later delete nid = Column (integer, Primary_key=true, autoincrement=true) host_id = Column (Integer, ForeignKey (' Host.nid ')) host_user_id = Column (Integer, ForeignkEY (' Host_user.nid ')) #创建表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_all ([Host (hostname= ' C1 '), Port= ' + ', ip= ' 1.2.1.2 '), host (hostname= ' C2 ', port= ' a ', ip= ' 1.2.1.3 '), Host (Hostname= ' C3 ', port= ' a ', ip= ' 1.2.1.1 ') , Host (hostname= ' C4 ', port= ', ip= ' 1.2.1.4 '), host (hostname= ' C5 ', port= ' a ', ip= ' 1.2.1.5 '),]) Session.commit () Sessi On.add_all ([Hostuser (username= ' root '), Hostuser (username= ' MySQL '), Hostuser (username= ' svn '), Hostuser (Usernam E= ' git '), Hostuser (username= ' Oracle '),]) Session.commit () Session.add_all ([Hosttohostuser (host_id= ' 1 ', host_user_ id=1), Hosttohostuser (host_id= ' 1 ', host_user_id=2), Hosttohostuser (host_id= ' 1 ', host_user_id=3), HostToHostUser (H Ost_id= ' 2 ', host_user_id=4), Hosttohostuser (host_id= ' 2 ', host_user_id=5), Hosttohostuser (host_id= ' 2 ', host_user_id= 1), Hosttohostuser (host_id= ' 3 ', host_user_id=1), Hosttohostuser (host_id= ' 3 ', host_user_id=2), Hosttohostuser (host_id= ' 3 ', host_user_id=3), HostToHostU Ser (host_id= ' 4 ', host_user_id=4), Hosttohostuser (host_id= ' 4 ', host_user_id=5), Hosttohostuser (host_id= ' 4 ', host_use r_id=1), Hosttohostuser (host_id= ' 5 ', host_user_id=4), Hosttohostuser (host_id= ' 5 ', host_user_id=5), HostToHostUser (host_id= ' 5 ', host_user_id=1),]) Session.commit () #多对多操作数据 # gets all users in Host 1 host_obj = Session.query (host). Filter ( Host.hostname = = ' C1 '). First () #host_obj. Nid (Find host id) Host_2_host_user = session.query (hosttohostuser.host_user_id). Filter (hosttohostuser.host_id = = Host_obj.nid). All () print (Host_2_host_user) #[(1,), (2,), (3,)]r = Zip (*host_2_host_ User) #print (list (R) [0]) #[1, 2, 3]users = Session.query (hostuser.username). Filter (HostUser.nid.in_ (list (R) [0]). All () print (users)

3, many-to-many query the simplest way

#！ /usr/bin/env python#-*-coding:utf-8-*-# author:wanghuafengfrom sqlalchemy.ext.declarative Import declarative_ Basefrom sqlalchemy Import Column, Integer, String, ForeignKey, UniqueConstraint, Indexfrom sqlalchemy.orm Import session Maker, Relationshipfrom sqlalchemy Import create_engineengine = Create_engine ("mysql+pymysql://root:[email  Protected]:3306/s13 ", Max_overflow =5) Base = Declarative_base () #多对多class Host (base): __tablename__ = ' host ' nid = Co Lumn (Integer, primary_key=true, autoincrement=true) hostname = column (string ()) port = column (string ()) IP = Column (String) class Hostuser (Base): __tablename__ = ' host_user ' nid = Column (Integer, Primary_key=true, AUTOINCR Ement=true) Username = Column (String ()) class Hosttohostuser (Base): __tablename__ = ' Host_to_hostuser ' #增加nid, convenient    Later delete nid = Column (integer, Primary_key=true, autoincrement=true) host_id = Column (Integer, ForeignKey (' Host.nid ')) host_user_id = Column (Integer, ForeignkEY (' Host_user.nid ')) host = relationship ("host", backref= ' h ') Host_user = relationship ("Hostuser", backref= ' u ') Sessi On = Sessionmaker (bind=engine) session = Session () #获取主机1中的所有用户 # host host_obj = Session.query (host). Filter (host.hostname= = ' C1 '). First () #host_to_hostuser表中的对象 #print (host_obj.h) for item in Host_obj.h:print (Item.host_user, Item.host_ User.nid, Item.host_user.username)

　　

DAY13 SQLAlchemy with table operation and fortress machine