SQLAlchemy进阶

前面一篇介绍了SQLAlchemy的入门,这里我讲讲它的进阶用法,其实主要是通过它来轻松实现一些庞杂查询。

SQLAlchemy中的映照关系有四种,分离是一对多、多对一、一对一、多对多。接下来我将详细解释怎样去定义这四种关系,然后再演示怎样通过这四种关系完成庞杂的查询和更新。

一对多

表现一对多的关系时,在子表类中通过 foreign key (外键)援用父表类。

然后,在父表类中通过 relationship() 办法来援用子表的类:

1
2
3
4
5
6
7
8
9
10
11
class Parent(Base):
__tablename__ = "parent"
id = Column(Integer, primary_key=True)
children = relationship("Child")
# 在父表类中通过 relationship() 办法来援用子表的类聚集

class Child(Base):
__tablename__ = "child"
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey("parent.id"))
# 在子表类中通过 foreign key (外键)援用父表的参考字段

多对一

在一对多的关系中树立双向的关系,这样的话在对方看来这就是一个多对一的关系,在子表类中附加一个relationship()办法,并且在双方的relationship()办法中应用relationship.back_populates办法参数:

1
2
3
4
5
6
7
8
9
10
11
12
class Parent(Base):
__tablename__ = "parent"
id = Column(Integer, primary_key=True)
children = relationship("Child", back_populates="parent")

class Child(Base):
__tablename__ = "child"
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey("parent.id"))
parent = relationship("Parent", back_populates="children")
# 子表类中附加一个 relationship() 办法
# 并且在(父)子表类的 relationship() 办法中应用 relationship.back_populates 参数

这样的话子表将会在多对一的关系中取得父表的属性

或,可以在单一的relationship()办法中应用backref参数来取代back_populates参数,推举应用这类方法,可以少些几句话。

1
2
3
4
5
6
7
8
9
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"))

一对一

一对一就是多对一和一对多的一个特例,只需在relationship加上一个参数uselist=False调换多的一端就是一对一

从一对多转换到一对一:

1
2
3
4
5
6
7
8
9
class Parent(Base):
__tablename__ = "parent"
id = Column(Integer, primary_key=True)
child = relationship("Child", uselist=False, backref="parent")

class Child(Base):
__tablename__ = "child"
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey("parent.id"))

从多对一转换到一对一:

1
2
3
4
5
6
7
8
9
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)

多对多

多对多关系须要一个中间关联表,通过参数secondary来指定。backref会主动的为子表类加载一样的secondary参数,所认为了简练起见依然推举这类写法:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
from sqlalchemy import Table, Text
post_keywords = Table("post_keywords",Base.metadata,
Column("post_id",Integer,ForeignKey("posts.id")),
Column("keyword_id",Integer,ForeignKey("keywords.id"))
)

class BlogPost(Base):
__tablename__ = "posts"
id = Column(Integer,primary_key=True)
body = Column(Text)
keywords = relationship("Keyword",secondary=post_keywords,backref="posts")

class Keyword(Base):
__tablename__ = "keywords"
id = Column(Integer,primary_key = True)
keyword = Column(String(50),nullable=False,unique=True)

如果应用back_populates,那末两个都要定义:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
from sqlalchemy import Table, Text
post_keywords = Table("post_keywords",Base.metadata,
Column("post_id",Integer,ForeignKey("posts.id")),
Column("keyword_id",Integer,ForeignKey("keywords.id"))
)

class BlogPost(Base):
__tablename__ = "posts"
id = Column(Integer,primary_key=True)
body = Column(Text)
keywords = relationship("Keyword",secondary=post_keywords,back_populates="parents")

class Keyword(Base):
__tablename__ = "keywords"
id = Column(Integer,primary_key = True)
keyword = Column(String(50),nullable=False,unique=True)
parents = relationship("BlogPost",secondary=post_keywords,back_populates="keywords")

一些主要参数

relationship()函数吸收的参数非常多,比如:backrefsecondaryprimaryjoin等等。下面罗列一下我用到的参数

  • backref 在一对多或多对一之间树立双向关系,比如
  • lazy:默许值是True, 懒加载
  • remote_side: 表中的外键援用的是本身时, 如Node类,如果想表现多对一的树形关系, 那末便可以够应用remote_side

    1
    2
    3
    4
    5
    6
    class Node(Base):
    __tablename__ = "node"
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey("node.id"))
    data = Column(String(50))
    parent = relationship("Node", remote_side=[id])
  • secondary: 多对多指定中间表症结字

  • order_by: 在一对多的关系中,以下代码:

    1
    2
    3
    4
    5
    class User(Base):
    # ...
    addresses = relationship(lambda: Address,
    order_by=lambda: desc(Address.email),
    primaryjoin=lambda: Address.user_id==User.id)
  • cascade: 级联删除

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    class Parent(Base):
    __tablename__ = "parent"
    id = Column(Integer,primary_key = True)
    children = relationship("Child",cascade="all",backref="parent")

    def delete_parent():
    session = Session()
    parent = session.query(Parent).get(2)
    session.delete(parent)
    session.commit()

不过不设置cascade,删除parent时,其关联的chilren不会删除,只会把chilren关联的parent.id置为空,设置cascade后便可以够级联删除children

对象的四种状况

对象在session中可能存在的四种状况包含:

  • Transient:实例还不在session中,还没有保留到数据库中去,没有数据库身份,想刚创立出来的对象比如User(),仅仅只有mapper()与之关联
  • Pending:用add()一个transient对象后,就变成了一个pending对象,这时候候依然没有flushed到数据库中去,直到flush产生。
  • Persistent:实例涌现在session中而且在数据库中也有记载了,通常是通过flush一个pending实例变成Persistent或从数据库中querying一个已存在的实例。
  • Detached:一个对象它有记载在数据库中,但是不在任何session中,

关联查询

查询:http://docs.sqlalchemy.org/en/rel_1_1/orm/query.html

关联查询:http://docs.sqlalchemy.org/en/rel_1_1/orm/query.html#sqlalchemy.orm.query.Query.join

非常简略的关联查询,外键就一个,体系知道如何去关联:

1
session.query(User).join(Address).filter(Address.email==”[email protected]”).all()

指定ON字段:

1
q = session.query(User).join(Address, User.id==Address.user_id)

多个join

1
2
q = session.query(User).join("orders", "items", "keywords")
q = session.query(User).join(User.orders).join(Order.items).join(Item.keywords)

子查询JOIN:

1
2
3
4
5
address_subq = session.query(Address).\
filter(Address.email_address == "[email protected]").\
subquery()

q = session.query(User).join(address_subq, User.addresses)

join from:

1
2
3
q = session.query(Address).select_from(User).\
join(User.addresses).\
filter(User.name == "ed")

和下面的SQL等价:

1
2
3
SELECT address.* FROM user
JOIN address ON user.id=address.user_id
WHERE user.name = :name_1

左外衔接,指定isouter=True,等价于Query.outerjoin()

1
2
3
q = session.query(Node).\
join("children", "children", aliased=True, isouter=True).\
filter(Node.name == "grandchild 1")