Python Learning Notes-SQLAlchemy (bottom)

Continue on a sqlalchemy learning journey.

Creation of many-to-many tables

Table host and table Hostuser are associated by table Hosttohostuser

from sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import  Declarative_basefrom sqlalchemy import column, integer, string, foreignkey,  UniqueConstraint, Index,Tablefrom sqlalchemy.orm import sessionmaker,  Relationshipengine = create_engine ("Mysql+pymysql://yli:[email protected]:3306/mydb",  max_ overflow=5) Base = declarative_base () Class hosttohostuser (Base):     __ tablename__ =  ' Host_to_host_user '     nid = column (Integer,  Primary_key=true,autoincrement=true)     host_id = column (Integer , ForeignKey (' Host.nid '))     host_user_id = column (Integer, foreignkey (' Host_ User.nid ')) Class host (Base):  # metaclass,host.table object     __tablename__  =  ' Host '     NID =&NBsp Column (integer, primary_key=true,autoincrement=true)     hostname = column ( String (+))     port = column (string)     ip =  Column (String)     # host_user = relationship (' Hostuser ',  secondary =hosttohostuser, backref= ' h ')     host_user = relationship (' HostUser ',  secondary=hosttohostuser.__table__, backref= ' h ') Class hostuser (Base):     __ tablename__ =  ' Host_user '     nid = column (Integer, primary_key= True,autoincrement=true)     username = column (String (+)) 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.1.1.1 '),     host (hostname= ' C2 ', port= ', ip= ' 1.1.1.2 '),     host (Hostname= ' C3 ', port= ' a ', ip= ' 1.1.1.3 '),     host (hostname= ' C4 ', port= ' a ', ip= ' 1.1.1.4 '),     host (hostname= ' C5 ', port= ' + ', IP = ' 1.1.1.5 '),] Session.commit () Session.add_all ([    hostuser (username= ' root '),     hostuser (username= ' db '),     hostuser (username= ' NB '),     Hostuser (username= ' SB '),]) 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 (host_id=2,host_user_id=2),     hosttohostuser (host_id=2,host_user_id=4),     hosttohostuser (host_ Id=2,host_user_id=3,]) sessIon.commit () 

The results are as follows

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/8A/5D/wKiom1gug5PTGEhPAABJRS1K_EE945.png "style=" float: none; "title=" 1.PNG "alt=" Wkiom1gug5ptgehpaabjrs1k_ee945.png "/>

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/8A/59/wKioL1gug5STfOkHAABW4OjOmTs833.png "style=" float: none; "title=" 2.PNG "alt=" Wkiol1gug5stfokhaabw4ojomts833.png "/>

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M01/8A/5D/wKiom1gug5TQ-D-sAAA-JyD-sQ8039.png "style=" float: none; "title=" 3.PNG "alt=" Wkiom1gug5tq-d-saaa-jyd-sq8039.png "/>

Example 1. Get all users of host 1, with the same principle as 1 to many, quickly navigate to the corresponding table by relationship

from sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import  Declarative_basefrom sqlalchemy import column, integer, string, foreignkey,  UniqueConstraint, Index,Tablefrom sqlalchemy.orm import sessionmaker,  Relationshipengine = create_engine ("Mysql+pymysql://yli:[email protected]:3306/mydb",  max_ overflow=5) Base = declarative_base () Class hosttohostuser (Base):     __ tablename__ =  ' Host_to_host_user '     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 ')     class host (Base):  # metaclass,host.table object     __tablename__ =  ' Host '      nid = column (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, Autoincrement=true)     username = column (String)      #def  init_db (): #    base.metadata.create_all (Engine) ## def drop_db ():#      base.metadata.drop_all (Engine) #init_db () Session = sessionmaker (Bind=engine) Session = sessiOn () Host_obj = session.query (host). Filter (host.hostname== ' C1 '). First () print (Host_obj.nid) print (host_ Obj.hostname) #  The third table corresponds to the object (reverse query) print (host_obj.h) #  loop gets the corresponding object for item in host_obj.h for the third table:     print (Item.host_user,item.host_user.nid,item.host_user.username)      --------------"C:\program files\python3\python.exe"   "C:/users/yli/documents/tencent files /38144205/filerecv/s13 Class Code/S13 Class code/S13DAY13 Class code/s13day13_ Class code/orm-de,p.py "1c1[<__main__. hosttohostuser object at 0x000002678c0a3cc0>, <__main__. hosttohostuser object at 0x000002678c0b53c8>, <__main__. hosttohostuser object at 0x000002678c0b5438>]<__main__. hostuser object at 0x000002678c0b5748> 1 root<__main__. hostuser object at 0x000002678c0b5908> 2 db<__main__. hostuser object at 0x000002678c0b5ac8> 3&Nbsp;nbprocess finished with exit code 0 

Example 2, another way to correlate multiple tables can be on the group table above

from sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import  Declarative_basefrom sqlalchemy import column, integer, string, foreignkey,  UniqueConstraint, Index,Tablefrom sqlalchemy.orm import sessionmaker,  Relationshipengine = create_engine ("Mysql+pymysql://yli:[email protected]:3306/mydb",  max_ overflow=5) Base = declarative_base () Class hosttohostuser (Base):     __ tablename__ =  ' Host_to_host_user '     nid = column (Integer,  Primary_key=true,autoincrement=true)     host_id = column (Integer , ForeignKey (' Host.nid '))     host_user_id = column (Integer, foreignkey (' Host_ User.nid ')          #注意, this is not a relationship here!     # host = relationship ("host",  backref= ' H ')     # host_user = relationship ("Hostuser",  backref= ' u ') class  Host (Base):  # metaclass,host.table object     __tablename__ =  ' host '      nid = column (integer, primary_key=true,autoincrement=true)      hostname = column (String)     port = column (string))      ip = column (String)         # This time it is an association on the host table, which specifies that the Hosttohostuser table is associated with Hostuser     host_user = relationship (' Hostuser ',  secondary=hosttohostuser.__table__, backref= ' h ') class hostuser (Base):     __tablename__ =  ' Host_user '     nid = column (integer,  Primary_key=true,autoincrement=true)     username = column (String)      # host =&nBsp;relationship ("Host",  backref= ' h ')     # host_user = relationship (" Hostuser ",  backref= ' U ') 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 () Host_obj = session.query ( Host). Filter (host.hostname ==  ' C1 '). First () print (Host_obj.host_user) for item in host_ Obj.host_user:    print (item.username)         ---- --------[<__main__. hostuser object at 0x000001d422bceba8>, <__main__. hostuser object at 0x000001d422bce550>, <__main__. Hostuser object at 0x000001d422bce630>]rootdbnb

The above two ways, personally think the first one easier to understand, the second write code more convenient

This article is from the "Mapo Tofu" blog, please be sure to keep this source http://beanxyz.blog.51cto.com/5570417/1874217

Python Learning Notes-SQLAlchemy (bottom)