版本:1.1.0b2 |发布日期:2016年7月1日

SQLAlchemy 1.1文档

更改和迁移

项目版本

SQLAlchemy 0.8有什么新东西?

关于本文档

本文档描述了2012年10月发布的SQLAlchemy 0.7版本和SQLAlchemy 0.8版本之间的更改,预计2013年初发布。

文件日期:2012年10月25日更新日期:2013年3月9日

引言¶ T0>

本指南介绍了SQLAlchemy 0.8版中的新增功能,并介绍了影响用户将其应用程序从SQLAlchemy 0.7系列迁移到0.8的更改。

SQLAlchemy版本将在1.0版本中关闭,0.5版本以后的每个新版本都会减少主要的使用更改。大部分应用现代0.7模式的应用程序应该可以移动到0.8而不改变。使用0.6甚至0.5模式的应用程序也应该直接迁移到0.8,尽管更大的应用程序可能需要使用每个临时版本进行测试。

平台支持

将Python 2.5及更高版本定位到

SQLAlchemy 0.8将针对Python 2.5并转发; Python 2.4的兼容性正在被丢弃。

The internals will be able to make usage of Python ternaries (that is, x if y else z) which will improve things versus the usage of y and x or z, which naturally has been the source of some bugs, as well as context managers (that is, with:) and perhaps in some cases try:/except:/else: blocks which will help with code readability.

SQLAlchemy最终也会减少2.5的支持 - 当2.6达到基线时,SQLAlchemy将移动到2.6 / 3.3原地兼容性,去掉2to3工具的使用,并维护一个源代码库与Python 2和3同时工作。

新的ORM功能

重写relationship()结构

0.8关于relationship()如何决定如何在两个实体之间进行连接,具有一个大大改进和有效的系统。新系统包含以下功能:

  • The primaryjoin argument is no longer needed when constructing a relationship() against a class that has multiple foreign key paths to the target. 只需要foreign_keys参数来指定应包含的列:

    class Parent(Base):
        __tablename__ = 'parent'
        id = Column(Integer, primary_key=True)
        child_id_one = Column(Integer, ForeignKey('child.id'))
        child_id_two = Column(Integer, ForeignKey('child.id'))
    
        child_one = relationship("Child", foreign_keys=child_id_one)
        child_two = relationship("Child", foreign_keys=child_id_two)
    
    class Child(Base):
        __tablename__ = 'child'
        id = Column(Integer, primary_key=True)
  • 现在支持与自引用的关系,列指向自身的复合外键规范情况如下:

    class Folder(Base):
        __tablename__ = 'folder'
        __table_args__ = (
          ForeignKeyConstraint(
              ['account_id', 'parent_id'],
              ['folder.account_id', 'folder.folder_id']),
        )
    
        account_id = Column(Integer, primary_key=True)
        folder_id = Column(Integer, primary_key=True)
        parent_id = Column(Integer)
        name = Column(String)
    
        parent_folder = relationship("Folder",
                            backref="child_folders",
                            remote_side=[account_id, folder_id]
                      )

    Above, the Folder refers to its parent Folder joining from account_id to itself, and parent_id to folder_id. 当SQLAlchemy构造一个自动连接时,不再可以假设“远程”一侧的所有列都是别名,而“本地”一侧的所有列都不是 - account_id列是两边所以内部关系机制被完全重写,以支持完全不同的系统,由此生成两个account_id副本,每个副本包含不同的注释以确定它们在语句中的角色。注意基本热切负载中的连接条件:

    SELECT
        folder.account_id AS folder_account_id,
        folder.folder_id AS folder_folder_id,
        folder.parent_id AS folder_parent_id,
        folder.name AS folder_name,
        folder_1.account_id AS folder_1_account_id,
        folder_1.folder_id AS folder_1_folder_id,
        folder_1.parent_id AS folder_1_parent_id,
        folder_1.name AS folder_1_name
    FROM folder
        LEFT OUTER JOIN folder AS folder_1
        ON
            folder_1.account_id = folder.account_id
            AND folder.folder_id = folder_1.parent_id
    
    WHERE folder.folder_id = ? AND folder.account_id = ?
  • 以前很难的自定义连接条件,如涉及函数和/或类型的CASTing,现在将在大多数情况下按预期工作:

    class HostEntry(Base):
        __tablename__ = 'host_entry'
    
        id = Column(Integer, primary_key=True)
        ip_address = Column(INET)
        content = Column(String(50))
    
        # relationship() using explicit foreign_keys, remote_side
        parent_host = relationship("HostEntry",
                            primaryjoin=ip_address == cast(content, INET),
                            foreign_keys=content,
                            remote_side=ip_address
                        )

    新的relationship()机制利用了被称为annotations的SQLAlchemy概念。这些注释也可以通过foreign()remote()函数明确地提供给应用程序代码,以此作为提高高级配置可读性的手段,或者直接注入精确配置,绕过通常的加入检查试探法:

    from sqlalchemy.orm import foreign, remote
    
    class HostEntry(Base):
        __tablename__ = 'host_entry'
    
        id = Column(Integer, primary_key=True)
        ip_address = Column(INET)
        content = Column(String(50))
    
        # relationship() using explicit foreign() and remote() annotations
        # in lieu of separate arguments
        parent_host = relationship("HostEntry",
                            primaryjoin=remote(ip_address) == \
                                    cast(foreign(content), INET),
                        )

