Python_way DAY13 SQLAlchemy

SQLAlchemy

One-to-many

Many-to-many

1, a pair of multi-

One, #CREATE TABLE StructureclassHost (Base):#all subclasses inherit this base class.    #CREATE TABLE Structure    __tablename__='hosts'ID= Column (Integer, primary_key=true,autoincrement=True) hostname= Column (String), unique=true,nullable=False) group_id= Column (Integer,foreignkey ('groups.id'))#Create an ID in the group_id associated to the Work_group outside the hostsGroup = relationship ("Group")#To write an uppercase instance name through a mapped relationship, you can query the hosts table for other value in the Foreign key table
　　  def __repr__ (self):

        return "<id =%s,jump_user_name=%s>"% (self.id, self.username)

 classGroup (Base):__tablename__='groups'
ID= Column (integer,primary_key=true)#automatic self-increment, primary key
　　　　Name = Column (String), unique=true,nullable=False)

Base.metadata.create_all (engine)#execute the above SQL 
diagram: Group
Key1Web
　  2DB host hostname

FOREIGN Key group_id
1 Nginx 1
2 MySQL 2

That's when we made the group_id and group.id of the host a foreign key association.
This means that I have to have a group, I can create the host group, or we can first put the host of this is to be empty, so you can first fill out the table of the host

Second, #插入数据

The first method of inserting data
1. We can insert the host directly
H1 = Host (hostname = "Nginx")
H2 = Host (hostname = "MySQL")

Session.add_all ([H1,H2])
Session.commit ()

2. Then we insert the group data again

G1 = Group (name = ' web ')
G2 = Group (name = ' db ')

Session.add_all ([G1,G2])
Session.commit ()

3, then do host and Group association (is to change the host group_id)

G1 = Session.query (Group). Filter (Group.name = = "Web"). First ()  #找出g1的对象
H1 = Session.query (Host). Filter (host.hostname== ' Nginx '). Update ({"group_id":g1.id
Session.commit ()

#插入数据时也可以以主动指定外键的值, but do not exceed the range of the associated keys to

H1 = Host (hostname = "Nginx", group_id = 1)
H2 = Host (hostname = "MySQL", group_id = 2)

H3 = Host (hostname = "Origer", group_id = 3)  This will be an error.

The second method of inserting data
Insert the Group table value before inserting the host table

Sessioncls = Sessionmaker (bind=engine)
Session = SESSIONCLS ()


G1 = Group (name = ' web ')    #先插入group表
G2 = Group (name = ' db ')
Session.add_all ([G1,G2])     
#
GW = Session.query (Group). Filter (Group.name = = ' web '). First ()     #不用提交g1就可以在内存中查询到group中对应的数据,
GB = Session.query (Group). Filter (Group.name = = ' db '). First ()
H1 = Host (hostname = "Nginx", group_id = gw.id) #直接复制给h1使用
H2 = Host (hostname = "MySQL", group_id = gb.id)
Session.add_all ([H1,H2])
Session.commit ()

Third, query

1. All and first

All ()
GW = Session.query (Group). All ()
Print (GW)
[<__main__. Group object at 0x0000003438c3f0f0> <__main__. Group object at 0x0000003438c3f160>]  #拿到了跟Group有关的所有数据, is a list

Print (Gw[0].name)   
Db
Print (gw[0].id)
2

First ()
GW = Session.query (Group). First ()

Print (GW)
<__main__. Group object at 0x000000c703531208>    #这个就是获取匹配到的第一个对象

Print (Gw.name)
Db
Print (gw.id)
2

2. Query ()

Query is the table to look for, which put the table's class name, here is a 2 of the situation

Query (Class)

GW = Session.query (Group). All ()
Print (GW)
[<__main__. Group object at 0x000000446e0c1080> <__main__. Group object at 0x000000446e0c10f0>]  #这样知道的是所有的类对象, is a list

Query (Class.args)

GW = Session.query (group.name). All ()
Print (GW)
[(' db ',), (' web ',)]

3, even table query

Sessioncls = Sessionmaker (bind=engine)
Session = SESSIONCLS ()


A, join:   inner  join
GW = Session.query (Group). Join (Host). All ()
Print (GW)
[<__main__. Group object at 0x0000002b1b345860>]

B, isouter=true:lelf join

GW = Session.query (Host). Join (Group,isouter=true). All ()
Print (GW)
[hostanme:nginx-group_id = 1, hostanme:mysql-group_id = 1]

+----+----------+----------+------+------+
| ID | hostname | group_id | ID | name |
+----+----------+----------+------+------+
| 1 | nginx | 1 | 1 | Web |
| 2 | mysql | 1 | 1 | Web |
+----+----------+----------+------+------+

Found that what we get is only the contents of the host table, and there is no content in the group

c, I if we want to get the contents of both the host table and the Gorup

GW = Session.query (Host,Group). Join (Group,isouter=true). All ()
Print (GW)
[(hostanme:nginx-group_id = 1, <__main__. Group object at 0x000000b3c53140b8>), (hostanme:mysql-group_id = 1, <__main__. Group object at 0x000000b3c53140b8>)]

D, we want to get accurate content, do not object how to do?

GW = Session.query (host.hostname,group.name). Join (Group,isouter=true). All ()
Print (GW)
[(' Nginx ', ' web '), (' MySQL ', ' web ')]

Custom Query return value:

classJumpuser (Base):__tablename__='Jump_user'ID= Column (Integer, Primary_key=true, autoincrement=True) Username= Column (integer,unique=true,nullable=False) passwd= Column (integer,nullable=False) Groups= Relationship ("Group", secondary=Lambda: Jumpuser_2_group.__table__, backref='jumpuser_list') Host_list= Relationship ("Hostuser", secondary=Lambda: Jumpuser_2_hostuser.__table__, backref='jumpuser_list')    def __repr__(self):return "<id =%s,jump_user_name=%s>"% (Self.id, self.username)

What we use to make the return value in Repr, and what to return at query time

　　

Python_way DAY13 SQLAlchemy