SQLAlchemy的使用(四)

admin 管理员组

文章数量: 886993

SQLAlchemy的使用(四)

SQLAlchemy的orm和core的区别：

SQLAlchemy Core: 基于schema（数据库的组织与结构）的，就有点类似于传统的SQL，在数据仓库，报表分析等方面能够对查询控制的更好。

SQLAlchemy ORM: 但是如果在考虑领域模型的设计时，ORM封装了大量底层的schema和元数据结构，这种封装使得开发人员和数据库的交互变得更加简单

在select的时候指定一个column的值：

UpdateID = case([(Deployment.ActionID.is_(None), updateID), ], else_=updateID).label("UpdateID")
随便指定一个值，不管是什么判定结果，都让他的值等于这个你想要的结果，然后在select的字段中加上这个变量名就行，这个是灵活使用case来达到目的。

check exist：检查是否存在的方法

if session.query(model).filter(some_filter).count():
这样可以使用计数的方式达到目的，只要计数不为0，那么就表示存在

object relational tutorial:

什么是keyedtuple:

>>> k = KeyedTuple([1, 2, 3], labels=["one", "two", "three"])
>>> k.one
1

每一个item都有与之对应的标签

每一次Query返回的结果都是named tuples，也就是keyedTuple：

>>> for row in session.query(User, User.name).all():
...    print(row.User, row.name)

也可以对每一个选出来的字段指定想要的名字，也就是加上标签：
User.name.label('name_label')

如果一个对象需要在query中用到多次，就可以命名：
user_alias = aliased(User, name='user_alias')

query中的and和or ：
and_(User.name == 'ed', User.fullname == 'Ed Jones')
or_(User.name == 'ed', User.name == 'wendy')

！！！！！！！使用文本sql， Textual SQL：！！！！！！！
文本字符串可以在query中使用，使用text()：

>>> from sqlalchemy import text
SQL>>> for user in session.query(User).\
...             filter(text("id<224")).\
...             order_by(text("id")).all():
...     print(user.name)

还可以使用params()来传递参数
※如果要使用整句文本SQL，可以结合from_statement()使用：

session.query(User).from_statement(
...                     text("SELECT * FROM users where name=:name")).\
...                     params(name='ed').all()

更复杂的使用方法：

>>> stmt = text("SELECT name, id FROM users where name=:name")
>>> stmt = stmt.columns(User.name, User.id)
>>> session.query(User.id, User.name).\
...          from_statement(stmt).params(name='ed').all()

很复杂的sql语句可以使用正常的query orm方式结合text()方式一起使用，使用案例：

case_check = case([(Update.DetectoidType != None, Update.DetectoidType), ], else_=session.query(Category).from_statement(text("(SELECT TOP 1 CategoryType FROM tbCategory WHERE tbCategory.CategoryID = tbUpdate.LocalUpdateID)")))result = session.query(Revision.RevisionID,Revision.LocalUpdateID,case_check).\join(Update, Revision.LocalUpdateID == Update.LocalUpdateID).\filter(Revision.IsLeaf == 0).all()

这里要注意，有一个容易报错的地方是没有打括号，因为它是直接把这段SQL语句插入进去，所以注意括号的位置。

querying with Joins:
可以直接使用filter()来隐式地join两个对象

如果两个表之间只有一个外键相连接，可以不用指定条件：
session.query(User).join(Address)
如果没有或者有多个外键，那么需要指定条件：
query.join(Address, User.id==Address.user_id)

！！！！！！如果使用query select from多个entities：
query = session.query(User, Address).select_from(Address).join(User)

使用子查询，Subqueries ：
示例：

SELECT users.*, adr_count.address_count FROM users LEFT OUTER JOIN(SELECT user_id, count(*) AS address_countFROM addresses GROUP BY user_id) AS adr_countON users.id=adr_count.user_id

使用query实现：

>>> stmt = session.query(Address.user_id, func.count('*').\
...         label('address_count')).\
...         group_by(Address.user_id).subquery()

这里需要使用subquery() 来代表一个select语句
这个subquery()生成的结果类似于一个table结构，可以用c来获取里面的字段：

>>> 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)

Eager Loading：
当需要load一组objects以及他们相关联的collections的时候，用subqueryload()：

>>> from sqlalchemy.orm import subqueryload
SQL>>> jack = session.query(User).\
...                 options(subqueryload(User.addresses)).\
...                 filter_by(name='jack').one()

更常用的方法是joinload() 所以join和load都是一步完成：

