Automated Operations Python series ForeignKey, Relationship Union table query

One-to-many and many-to-many

database table structure Design is an important link before the program project development, the late database operation is around the design of the table structure, if the table structure design problems, the whole program project there is a need for the entire overthrow of the risk of reconstruction ...

database table Structure In addition to simple single-table operations, there are one-to-many, many-to-many, and so on.

One-to-many

Based on SQLAlchemy we can first create 2 tables of the following structure, and then see how to do it with foreign keys foreignkey or relationship.

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/8C/6F/wKioL1hss7GgxCE9AAAsM96DK3I864.png "title=" Table Query _ FOREIGN key. png "alt=" Wkiol1hss7ggxce9aaasm96dk3i864.png "/>

Create a table

from sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import  Column, integer, string, foreignkeyfrom sqlalchemy.orm import sessionmaker,  relationshipfrom sqlalchemy import create_engine engine = create_engine (" Mysql+pymysql://root:[email protected]:3306/s13?charset=utf8 ",                                                                        max_overflow=5)  base = declarative_base ()  #   Create User Group table Class group (Base):    __tablename__ =  ' group '      nid = column (INteger, primary_key=true, autoincrement=true)     caption = column ( String (+))  #  Create user table Class user (Base):    __tablename__ =  ' user '     nid = column (integer, primary_key=true, autoincrement=true)      name = column (String)     #  added foreign key   indicates additional user   group_id  can only be present in the table group id  otherwise error     group_id = column (integer,  ForeignKey (' Group.nid '))  def init_db ():     base.metadata.create_all (Engine)  def drop_db ():     base.metadata.drop_all (engine)  # init_db perform CREATE TABLE operation #  init_db ()  #  Instantiate class   Start subsequent query operations Session = sessionmaker (bind=engine) session =  session ()  --------------  Insert data  ---------------------#  Insert Group name # session.add ( caption= ' operations ') # session.add (Group(caption= ' development ')) # session.commit ()  #  Insert User # session.add_all ([#     user (name= ' user _01 ',  group_id=1), #     user (name= ' user_02 ',  group_id=1),#      user (name= ' user_03 ',  group_id=2), # ]) # session.commit ()

__repr__

Single-table query results get a Memory object, we can add a special object method to the table __repr__, custom query display results

#  gets only an object Ret = session.query (User). Filter (user.name ==  ' user_01 '). All () print (ret)  #  output [<day13.s1.user object at 0x00000288737c1898>] #  Add __repr to the User table __ Method   Query automatically executes Class user (Base):    __tablename__ =  ' User '      nid = column (integer, primary_key=true, autoincrement=true)      name = column (String)     group_id = column (Integer,  ForeignKey (' Group.nid '))      # __repr__ method automatically executes     def _ _repr__ (self):        temp =  '%s  %s %s '  % (self.nid, self.name, self.group_id)         return  temp #  results after re-execution [1  user_01 1] obj = ret[0]print (obj.nid,  OBJ.NAME,&NBSP;OBJ.GROUP_ID) #  output 1 user_01 1#  only the user name Ret = session.query (user.name). All () print (ret) #  output [(' User_01 ',),  (' user_02 ',),  (' user_03 ',)]

Linked table Query

# The principle of the Union table query is to automatically generate SQL statements for you and then execute sql = session.query (user). Join (group) print (SQL) ret = session.query (user). Join (Group). All () Print (ret) # left join# ret = Session.query (User). Join (Group, isouter=true). All () # print (ret) # OUTPUT Select User.nid as User_  Nid, User.Name as user_name, user.group_id asuser_group_id from the user INNER JOIN ' group ' on ' group '. Nid = User.group_id[1 User_01 1, 2 user_02 1, 3 user_03 2]

Specifying mapping relationships

# Specify the Mapping relationship Table Query ret = session.query (User.Name, group.caption). Join (Group). All () print (ret) # output [(' user_01 ', ' Ops '), (' User_ 02 ', ' Ops '), (' user_03 ', ' development ')]

Relationship forward Lookup

To simplify federated queries, we can also create a virtual relationship relationship between 2 tables, which is independent of the table structure and is only convenient for our subsequent queries

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/8C/73/wKiom1hss8HCRscyAABhupnkcpA254.png "title=" Table Query _ Forward query. png "alt=" Wkiom1hss8hcrscyaabhupnkcpa254.png "/>

Class user (Base):    __tablename__ =  ' User '     nid  = column (integer, primary_key=true, autoincrement=true)     name  = column (String)     #  foreign key     group_id = column ( Integer, foreignkey (' Group.nid '))     #  Create virtual relationships  relationship  use with foreign keys     group = relationship ("group",  backref= ' UUU ')       def __repr__ (self):        temp =  '%s   %s %s '  % (self.nid, self.name, self.group_id)          return tempret = session.query (User). All () for obj in ret:     # obj  Rep  User    # group  Rep New  Group     print (obj.nid,  obj.name, obj.group_id, obj.group.nid, obj.group.caption)  #  output 1 user_01 1  1  Operation 2 user_02 1 1  operation and maintenance 3 user_03 2 2  development

Relationship Reverse Lookup

