SQLAlchemy使用笔记--SQLAlchemy ORM(二) 转
建立表之间带关系
建立外键
在address添加user的外键
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
<code class = "hljs haskell" >from sqlalchemy import ForeignKey, Column, String, Integer from sqlalchemy.orm import relationship class User(Base): __tablename__ = 'users' id = Column(Integer, primary_key=True) name = Column(String) fullname = Column(String) password = Column(String) class Address(Base): __tablename__ = 'addresses' id = Column(Integer, primary_key=True) email_address = Column(String, nullable=False) user_id = Column(Integer, ForeignKey( 'users.id' )) user = relationship( 'User' , backref=backref( 'addresses' , order_by=id))</code> |
relationship中的backref参数使用形式:
1
2
3
|
<code class = "hljs bash" >backref= "addresses" #直接使用表名的字符串 backref=backref( 'addresses' ) #使用backref函数 backref=backref( 'addresses' , order_by=id)) #brackref函数可以添加参数,详见http: //docs.sqlalchemy.org/en/rel_1_0/orm/backref.html#backref-arguments</code> |
可以使用user.addresses 从user获取address 和,使用address.users 虫address获取user
backref 会在User跟Address上都加上关系,它本质是:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
<code class = "hljs mathematica" >from sqlalchemy import Integer, ForeignKey, String, Column from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import relationship Base = declarative_base() class User(Base): __tablename__ = 'user' id = Column(Integer, primary_key=True) name = Column(String) addresses = relationship( "Address" , back_populates= "user" ) class Address(Base): __tablename__ = 'address' id = Column(Integer, primary_key=True) email = Column(String) user_id = Column(Integer, ForeignKey( 'user.id' )) user = relationship( "User" , back_populates= "addresses" )</code> |
relationship中的
添加
1
2
3
|
<code class = "hljs r" >>>> jack.addresses = [ ... Address(email_address= 'jack@google.com' ), ... Address(email_address= 'j25@yahoo.com' )]</code> |
获取
1
2
3
4
|
<code class = "hljs scss" >>>> jack.addresses[ 1 ] >>> jack.addresses[ 1 ].user <user(name= 'jack' , fullname= "Jack Bean" password= "gjffdd" ></user(name= 'jack' ,></address(email_address= 'j25@yahoo.com' )></code> |
commit
1
2
|
<code class = "hljs avrasm" >session.add(jack) session.commit()</code> |
address 会自动的添加
one to many 关系
1
2
3
4
5
6
7
8
9
|
<code class = "hljs mathematica" > class Parent(Base): __tablename__ = 'parent' id = Column(Integer, primary_key=True) children = relationship( "Child" , backref= "parent" ) class Child(Base): __tablename__ = 'child' id = Column(Integer, primary_key=True) parent_id = Column(Integer, ForeignKey( 'parent.id' ))</code> |
many to one 关系
1
2
3
4
5
6
7
8
9
|
<code class = "hljs mathematica" > class Parent(Base): __tablename__ = 'parent' id = Column(Integer, primary_key=True) child_id = Column(Integer, ForeignKey( 'child.id' )) child = relationship( "Child" , backref= "parents" ) class Child(Base): __tablename__ = 'child' id = Column(Integer, primary_key=True)</code> |
one to one 关系
1
2
3
4
5
6
7
8
9
10
11
|
<code class = "hljs python" >from sqlalchemy.orm import backref class Parent(Base): __tablename__ = 'parent' id = Column(Integer, primary_key=True) child_id = Column(Integer, ForeignKey( 'child.id' )) child = relationship( "Child" , backref=backref( "parent" , uselist=False)) class Child(Base): __tablename__ = 'child' id = Column(Integer, primary_key=True)</code> |
many to many 关系
需要一个中间表和在relatonship 添加secondary参数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
<code class = "hljs mathematica" >association_table = Table( 'association' , Base.metadata, Column( 'left_id' , Integer, ForeignKey( 'left.id' )), Column( 'right_id' , Integer, ForeignKey( 'right.id' )) ) class Parent(Base): __tablename__ = 'left' id = Column(Integer, primary_key=True) children = relationship( "Child" , secondary=association_table, backref= "parents" ) class Child(Base): __tablename__ = 'right' id = Column(Integer, primary_key=True)</code> |
这样在child添加删除parent或者parent添加删除child时,无需对中间表进行操作,直接添加删除即可。
1
2
|
<code class = "hljs avrasm" >parent.children.append(child) child.parents.append(parent)</code> |
也可以 使用类来创建中间表,这样可以在中间表中保存一些其他的信息。但是就不能想前面一样自动对中间表进行操作。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
<code class = "hljs mathematica" > class Association(Base): __tablename__ = 'association' left_id = Column(Integer, ForeignKey( 'left.id' ), primary_key=True) right_id = Column(Integer, ForeignKey( 'right.id' ), primary_key=True) extra_data = Column(String( 50 )) child = relationship( "Child" , back_populates= "parents" ) parent = relationship( "Parent" , back_populates= "children" ) class Parent(Base): __tablename__ = 'left' id = Column(Integer, primary_key=True) children = relationship( "Association" , back_populates= "parent" ) class Child(Base): __tablename__ = 'right' id = Column(Integer, primary_key=True) parents = relationship( "Association" , back_populates= "child" )</code> |
join 操作
可以使用Query.join()
1
2
3
4
|
<code class = "hljs r" >>>> session.query(User).join(Address).\ ... filter(Address.email_address== 'jack@google.com' ).\ ... all() [<user(name= 'jack' , fullname= "Jack Bean" password= "gjffdd" >]</user(name= 'jack' ,></code> |
在User上可以直接使用join(Address) 因为只有一个外建在User和Address之间,其他join形式:
1
2
3
4
|
<code class = "hljs vbscript" >query.join(Address, User.id==Address.user_id) # explicit condition query.join(User.addresses) # specify relationship from left to right query.join(Address, User.addresses) # same, with explicit target query.join( 'addresses' ) # same, using a string</code> |
使用外链接
1
|
<code class = "hljs avrasm" >query.outerjoin(User.addresses) # 默认是左外连接。</code> |
当query中有多个实体点使用,使用join默认join追左边的那个,
例如:
1
2
|
<code class = "hljs avrasm" >query = session.query(User, Address).join(User) # 报错 query = session.query(Address, User).join(User) # 正确</code> |
如果想自定使用join那个表,可以使用select_form
1
|
<code class = "hljs avrasm" >query = Session.query(User, Address).select_from(Address).join(User)</code> |
alias 别名
如果想join自己,可以使用别名
1
2
3
4
5
6
7
8
9
10
11
12
|
<code class = "hljs cs" >from sqlalchemy.orm import aliased adalias1 = aliased(Address) adalias2 = aliased(Address) for username, email1, email2 in \ session.query(User.name, adalias1.email_address, adalias2.email_address).\ join(adalias1, User.addresses).\ join(adalias2, User.addresses).\ filter(adalias1.email_address== 'jack@google.com' ).\ filter(adalias2.email_address== 'j25@yahoo.com' ): print(username, email1, email2)</code> |
使用子查询
直接看官方文档的例子:
1
2
3
4
5
6
7
8
9
10
11
12
|
<code class = "hljs r" >>>> from sqlalchemy.sql import func >>> stmt = session.query(Address.user_id, func.count( '*' ).\ ... label( 'address_count' )).\ ... group_by(Address.user_id).subquery() >>> for u, count in session.query(User, stmt.c.address_count).\ ... outerjoin(stmt, User.id==stmt.c.user_id).order_by(User.id): ... print(u, count) <user(name= 'ed' , fullname= "Ed Jones" password= "f8s7ccs" > None <user(name= 'wendy' , fullname= "Wendy Williams" password= "foobar" > None <user(name= 'mary' , fullname= "Mary Contrary" password= "xxg527" > None <user(name= 'fred' , fullname= "Fred Flinstone" password= "blah" > None <user(name= 'jack' , fullname= "Jack Bean" password= "gjffdd" > 2 </user(name= 'jack' ,></user(name= 'fred' ,></user(name= 'mary' ,></user(name= 'wendy' ,></user(name= 'ed' ,></code> |
使用EXISTS
看官方文档的例子:
1
2
3
4
5
|
<code class = "hljs r" >>>> from sqlalchemy.sql import exists >>> stmt = exists().where(Address.user_id==User.id) SQL>>> for name, in session.query(User.name).filter(stmt): ... print(name) jack</code> |
等价于:
1
2
3
4
|
<code class = "hljs r" >>>> for name, in session.query(User.name).\ ... filter(User.addresses.any()): ... print(name) jack</code> |
user.addresses 可以像user中其他属性一样在filter使用==、!=、any等等。
1
2
3
4
5
6
7
8
9
10
|
<code class = "hljs livecodeserver" >query.filter(Address.user == someuser) query.filter(Address.user != someuser) query.filter(Address.user == None) query.filter(User.addresses.contains(someaddress)) query.filter(User.addresses.any(Address.email_address == 'bar' ))# also takes keyword arguments: query.filter(User.addresses.any(email_address= 'bar' )) query.filter(Address.user.has(name= 'ed' )) session.query(Address).with_parent(someuser, 'addresses' )</code> |