也可以看看

Configuring how Relationship Joins - 关于relationship()的新修订部分,详细介绍了定制相关属性和集合访问的最新技术。

#1401 #610

新类/对象检查系统

许多SQLAlchemy用户正在编写需要检查映射类属性的系统,包括能够获取主键列,对象关系,普通属性等等,通常用于构建数据编组系统,像JSON / XML转换方案,当然还有形式库丰富。

原来,TableColumn模型是原始的检查点,有一个完整的文件系统。虽然SQLAlchemy的ORM模型也是完全可以反省的,但这从来不是一个完全稳定和支持的功能,用户往往不清楚如何得到这些信息。

现在,0.8为此提供了一个一致的,稳定的,完全记录的API,包括一个对映射类,实例,属性以及其他Core和ORM结构进行检查的检查系统。这个系统的入口点是核心级的inspect()函数。在大多数情况下,被检查的对象已经是SQLAlchemy系统的一部分,比如MapperInstanceStateInspector在某些情况下,添加新对象的工作是在特定的上下文中提供检查API,比如AliasedInspAttributeState

一些关键功能的演练如下:

>>> class User(Base):
...     __tablename__ = 'user'
...     id = Column(Integer, primary_key=True)
...     name = Column(String)
...     name_syn = synonym(name)
...     addresses = relationship("Address")
...

>>> # universal entry point is inspect()
>>> b = inspect(User)

>>> # b in this case is the Mapper
>>> b
<Mapper at 0x101521950; User>

>>> # Column namespace
>>> b.columns.id
Column('id', Integer(), table=<user>, primary_key=True, nullable=False)

>>> # mapper's perspective of the primary key
>>> b.primary_key
(Column('id', Integer(), table=<user>, primary_key=True, nullable=False),)

>>> # MapperProperties available from .attrs
>>> b.attrs.keys()
['name_syn', 'addresses', 'id', 'name']

>>> # .column_attrs, .relationships, etc. filter this collection
>>> b.column_attrs.keys()
['id', 'name']

>>> list(b.relationships)
[<sqlalchemy.orm.properties.RelationshipProperty object at 0x1015212d0>]

>>> # they are also namespaces
>>> b.column_attrs.id
<sqlalchemy.orm.properties.ColumnProperty object at 0x101525090>

>>> b.relationships.addresses
<sqlalchemy.orm.properties.RelationshipProperty object at 0x1015212d0>

>>> # point inspect() at a mapped, class level attribute,
>>> # returns the attribute itself
>>> b = inspect(User.addresses)
>>> b
<sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x101521fd0>

>>> # From here we can get the mapper:
>>> b.mapper
<Mapper at 0x101525810; Address>

>>> # the parent inspector, in this case a mapper
>>> b.parent
<Mapper at 0x101521950; User>

>>> # an expression
>>> print(b.expression)
"user".id = address.user_id

>>> # inspect works on instances
>>> u1 = User(id=3, name='x')
>>> b = inspect(u1)

>>> # it returns the InstanceState
>>> b
<sqlalchemy.orm.state.InstanceState object at 0x10152bed0>

>>> # similar attrs accessor refers to the
>>> b.attrs.keys()
['id', 'name_syn', 'addresses', 'name']

>>> # attribute interface - from attrs, you get a state object
>>> b.attrs.id
<sqlalchemy.orm.state.AttributeState object at 0x10152bf90>

>>> # this object can give you, current value...
>>> b.attrs.id.value
3

>>> # ... current history
>>> b.attrs.id.history
History(added=[3], unchanged=(), deleted=())

>>> # InstanceState can also provide session state information
>>> # lets assume the object is persistent
>>> s = Session()
>>> s.add(u1)
>>> s.commit()

>>> # now we can get primary key identity, always
>>> # works in query.get()
>>> b.identity
(3,)

>>> # the mapper level key
>>> b.identity_key
(<class '__main__.User'>, (3,))

>>> # state within the session
>>> b.persistent, b.transient, b.deleted, b.detached
(True, False, False, False)

>>> # owning session
>>> b.session
<sqlalchemy.orm.session.Session object at 0x101701150>

也可以看看

Runtime Inspection API

#2208 T0>

新的with_polymorphic()功能,可以在任何地方使用

The Query.with_polymorphic() method allows the user to specify which tables should be present when querying against a joined-table entity. 不幸的是,这个方法很尴尬,只适用于列表中的第一个实体,否则在使用和内部使用上都会有一些尴尬的行为。已经增加了对aliased()构造的新增功能,称为with_polymorphic(),允许任何实体被“别名”为自己的“多态”版本,任何地方:

from sqlalchemy.orm import with_polymorphic
palias = with_polymorphic(Person, [Engineer, Manager])
session.query(Company).\
            join(palias, Company.employees).\
            filter(or_(Engineer.language=='java', Manager.hair=='pointy'))

也可以看看

Basic Control of Which Tables are Queried - 用于多态加载控制的新更新的文档。

#2333 T0>

of_type() works with alias(), with_polymorphic(), any(), has(), joinedload(), subqueryload(), contains_eager()

PropComparator.of_type()方法用于指定在relationship()构造SQL表达式时使用的具有polymorphic映射作为其目标。This method can now be used to target any number of target subtypes, by combining it with the new with_polymorphic() function:

# use eager loading in conjunction with with_polymorphic targets
Job_P = with_polymorphic(Job, [SubJob, ExtraJob], aliased=True)
q = s.query(DataContainer).\
            join(DataContainer.jobs.of_type(Job_P)).\
                options(contains_eager(DataContainer.jobs.of_type(Job_P)))

The method now works equally well in most places a regular relationship attribute is accepted, including with loader functions like joinedload(), subqueryload(), contains_eager(), and comparison methods like PropComparator.any() and PropComparator.has():

# use eager loading in conjunction with with_polymorphic targets
Job_P = with_polymorphic(Job, [SubJob, ExtraJob], aliased=True)
q = s.query(DataContainer).\
            join(DataContainer.jobs.of_type(Job_P)).\
                options(contains_eager(DataContainer.jobs.of_type(Job_P)))

# pass subclasses to eager loads (implicitly applies with_polymorphic)
q = s.query(ParentThing).\
                options(
                    joinedload_all(
                        ParentThing.container,
                        DataContainer.jobs.of_type(SubJob)
                ))

# control self-referential aliasing with any()/has()
Job_A = aliased(Job)
q = s.query(Job).join(DataContainer.jobs).\
                filter(
                    DataContainer.jobs.of_type(Job_A).\
                        any(and_(Job_A.id < Job.id, Job_A.type=='fred')
                    )
                )

#2438 #1106

事件可以应用于未映射的超类

Mapper和实例事件现在可以与一个未映射的超类相关联,其中这些事件将被映射到这些子类中,并传播到子类。应该使用propagate=True标志。这个特性允许事件和一个声明式的基类相关联:

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

@event.listens_for("load", Base, propagate=True)
def on_load(target, context):
    print("New instance loaded:", target)

# on_load() will be applied to SomeClass
class SomeClass(Base):
    __tablename__ = 'sometable'

    # ...

#2585 T0>

声明区分模块/软件包

Declarative的一个关键特性是能够使用其字符串名称引用其他映射类。类名的注册现在对给定类的拥有模块和包是敏感的。类可以在表达式中通过虚线名称引用:

class Snack(Base):
    # ...

    peanuts = relationship("nuts.Peanut",
            primaryjoin="nuts.Peanut.snack_id == Snack.id")

该解决方案允许使用任何完整或部分消除歧义的软件包名称。如果特定类的路径仍然不明确,则会引发错误。

#2338 T0>

声明式中的新DeferredReflection特性

“延迟反射”示例已被移至声明中的支持功能。这个特性允许构造声明性的映射类,只有占位符Table元数据,直到prepare()步骤被调用,给定一个Engine充分反映所有表格并建立实际的映射。系统支持重写列,单个和联合继承,以及不同的基于每个引擎。一个完整的声明式配置现在可以针对在一个步骤中在引擎创建时组装的现有表创建:

class ReflectedOne(DeferredReflection, Base):
    __abstract__ = True

class ReflectedTwo(DeferredReflection, Base):
    __abstract__ = True

class MyClass(ReflectedOne):
    __tablename__ = 'mytable'

class MyOtherClass(ReflectedOne):
    __tablename__ = 'myothertable'

class YetAnotherClass(ReflectedTwo):
    __tablename__ = 'yetanothertable'

ReflectedOne.prepare(engine_one)
ReflectedTwo.prepare(engine_two)

也可以看看

DeferredReflection

#2485 T0>

由Core构造接受的ORM类

While the SQL expressions used with Query.filter(), such as User.id == 5, have always been compatible for use with core constructs such as select(), the mapped class itself would not be recognized when passed to select(), Select.select_from(), or Select.correlate(). 一个新的SQL注册系统允许映射的类在核心内被接受为FROM子句:

from sqlalchemy import select

stmt = select([User]).where(User.id == 5)

上面,映射的User类将扩展到映射UserTable

#2245 T0>

Query.update()支持UPDATE..FROM

新的UPDATE..FROM机制在query.update()中工作。下面,我们针对SomeEntity发出一个UPDATE,对SomeOtherEntity添加一个FROM子句(或者等价的,取决于后端):

query(SomeEntity).\
    filter(SomeEntity.id==SomeOtherEntity.id).\
    filter(SomeOtherEntity.foo=='bar').\
    update({"data":"x"})