Requirements: Find all the things in table 2 for the OPS job.

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M02/8C/6F/wKioL1hss9CDvP7rAABKdLrmr2Y589.png "title=" Table Query _ Reverse query. png "alt=" Wkiol1hss9cdvp7raabkdlrmr2y589.png "/>

#  Check all   OPS   Ret = session.query (user.name, group.caption). Join (group,  Isouter=true) .                    filter (group.caption ==  ' operations ')). All ()                                                                           print (ret)  #  reverse query with relationship  new Way obj  = session.query (Group). Filter (group.caption ==  ' operations '). First () print (obj.nid,  Obj.caption) # uuu  represents everyone under this group   is a list of print (OBJ.UUU)  #  output [(' User_01 ',  ' Ops '),  (' user_02 ',  ' Ops ')] 1  operation and Maintenance [1  USER_01 1, 2  USER_02 1] 

Many-to-many

For example, many servers in a company can log in with many accounts, including root users, which is a simple many-to-many structure

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/8C/73/wKiom1hss-KTgnChAABMSKVmowQ283.png "title=" Many-to-many. png "alt=" Wkiom1hss-ktgnchaabmskvmowq283.png "/>

Old ways to find C2 hosts which accounts can login

# 1, find hostname for C1 nidhost_obj = Session.query (Host). Filter (Host.hostname = = ' C2 '). First () print (Host_obj.nid) # 2, Specifies the mapping relationship to find the corresponding host user Idhost_to_host_user = Session.query (hosttohostuser.host_user_id). filter# (Hosttohos  tuser.host_id = = Host_obj.nid). All () print (Host_to_host_user) # [(1,), (2,), (3,)]# [1, 2, 3] r = Zip (*host_to_host_user) # 3. Find User users = Session.query (hostuser.username). Filter (HostUser.nid.in_ (list (R) [0]). All () print (users) # output 1[(1,), (3,)] [(' Root ',), (' db ',)]

Simplify many-to-many queries with relationship relationships

650) this.width=650; "Src=" http://s2.51cto.com/wyfs02/M02/8C/6F/ Wkiol1hss-2bgrgsaabrhk9_r3e928.png "title=" many-to-many _2.png "alt=" Wkiol1hss-2bgrgsaabrhk9_r3e928.png "/>

from sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import  Column, integer, string, foreignkeyfrom sqlalchemy.orm import sessionmaker,  relationshipfrom sqlalchemy import create_engine engine = create_engine (" Mysql+pymysql://root:[email protected]:3306/s13 ",                                                            max_overflow=5)  Base =  Declarative_base ()  class host (base):    __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)  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 '))      #  building relationships      host = 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)  #  CREATE TABLE # init_db ()  #  Insert Data Session = sessionmaker (bind=engine) session = session ()  # session.add_all ([#      host (hostname= ' C1 ',  port= ' a ',  ip= ' 1.1.1.1 '),#      host (hostname= ' C2 ',  port= ' a ',  ip= ' 1.1.1.2 '), # ]) # session.commit ()  #  Session.add_all ([#     hostuser (username= ' root '),#      Hostuser (username= ' sa '), #     hostuser (username= ' db '), # ]) # session.commit ( )  # session.add_all ([#     hosttohostuser (host_id=1, host_user_id=1), #      hosttohostuser (host_id=1, host_user_id=2),#      Hosttohostuser (HOST_ID=2,&NBsp;host_user_id=1), #     hosttohostuser (host_id=2, host_user_id=3), # ]) #  session.commit ()  # relationship  Many-to-many query Host_obj = session.query (Host). Filter ( host.hostname ==  ' C2 '). First () For item in host_obj.h:    print ( Item.host_user.username)  #  Output Rootdb

From sqlalchemy import create_engine,and_,or_,func,tablefrom sqlalchemy.ext.declarative  import declarative_basefrom sqlalchemy import column, integer, string, Foreignkeyfrom sqlalchemy.orm import sessionmaker,relationship engine = create_ Engine ("Mysql+pymysql://root:[email protected]:3306/s13",                                                             max_overflow=5)  Base  = declarative_base ()  class hosttohostuser (base):     __tablename__  =  ' Host_to_host_user '     nid = column (integer, primary_key=true,  autoincrement=true) &NBsp;    host_id = column (Integer, foreignkey (' Host.nid '))      host_user_id = column (Integer, foreignkey (' Host_user.nid '))  class host (Base):     __tablename__ =  ' Host '     nid = column (Integer ,  primary_key=true, autoincrement=true)     hostname = column (String (32 ))     port = column (String ())     ip = column ( String (+)         #  in one of the tables, add the following secondary     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 (+))  Session  = sessionmaker (Bind=engine) session = session ()  host_obj = session.query ( Host). Filter (host.hostname ==  ' C2 '). First () print (Host_obj.host_user) for item in host_ Obj.host_user:    print (item.username)  #  output [<__main__. hostuser object at 0x000001e44af49390>, <__main__. Hostuser object at 0x000001e44af493c8>]rootdb

This article comes from the "change from every day" blog, so be sure to keep this source http://lilongzi.blog.51cto.com/5519072/1888994

Automated Operations Python series ForeignKey, Relationship Union table query