"13" Python Application series--orm

DB First

Manipulating databases through classes and objects

Code First

• Customizing the Build Table

Class Class (Base):     column 1     column 2 create table based on class

• Using the Class action table

Session.query (Class)

1. Create a single table, one-to-many

Rom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column , Integer, String, ForeignKey, UniqueConstraint, indexfrom sqlalchemy.orm import sessionmaker, relationshipengine = creat E_engine ("Mysql+pymysql://[email protected]:3306/s13", max_overflow=5) Base = Declarative_base () # Single table # CREATE TABLE class  Test (Base): __tablename__ = ' Test ' # table name nid = Column (Integer, Primary_key=true, autoincrement=true) # Create Column name = Column (String (32)) # Create columns # One-to-many # Create TABLE Class Group (Base): __tablename__ = ' group ' # table name nid = Column (Integer, Primary_ Key=true, Autoincrement=true) # Create Column caption = Column (String (32)) # Create a column # Create a table Class User (Base): __tablename__ = ' user ' # table name nid = Column (Integer, Primary_key=true, autoincrement=true) # Create Column name = Column (String (32)) # Create Column group _id = Column (Integer, ForeignKey (' Group.nid ')) # FOREIGN key Foreignkey,group table's Nid column # output def __repr__ (self): pass D EF __str__ (self): Passdef init_db (): Base.metadata.create_all (Engine) def drop_db (): Base.metadata.drop_all (Engine) init_db () 

2. Even table query (one-to-many)

# # New Way (forward query) ret = Session.query (User). All () for obj in ret:    # obj refers to each row of data in the User table    # Obj.group refers to the Group object    print ( Obj.nid, Obj.username, obj.group_id, Obj.group, Obj.group.nid, obj.group.caption) # original WAY ret = Session.query ( User.username, group.caption). Join (Group, isouter=true). Filter (Group.caption = = ' DBA '). All () print (ret) # New Way (reverse query) obj = Session.query (Group). Filter (Group.caption = = ' DBA '). First () print (Obj.nid) print (obj.caption) print ( OBJ.UUU)

3. Many-to-many queries

From SQLAlchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy Import Column, Integer, String, ForeignKey, UniqueConstraint, indexfrom sqlalchemy.orm import Sessionmaker, Relationshipengine = Create_engine ("Mysql+pymysql://[email protected]:3306/s13", max_overflow=5) Base = Declarative_base () #  CREATE TABLE 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 (B ASE): __tablename__ = ' host_to_host_user ' nid = Column (Integer, primary_key=true,autoincrement=true) host_id = Co Lumn (Integer,foreignkey (' Host.nid ')) host_user_id = Column (Integer,foreignkey (' Host_user.nid ')) # CREATE TABLE Def init_db (): B Ase.metadata.create_all (engine) # Delete Table Def drop_db(): Base.metadata.drop_all (Engine) init_db () # Simulation Environment Data Session = Sessionmaker (bind=engine) session = Session () Sion.add_ All ([Host (Hostname= ' C1 ', port= ', ip= ' 1.1.1.1 '), host (hostname= ' C2 ', port= ' a ', ip= ' 1.1.1.2 '), Host (Hostname= ' C3 '), Port= ', ip= ' 1.1.1.3 '), host (hostname= ' C4 ', port= ' a ', ip= ' 1.1.1.4 '), host (hostname= ' C5 ', port= ' a ', 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), HostToHost User (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 () # # # Requirement: Server C1 User # Find Server Idhost_obj = Session.query (host) for C1. filter (Host.hostname = = ' C1 '). First () # print (Host_obj.nid) # Find the user on C1 based on the server id idhost_2_host_user = session.query (HOSTTOHOSTUSER.HOST_USER_ID). Filter (hosttohostuser.host_id = = Host_obj.nid). All () # print (host_2_host_user) # Process User ID Information r = Zip (*host_2_host_user) # print (list (r) [0]) # Find the user who corresponds to the user id, users = session.query (hostuser.username). Filter ( HostUser.nid.in_ (List (R) [0]). All ()

Many-to-many queries: a new Approach

# Many-to-many query class Host:    __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 '))    host = relationship ("host", backref= ' h ')    Host_user = Relationship (" Hostuser ", backref= ' u ') host_obj = Session.query (host). Filter (host.hostname== ' C1 '). First () for item in HOST_OBJ.H:    Print (Item.host_user.username)

4. Summary

• Create a table
• Action table
  • Single table
  • Even table:
    • . Join
    • Relationship
      • One-to-many: FK, relationship
      • Many-to-many: one more table FK, FK
      • Relationship tables: Relationships
      • A: Relationship, (B,AB)

"13" Python Application series--orm