其他持久性技术¶

将SQL插入/更新表达式嵌入到刷新中

此功能允许将数据库列的值设置为SQL表达式,而不是文字值。它对于原子更新、调用存储过程等特别有用。您所要做的就是为属性分配一个表达式::

class SomeClass(Base):
    __tablename__ = "some_table"

    # ...

    value = Column(Integer)

someobject = session.query(SomeClass).get(5)

# set 'value' attribute to a SQL expression adding one
someobject.value = SomeClass.value + 1

# issues "UPDATE some_table SET value=value+1"
session.commit()

这种技术对INSERT和UPDATE语句都有效。执行刷新/提交操作后, value 属性对 someobject 上面的已过期,因此下次访问时,将从数据库加载新生成的值。

该功能还具有与主键列一起使用的条件支持。一个支持返回的数据库,例如PostgreSQL、Oracle或SQL Server,或者作为一种特殊情况,当使用带pysqlite驱动程序的sqlite和一个自动递增列时,也可以将一个SQL表达式分配给主键列。这既允许计算SQL表达式,也允许在插入时修改主键值的任何服务器端触发器被ORM成功检索为对象主键的一部分:

class Foo(Base):
    __tablename__ = 'foo'
    pk = Column(Integer, primary_key=True)
    bar = Column(Integer)

e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.create_all(e)

session = Session(e)