特别是,对已加入继承实体的更新是受支持的,只要UPDATE的目标是对正被过滤的表的本地,或者如果父表和子表混合在一起,则它们将显式连接到查询中。下面,将Engineer作为Person的联合子类:

query(Engineer).\
        filter(Person.id==Engineer.id).\
        filter(Person.name=='dilbert').\
        update({"engineer_data":"java"})

会产生:

UPDATE engineer SET engineer_data='java' FROM person
WHERE person.id=engineer.id AND person.name='dilbert'

#2365 T0>

rollback()只会从begin_nested()回滚“脏”对象

rollback()上通过Session.begin_nested()使用SAVEPOINT的用户应该改善效率的行为改变,只有那些自上次刷新后变脏的对象过期,剩余的Session保持不变。这是因为对SAVEPOINT的ROLLBACK不会终止包含事务的隔离,所以除了那些在当前事务中没有刷新的更改之外,不需要过期。

#2452 T0>

缓存示例现在使用dogpile.cache

缓存示例现在使用dogpile.cacheDogpile.cache是​​Beaker缓存部分的重写,具有极其简单和快速的操作,以及对分布式锁定的支持。

请注意,Dogpile示例以及以前的Beaker示例所使用的SQLAlchemy API稍有变化,特别是如Beaker示例所示,需要进行此更改:

--- examples/beaker_caching/caching_query.py
+++ examples/beaker_caching/caching_query.py
@@ -222,7 +222,8 @@

         """
         if query._current_path:
-            mapper, key = query._current_path[-2:]
+            mapper, prop = query._current_path[-2:]
+            key = prop.key

             for cls in mapper.class_.__mro__:
                 if (cls, key) in self._relationship_options:

也可以看看

dogpile_caching

#2589 T0>

新的核心功能

Core 中完全可扩展的类型级别操作符支持

The Core has to date never had any system of adding support for new SQL operators to Column and other expression constructs, other than the ColumnOperators.op() method which is “just enough” to make things work. 核心从来没有任何系统可以让现有的操作员的行为被覆盖。Up until now, the only way operators could be flexibly redefined was in the ORM layer, using column_property() given a comparator_factory argument. 因此,像GeoAlchemy这样的第三方库不得不以ORM为中心,依靠一系列黑客来应用新的操作,并使其正确传播。

Core中的新运算符系统添加了一个一直缺少的钩子,即将新的和重载的运算符与类型相关联。毕竟,它并不是真正的列,CAST运算符或SQL函数,它们真正驱动着什么类型的操作,它是表达式的类型实现的细节是最小的 - 只有一些额外的方法被添加到核心ColumnElement类型,以便它查阅它的TypeEngine对象的一个​​可选的运算符集。新的或修改的操作可以通过使用TypeDecorator或“全局全局”通过附加新的TypeEngine.Comparator

例如,要为Numeric类型添加对数支持:

from sqlalchemy.types import Numeric
from sqlalchemy.sql import func

class CustomNumeric(Numeric):
    class comparator_factory(Numeric.Comparator):
        def log(self, other):
            return func.log(self.expr, other)

新的类型可以像任何其他类型一样使用:

data = Table('data', metadata,
          Column('id', Integer, primary_key=True),
          Column('x', CustomNumeric(10, 5)),
          Column('y', CustomNumeric(10, 5))
     )

stmt = select([data.c.x.log(data.c.y)]).where(data.c.x.log(2) < value)
print(conn.execute(stmt).fetchall())

由此产生的新特性立即包括对PostgreSQL的HSTORE类型的支持,以及与Postgresql的ARRAY类型相关的新操作。它也为现有类型获取更多的特定于这些类型的运算符(比如更多字符串,整数和日期运算符)铺平了道路。

#2547 T0>

插入的多个VALUES支持

The Insert.values() method now supports a list of dictionaries, which will render a multi-VALUES statement such as VALUES (<row1>), (<row2>), .... 这只与支持这种语法的后端相关,包括Postgresql,SQLite和MySQL。这与INSERT的executemany()风格不一样:

users.insert().values([
                    {"name": "some name"},
                    {"name": "some other name"},
                    {"name": "yet another name"},
                ])

也可以看看

Insert.values()

#2623 T0>

输入表达式

SQL表达式现在可以与类型关联。Historically, TypeEngine has always allowed Python-side functions which receive both bound parameters as well as result row values, passing them through a Python side conversion function on the way to/back from the database. 新功能允许类似的功能,除了在数据库方面:

from sqlalchemy.types import String
from sqlalchemy import func, Table, Column, MetaData

class LowerString(String):
    def bind_expression(self, bindvalue):
        return func.lower(bindvalue)

    def column_expression(self, col):
        return func.lower(col)

metadata = MetaData()
test_table = Table(
        'test_table',
        metadata,
        Column('data', LowerString)
)

在上面,LowerString类型定义了一个SQL表达式,当在SELECT语句的columns子句中呈现test_table.c.data列时,将会发出一个SQL表达式:

>>> print(select([test_table]).where(test_table.c.data == 'HI'))
SELECT lower(test_table.data) AS data
FROM test_table
WHERE test_table.data = lower(:data_1)

这个特性也被GeoAlchemy的新版本大量使用,可以根据类型规则在SQL中嵌入PostGIS表达式。

#1534 T0>

核心检查系统

The inspect() function introduced in New Class/Object Inspection System also applies to the core. 应用于Engine时,会产生一个Inspector对象:

from sqlalchemy import inspect
from sqlalchemy import create_engine

engine = create_engine("postgresql://scott:tiger@localhost/test")
insp = inspect(engine)
print(insp.get_table_names())

It can also be applied to any ClauseElement, which returns the ClauseElement itself, such as Table, Column, Select, etc. 这使得它可以在Core和ORM结构之间流畅地工作。

新方法Select.correlate_except()

select() now has a method Select.correlate_except() which specifies “correlate on all FROM clauses except those specified”. 它可用于映射相关子查询应正常关联的场景,除了可选的特定目标:

class SnortEvent(Base):
    __tablename__ = "event"

    id = Column(Integer, primary_key=True)
    signature = Column(Integer, ForeignKey("signature.id"))

    signatures = relationship("Signature", lazy=False)

class Signature(Base):
    __tablename__ = "signature"

    id = Column(Integer, primary_key=True)

    sig_count = column_property(
                    select([func.count('*')]).\
                        where(SnortEvent.signature == id).
                        correlate_except(SnortEvent)
                )

Postgresql HSTORE类型

对Postgresql的HSTORE类型的支持现在可作为postgresql.HSTORE使用。This type makes great usage of the new operator system to provide a full range of operators for HSTORE types, including index access, concatenation, and containment methods such as has_key(), has_any(), and matrix():

from sqlalchemy.dialects.postgresql import HSTORE

data = Table('data_table', metadata,
        Column('id', Integer, primary_key=True),
        Column('hstore_data', HSTORE)
    )

engine.execute(
    select([data.c.hstore_data['some_key']])
).scalar()

engine.execute(
    select([data.c.hstore_data.matrix()])
).scalar()

#2606 T0>

增强的Postgresql ARRAY类型

postgresql.ARRAY类型将接受可选的“维度”参数,将其固定为固定的维度数量,并在检索结果时大大提高效率:

# old way, still works since PG supports N-dimensions per row:
Column("my_array", postgresql.ARRAY(Integer))

# new way, will render ARRAY with correct number of [] in DDL,
# will process binds and results more efficiently as we don't need
# to guess how many levels deep to go
Column("my_array", postgresql.ARRAY(Integer, dimensions=2))

该类型还引入了新的运算符,使用新的类型特定的运算符框架。新的操作包括索引访问:

result = conn.execute(
    select([mytable.c.arraycol[2]])
)

在SELECT中切片访问:

result = conn.execute(
    select([mytable.c.arraycol[2:4]])
)

在更新中切片更新:

conn.execute(
    mytable.update().values({mytable.c.arraycol[2:3]: [7, 8]})
)

独立数组文字:

>>> from sqlalchemy.dialects import postgresql
>>> conn.scalar(
...    select([
...        postgresql.array([1, 2]) + postgresql.array([3, 4, 5])
...    ])
...  )
[1, 2, 3, 4, 5]

array concatenation, where below, the right side [4, 5, 6] is coerced into an array literal:

select([mytable.c.arraycol + [4, 5, 6]])

#2441 T0>

SQLite的新的,可配置的DATE,TIME类型

SQLite没有内置的DATE,TIME或DATETIME类型,而是提供一些支持将日期和时间值存储为字符串或整数。SQLite的日期和时间类型在0.8中得到了增强,对于特定的格式可以更好地配置,包括“微秒”部分是可选的,以及几乎所有的东西。

Column('sometimestamp', sqlite.DATETIME(truncate_microseconds=True))
Column('sometimestamp', sqlite.DATETIME(
                    storage_format=(
                                "%(year)04d%(month)02d%(day)02d"
                                "%(hour)02d%(minute)02d%(second)02d%(microsecond)06d"
                    ),
                    regexp="(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})(\d{6})"
                    )
            )
Column('somedate', sqlite.DATE(
                    storage_format="%(month)02d/%(day)02d/%(year)04d",
                    regexp="(?P<month>\d+)/(?P<day>\d+)/(?P<year>\d+)",
                )
            )

非常感谢Nate Dub在2012年Pycon上的冲刺。

#2363 T0>

所有方言都支持“COLLATE”;特别是MySQL,Postgresql,SQLite

The “collate” keyword, long accepted by the MySQL dialect, is now established on all String types and will render on any backend, including when features such as MetaData.create_all() and cast() is used:

>>> stmt = select([cast(sometable.c.somechar, String(20, collation='utf8'))])
>>> print(stmt)
SELECT CAST(sometable.somechar AS VARCHAR(20) COLLATE "utf8") AS anon_1
FROM sometable

也可以看看

String

#2276 T0>

现在“前缀”支持update()delete()

面向MySQL,可以在任何这些结构中呈现“前缀”。例如。:

stmt = table.delete().prefix_with("LOW_PRIORITY", dialect="mysql")


stmt = table.update().prefix_with("LOW_PRIORITY", dialect="mysql")

除了那些已经存在于insert()select()Query上的方法以外,这个方法是新的。

#2431 T0>

行为改变

“待决”对象作为“孤儿”的考虑已经变得更为激进

这是0.8系列的一个延迟,但是希望新的行为在更广泛的情况下更加一致和直观。The ORM has since at least version 0.4 included behavior such that an object that’s “pending”, meaning that it’s associated with a Session but hasn’t been inserted into the database yet, is automatically expunged from the Session when it becomes an “orphan”, which means it has been de-associated with a parent object that refers to it with delete-orphan cascade on the configured relationship(). 此行为旨在近似镜像持久性(即已插入)对象的行为,其中ORM将针对基于拦截事件的孤立成为孤立对象的这些对象发出DELETE。

对于由多种父类引用的对象,行为变化发挥作用,每种父对象指定delete-orphan;典型的例子是一个association object,以多对多的模式连接其他两种对象。以前,行为是这样的,只有在与其父母的all关联时,挂起的对象才会被清除。随着行为的改变,一旦从与之前关联的父母的任何去相关联,待处理对象就被删除。此行为旨在更加紧密地匹配持久对象的行为,这些持久对象与父对象关联后立即被删除。

较老的行为的基本原理至少可以追溯到0.4版本,基本上是一个防御性的决定,当一个对象仍然在为INSERT构建时,试图减轻混淆。但实际情况是,只要在任何情况下将对象连接到任何新的父对象,对象就会与Session

如果对象不是首先与父对象关联,或者是被清除的,然后再与一个Session通过后续的附件事件,但仍然没有完全关联。在这种情况下,预计数据库会发出一个完整性错误,因为可能有非空的外键列。ORM决定让这些INSERT尝试发生,这是基于这样的判断:一个只与其父母部分相关但与其中一些主动关联的对象通常不是用户错误,而是故意遗漏应该被默默地跳过 - 默默地跳过这里的INSERT会使这种性质的用户错误非常难以调试。

通过指定flag legacy_is_orphan作为映射器选项,可以重新为任何Mapper启用可能依赖于它的应用程序的旧行为。

新的行为允许下面的测试案例工作:

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String(64))

class UserKeyword(Base):
    __tablename__ = 'user_keyword'
    user_id = Column(Integer, ForeignKey('user.id'), primary_key=True)
    keyword_id = Column(Integer, ForeignKey('keyword.id'), primary_key=True)

    user = relationship(User,
                backref=backref("user_keywords",
                                cascade="all, delete-orphan")
            )

    keyword = relationship("Keyword",
                backref=backref("user_keywords",
                                cascade="all, delete-orphan")
            )

    # uncomment this to enable the old behavior
    # __mapper_args__ = {"legacy_is_orphan": True}

class Keyword(Base):
    __tablename__ = 'keyword'
    id = Column(Integer, primary_key=True)
    keyword = Column('keyword', String(64))

from sqlalchemy import create_engine
from sqlalchemy.orm import Session

# note we're using Postgresql to ensure that referential integrity
# is enforced, for demonstration purposes.
e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)

Base.metadata.drop_all(e)
Base.metadata.create_all(e)

session = Session(e)

u1 = User(name="u1")
k1 = Keyword(keyword="k1")

session.add_all([u1, k1])

uk1 = UserKeyword(keyword=k1, user=u1)

# previously, if session.flush() were called here,
# this operation would succeed, but if session.flush()
# were not called here, the operation fails with an
# integrity error.
# session.flush()
del u1.user_keywords[0]

session.commit()

#2655 T0>

after_attach事件在与Session相关而不是在之前触发; before_attach添加

使用after_attach的事件处理程序现在可以假定给定的实例与给定的会话相关联:

@event.listens_for(Session, "after_attach")
def after_attach(session, instance):
    assert instance in session

一些用例要求它以这种方式工作。但是,其他用例要求该项目不是不是,而是会话的一部分,例如,用于加载实例所需状态的查询首先发出自动刷新,否则会过早刷新目标目的。这些用例应该使用新的“before_attach”事件:

@event.listens_for(Session, "before_attach")
def before_attach(session, instance):
    instance.some_necessary_attribute = session.query(Widget).\
                                            filter_by(instance.widget_name).\
                                            first()

#2464 T0>

查询现在自动关联,就像select()做

以前有必要调用Query.correlate()以使列或WHERE子查询与父代相关联:

subq = session.query(Entity.value).\
                filter(Entity.id==Parent.entity_id).\
                correlate(Parent).\
                as_scalar()
session.query(Parent).filter(subq=="some value")

这是一个普通的select()结构的相反的行为,默认情况下会采用自动关联。0.8中的上述语句将自动关联:

subq = session.query(Entity.value).\
                filter(Entity.id==Parent.entity_id).\
                as_scalar()
session.query(Parent).filter(subq=="some value")

like in select(), correlation can be disabled by calling query.correlate(None) or manually set by passing an entity, query.correlate(someentity).

#2179 T0>

相关性现在始终是特定于上下文的

To allow a wider variety of correlation scenarios, the behavior of Select.correlate() and Query.correlate() has changed slightly such that the SELECT statement will omit the “correlated” target from the FROM clause only if the statement is actually used in that context. 另外,在封闭的SELECT语句中作为FROM放置的SELECT语句不再可能“关联”(即省略)FROM子句。

这种改变只会使SQL更好,因为在FROM对象相对于被选中的对象不足的情况下,不再能够呈现非法的SQL:

from sqlalchemy.sql import table, column, select

t1 = table('t1', column('x'))
t2 = table('t2', column('y'))
s = select([t1, t2]).correlate(t1)

print(s)

在此之前,上述将返回:

SELECT t1.x, t2.y FROM t2

这是无效的SQL,因为“t1”在任何FROM子句中都没有引用。

现在,在没有封闭的SELECT的情况下,它返回:

SELECT t1.x, t2.y FROM t1, t2

在SELECT中,相关性按预期生效:

s2 = select([t1, t2]).where(t1.c.x == t2.c.y).where(t1.c.x == s)

print(s2)

SELECT t1.x, t2.y FROM t1, t2
WHERE t1.x = t2.y AND t1.x =
    (SELECT t1.x, t2.y FROM t2)

预期此更改不会影响任何现有的应用程序,因为相关行为对于正确构建的表达式而言仍然相同。只有一个应用程序(很可能在测试场景中)依赖于在非相关上下文中使用的相关SELECT的无效字符串输出,才会看到任何更改。

#2668 T0>

现在,create_all()和drop_all()将授予一个空列表

The methods MetaData.create_all() and MetaData.drop_all() will now accept a list of Table objects that is empty, and will not emit any CREATE or DROP statements. 以前,一个空的列表与传递None的集合是一样的,并且无条件地为所有项目发出CREATE / DROP。

这是一个错误修复,但一些应用程序可能依赖于以前的行为。

#2664 T0>

Repaired the Event Targeting of InstrumentationEvents

The InstrumentationEvents series of event targets have documented that the events will only be fired off according to the actual class passed as a target. 通过0.7,情况并非如此,任何应用于InstrumentationEvents的事件监听器都将被映射的所有类调用。在0.8中,增加了额外的逻辑,这样事件只会调用那些发送的类。默认情况下,propagate标志设置为True,因为类检测事件通常用于拦截尚未创建的类。

#2590 T0>

当与MS-SQL中的子查询相比较时,不再需要对IN进行“=”的强制转换

我们在MSSQL方言中发现了一个非常古老的行为,当尝试这样做时,他们会尝试从用户那里拯救用户:

scalar_subq = select([someothertable.c.id]).where(someothertable.c.data=='foo')
select([sometable]).where(sometable.c.id==scalar_subq)

SQL Server不允许与标量SELECT进行相等比较,即“x =(SELECT something)”。MSSQL方言会将其转换为IN。然而,像“(SELECT something)= x”这样的比较会发生同样的事情,总的来说,这种猜测级别超出了SQLAlchemy通常的范围,所以行为被删除了。

#2277 T0>

修正了Session.is_modified()

Session.is_modified()方法接受一个参数passive,这个参数基本上是不需要的,参数在所有情况下都应该是True当它保留默认的False时,会触发数据库,​​经常会触发autoflush,这本身就会改变结果。在0.8中,passive参数将不起作用,并且卸载的属性将永远不会被检查历史记录,因为根据定义,在未加载的属性上不会有挂起的状态变化。

也可以看看

Session.is_modified()

#2320 T0>

Column.key is honored in the Select.c attribute of select() with Select.apply_labels()

表达式系统的用户知道Select.apply_labels()在每个列名前面加上表名,影响Select.c中可用的名字:

s = select([table1]).apply_labels()
s.c.table1_col1
s.c.table1_col2

在0.8之前,如果Column具有不同的Column.key,则此键将被忽略,与Select.apply_labels()不一致用过的:

# before 0.8
table1 = Table('t1', metadata,
    Column('col1', Integer, key='column_one')
)
s = select([table1])
s.c.column_one # would be accessible like this
s.c.col1 # would raise AttributeError

s = select([table1]).apply_labels()
s.c.table1_column_one # would raise AttributeError
s.c.table1_col1 # would be accessible like this

In 0.8, Column.key is honored in both cases:

# with 0.8
table1 = Table('t1', metadata,
    Column('col1', Integer, key='column_one')
)
s = select([table1])
s.c.column_one # works
s.c.col1 # AttributeError

s = select([table1]).apply_labels()
s.c.table1_column_one # works
s.c.table1_col1 # AttributeError

关于“name”和“key”的所有其他行为都是相同的,包括呈现的SQL仍然使用<tablename>_<colname>的形式 - 这里的重点是防止Column.key内容被呈现到SELECT语句中,以便在Column.key中使用特殊/非ascii字符。

#2397 T0>

single_parent警告现在是一个错误

一个relationship()是多对一或者多对多的,并且指定了“cascade ='all,delete-orphan'”,这是一个尴尬但是仍然受支持的用例)如果关系没有指定single_parent=True选项,现在将引发一个错误。以前它只会发出一个警告,但是在任何情况下,在属性系统中几乎都会立即出现失败。

#2405 T0>

inspector参数添加到column_reflect事件

0.7添加了一个名为column_reflect的新事件,这样可以反映列的反射。我们得到这个事件有点不对,因为这个事件没有办法得到用于反射的当前的InspectorConnection,在来自数据库的附加信息的情况下是必要的。由于这是一个尚未广泛使用的新事件,因此我们直接将inspector参数添加到其中:

@event.listens_for(Table, "column_reflect")
def listen_for_col(inspector, table, column_info):
    # ...

#2418 T0>

禁用排序规则的自动检测,MySQL的外壳

MySQL方言需要两次调用,一次是非常昂贵的,用于从数据库加载所有可能的排序规则以及第一次连接Engine时的套管信息。这些集合都不用于任何SQLAlchemy函数,所以这些调用将被改为不再自动发射。engine.dialect中可能依赖这些集合的应用程序将需要直接调用_detect_collations()_detect_casing()

#2404 T0>

“未消化的列名”警告成为异常

引用insert()update()结构中不存在的列会引发错误而不是警告:

t1 = table('t1', column('x'))
t1.insert().values(x=5, z=5) # raises "Unconsumed column names: z"

#2415 T0>

Inspector.get_primary_keys()已弃用,请使用Inspector.get_pk_constraint

Inspector上的这两个方法是多余的,其中get_primary_keys()将返回与get_pk_constraint()相同的信息减去约束的名称:

>>> insp.get_primary_keys()
["a", "b"]

>>> insp.get_pk_constraint()
{"name":"pk_constraint", "constrained_columns":["a", "b"]}

#2422 T0>

在大多数情况下,不区分大小写的结果行名称将被禁用

一个非常古老的行为,RowProxy中的列名始终不区分大小写:

>>> row = result.fetchone()
>>> row['foo'] == row['FOO'] == row['Foo']
True

这是为了早期需要的一些方言的好处,比如甲骨文和火鸟,但在现代用法中,我们有更准确的方法来处理这两个平台的不区分大小写的行为。

展望未来,只有通过将标志`case_sensitive=False`传递给`create_engine()`,这种行为才可用,尽可能匹配套管。

#2423 T0>

InstrumentationManager and alternate class instrumentation is now an extension

sqlalchemy.orm.interfaces.InstrumentationManager类移至sqlalchemy.ext.instrumentation.InstrumentationManager“替代仪器”系统是为了少数需要与现有的或不寻常的类仪器系统一起工作的安装而建立的,通常很少使用。这个系统的复杂性已经被输出到ext.模块。It remains unused until once imported, typically when a third party library imports InstrumentationManager, at which point it is injected back into sqlalchemy.orm by replacing the default InstrumentationFactory with ExtendedInstrumentationRegistry.

除去¶ T0>

SQLSoup ¶ T0>

SQLSoup是一个方便的包,它在SQLAlchemy ORM之上提供了一个可选接口。SQLSoup现在被移植到自己的项目中,并单独记录/发布。请参阅https://bitbucket.org/zzzeek/sqlsoup

SQLSoup是一个非常简单的工具,也可以从对其使用风格感兴趣的贡献者中受益。

#2262 T0>

MutableType ¶ T0>

SQLAlchemy ORM中较旧的“可变”系统已被删除。This refers to the MutableType interface which was applied to types such as PickleType and conditionally to TypeDecorator, and since very early SQLAlchemy versions has provided a way for the ORM to detect changes in so-called “mutable” data structures such as JSON structures and pickled objects. 然而,这种实现从来都不合理,并且强制在工作单元上使用非常低效的使用模式,这导致在刷新期间对所有对象进行昂贵的扫描。在0.7中,引入了sqlalchemy.ext.mutable扩展,以便用户定义的数据类型可以在发生更改时适当地将事件发送到工作单元。

今天,MutableType的使用量预计会很低,因为其效率低下已经有几年了。

#2442 T0>

sqlalchemy.exceptions(多年来一直是sqlalchemy.exc)

我们留下了一个别名sqlalchemy.exceptions来尝试使尚未升级到使用sqlalchemy.exc的一些非常旧的库稍微容易一些。有些用户仍然对此感到困惑,但是在0.8版本中,我们正在彻底消除这种混淆。

#2433 T0>