>>> jack = session.query(User).\
...                        options(joinedload(User.addresses)).\
...                        filter_by(name='jack').one()

Mapper Configuration:

有两种mapper的形式：
一种是Declarative Mapping：这种直接使用class User(Base)这种方式建立映射表。
另一种是Classical Mappings: 这种方法需要建立Table，然后建立一个与之关联的类，最后用mapper()的方式关联起来。

session的state management

首先了解一个实例可能对于session拥有的状态：

1. Transient：短暂的，一个实例既没有加到session，也不存在与数据库中，这样的实例是刚刚通过ORM创建的，只是有一个mapper()关系
2. Pending：待定的，当把上面的一个实例加到session中，它就变成pending的了，它目前还不存在于数据库
3. Persistent： 一贯的，当一个实例在session里面或者在数据库里面的时候，状态为Persistent。当把Pending的实例flush的时候，它的状态会变成Persistent。或者当你query一条数据库里面已有的数据的时候，状态也是Persistent。
4. Deleted：删除的，当一个实例在flush的时候被删除了，但是这个transaction还没有被关闭的时候。
5. Detached：分离的，一个实例现在或者之前在数据库里面有record，但是现在不在session中

使用inspect()方法来获取对象的当前状态：

>>> from sqlalchemy import inspect
>>> insp = inspect(my_object)
>>> insp.persistent
True

session也记录了新增，修改，删除的对象：

# pending objects新创建的对象
session.new# persistent objects被检测到了修改
session.dirty# persistent objects通过session.delete(obj)方法删除的
session.deleted# 所有persistent objects组成的字典
session.identity_map

Merging

merge()会将外部对象的状态传递给session内的新实例或者已经存在的实例
merged_object = session.merge(existing_object)
给定一个实例，会经历以下步骤：

1. 它会检查该实例的主键，如果存在，它会尝试在本地映射local identity map中找到实例的定位。

2. 如果没有找到主键，或者根据主键没有在数据库里面定位该实例，那么创建一个新的实例。

3. 这个给定的实例的状态会被复制到本地/新建的实例里面。对于source上不存在的映射属性，该属性在target上过期，丢弃其现有值。

   如果load=True as default，那么复制过程会产生event，并且会加载target上面被卸载的属性集合，这样的话，source的状态会和数据库里面的东西协调。如果该值设置为False，那么incoming data会直接覆盖原有数据而不产生任何历史记录。

4. 这个操作是级联的

5. 新的实例会被返回

merge()过后，这个给定的source不会被修改也不会关联任何session，所以它可以继续用来做merge。
应用场景：将一个文件内容导入数据库，第一次用merge()保存到数据库。后面，这个文件可能被修改了，然后之前的步骤可能被重新运行，可以再一次使用merge，session会根据数据的变更对数据库进行更新：根据主键加载数据库的对象，然后根据新的state对该对象进行更新。

merge()的典型使用方法：
使用最简单的User和Address对象关系：

>>> u1 = User(name='ed', addresses[Address(email_address='ed@ed')])
>>> session.add(u1)
>>> sessionmit()

这样新增了一条user以及对应的address，他们现在都是persistent的状态了。

我们现在创建一条session之外的对象：

>>> existing_a1 = u1.addresses[0]
>>> a1 = Address(id=existing_a1.id)

！！！继续上面的代码：
报错一：

>>> a1.user = u1
>>> a1 = session.merge(a1)
>>> sessionmit()

这样报错的原因是因为我们没有关心级联，因为a1.user的等于了一个persistent对象，就会将a1的状态变成pending，这样的话，下面的merge()操作就无效了，因为a1已经存在于session中了。

正常方法：

>>> a1 = Address(id=existing_a1, user_id=user.id)
>>> a1.user
>>> a1.__dict__
{'_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x1298d10>,'user_id': 1,'id': 1,'user': None}
>>> # we don't want user=None merged, remove it
>>> del a1.user
>>> a1 = session.merge(a1)
>>> # success
>>> sessionmit()

flush()和commit()之间的区别：

The session object registers transaction operations with session.add()，但是直到flush()被调用，这些变更都没有与数据库进行交流。flush()被调用的时候，数据库也只是将变更看作是pending的操作，不会被持久地保存入本地数据库。
commit()会将这些变更加载到数据库中，flush()永远会在commit()中被同时调用。

当你使用session的query出数据库里面的数据的时候，query不仅会返回数据库里面的数据，还会返回flush过的数据。默认情况下，session对象会autoflush里面的操作。

本文标签： SQLAlchemy的使用(四)