foo = Foo(pk=sql.select([sql.func.coalesce(sql.func.max(Foo.pk) + 1, 1)])
session.add(foo)
session.commit()

在PostgreSQL上,上面 Session 将发出以下插入:

INSERT INTO foo (foopk, bar) VALUES
((SELECT coalesce(max(foo.foopk) + %(max_1)s, %(coalesce_2)s) AS coalesce_1
FROM foo), %(bar)s) RETURNING foo.foopk

1.3 新版功能: SQL表达式现在可以在ORM刷新期间传递到主键列;如果数据库支持返回,或者如果使用了pysqlite,ORM将能够检索服务器生成的值作为主键属性的值。

在会话中使用SQL表达式

SQL表达式和字符串可以通过 Session 在其事务上下文中。使用 execute() 方法,它返回 ResultProxy 以与 EngineConnection ::

Session = sessionmaker(bind=engine)
session = Session()

# execute a string statement
result = session.execute("select * from table where id=:id", {'id':7})

# execute a SQL expression construct
result = session.execute(select([mytable]).where(mytable.c.id==7))

Connection 持有的 Session 可以使用 connection() 方法:

connection = session.connection()

上面的例子涉及 Session 那一定是单身 EngineConnection . 使用 Session 它要么绑定到多个引擎,要么完全不绑定(即依赖绑定的元数据),两者都是 execute()connection() 接受 mapper 关键字参数,它将传递一个映射类或 Mapper 实例,用于定位所需引擎的适当上下文:

Session = sessionmaker()
session = Session()

# need to specify mapper or class when executing
result = session.execute("select * from table where id=:id", {'id':7}, mapper=MyMappedClass)

result = session.execute(select([mytable], mytable.c.id==7), mapper=MyMappedClass)

connection = session.connection(MyMappedClass)

对具有默认值的列强制空值

ORM将从未在对象上设置的任何属性视为“默认”情况;该属性将从insert语句中省略::

class MyObject(Base):
    __tablename__ = 'my_table'
    id = Column(Integer, primary_key=True)
    data = Column(String(50), nullable=True)

obj = MyObject(id=1)
session.add(obj)
session.commit()  # INSERT with the 'data' column omitted; the database
                  # itself will persist this as the NULL value

从insert中省略一列意味着该列将设置空值, 除非 该列有一个默认设置,在这种情况下,将保留默认值。这既适用于纯SQL视角下的服务器端默认值,也适用于SQLAlchemy插入行为中的客户端和服务器端默认值:

class MyObject(Base):
    __tablename__ = 'my_table'
    id = Column(Integer, primary_key=True)
    data = Column(String(50), nullable=True, server_default="default")

obj = MyObject(id=1)
session.add(obj)
session.commit()  # INSERT with the 'data' column omitted; the database
                  # itself will persist this as the value 'default'

但是,在ORM中,即使指定了python值 None 显式地对对象,这将被处理为 same 好像从未分配过值:

class MyObject(Base):
    __tablename__ = 'my_table'
    id = Column(Integer, primary_key=True)
    data = Column(String(50), nullable=True, server_default="default")

obj = MyObject(id=1, data=None)
session.add(obj)
session.commit()  # INSERT with the 'data' column explicitly set to None;
                  # the ORM still omits it from the statement and the
                  # database will still persist this as the value 'default'

以上操作将持续到 data 列服务器默认值 "default" 而不是SQL空值,即使 None 已通过;这是ORM的一个长期行为,许多应用程序将其作为假设。

那么,如果我们真的想将空值放入这个列中,即使这个列有一个默认值,又该怎么办呢?有两种方法。一种是在每个实例级别上,我们使用 null SQL构造:

from sqlalchemy import null

obj = MyObject(id=1, data=null())
session.add(obj)
session.commit()  # INSERT with the 'data' column explicitly set as null();
                  # the ORM uses this directly, bypassing all client-
                  # and server-side defaults, and the database will
                  # persist this as the NULL value

这个 null SQL构造总是转换为直接出现在目标插入语句中的SQL空值。

如果我们想使用python值 None 并且,尽管存在列默认值,但也将其保持为空,我们可以使用核心级别的修饰符为ORM配置它。 TypeEngine.evaluates_none() ,指示ORM应在其中处理值的类型 None 与任何其他值相同并将其传递,而不是将其作为“缺少的”值省略:

class MyObject(Base):
    __tablename__ = 'my_table'
    id = Column(Integer, primary_key=True)
    data = Column(
      String(50).evaluates_none(),  # indicate that None should always be passed
      nullable=True, server_default="default")

obj = MyObject(id=1, data=None)
session.add(obj)
session.commit()  # INSERT with the 'data' column explicitly set to None;
                  # the ORM uses this directly, bypassing all client-
                  # and server-side defaults, and the database will
                  # persist this as the NULL value

无评价

这个 TypeEngine.evaluates_none() 修饰符主要用于表示一个类型,其中python值“none”很重要,主要示例是一个JSON类型,它可能希望持久化JSON。 null 值而不是SQL空值。我们在这里稍作调整,以便向ORM发出我们想要的信号 None 即使没有为其分配特殊类型级别的行为,也要在任何时候传递到类型中。

1.1 新版功能: 增加了 TypeEngine.evaluates_none() 方法以指示“无”值应视为重要值。

正在获取服务器生成的默认值

如章节所述 服务器调用了DDL显式默认表达式标记隐式生成的值、时间戳和触发的列 核心支持数据库列的概念,数据库本身在插入时为其生成一个值,在不太常见的情况下,则在更新语句时为其生成一个值。ORM的特点是支持这样的列,即在刷新时能够获取这些新生成的值。对于由服务器生成的主键列,此行为是必需的,因为一旦对象被持久化,ORM就必须知道该对象的主键。

在绝大多数情况下,由数据库自动生成值的主键列是简单的整数列,由数据库作为所谓的“autoincrement”列或与该列关联的序列实现。sqlAlchemy core中的每个数据库方言都支持检索这些主键值的方法,这些主键值通常是python dbapi固有的,而且通常这个过程是自动的,除了像oracle这样的数据库要求我们指定 Sequence 明确地。有关此问题的更多文档,请访问 Column.autoincrement .

对于服务器生成的不是主键列或不是简单的自动递增整数列的列,ORM要求用适当的服务器默认指令标记这些列,该指令允许ORM检索该值。但并非所有后端都支持所有方法,因此必须注意使用适当的方法。需要回答的两个问题是1。此列是否为主键的一部分,以及2。数据库是否支持返回或类似的内容,例如“output inserted”;这些SQL语句在调用insert或update语句的同时返回服务器生成的值。支持返回或等效的数据库包括PostgreSQL、Oracle和SQL Server。不包括sqlite和mysql的数据库。

情况1:支持非主键、返回或等效项

在这种情况下,列应标记为 FetchedValue 或者用一个明确的 Column.server_default . 这个 orm.mapper.eager_defaults 标志可用于指示插入时应立即提取这些列,有时还应更新::

class MyModel(Base):
    __tablename__ = 'my_table'

    id = Column(Integer, primary_key=True)
    timestamp = Column(DateTime(), server_default=func.now())

    # assume a database trigger populates a value into this column
    # during INSERT
    special_identifier = Column(String(50), server_default=FetchedValue())

    __mapper_args__ = {"eager_defaults": True}

上面,没有从客户端为“timestamp”或“special_identifier”指定显式值的insert语句将在返回子句中包含“timestamp”和“special_identifier”列,以便它们立即可用。在PostgreSQL数据库中,上表的插入内容如下:

INSERT INTO my_table DEFAULT VALUES RETURNING my_table.id, my_table.timestamp, my_table.special_identifier

案例2:不支持或不需要非主键、返回或等效项

这种情况与上面的情况1相同,只是我们没有指定 orm.mapper.eager_defaults ::

class MyModel(Base):
    __tablename__ = 'my_table'

    id = Column(Integer, primary_key=True)
    timestamp = Column(DateTime(), server_default=func.now())

    # assume a database trigger populates a value into this column
    # during INSERT
    special_identifier = Column(String(50), server_default=FetchedValue())

插入具有上述映射的记录后,“timestamp”和“special_identifier”列将保持为空,并在刷新后首次访问时通过第二个select语句获取,例如,它们被标记为“expired”。

如果 orm.mapper.eager_defaults 仍在使用,并且后端数据库不支持返回或等效数据库,ORM将在insert语句之后立即发出此select语句。这通常是不可取的,因为它向可能不需要的刷新进程添加了额外的select语句。使用上面的映射 orm.mapper.eager_defaults 对mysql设置为true的标志会在刷新时产生这样的SQL结果(减去注释,仅用于澄清):

INSERT INTO my_table () VALUES ()

-- when eager_defaults **is** used, but RETURNING is not supported
SELECT my_table.timestamp AS my_table_timestamp, my_table.special_identifier AS my_table_special_identifier
FROM my_table WHERE my_table.id = %s

案例3:支持主键、返回键或等效键

插入时必须立即获取具有服务器生成值的主键列;ORM只能访问其具有主键值的行,因此如果主键是由服务器生成的,ORM需要一种方法,以便数据库在插入时立即为我们提供该新值。

如前所述,对于整数“autoincrement”列以及postgresql-serial,这些类型由核心自动处理;数据库包括获取“last inserted id”的函数,其中不支持返回,并且在支持返回的地方,sqlachemy将使用该函数。

但是,对于非整数值以及必须显式链接到序列或其他触发例程的整数值,必须在表元数据中标记服务器默认生成。

对于我们与Oracle一起使用的显式序列,这意味着我们正在使用 Sequence 结构:

class MyOracleModel(Base):
    __tablename__ = 'my_table'

    id = Column(Integer, Sequence("my_sequence"), primary_key=True)
    data = Column(String(50))

Oracle上上述模型的插入内容如下:

INSERT INTO my_table (id, data) VALUES (my_sequence.nextval, :data) RETURNING my_table.id INTO :ret_0

在上面的位置,SQLAlchemy呈现 my_sequence.nextval 对于主键列,还使用返回立即返回新值。

对于自动生成值的数据类型或由触发器填充的列,我们使用 FetchedValue . 下面是一个使用SQL Server时间戳列作为主键的模型,它自动生成值:

class MyModel(Base):
    __tablename__ = 'my_table'

    timestamp = Column(TIMESTAMP(), server_default=FetchedValue(), primary_key=True)

在SQL Server上为上表插入的内容如下:

INSERT INTO my_table OUTPUT inserted.timestamp DEFAULT VALUES

情况4:不支持主键、返回键或等效键

在这个区域中,我们为数据库(如sqlite或mysql)生成行,其中一些生成默认值的方法出现在服务器上,但不在数据库的常规自动增量例程中。在这种情况下,我们必须确保SQLAlchemy可以“预执行”默认值,这意味着它必须是显式SQL表达式。

注解

本节将说明涉及mysql和sqlite的datetime值的多个方法,因为这两个后端上的datetime数据类型具有其他有用的特殊要求。但是请记住,sqlite和mysql需要一个显式的“预执行”默认生成器 any 自动生成的数据类型用作主键,而不是常规的单列自动递增整数值。

带日期时间主键的MySQL

使用 DateTime 对于mysql,我们使用“now()”sql函数添加了一个显式的预执行支持的默认值:

class MyModel(Base):
    __tablename__ = 'my_table'

    timestamp = Column(DateTime(), default=func.now(), primary_key=True)

在上面的位置,我们选择“now()”函数向列传递日期时间值。上面生成的SQL是:

SELECT now() AS anon_1
INSERT INTO my_table (timestamp) VALUES (%s)
('2018-08-09 13:08:46',)

带时间戳主键的MySQL

当使用 TIMESTAMP 数据类型与mysql,mysql通常会自动将服务器端默认值与该数据类型关联起来。但是,当我们使用一个作为主键时,核心无法检索新生成的值,除非我们自己执行函数。AS TIMESTAMP 在mysql上,实际上存储了一个二进制值,我们需要在“now()”的用法中添加一个额外的“cast”,以便检索可以持久化到列中的二进制值:

from sqlalchemy import cast, Binary

class MyModel(Base):
    __tablename__ = 'my_table'

    timestamp = Column(
        TIMESTAMP(),
        default=cast(func.now(), Binary),
        primary_key=True)

上面,除了选择“now()”函数外,我们还使用 Binary 数据类型与 cast() 所以返回的值是二进制的。在插入中从上面呈现的SQL如下所示:

SELECT CAST(now() AS BINARY) AS anon_1
INSERT INTO my_table (timestamp) VALUES (%s)
(b'2018-08-09 13:08:46',)

带日期时间主键的sqlite

对于sqlite,可以使用sql函数生成新的时间戳 datetime('now', 'localtime') (或指定) 'utc' 但是,对于UTC,使事情变得更复杂的是,这将返回一个字符串值,该值随后与SQLAlchemy的值不兼容。 DateTime 数据类型(即使数据类型将信息转换回sqlite后端的字符串,它也必须作为python datetime进行传递)。因此,我们还必须指定要强制返回值为 DateTime 当它从函数返回时,我们通过将其作为 type_ 参数::

class MyModel(Base):
    __tablename__ = 'my_table'

    timestamp = Column(
        DateTime,
        default=func.datetime('now', 'localtime', type_=DateTime),
        primary_key=True)

插入时的上述映射如下所示:

SELECT datetime(?, ?) AS datetime_1
('now', 'localtime')
INSERT INTO my_table (timestamp) VALUES (?)
('2018-10-02 13:37:33.000000',)

分区策略(例如,每个会话有多个数据库后端)

简单垂直分区

垂直分区通过配置 SessionSession.binds 争论。此参数接收一个字典,该字典包含ORM映射类、映射层次结构中的任意类(如声明性基类或混合类)的任何组合, Table 对象,以及 Mapper 对象作为键,然后通常引用 Engine 或者不太典型 Connection 对象作为目标。每当 Session 需要代表特定类型的映射类发出SQL,以便找到适当的数据库连接源::

engine1 = create_engine('postgresql://db1')
engine2 = create_engine('postgresql://db2')

Session = sessionmaker()

# bind User operations to engine 1, Account operations to engine 2
Session.configure(binds={User:engine1, Account:engine2})

session = Session()

上面,针对任何一个类的SQL操作都将使用 Engine 链接到那个类。该功能在读写操作中都是全面的;a Query 这是针对映射到的实体的 engine1 (通过查看请求项目列表中的第一个实体确定)将利用 engine1 运行查询。刷新操作将利用 both 当引擎刷新类型的对象时,基于类的引擎 UserAccount .

在更常见的情况下,通常可以使用基本类或混合类来区分针对不同数据库连接的操作。这个 Session.binds 参数可以容纳任意的python类作为键,如果发现它在 __mro__ (python方法解析顺序)用于特定的映射类。假设两个声明性基表示两个不同的数据库连接:

BaseA = declarative_base()

BaseB = declarative_base()

class User(BaseA):
    # ...

class Address(BaseA):
    # ...


class GameInfo(BaseB):
    # ...

class GameStats(BaseB):
    # ...


Session = sessionmaker()

# all User/Address operations will be on engine 1, all
# Game operations will be on engine 2
Session.configure(binds={BaseA:engine1, BaseB:engine2})

上面,从 BaseABaseB 将把它们的SQL操作路由到两个引擎中的一个,根据它们从哪个超类(如果有的话)下降。对于从多个“绑定”超类下降的类,将选择目标类层次结构中最高的超类来表示应使用哪个引擎。

参见

Session.binds

多引擎会话的事务协调

使用多绑定引擎的一个警告是,在一个后端上的提交操作在另一个后端上成功之后可能会失败。这是一个在关系数据库中使用“两阶段事务”解决的不一致问题,它在提交序列中添加了一个额外的“准备”步骤,允许多个数据库在实际完成事务之前同意提交。

由于DBAPIS内部的支持有限,SQLAlchemy对后端的两阶段事务的支持有限。最典型的是,人们知道它可以很好地与PostgreSQL后端一起工作,而在较小程度上与MySQL后端一起工作。然而, Session 通过设置 Session.use_twophase 内旗 sessionmakerSession . 见 启用两阶段提交 举个例子。

自定义垂直分区

通过重写 Session.get_bind() 方法。下面我们将展示一个习惯 Session 它提供了以下规则:

  1. 刷新操作将传递到名为 master .

  2. 对子类对象的操作 MyOtherClass 都发生在 other 引擎。

  3. 所有其他类的读取操作都发生在 slave1slave2 数据库。

engines = {
    'master':create_engine("sqlite:///master.db"),
    'other':create_engine("sqlite:///other.db"),
    'slave1':create_engine("sqlite:///slave1.db"),
    'slave2':create_engine("sqlite:///slave2.db"),
}

from sqlalchemy.orm import Session, sessionmaker
import random

class RoutingSession(Session):
    def get_bind(self, mapper=None, clause=None):
        if mapper and issubclass(mapper.class_, MyOtherClass):
            return engines['other']
        elif self._flushing:
            return engines['master']
        else:
            return engines[
                random.choice(['slave1','slave2'])
            ]

以上 Session 类已使用 class_ 参数 sessionmaker ::

Session = sessionmaker(class_=RoutingSession)

这种方法可以与多个 MetaData 对象,使用声明性的方法 __abstract__ 关键字,描述于 __abstract__ .

参见

Django-style Database Routers in SQLAlchemy - blog post on a more comprehensive example of Session.get_bind()

水平分区

水平分区将单个表(或一组表)的行跨多个数据库进行分区。圣卢西亚 Session 包含对此概念的支持,但是要完全使用它,需要 SessionQuery 使用子类。这些子类的基本版本可在 水平切分 ORM扩展。使用示例如下: 水平切分 .

散装作业

注解

批量操作模式是在 Session 对象的目的是以大大减少的python开销调用insert和update语句,而牺牲的功能、自动化和错误检查要少得多。从SQLAlchemy 1.0开始,这些功能应该被视为“beta”,而且还应该面向高级用户。

1.0.0 新版功能.

上的批量操作 Session 包括 Session.bulk_save_objects()Session.bulk_insert_mappings()Session.bulk_update_mappings() . 这些方法的目的是直接公开工作单元系统的内部元素,这样就可以单独使用发出给定字典或对象状态的插入和更新语句的工具,而不必使用状态、关系和属性管理的正常工作单元机制。这种方法的优点严格来说是减少了Python开销:

应使用 性能 示例套件。这是一系列示例脚本,演示了跨各种场景(包括大容量插入和更新场景)的Python调用计数。

参见

性能 -包括与传统的核心和ORM方法(包括性能指标)相比的批量操作的详细示例。

使用

每个方法都在 Session 对象的事务,与其他事务一样:

s = Session()
objects = [
    User(name="u1"),
    User(name="u2"),
    User(name="u3")
]
s.bulk_save_objects(objects)

为了 Session.bulk_insert_mappings()Session.bulk_update_mappings() ,传递字典::

s.bulk_insert_mappings(User,
  [dict(name="u1"), dict(name="u2"), dict(name="u3")]
)

与核心插入/更新结构的比较

批量方法提供的性能在特定情况下可以接近使用核心的性能 InsertUpdate 在“ExecuteMany”上下文中构造(有关“ExecuteMany”的描述,请参见 执行多个语句 在核心教程中)。为了实现这一点, Session.bulk_insert_mappings.return_defaults 应禁用标志,以便可以将行批处理在一起。中的示例套件 性能 应仔细研究,以熟悉批量性能的实现速度。

ORM兼容性

与传统的ORM使用相比,批量插入/更新方法会丢失大量的功能。以下是以下功能的列表: 无法使用的 使用这些方法时:

  • 坚持下去 relationship() 连杆机构

  • 按依赖项的顺序对行进行排序;按传递给方法的顺序直接插入或更新行

  • 对给定对象进行会话管理,包括会话的附件、标识映射管理。

  • 更新级联时与主键突变相关的功能

  • SQL表达式插入/更新(例如 将SQL插入/更新表达式嵌入到刷新中

  • ORM事件,例如 MapperEvents.before_insert() 等等。批量会话方法没有事件支持。

特点是 是可用的 包括:

  • 插入和更新映射对象

  • 版本标识符支持

  • 多表映射,例如联合继承-但是,要跨多个表插入的对象要么需要提前完全填充主键标识符,要么 Session.bulk_save_objects.return_defaults 必须使用标志,这将大大降低性能优势