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

SQLAlchemy 1.1文档

更改和迁移

项目版本

SQLAlchemy 1.0有哪些新特性?

关于本文档

本文档介绍了2014年5月发布的SQLAlchemy 0.9版本和2015年4月发布的SQLAlchemy 1.0版本之间的变化。

文件最后更新:2015年6月9日

引言¶ T0>

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

请仔细阅读有关行为变化的章节,以了解行为可能出现的后向不相容变化。

新功能和改进 - ORM

新会话批量INSERT / UPDATE API

已经创建了一系列新的Session方法,这些方法直接将工作单元挂接到工作单元的工作单元,以发出INSERT和UPDATE语句。如果使用正确,这个面向专家的系统可以允许使用ORM映射来生成批量插入和更新语句,批量插入到executemany组中,从而使得语句的执行速度可以与直接使用Core的速度相媲美。

也可以看看

Bulk Operations - introduction and full documentation

#3100 T0>

新的性能示例套件

受到针对Bulk Operations功能以及How can I profile a SQLAlchemy powered application?常见问题解答部分,增加了一个新的示例部分,其中有几个脚本,旨在说明各种核心和ORM技术的相对性能概况。这些脚本被组织到用例中,并被封装在一个单一的控制台界面下,从而可以运行演示的任何组合,抛出时间,Python配置文件结果和/或RunSnake配置文件显示。

也可以看看

Performance

“烘焙”查询

“烘焙”查询功能是一种不同寻常的新方法,它允许直接构建使用缓存对Query对象的调用,通过连续调用功能大大减少了Python函数调用开销(超过75%)。通过将一个Query对象指定为一系列仅被调用一次的lambda表达式,作为预编译单元的查询开始变得可行:

from sqlalchemy.ext import baked
from sqlalchemy import bindparam

bakery = baked.bakery()

def search_for_user(session, username, email=None):

    baked_query = bakery(lambda session: session.query(User))
    baked_query += lambda q: q.filter(User.name == bindparam('username'))

    baked_query += lambda q: q.order_by(User.id)

    if email:
        baked_query += lambda q: q.filter(User.email == bindparam('email'))

    result = baked_query(session).params(username=username, email=email).all()

    return result

也可以看看

Baked Queries

#3054 T0>

ORM全部对象的读取速度提高了25%

The mechanics of the loading.py module as well as the identity map have undergone several passes of inlining, refactoring, and pruning, so that a raw load of rows now populates ORM-based objects around 25% faster. 假设有一个1M行表,类似下面的脚本说明了最大改进的加载类型:

import time
from sqlalchemy import Integer, Column, create_engine, Table
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Foo(Base):
    __table__ = Table(
        'foo', Base.metadata,
        Column('id', Integer, primary_key=True),
        Column('a', Integer(), nullable=False),
        Column('b', Integer(), nullable=False),
        Column('c', Integer(), nullable=False),
    )

engine = create_engine(
    'mysql+mysqldb://scott:tiger@localhost/test', echo=True)

sess = Session(engine)

now = time.time()

# avoid using all() so that we don't have the overhead of building
# a large list of full objects in memory
for obj in sess.query(Foo).yield_per(100).limit(1000000):
    pass

print("Total time: %d" % (time.time() - now))

本地MacBookPro的结果从0.9秒的19秒降至1.0的14秒。在批量处理大量行时,Query.yield_per()调用总是一个好主意,因为它可以防止Python解释器不得不一次为所有对象及其工具分配大量内存。没有Query.yield_per(),MacBookPro上面的脚本在0.9上是31秒,在1.0上是26秒,因此需要花费额外的时间来设置非常大的内存缓冲区。

New KeyedTuple的实现速度显着加快

我们看了一下KeyedTuple实现,希望改进这样的查询:

rows = sess.query(Foo.a, Foo.b, Foo.c).all()

使用KeyedTuple类而不是Python的collections.namedtuple(),因为后者有一个非常复杂的类型创建例程,其基准测试比KeyedTuple但是,当获取数十万行时,collections.namedtuple()快速超过KeyedTuple,随着实例调用的进行,该怎么办?两种方法之间的新型对冲。对于“大小”(返回行数)和“num”(不同查询数)的所有三种类型,基于哪种情况,新的“轻量级键控元组”要么优于两者,要么滞后于更快的对象。在“甜蜜点”中,我们既创建了许多新类型,又获取了很多行,轻量级对象完全吸引了名为tuple和KeyedTuple:

-----------------
size=10 num=10000                 # few rows, lots of queries
namedtuple: 3.60302400589         # namedtuple falls over
keyedtuple: 0.255059957504        # KeyedTuple very fast
lw keyed tuple: 0.582715034485    # lw keyed trails right on KeyedTuple
-----------------
size=100 num=1000                 # <--- sweet spot
namedtuple: 0.365247011185
keyedtuple: 0.24896979332
lw keyed tuple: 0.0889317989349   # lw keyed blows both away!
-----------------
size=10000 num=100
namedtuple: 0.572599887848
keyedtuple: 2.54251694679
lw keyed tuple: 0.613876104355
-----------------
size=1000000 num=10               # few queries, lots of rows
namedtuple: 5.79669594765         # namedtuple very fast
keyedtuple: 28.856498003          # KeyedTuple falls over
lw keyed tuple: 6.74346804619     # lw keyed trails right on namedtuple

#3176 T0>

结构性内存使用的重大改进

对许多内部对象来说,通过对__slots__的更为重要的使用,已经改进了结构化内存的使用。这种优化尤其适用于具有大量表和列的大型应用程序的基本内存大小,并可减少各种大容量对象(包括事件监听内部事件,比较对象以及ORM属性和加载器策略系统的一部分)的内存大小。

利用heapy测量Nova的启动大小的一个工作台说明了在基本导入“nova.db”的过程中,SQLAlchemy的对象,相关字典和相关字典所占用的大约3.7个megs或46%的差异。 sqlalchemy.models”:

# reported by heapy, summation of SQLAlchemy objects +
# associated dicts + weakref-related objects with core of Nova imported:

    Before: total count 26477 total bytes 7975712
    After: total count 18181 total bytes 4236456

# reported for the Python module space overall with the
# core of Nova imported:

    Before: Partition of a set of 355558 objects. Total size = 61661760 bytes.
    After: Partition of a set of 346034 objects. Total size = 57808016 bytes.

UPDATE语句现在在flush 中与executemany()一起进行批处理

UPDATE语句现在可以在一个ORM flush中被批处理为执行更多的executemany()调用,类似于INSERT语句可以批处理的方式;这将在基于以下标准的flush内被调用:

  • 两个或多个UPDATE语句按顺序包含要修改的相同的一组列。
  • 该语句在SET子句中没有嵌入式SQL表达式。
  • 映射不使用version_id_col,或者后端dialect支持executemany()操作的“正常”行计数;大多数DBAPI现在都能正确支持这一点。

Session.get_bind()处理更广泛的继承场景

The Session.get_bind() method is invoked whenever a query or unit of work flush process seeks to locate the database engine that corresponds to a particular class. 该方法已得到改进,可处理各种面向继承的场景,其中包括:

  • 绑定到Mixin或抽象类:

    class MyClass(SomeMixin, Base):
        __tablename__ = 'my_table'
        # ...
    
    session = Session(binds={SomeMixin: some_engine})
  • 根据表分别绑定到继承的具体子类:

    class BaseClass(Base):
        __tablename__ = 'base'
    
        # ...
    
    class ConcreteSubClass(BaseClass):
        __tablename__ = 'concrete'
    
        # ...
    
        __mapper_args__ = {'concrete': True}
    
    
    session = Session(binds={
        base_table: some_engine,
        concrete_table: some_other_engine
    })

#3035 T0>

Session.get_bind()将在所有相关的查询案例中接收映射器

A series of issues were repaired where the Session.get_bind() would not receive the primary Mapper of the Query, even though this mapper was readily available (the primary mapper is the single mapper, or alternatively the first mapper, that is associated with a Query object).

The Mapper object, when passed to Session.get_bind(), is typically used by sessions that make use of the Session.binds parameter to associate mappers with a series of engines (although in this use case, things frequently “worked” in most cases anyway as the bind would be located via the mapped table object), or more specifically implement a user-defined Session.get_bind() method that provies some pattern of selecting engines based on mappers, such as horizontal sharding or a so-called “routing” session that routes queries to different backends.

这些情况包括:

  • Query.count()

    session.query(User).count()
  • Query.update()Query.delete(),既适用于UPDATE / DELETE语句也适用于“fetch”策略使用的SELECT:

    session.query(User).filter(User.id == 15).update(
            {"name": "foob"}, synchronize_session='fetch')
    
    session.query(User).filter(User.id == 15).delete(
            synchronize_session='fetch')
  • 查询个别栏目:

    session.query(User.id, User.name).all()
  • SQL函数和其他针对间接映射的表达式,如column_property

    class User(Base):
        # ...
    
        score = column_property(func.coalesce(self.tables.users.c.name, None)))
    
    session.query(func.max(User.score)).scalar()

#3227 #3242 #1326

.info字典改进

InspectionAttr.info集合现在可用于从Mapper.all_orm_descriptors集合检索的每种对象。这包括hybrid_propertyassociation_proxy()However, as these objects are class-bound descriptors, they must be accessed separately from the class to which they are attached in order to get at the attribute. 下面是使用Mapper.all_orm_descriptors命名空间的说明:

class SomeObject(Base):
    # ...

    @hybrid_property
    def some_prop(self):
        return self.value + 5


inspect(SomeObject).all_orm_descriptors.some_prop.info['foo'] = 'bar'

It is also available as a constructor argument for all SchemaItem objects (e.g. ForeignKey, UniqueConstraint etc.) 以及其余的ORM结构,如orm.synonym()

#2971 T0>

#2963 T0>

使用别名order_by ,ColumnProperty结构可以更好地工作

有关column_property()的各种问题已经被修正,最具体的是针对aliased()结构以及0.9中引入的“按标号排序”逻辑请参阅Label constructs can now render as their name alone in an ORDER BY中单独呈现其名称)。

给定如下映射:

class A(Base):
    __tablename__ = 'a'

    id = Column(Integer, primary_key=True)

class B(Base):
    __tablename__ = 'b'

    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey('a.id'))


A.b = column_property(
        select([func.max(B.id)]).where(B.a_id == A.id).correlate(A)
    )

包含“A.b”两次的简单场景将无法正确呈现:

print(sess.query(A, a1).order_by(a1.b))

这将按错误的列排序:

SELECT a.id AS a_id, (SELECT max(b.id) AS max_1 FROM b
WHERE b.a_id = a.id) AS anon_1, a_1.id AS a_1_id,
(SELECT max(b.id) AS max_2
FROM b WHERE b.a_id = a_1.id) AS anon_2
FROM a, a AS a_1 ORDER BY anon_1

新产出:

SELECT a.id AS a_id, (SELECT max(b.id) AS max_1
FROM b WHERE b.a_id = a.id) AS anon_1, a_1.id AS a_1_id,
(SELECT max(b.id) AS max_2
FROM b WHERE b.a_id = a_1.id) AS anon_2
FROM a, a AS a_1 ORDER BY anon_2

也有很多情况下,“order by”逻辑将不能按标签排序,例如映射是“多态的”:

class A(Base):
    __tablename__ = 'a'

    id = Column(Integer, primary_key=True)
    type = Column(String)

    __mapper_args__ = {'polymorphic_on': type, 'with_polymorphic': '*'}

order_by将无法使用该标签,因为由于多态加载,它将被匿名化:

SELECT a.id AS a_id, a.type AS a_type, (SELECT max(b.id) AS max_1
FROM b WHERE b.a_id = a.id) AS anon_1
FROM a ORDER BY (SELECT max(b.id) AS max_2
FROM b WHERE b.a_id = a.id)

现在,标签的顺序跟踪匿名标签,现在可以工作:

SELECT a.id AS a_id, a.type AS a_type, (SELECT max(b.id) AS max_1
FROM b WHERE b.a_id = a.id) AS anon_1
FROM a ORDER BY anon_1

这些修复包括了各种各样的heisenbugs,可能会破坏aliased()结构的状态,使标签逻辑再次失败;这些也已经修复了。

#3148 #3188

新功能和改进 - 核心

选择/查询LIMIT / OFFSET可以被指定为一个任意的SQL表达式

The Select.limit() and Select.offset() methods now accept any SQL expression, in addition to integer values, as arguments. ORM Query对象也通过任何表达式传递给底层的Select对象。通常用于允许传递一个绑定参数,以后可以用一个值代替:

sel = select([table]).limit(bindparam('mylimit')).offset(bindparam('myoffset'))

不支持非整数LIMIT或OFFSET表达式的方言可能会继续不支持这种行为;第三方方言也可能需要修改才能利用新的行为。当前使用._limit._offset属性的方言在极限/偏移被指定为简单整数值的情况下将继续起作用。但是,当指定SQL表达式时,这两个属性将在访问时引发CompileError希望支持新功能的第三方方言现在应该调用._limit_clause._offset_clause属性来接收完整的SQL表达式,而不是整数值。

ForeignKeyConstraint上的use_alter标志(通常不再需要)

The MetaData.create_all() and MetaData.drop_all() methods will now make use of a system that automatically renders an ALTER statement for foreign key constraints that are involved in mutually-dependent cycles between tables, without the need to specify ForeignKeyConstraint.use_alter. 另外,外键约束不再需要有一个名字才能通过ALTER创建;只有DROP操作需要一个名字。在DROP的情况下,该特性将确保只有具有显式名称的约束实际上包含在ALTER语句中。如果DROP无法解决,则系统现在发出一个简洁明了的错误信息,如果DROP无法继续进行的话。

The ForeignKeyConstraint.use_alter and ForeignKey.use_alter flags remain in place, and continue to have the same effect of establishing those constraints for which ALTER is required during a CREATE/DROP scenario.

从版本1.0.1开始,特殊逻辑在SQLite的情况下接管,不支持ALTER,在DROP期间,给定的表有一个无法解析的周期;在这种情况下会发出一个警告,并且按照no顺序删除这些表,除非启用约束,否则在SQLite上这通常很好。为了解决这个警告并至少继续对SQLite数据库进行部分排序,特别是在启用了约束的情况下,对这些ForeignKeyForeignKeyConstraint重新应用“use_alter”对象应该明确从这种排除。

也可以看看

Creating/Dropping Foreign Key Constraints via ALTER - 新行为的完整描述。

#3282 T0>

ResultProxy“自动关闭”现在是一个“软”关闭

For many releases, the ResultProxy object has always been automatically closed out at the point at which all result rows have been fetched. This was to allow usage of the object without the need to call upon ResultProxy.close() explicitly; as all DBAPI resources had been freed, the object was safe to discard. However, the object maintained a strict “closed” behavior, which meant that any subsequent calls to ResultProxy.fetchone(), ResultProxy.fetchmany() or ResultProxy.fetchall() would now raise a ResourceClosedError:

>>> result = connection.execute(stmt)
>>> result.fetchone()
(1, 'x')
>>> result.fetchone()
None  # indicates no more rows
>>> result.fetchone()
exception: ResourceClosedError

这种行为与pep-249状态不一致,即使在结果耗尽之后也可以重复调用获取方法。它也会干扰结果代理的某些实现的行为,如针对某些数据类型的cx_oracle方言使用的BufferedColumnResultProxy

To solve this, the “closed” state of the ResultProxy has been broken into two states; a “soft close” which does the majority of what “close” does, in that it releases the DBAPI cursor and in the case of a “close with result” object will also release the connection, and a “closed” state which is everything included by “soft close” as well as establishing the fetch methods as “closed”. The ResultProxy.close() method is now never called implicitly, only the ResultProxy._soft_close() method which is non-public:

>>> result = connection.execute(stmt)
>>> result.fetchone()
(1, 'x')
>>> result.fetchone()
None  # indicates no more rows
>>> result.fetchone()
None  # still None
>>> result.fetchall()
[]
>>> result.close()
>>> result.fetchone()
exception: ResourceClosedError  # *now* it raises

#3330 #3329

CHECK约束现在支持命名约定中的%(column_0_name)s标记

%(column_0_name)s将从在CheckConstraint的表达式中找到的第一列派生:

metadata = MetaData(
    naming_convention={"ck": "ck_%(table_name)s_%(column_0_name)s"}
)

foo = Table('foo', metadata,
    Column('value', Integer),
)

CheckConstraint(foo.c.value > 5)

将呈现:

CREATE TABLE foo (
    value INTEGER,
    CONSTRAINT ck_foo_value CHECK (value > 5)
)

命名约定与由SchemaType(如BooleanEnum)生成的约束的组合现在也将使用所有CHECK约束约定。

#3299 T0>

引用未附加列的约束可以在附加了引用列时自动附加到表上

由于至少有0.8版本,所以Constraint有能力根据传递的表格附加列自动附加到Table

from sqlalchemy import Table, Column, MetaData, Integer, UniqueConstraint

m = MetaData()

t = Table('t', m,
    Column('a', Integer),
    Column('b', Integer)
)

uq = UniqueConstraint(t.c.a, t.c.b)  # will auto-attach to Table

assert uq in t.constraints

为了协助某些倾向于使用声明的情况,即使Column对象尚未与Table关联,现在也可以使用相同的自动附加逻辑。 ;附加的事件被建立起来,当那些Column对象关联时,Constraint也被添加:

from sqlalchemy import Table, Column, MetaData, Integer, UniqueConstraint

m = MetaData()

a = Column('a', Integer)
b = Column('b', Integer)

uq = UniqueConstraint(a, b)

t = Table('t', m, a, b)

assert uq in t.constraints  # constraint auto-attached

上述功能是从版本1.0.0b3开始的。如果#3411版本为1.0.4,则确保如果Constraint引用Column对象的混合并且字符串列名称;因为我们还没有跟踪名称添加到Table

from sqlalchemy import Table, Column, MetaData, Integer, UniqueConstraint

m = MetaData()

a = Column('a', Integer)
b = Column('b', Integer)

uq = UniqueConstraint(a, 'b')

t = Table('t', m, a, b)

# constraint *not* auto-attached, as we do not have tracking
# to locate when a name 'b' becomes available on the table
assert uq not in t.constraints

Above, the attachment event for column “a” to table “t” will fire off before column “b” is attached (as “a” is stated in the Table constructor before “b”), and the constraint will fail to locate “b” if it were to attempt an attachment. 为了一致性,如果约束引用任何字符串名称,则会跳过自动附加列连接逻辑。

如果Table在构造Constraint时已经包含了所有的目标Column对象,那么原始的自动附加逻辑当然会保持不变:

from sqlalchemy import Table, Column, MetaData, Integer, UniqueConstraint

m = MetaData()

a = Column('a', Integer)
b = Column('b', Integer)


t = Table('t', m, a, b)

uq = UniqueConstraint(a, 'b')

# constraint auto-attached normally as in older versions
assert uq in t.constraints

#3341 #3411

INSERT FROM SELECT现在包含Python和SQL表达式默认值

Insert.from_select() now includes Python and SQL-expression defaults if otherwise unspecified; the limitation where non-server column defaults aren’t included in an INSERT FROM SELECT is now lifted and these expressions are rendered as constants into the SELECT statement:

from sqlalchemy import Table, Column, MetaData, Integer, select, func

m = MetaData()

t = Table(
    't', m,
    Column('x', Integer),
    Column('y', Integer, default=func.somefunction()))

stmt = select([t.c.x])
print(t.insert().from_select(['x'], stmt))

将呈现:

INSERT INTO t (x, y) SELECT t.x, somefunction() AS somefunction_1
FROM t

该功能可以使用Insert.from_select.include_defaults禁用。

列服务器默认值现在呈现文字值

当由Column.server_default设置的DefaultClause作为要编译的SQL表达式存在时,“文字绑定”编译器标志被打开。这允许嵌入在SQL中的文字正确呈现,例如:

from sqlalchemy import Table, Column, MetaData, Text
from sqlalchemy.schema import CreateTable
from sqlalchemy.dialects.postgresql import ARRAY, array
from sqlalchemy.dialects import postgresql

metadata = MetaData()

tbl = Table("derp", metadata,
    Column("arr", ARRAY(Text),
                server_default=array(["foo", "bar", "baz"])),
)

print(CreateTable(tbl).compile(dialect=postgresql.dialect()))

现在呈现:

CREATE TABLE derp (
    arr TEXT[] DEFAULT ARRAY['foo', 'bar', 'baz']
)

Previously, the literal values "foo", "bar", "baz" would render as bound parameters, which are useless in DDL.

#3087 T0>

UniqueConstraint现在是表反射过程的一部分

现在,使用autoload=True填充的Table对象将包含UniqueConstraint结构以及Index结构。这个逻辑对于Postgresql和Mysql有一些注意事项:

的PostgreSQL ¶ T0>

Postgresql具有这样的行为,当创建一个UNIQUE约束时,它隐式地创建一个对应于该约束的UNIQUE INDEX。The Inspector.get_indexes() and the Inspector.get_unique_constraints() methods will continue to both return these entries distinctly, where Inspector.get_indexes() now features a token duplicates_constraint within the index entry indicating the corresponding constraint when detected. However, when performing full table reflection using Table(..., autoload=True), the Index construct is detected as being linked to the UniqueConstraint, and is not present within the Table.indexes collection; only the UniqueConstraint will be present in the Table.constraints collection. 这个重复数据删除逻辑通过在查询pg_index时加入到pg_constraint表来查看两个结构是否被链接。

MySQL的¶ T0>

MySQL对于UNIQUE INDEX和UNIQUE约束没有单独的概念。虽然它在创建表和索引时支持这两种语法,但不会以任何不同的方式存储它们。The Inspector.get_indexes() and the Inspector.get_unique_constraints() methods will continue to both return an entry for a UNIQUE index in MySQL, where Inspector.get_unique_constraints() features a new token duplicates_index within the constraint entry indicating that this is a dupe entry corresponding to that index. However, when performing full table reflection using Table(..., autoload=True), the UniqueConstraint construct is not part of the fully reflected Table construct under any circumstances; this construct is always represented by a Index with the unique=True setting present in the Table.indexes collection.

#3184 T0>

新系统安全地发出参数化警告

很长一段时间,警告消息不能引用数据元素,这样一个特定的函数可能会发出无数个唯一的警告。关键的地方在于Unicode 类型 接收 tt> 非unicode / t5> 参数 警告。在这个消息中放置数据值将意味着该模块的Python __warningregistry__,或者在某些情况下,Python全局warnings.onceregistry将会变得无限,就像大多数警告情况下,这两个集合中的一个会填充每个不同的警告消息。

The change here is that by using a special string type that purposely changes how the string is hashed, we can control that a large number of parameterized messages are hashed only on a small set of possible hash values, such that a warning such as Unicode type received non-unicode bind param value can be tailored to be emitted only a specific number of times; beyond that, the Python warnings registry will begin recording them as duplicates.

为了说明,下面的测试脚本将只显示10个参数集中的10个警告,总共1000个:

from sqlalchemy import create_engine, Unicode, select, cast
import random
import warnings

e = create_engine("sqlite://")

# Use the "once" filter (which is also the default for Python
# warnings).  Exactly ten of these warnings will
# be emitted; beyond that, the Python warnings registry will accumulate
# new values as dupes of one of the ten existing.
warnings.filterwarnings("once")

for i in range(1000):
    e.execute(select([cast(
        ('foo_%d' % random.randint(0, 1000000)).encode('ascii'), Unicode)]))

这里的警告格式是:

/path/lib/sqlalchemy/sql/sqltypes.py:186: SAWarning: Unicode type received
  non-unicode bind param value 'foo_4852'. (this warning may be
  suppressed after 10 occurrences)

#3178 T0>

关键行为改变 - ORM

query.update()现在将字符串名称解析为映射的属性名称

对于Query.update()的文档指出,给定的values字典是“以属性名称作为关键字的字典”,暗示这些是映射的属性名称。不幸的是,这个函数被设计得更加接近属性和SQL表达式,而不是那么多的字符串。当字符串被传递时,这些字符串将被直接传递到核心更新语句,而没有任何解决方法,就这些名称在映射类中的表示方式而言,这意味着该名称必须完全匹配表列的名称,而不是如何该名称的属性被映射到类上。

字符串名称现在被认真解析为属性名称:

class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column('user_name', String(50))

上面,列user_name被映射为name以前,调用传递字符串的Query.update()将不得不被调用,如下所示:

session.query(User).update({'user_name': 'moonbeam'})

给定的字符串现在解析为实体:

session.query(User).update({'name': 'moonbeam'})

通常最好直接使用该属性,以避免任何含糊之处:

session.query(User).update({User.name: 'moonbeam'})

该更改还表示同义词和混合属性也可以通过字符串名称引用:

class User(Base):
    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    name = Column('user_name', String(50))

    @hybrid_property
    def fullname(self):
        return self.name

session.query(User).update({'fullname': 'moonbeam'})

#3228 T0>

将对象与无值与关系进行比较时发出警告

这个变化是从1.0.1开始的。一些用户正在执行基本上是这种形式的查询:

session.query(Address).filter(Address.user == User(id=None))

SQLAlchemy当前不支持此模式。对于所有版本,它发出的SQL类似于:

SELECT address.id AS address_id, address.user_id AS address_user_id,
address.email_address AS address_email_address
FROM address WHERE ? = address.user_id
(None,)

注意上面,有一个比较WHERE = address.user_id绑定值?在SQL中接收NoneNULL这总是会在SQL中返回False这里的比较理论上会产生SQL如下:

SELECT address.id AS address_id, address.user_id AS address_user_id,
address.email_address AS address_email_address
FROM address WHERE address.user_id IS NULL

但现在,没有依靠“NULL = NULL”在所有情况下产生False这一事实的应用程序有可能在某一天运行风险,SQLAlchemy可能会解决这个问题以生成“IS NULL”,然后查询会产生不同的结果。因此,通过这种操作,您将看到一个警告:

SAWarning: Got None for value of column user.id; this is unsupported
for a relationship comparison and will not currently produce an
IS comparison (but may in a future release)

注意这个模式在大多数情况下在1.0.0版本中被打破,包括所有的beta;会产生像SYMBOL('NEVER_SET')这样的值。这个问题已经解决了,但是通过识别这个模式,现在发出了警告,这样我们就可以更安全地修复这个破坏行为(现在在#3373中捕获)。

#3371 T0>

“否定包含或等于”关系比较将使用属性的当前值,而不是数据库值

这个变化在1.0.1是新的;而我们本来希望在1.0.0版本中,它只是由于#3371而变得明显。

给定一个映射:

class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)

class B(Base):
    __tablename__ = 'b'
    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey('a.id'))
    a = relationship("A")

Given A, with primary key of 7, but which we changed to be 10 without flushing:

s = Session(autoflush=False)
a1 = A(id=7)
s.add(a1)
s.commit()

a1.id = 10

针对与此对象作为目标的多对一关系的查询将使用绑定参数中的值10:

s.query(B).filter(B.a == a1)

生产:

SELECT b.id AS b_id, b.a_id AS b_a_id
FROM b
WHERE ? = b.a_id
(10,)

However, before this change, the negation of this criteria would not use 10, it would use 7, unless the object were flushed first:

s.query(B).filter(B.a != a1)

产生(在1.0.1之前的0.9和所有版本):

SELECT b.id AS b_id, b.a_id AS b_a_id
FROM b
WHERE b.a_id != ? OR b.a_id IS NULL
(7,)

对于一个瞬态对象,它会产生一个破坏的查询:

SELECT b.id, b.a_id
FROM b
WHERE b.a_id != :a_id_1 OR b.a_id IS NULL
{u'a_id_1': symbol('NEVER_SET')}

这个不一致性已经被修复,并且在所有查询中,现在将使用当前属性值,在这个例子中10

#3374 T0>

属性事件和其他有关没有预先存在的值的操作的更改

在这个改变中,访问一个对象时默认的返回值是None现在在每次访问时动态地返回,而不是在第一次访问时用一个特殊的“set”操作隐式设置属性的状态。The visible result of this change is that obj.__dict__ is not implicitly modified on get, and there are also some minor behavioral changes for attributes.get_history() and related functions.

给定一个没有状态的对象:

>>> obj = Foo()

一直以来,SQLAlchemy的行为都是这样的:如果我们访问一个从未设置的标量属性或多对一属性,它将返回为None

>>> obj.someattr
None

This value of None is in fact now part of the state of obj, and is not unlike as though we had set the attribute explicitly, e.g. obj.someattr = None. 然而,这里的“set on get”在历史和事件方面会有不同的表现。它不会发射任何属性事件,另外如果我们查看历史记录,我们可以看到:

>>> inspect(obj).attrs.someattr.history
History(added=(), unchanged=[None], deleted=())   # 0.9 and below

也就是说,它的属性始终是None,并且从未更改过。这与我们首先设置属性的情况明显不同:

>>> obj = Foo()
>>> obj.someattr = None
>>> inspect(obj).attrs.someattr.history
History(added=[None], unchanged=(), deleted=())  # all versions

上面的意思是我们的“set”操作的行为可能会被之前通过“get”访问的值所破坏。在1.0中,这种不一致已经被解决了,当使用默认的“getter”的时候,不再设置任何东西。

>>> obj = Foo()
>>> obj.someattr
None
>>> inspect(obj).attrs.someattr.history
History(added=(), unchanged=(), deleted=())  # 1.0
>>> obj.someattr = None
>>> inspect(obj).attrs.someattr.history
History(added=[None], unchanged=(), deleted=())

上述行为没有太大影响的原因是因为关系数据库中的INSERT语句在大多数情况下认为缺失值与NULL相同。无论SQLAlchemy是否收到一个设置为None的特定属性的历史事件通常都不重要;因为发送None / NULL之间的差异不会影响。However, as #3060 (described here in Priority of attribute changes on relationship-bound attributes vs. FK-bound may appear to change) illustrates, there are some seldom edge cases where we do in fact want to positively have None set. 此外,在这里允许属性事件意味着现在可以为ORM映射的属性创建“默认值”函数。

作为这种变化的一部分,隐含的“无”的生成现在被禁止用于其以前发生的其他情况;这包括何时收到多对一的属性设置操作;以前,如果没有另行设置,“旧”价值将是“无”它现在将发送orm.attributes.NEVER_SET值,该值是可能现在发送给属性侦听器的值。This symbol may also be received when calling on mapper utility functions such as Mapper.primary_key_from_instance(); if the primary key attributes have no setting at all, whereas the value would be None before, it will now be the orm.attributes.NEVER_SET symbol, and no change to the object’s state occurs.

#3061 T0>

关系绑定属性与FK绑定属性变化的优先级可能会改变

作为#3060的一个副作用,将关系绑定的属性设置为None现在是一个跟踪的历史事件,指的是持续存在的意图None因为设置关系绑定属性的情况一直是直接分配给外键属性的情况,所以在分配None时可以看到行为更改。给定一个映射:

class A(Base):
    __tablename__ = 'table_a'

    id = Column(Integer, primary_key=True)

class B(Base):
    __tablename__ = 'table_b'

    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey('table_a.id'))
    a = relationship(A)

在1.0中,关系绑定属性在所有情况下优先于FK绑定属性,无论我们赋值是否是对A对象的引用,或者是None在0.9中,行为是不一致的,只有赋值时才会生效。没有不考虑:

a1 = A(id=1)
a2 = A(id=2)
session.add_all([a1, a2])
session.flush()

b1 = B()
b1.a = a1   # we expect a_id to be '1'; takes precedence in 0.9 and 1.0

b2 = B()
b2.a = None  # we expect a_id to be None; takes precedence only in 1.0

b1.a_id = 2
b2.a_id = 2

session.add_all([b1, b2])
session.commit()

assert b1.a is a1  # passes in both 0.9 and 1.0
assert b2.a is None  # passes in 1.0, in 0.9 it's a2

#3060 T0>

session.expunge()将完全分离已被删除的对象

Session.expunge()的行为有一个错误,导致关于已删除对象的行为不一致。在清除之后,object_session()函数以及InstanceState.session属性仍然会将对象报告为属于Session

u1 = sess.query(User).first()
sess.delete(u1)

sess.flush()

assert u1 not in sess
assert inspect(u1).session is sess  # this is normal before commit

sess.expunge(u1)

assert u1 not in sess
assert inspect(u1).session is None  # would fail

Note that it is normal for u1 not in sess to be True while inspect(u1).session still refers to the session, while the transaction is ongoing subsequent to the delete operation and Session.expunge() has not been called; the full detachment normally completes once the transaction is committed. 这个问题也会影响依赖于Session.expunge()的函数,比如make_transient()

#3139 T0>

加入/子查询显式加载显式不允许yield_per

为了方便使用Query.yield_per()方法,如果在使用yield_per时任何子查询渴望加载器或加入的将要使用集合的渴望加载器都要生效,因为它们目前与yield-per不兼容(然而在理论上子查询加载可能)。发生此错误时,可以使用星号发送lazyload()选项:

q = sess.query(Object).options(lazyload('*')).yield_per(100)

或使用Query.enable_eagerloads()

q = sess.query(Object).enable_eagerloads(False).yield_per(100)

lazyload()选项的优点是仍然可以使用额外的多对一连接的加载器选项:

q = sess.query(Object).options(
    lazyload('*'), joinedload("some_manytoone")).yield_per(100)

修改和修复重复连接目标

这里的变化包括一些错误,在某些情况下,当连接到实体两次,或者多个单表实体针对同一个表时,在不使用基于关系的ON子句的情况下以及连接多次时,会出现意想不到的不一致行为以相同的目标关系。

以映射开始:

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

Base = declarative_base()

class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    bs = relationship("B")

class B(Base):
    __tablename__ = 'b'
    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey('a.id'))

连接到A.bs两次的查询:

print(s.query(A).join(A.bs).join(A.bs))

将呈现:

SELECT a.id AS a_id
FROM a JOIN b ON a.id = b.a_id

该查询会重复删除多余的A.bs,因为它试图支持如下所示的情况:

s.query(A).join(A.bs).\
    filter(B.foo == 'bar').\
    reset_joinpoint().join(A.bs, B.cs).filter(C.bar == 'bat')

That is, the A.bs is part of a “path”. 作为#3367的一部分,两次到达相同的端点而不是大路径的一部分现在将发出一个警告:

SAWarning: Pathed join target A.bs has already been joined to; skipping

当加入实体而不使用关系路径时,更大的变化涉及到。如果我们两次加入B

print(s.query(A).join(B, B.a_id == A.id).join(B, B.a_id == A.id))

在0.9中,这将呈现如下:

SELECT a.id AS a_id
FROM a JOIN b ON b.a_id = a.id JOIN b AS b_1 ON b_1.a_id = a.id

这是有问题的,因为别名是隐含的,在不同的ON子句的情况下会导致不可预知的结果。

在1.0中,没有应用自动别名,我们得到:

SELECT a.id AS a_id
FROM a JOIN b ON b.a_id = a.id JOIN b ON b.a_id = a.id

这将从数据库中引发错误。虽然如果我们加入冗余关系和冗余非关系的目标,如果“重复连接目标”的作用是相同的,那么现在我们只是在更严重的情况下改变行为,在这种情况下隐式锯齿以前会发生,只是在关系案件中发出警告。最终,两次加入同一个事物而没有任何混淆消除歧义,应该在所有情况下都会产生错误。

此更改还会影响单表继承目标。使用映射如下:

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

Base = declarative_base()

class A(Base):
    __tablename__ = "a"

    id = Column(Integer, primary_key=True)
    type = Column(String)

    __mapper_args__ = {'polymorphic_on': type, 'polymorphic_identity': 'a'}


class ASub1(A):
    __mapper_args__ = {'polymorphic_identity': 'asub1'}


class ASub2(A):
    __mapper_args__ = {'polymorphic_identity': 'asub2'}


class B(Base):
    __tablename__ = 'b'

    id = Column(Integer, primary_key=True)

    a_id = Column(Integer, ForeignKey("a.id"))

    a = relationship("A", primaryjoin="B.a_id == A.id", backref='b')

s = Session()

print(s.query(ASub1).join(B, ASub1.b).join(ASub2, B.a))

print(s.query(ASub1).join(B, ASub1.b).join(ASub2, ASub2.id == B.a_id))

底部的两个查询是相同的,并且应该呈现相同的SQL:

SELECT a.id AS a_id, a.type AS a_type
FROM a JOIN b ON b.a_id = a.id JOIN a ON b.a_id = a.id AND a.type IN (:type_1)
WHERE a.type IN (:type_2)

上面的SQL是无效的,因为它在FROM列表中呈现两次“a”。但是,隐式别名错误只会在第二个查询中出现,而不是渲染出来:

SELECT a.id AS a_id, a.type AS a_type
FROM a JOIN b ON b.a_id = a.id JOIN a AS a_1
ON a_1.id = b.a_id AND a_1.type IN (:type_1)
WHERE a_1.type IN (:type_2)

在上面,第二个连接到“a”是别名。虽然这看起来很方便,但并不是单一继承查询如何工作,而且具有误导性和不一致性。

最终结果是,依赖于这个bug的应用程序现在会有数据库引发的错误。解决方案是使用预期的形式。在查询中引用单一继承实体的多个子类时,必须手动使用别名来消除表的歧义,因为所有子类通常引用同一个表:

asub2_alias = aliased(ASub2)

print(s.query(ASub1).join(B, ASub1.b).join(asub2_alias, B.a.of_type(asub2_alias)))

#3233 #3367

延期的列不再隐式地不延迟

如果标记为延迟的映射属性没有显式未延迟,现在将保持“延迟”状态,即使它们的列以某种方式存在于结果集中。这是一个性能增强,因为当获得结果集时,ORM加载不再花费时间搜索每个延迟列。但是,对于依赖于此的应用程序,现在应该使用显式的undefer()或类似选项,以防止在访问属性时发出SELECT。

已弃用的ORM事件挂钩已删除

下面的ORM事件挂钩已经被删除:translate_rowpopulate_instanceappend_resultcreate_instance这些钩子的用例起源于早期的0.1 / 0.2系列的SQLAlchemy,并且早已不必要了。特别的是,这些钩子在很大程度上是不可用的,因为这些事件中的行为契约与周围的内部联系紧密相关,例如需要如何创建和初始化实例以及如何在ORM生成的行内定位列。删除这些钩子大大简化了ORM对象加载的机制。

当使用自定义行加载器时,对新的Bundle功能的API更改

当自定义类上的create_row_processor()方法被覆盖时,0.9的新的Bundle对象在API中有一个小的改变。以前,示例代码如下所示:

from sqlalchemy.orm import Bundle

class DictBundle(Bundle):
    def create_row_processor(self, query, procs, labels):
        """Override create_row_processor to return values as dictionaries"""
        def proc(row, result):
            return dict(
                        zip(labels, (proc(row, result) for proc in procs))
                    )
        return proc

未使用的result成员现在被删除:

from sqlalchemy.orm import Bundle

class DictBundle(Bundle):
    def create_row_processor(self, query, procs, labels):
        """Override create_row_processor to return values as dictionaries"""
        def proc(row):
            return dict(
                        zip(labels, (proc(row) for proc in procs))
                    )
        return proc

也可以看看

Column Bundles

右内连接嵌套现在是用innerjoin = True 连接的默认连接

joinedload.innerjoin以及relationship.innerjoin的行为现在是使用“嵌套的”内部联接,也就是右嵌套的行为,连接加入的急切负载被链接到一个外连接急切的加载。为了获得在外连接存在的情况下将所有连接的热切负载链接为外连接的旧行为,请使用innerjoin="unnested"

As introduced in Right-nested inner joins available in joined eager loads from version 0.9, the behavior of innerjoin="nested" is that an inner join eager load chained to an outer join eager load will use a right-nested join. "nested" is now implied when using innerjoin=True:

query(User).options(
    joinedload("orders", innerjoin=False).joinedload("items", innerjoin=True))

在新的默认情况下,这将以如下格式呈现FROM子句:

FROM users LEFT OUTER JOIN (orders JOIN items ON <onclause>) ON <onclause>

也就是说,对INNER连接使用右嵌套连接,以便可以返回users的完整结果。INNER连接的使用比使用OUTER连接更有效率,并允许joinedload.innerjoin优化参数在所有情况下都生效。

要获得较旧的行为,请使用innerjoin="unnested"

query(User).options(
    joinedload("orders", innerjoin=False).joinedload("items", innerjoin="unnested"))

这将避免使用所有OUTER连接的right-nested连接并将连接链接在一起,尽管使用了innerjoin指令:

FROM users LEFT OUTER JOIN orders ON <onclause> LEFT OUTER JOIN items ON <onclause>

正如在0.9的注释中指出的那样,右嵌套连接唯一的数据库后端是SQLite;从0.9版开始,SQLAlchemy将右嵌套连接转换为子查询,作为SQLite上的连接目标。

也可以看看

Right-nested inner joins available in joined eager loads - 0.9.4中引入的功能描述。

#3008 T0>

子查询不再应用于使用list = False加入的预载

给定一个加入像下面这样的热切加载:

class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    b = relationship("B", uselist=False)


class B(Base):
    __tablename__ = 'b'
    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey('a.id'))

s = Session()
print(s.query(A).options(joinedload(A.b)).limit(5))

SQLAlchemy considers the relationship A.b to be a “one to many, loaded as a single value”, which is essentially a “one to one” relationship. 但是,加入预加载一直将上述情况视为主查询需要位于子查询中的情况,正如通常在主查询应用LIMIT时收集B对象所需的那样:

SELECT anon_1.a_id AS anon_1_a_id, b_1.id AS b_1_id, b_1.a_id AS b_1_a_id
FROM (SELECT a.id AS a_id
FROM a LIMIT :param_1) AS anon_1
LEFT OUTER JOIN b AS b_1 ON anon_1.a_id = b_1.a_id

但是,由于内部查询与外部查询之间的关系最多只有一行在uselist=False的情况下(与多对一相同)共享,在这种情况下,LIMIT +加入了急切加载的“子查询”现在被删除:

SELECT a.id AS a_id, b_1.id AS b_1_id, b_1.a_id AS b_1_a_id
FROM a LEFT OUTER JOIN b AS b_1 ON a.id = b_1.a_id
LIMIT :param_1

在LEFT OUTER JOIN返回多于一行的情况下,ORM总是在这里发出一个警告,忽略了uselist=False的附加结果,所以在这种错误情况下的结果不应该改变。

#3249 T0>

query.update()/ query.delete()如果与join(),select_from(),from_self()一起使用,则引发

A warning is emitted in SQLAlchemy 0.9.10 (not yet released as of June 9, 2015) when the Query.update() or Query.delete() methods are invoked against a query which has also called upon Query.join(), Query.outerjoin(), Query.select_from() or Query.from_self(). 这些不支持的用例在0.9系列中默默无效,直到发出警告。在1.0中,这些情况引发了一个例外。

#3349 T0>

query.update()与synchronize_session='evaluate'引发多表更新

对于Query.update()的“评估程序”不适用于多表更新,需要设置为synchronize_session=Falsesynchronize_session='fetch'新的行为是,现在引发一个明确的异常,并带有消息来改变同步设置。这是从0.9.7发出的警告升级而来的。

#3117 T0>

复活事件已被删除

“复活”ORM事件已被完全删除。从0.8版本开始,这个事件不再具有任何功能,从工作单元中删除了旧的“可变”系统。

Change to single-table-inheritance criteria when using from_self(), count()

给定一个单表继承映射,如:

class Widget(Base):
    __table__ = 'widget_table'

class FooWidget(Widget):
    pass

对子类使用Query.from_self()Query.count()将产生子查询,但是将子类的“WHERE”标准添加到外部:

sess.query(FooWidget).from_self().all()

渲染:

SELECT
    anon_1.widgets_id AS anon_1_widgets_id,
    anon_1.widgets_type AS anon_1_widgets_type
FROM (SELECT widgets.id AS widgets_id, widgets.type AS widgets_type,
FROM widgets) AS anon_1
WHERE anon_1.widgets_type IN (?)

这个问题是,如果内部查询没有指定所有的列,那么我们不能在外部添加WHERE子句(它实际上会尝试,并产生一个错误的查询)。这个决定显然要回到0.6.5,注意“可能需要对此做更多的调整”。那么这些调整已经到了!所以现在上面的查询将呈现:

SELECT
    anon_1.widgets_id AS anon_1_widgets_id,
    anon_1.widgets_type AS anon_1_widgets_type
FROM (SELECT widgets.id AS widgets_id, widgets.type AS widgets_type,
FROM widgets
WHERE widgets.type IN (?)) AS anon_1

所以那些不包含“type”的查询仍然可以工作!:

sess.query(FooWidget.id).count()

呈现:

SELECT count(*) AS count_1
FROM (SELECT widgets.id AS widgets_id
FROM widgets
WHERE widgets.type IN (?)) AS anon_1

#3177 T0>

单表继承条件无条件地添加到所有ON子句

当连接到单表继承子类目标时,ORM在加入关系时总是添加“单表标准”。给定映射为:

class Widget(Base):
    __tablename__ = 'widget'
    id = Column(Integer, primary_key=True)
    type = Column(String)
    related_id = Column(ForeignKey('related.id'))
    related = relationship("Related", backref="widget")
    __mapper_args__ = {'polymorphic_on': type}


class FooWidget(Widget):
    __mapper_args__ = {'polymorphic_identity': 'foo'}


class Related(Base):
    __tablename__ = 'related'
    id = Column(Integer, primary_key=True)

一段时间以来,加入关系的行为将为这种类型提供一个“单一继承”子句:

s.query(Related).join(FooWidget, Related.widget).all()

SQL输出:

SELECT related.id AS related_id
FROM related JOIN widget ON related.id = widget.related_id AND widget.type IN (:type_1)

Above, because we joined to a subclass FooWidget, Query.join() knew to add the AND widget.type IN ('foo') criteria to the ON clause.

The change here is that the AND widget.type IN() criteria is now appended to any ON clause, not just those generated from a relationship, including one that is explicitly stated:

# ON clause will now render as
# related.id = widget.related_id AND widget.type IN (:type_1)
s.query(Related).join(FooWidget, FooWidget.related_id == Related.id).all()

以及没有任何类型的ON子句时的“隐式”连接:

# ON clause will now render as
# related.id = widget.related_id AND widget.type IN (:type_1)
s.query(Related).join(FooWidget).all()

以前,这些ON子句不包括单继承条件。已经添加此标准来解决此问题的应用程序将要删除其显式使用,但如果在此期间发生两次标准,它应该继续正常工作。

#3222 T0>

关键行为改变 - 核心

将完整的SQL片段强制转换为文本()时发出警告

自从SQLAlchemy开始以来,一直强调不妨碍使用纯文本。The Core and ORM expression systems were intended to allow any number of points at which the user can just use plain text SQL expressions, not just in the sense that you can send a full SQL string to Connection.execute(), but that you can send strings with SQL expressions into many functions, such as Select.where(), Query.filter(), and Select.order_by().

Note that by “SQL expressions” we mean a full fragment of a SQL string, such as:

# the argument sent to where() is a full SQL expression
stmt = select([sometable]).where("somecolumn = 'value'")

and we are not talking about string arguments, that is, the normal behavior of passing string values that become parameterized:

# This is a normal Core expression with a string argument -
# we aren't talking about this!!
stmt = select([sometable]).where(sometable.c.somecolumn == 'value')

核心教程长期以来使用这种技术的一个例子,使用一个select()结构,其中几乎所有的组件都被指定为直线字符串。However, despite this long-standing behavior and example, users are apparently surprised that this behavior exists, and when asking around the community, I was unable to find any user that was in fact not surprised that you can send a full string into a method like Query.filter().

所以这里的改变是鼓励用户在编写部分或全部由文本片段组成的SQL时限定文本字符串。编写选择如下:

stmt = select(["a", "b"]).where("a = b").select_from("sometable")

这个声明是正常建立起来的,与以前一样强制。但是,会看到以下警告:

SAWarning: Textual column expression 'a' should be explicitly declared
with text('a'), or use column('a') for more specificity
(this warning may be suppressed after 10 occurrences)

SAWarning: Textual column expression 'b' should be explicitly declared
with text('b'), or use column('b') for more specificity
(this warning may be suppressed after 10 occurrences)

SAWarning: Textual SQL expression 'a = b' should be explicitly declared
as text('a = b') (this warning may be suppressed after 10 occurrences)

SAWarning: Textual SQL FROM expression 'sometable' should be explicitly
declared as text('sometable'), or use table('sometable') for more
specificity (this warning may be suppressed after 10 occurrences)

这些警告试图通过显示参数以及接收字符串的位置来准确显示问题的出处。警告使用Session.get_bind() handles a wider variety of inheritance scenarios,以便参数化的警告可以安全地发出而不会耗尽内存,并且如果希望警告例外,应该使用Python警告过滤器

import warnings
warnings.simplefilter("error")   # all warnings raise an exception

鉴于上述警告,我们的声明正常,但为了摆脱这些警告,我们将重写我们的声明如下:

from sqlalchemy import select, text
stmt = select([
        text("a"),
        text("b")
    ]).where(text("a = b")).select_from(text("sometable"))

正如警告所暗示的那样,如果我们使用column()table(),我们可以更具体地说明文本。

from sqlalchemy import select, text, column, table

stmt = select([column("a"), column("b")]).\
    where(text("a = b")).select_from(table("sometable"))

还要注意,现在可以从“sqlalchemy”中导入table()column(),而不使用“sql”部分。

The behavior here applies to select() as well as to key methods on Query, including Query.filter(), Query.from_statement() and Query.having().

ORDER BY和GROUP BY是特殊情况

有一种情况使用字符串具有特殊的含义,作为这种改变的一部分,我们已经增强了它的功能。当我们有一个select()Query引用了一些列名或命名标签时,我们可能需要GROUP BY和/或ORDER BY已知列或标签:

stmt = select([
    user.c.name,
    func.count(user.c.id).label("id_count")
]).group_by("name").order_by("id_count")

在上面的语句中,我们希望看到“ORDER BY id_count”,而不是函数的重新声明。The string argument given is actively matched to an entry in the columns clause during compilation, so the above statement would produce as we expect, without warnings (though note that the "name" expression has been resolved to users.name! ):

SELECT users.name, count(users.id) AS id_count
FROM users GROUP BY users.name ORDER BY id_count

但是,如果我们提到一个不能定位的名字,那么我们再次得到警告,如下所示:

stmt = select([
        user.c.name,
        func.count(user.c.id).label("id_count")
    ]).order_by("some_label")

输出结果是我们所说的,但它又一次警告我们:

SAWarning: Can't resolve label reference 'some_label'; converting to
text() (this warning may be suppressed after 10 occurrences)

SELECT users.name, count(users.id) AS id_count
FROM users ORDER BY some_label

上述行为适用于所有我们可能想要参考所谓“标签参考”的地方; ORDER BY和GROUP BY,而且在OVER子句中,还有一个引用列的DISTINCT ON子句(例如Postgresql语法)。

我们仍然可以使用text()为ORDER BY或其他表达式指定任意表达式:

stmt = select([users]).order_by(text("some special expression"))

整个变化的结果是,SQLAlchemy现在希望我们告诉它什么时候发送一个字符串,这个字符串显式是一个text()结构,或一个列,表等,如果我们使用它作为一个按照,按组或其他表达式的标签名称,SQLAlchemy期望字符串解析为已知的东西,否则应该再次用text()或类似的限定。

#2992 T0>

在使用多值插入时,为每行调用Python方默认值

当使用多值版本的Insert.values()时,支持Python端列的默认设置基本上没有实现,只有在特定情况下“偶然”使用非定位(例如命名)风格的绑定参数,以及何时不需要为每一行调用Python方面的可调用方法。

该功能已被彻底修改,使其与“executemany”风格的调用更类似:

import itertools

counter = itertools.count(1)
t = Table(
    'my_table', metadata,
    Column('id', Integer, default=lambda: next(counter)),
    Column('data', String)
)

conn.execute(t.insert().values([
    {"data": "d1"},
    {"data": "d2"},
    {"data": "d3"},
]))

上面的例子将会像预期的那样分别为每行调用next(counter)

INSERT INTO my_table (id, data) VALUES (?, ?), (?, ?), (?, ?)
(1, 'd1', 2, 'd2', 3, 'd3')

以前,位置方言会失败,因为不会为其他位置产生约束:

Incorrect number of bindings supplied. The current statement uses 6,
and there are 4 supplied.
[SQL: u'INSERT INTO my_table (id, data) VALUES (?, ?), (?, ?), (?, ?)']
[parameters: (1, 'd1', 'd2', 'd3')]

而用“命名”方言,“id”的相同值将在每一行中重复使用(因此,这种改变与依赖于此的系统是向后不相容的):

INSERT INTO my_table (id, data) VALUES (:id, :data_0), (:id, :data_1), (:id, :data_2)
{u'data_2': 'd3', u'data_1': 'd2', u'data_0': 'd1', 'id': 1}

系统也将拒绝调用“服务器端”默认为内联呈现的SQL,因为不能保证服务器端默认与此兼容。如果VALUES子句为特定列呈现,则需要Python端值;如果省略的值仅指服务器端默认值,则会引发异常:

t = Table(
    'my_table', metadata,
    Column('id', Integer, primary_key=True),
    Column('data', String, server_default='some default')
)

conn.execute(t.insert().values([
    {"data": "d1"},
    {"data": "d2"},
    {},
]))

会提高:

sqlalchemy.exc.CompileError: INSERT value for column my_table.data is
explicitly rendered as a boundparameter in the VALUES clause; a
Python-side value or SQL expression is required

以前,值“d1”将被复制到第三行(但是只有命名格式!):

INSERT INTO my_table (data) VALUES (:data_0), (:data_1), (:data_0)
{u'data_1': 'd2', u'data_0': 'd1'}

#3288 T0>

事件侦听器不能添加或从该事件的运行者中删除

从同一事件本身中删除事件侦听器将在迭代期间修改列表的元素,这将导致静态连接的事件侦听器无法启动。为了防止这种情况发生,同时仍然保持性能,列表已被collections.deque()取代,它不允许在迭代过程中进行任何添加或删除,而是引发RuntimeError

#3163 T0>

现在INSERT ... FROM SELECT结构意味着inline=True

现在使用Insert.from_select()意味着inline=Trueinsert()上。这有助于修复INSERT ... FROM SELECT构造无意中编译为支持后端的“隐式返回”的错误,这会在插入零行的INSERT情况下导致破坏(因为隐式返回期望行) ,以及插入多行的INSERT(例如,只有许多行的第一行)情况下的任意返回数据。一个类似的更改也适用于具有多个参数集的INSERT..VALUES;隐式RETURNING将不再为这个语句发出。由于这两个构造都处理可变数量的行,所以ResultProxy.inserted_primary_key存取器不适用。以前,有一个文档说明,有人可能更喜欢使用INSERT..FROM SELECT的inline=True,因为有些数据库不支持返回,因此不能做“隐式”返回,但是没有任何理由INSERT ... FROM SELECT需要在任何情况下隐式返回。如果需要插入数据,则应使用常规的显式Insert.returning()返回结果行的可变数目。

#3169 T0>

autoload_with现在意味着autoload=True

可以通过单独传递Table.autoload_with来设置Table进行反射:

my_table = Table('my_table', metadata, autoload_with=some_engine)

#3027 T0>

DBAPI异常包装和handle_error()事件改进

Connection对象失效,然后尝试重新连接并遇到错误的情况下,SQLAlchemy不会发生DBAPI异常的包装。这已经解决了。

此外,最近添加的ConnectionEvents.handle_error()事件现在被调用,用于初始连接时发生的错误,重新连接时以及使用create_engine()给定的连接通过create_engine.creator功能。

The ExceptionContext object has a new datamember ExceptionContext.engine that will always refer to the Engine in use, in those cases when the Connection object is not available (e.g. on initial connect).

#3266 T0>

ForeignKeyConstraint.columns现在是一个ColumnCollection

ForeignKeyConstraint.columns was previously a plain list containing either strings or Column objects, depending on how the ForeignKeyConstraint was constructed and whether it was associated with a table. 该集合现在是ColumnCollection,并且仅在ForeignKeyConstraintTable关联之后才被初始化。无论对象是如何构造的或其当前状态如何,新的访问器ForeignKeyConstraint.column_keys被添加到无条件地返回本地列集合的字符串键值。

MetaData.sorted_tables访问器是“确定性”

The sorting of tables resulting from the MetaData.sorted_tables accessor is “deterministic”; the ordering should be the same in all cases regardless of Python hashing. 这是通过首先按名称对表格进行排序,然后将它们传递给拓扑算法,该拓扑算法在迭代时保持排序。

Note that this change does not yet apply to the ordering applied when emitting MetaData.create_all() or MetaData.drop_all().

#3084 T0>

null(),false()和true()常量不再是单身人士

这三个常量被更改为返回0.9中的“单例”值;不幸的是,这将导致类似下面的查询不按预期呈现:

select([null(), null()])

rendering only SELECT NULL AS anon_1, because the two null() constructs would come out as the same NULL object, and SQLAlchemy’s Core model is based on object identity in order to determine lexical significance. 除了节省物体开销之外,0.9的变化没有任何重要性;一般来说,一个未命名的结构需要保持词汇的独特性,以便得到唯一的标记。

#3170 T0>

SQLite / Oracle具有不同的临时表/视图名称报告方法

在SQLite / Oracle的情况下,Inspector.get_table_names()Inspector.get_view_names()方法也会返回临时表和视图的名称,任何其他的方言(在MySQL的情况下,至少是不可能的)。This logic has been moved out to two new methods Inspector.get_temp_table_names() and Inspector.get_temp_view_names().

Note that reflection of a specific named temporary table or temporary view, either by Table('name', autoload=True) or via methods like Inspector.get_columns() continues to function for most if not all dialects. 对于SQLite,具体来说,还有一个针对临时表的UNIQUE约束反射的错误修正,即#3203

#3204 T0>

方言的改进和改变 - Postgresql

ENUM类型创建/删除规则的检修

对于创建和删除类型,Postgresql postgresql.ENUM的规则已经变得更为严格。

An postgresql.ENUM that is created without being explicitly associated with a MetaData object will be created and dropped corresponding to Table.create() and Table.drop():

table = Table('sometable', metadata,
    Column('some_enum', ENUM('a', 'b', 'c', name='myenum'))
)

table.create(engine)  # will emit CREATE TYPE and CREATE TABLE
table.drop(engine)  # will emit DROP TABLE and DROP TYPE - new for 1.0

这意味着如果第二个表也有一个名为“myenum”的枚举,那么上面的DROP操作现在将失败。为了适应公共共享枚举类型的用例,元数据关联枚举的行为已经得到了增强。

创建的postgresql.ENUMMetaData对象显式关联不会创建对应于Table.create()Table.drop(),而Table.create()checkfirst=True标志调用:

my_enum = ENUM('a', 'b', 'c', name='myenum', metadata=metadata)

table = Table('sometable', metadata,
    Column('some_enum', my_enum)
)

# will fail: ENUM 'my_enum' does not exist
table.create(engine)

# will check for enum and emit CREATE TYPE
table.create(engine, checkfirst=True)

table.drop(engine)  # will emit DROP TABLE, *not* DROP TYPE

metadata.drop_all(engine) # will emit DROP TYPE

metadata.create_all(engine) # will emit CREATE TYPE

#3319 T0>

新的Postgresql表选项

当通过Table构造渲染DDL时,增加了对PG表选项TABLESPACE,ON COMMIT,WITH(OUT)OIDS和INHERITS的支持。

#2051 T0>

使用Postgresql Dialect的新get_enums()方法

在Postgresql中,inspect()方法返回一个PGInspector对象,其中包含一个新的PGInspector.get_enums()所有可用的ENUM类型:

from sqlalchemy import inspect, create_engine

engine = create_engine("postgresql+psycopg2://host/dbname")
insp = inspect(engine)
print(insp.get_enums())

Postgresql方言反映物化视图,外部表

变化如下:

The change to reflection involves adding 'm' and 'f' to the list of qualifiers we use when querying pg_class.relkind, but this change is new in 1.0.0 to avoid any backwards-incompatible surprises for those running 0.9 in production.

#2891 T0>

Postgresql has_table()现在可用于临时表

这是一个简单的修复,使临时表的“有表”现在可以工作,以便类似下面的代码可以继续:

from sqlalchemy import *

metadata = MetaData()
user_tmp = Table(
    "user_tmp", metadata,
    Column("id", INT, primary_key=True),
    Column('name', VARCHAR(50)),
    prefixes=['TEMPORARY']
)

e = create_engine("postgresql://scott:tiger@localhost/test", echo='debug')
with e.begin() as conn:
    user_tmp.create(conn, checkfirst=True)

    # checkfirst will succeed
    user_tmp.create(conn, checkfirst=True)

这种行为将导致非失败的应用程序的行为不同,这是非常不可能的,因为Postgresql允许非临时表以静默方式覆盖临时表。因此,像下面这样的代码现在将完全不同,不再在临时表之后创建真实表:

from sqlalchemy import *

metadata = MetaData()
user_tmp = Table(
    "user_tmp", metadata,
    Column("id", INT, primary_key=True),
    Column('name', VARCHAR(50)),
    prefixes=['TEMPORARY']
)

e = create_engine("postgresql://scott:tiger@localhost/test", echo='debug')
with e.begin() as conn:
    user_tmp.create(conn, checkfirst=True)

    m2 = MetaData()
    user = Table(
        "user_tmp", m2,
        Column("id", INT, primary_key=True),
        Column('name', VARCHAR(50)),
    )

    # in 0.9, *will create* the new table, overwriting the old one.
    # in 1.0, *will not create* the new table
    user.create(conn, checkfirst=True)

#3264 T0>

Postgresql FILTER关键字

Postgresql从9.4开始支持用于聚合函数的SQL标准FILTER关键字。SQLAlchemy允许使用FunctionElement.filter()

func.count(1).filter(True)

PG8000方言支持客户端编码

The create_engine.encoding parameter is now honored by the pg8000 dialect, using on connect handler which emits SET CLIENT_ENCODING matching the selected encoding.

PG8000原生JSONB支持

已经添加了对PG8000版本大于1.10.1的支持,其中原生支持JSONB。

支持pypy 上的psycopg2cffi Dialect

增加了对pypy psycopg2cffi方言的支持。

方言的改进和改变 - MySQL

在所有情况下,MySQL TIMESTAMP Type现在都会呈现NULL / NOT NULL

如果使用nullable=True设置列,则MySQL方言一直通过为这样的类型发送NULL来解决MySQL与TIMESTAMP列相关的隐式NOT NULL缺省问题。However, MySQL 5.6.6 and above features a new flag explicit_defaults_for_timestamp which repairs MySQL’s non-standard behavior to make it behave like any other type; to accommodate this, SQLAlchemy now emits NULL/NOT NULL unconditionally for all TIMESTAMP columns.

#3155 T0>

MySQL SET Type大小支持空集,unicode,空值处理

mysql.SET类型历史上不包括分别处理空白集和空值的系统;由于不同的驱动程序对于空字符串和空字符集表示的处理有不同的行为,所以SET类型只试图在这些行为之间进行对冲,选择将空集作为set([''])这里的一个基本原理是,否则实际上不可能在MySQL SET中存储一个空字符串,因为驱动程序给我们返回字符串而无法辨别set([''])set()It was left to the user to determine if set(['']) actually meant “empty set” or not.

新的行为将空字符串的用例(这是一个甚至在MySQL文档中没有记录的异常情况)移动到一个特殊情况,现在mysql.SET的默认行为是:

  • 将MySQL-python返回的空字符串''处理为空set set()
  • 将MySQL-Connector-Python返回的单空值集合set([''])转换为空set set()
  • 为了处理实际希望在其可能值列表中包含空值''的情况,实现了一个新特性(在这个用例中是必需的),从而设置值被持久化加载为一个按位整数值;添加标志mysql.SET.retrieve_as_bitwise以启用此功能。

通过使用mysql.SET.retrieve_as_bitwise标志,可以保持集合的持久性,并且不会产生任何含糊的值。理论上这个标志可以在任何情况下被打开,只要给定类型的值列表与数据库中声明的顺序完全一致即可。它只会使SQL echo输出更加不寻常。

mysql.SET的默认行为保持不变,使用字符串往返传值。基于字符串的行为现在支持unicode,包括use_unicode = 0的MySQL-python。

#3283 T0>

MySQL内部“没有这样的表”异常没有传递给事件处理程序

现在,MySQL方言将禁用来自内部使用的用于检测表是否存在的那些语句的ConnectionEvents.handle_error()事件。这是通过使用执行选项skip_user_error_events来实现的,该选项为该执行的范围禁用句柄错误事件。通过这种方式,重写异常的用户代码不需要担心偶尔需要捕获SQLAlchemy特定异常的MySQL方言或其他方言。

更改了MySQL连接器的raise_on_warnings的默认值

MySQL-Connector将“raise_on_warnings”的默认值更改为False。由于某种原因,这被设置为True。不幸的是,“缓冲”标志必须保持为True,因为MySQL连接器不允许游标被关闭,除非所有结果都被完全获取。

#2515 T0>

MySQL布尔符号“true”,“false”再次工作

对IS / IS NOT运算符进行0.9版本的修改以及#2682中的布尔类型不允许MySQL方言在“IS”的上下文中使用“true”和“false” / “不是”。显然,即使MySQL没有“boolean”类型,当使用特殊的“true”和“false”符号时,即使它们与“1”和“0”(和IS /不是不使用数字)。

因此,这里的变化是MySQL方言仍然是“非本地布尔”,但是true()false()符号再次产生关键字“true”和“false “,这样像column.is_(true())这样的表达式再次适用于MySQL。

#3186 T0>

match()运算符现在返回与MySQL的浮点返回值兼容的不可知的MatchType

一个ColumnOperators.match()表达式的返回类型现在是一个名为MatchType的新类型。这是Boolean的一个子类,可以被方言拦截,以便在SQL执行时产生不同的结果类型。

像下面这样的代码现在可以正常工作,并返回MySQL上的浮点数:

>>> connection.execute(
...    select([
...        matchtable.c.title.match('Agile Ruby Programming').label('ruby'),
...        matchtable.c.title.match('Dive Python').label('python'),
...        matchtable.c.title
...    ]).order_by(matchtable.c.id)
... )
[
    (2.0, 0.0, 'Agile Web Development with Ruby On Rails'),
    (0.0, 2.0, 'Dive Into Python'),
    (2.0, 0.0, "Programming Matz's Ruby"),
    (0.0, 0.0, 'The Definitive Guide to Django'),
    (0.0, 1.0, 'Python in a Nutshell')
]

#3263 T0>

细雨方言现在是一种外语

The dialect for Drizzle is now an external dialect, available at https://bitbucket.org/zzzeek/sqlalchemy-drizzle. 在SQLAlchemy能够容纳第三方方言之前,这种方言被添加到了SQLAlchemy中;所有不属于“无所不在”类别的数据库都是第三方方言。方言的实现没有改变,仍然基于SQLAlchemy中的MySQL + MySQLdb方言。方言尚未发布,处于“阁楼”地位;然而,它通过了大部分的测试,并且一般来说都是体面的工作顺序,如果有人想要拿起来打磨。

方言的改进和改变 - SQLite

SQLite命名和未命名的UNIQUE和FOREIGN KEY约束将检查和反映

现在,UNIQUE和FOREIGN KEY约束已经全面反映在SQLite上,无论是否有名字。以前,外键名称被忽略,未命名的唯一约束被忽略。特别是这将有助于Alembic的新SQLite迁移功能。

为了实现这一点,对于外键和唯一约束,PRAGMA foreign_keys,index_list和index_info的结果与CREATE TABLE语句的正则表达式解析相结合,以形成约束名称的完整图片,以及区分UNIQUE创建为独特与未命名INDEXes的约束。

#3244 T0>

#3261 T0>

方言的改进和改变 - SQL Server

PyODBC驱动程序的名称是必需的与基于主机名的SQL Server连接

使用无DSN连接的PyODBC连接到SQL Server使用明确的主机名,现在需要一个驱动程序名 - SQLAlchemy将不再尝试猜测默认值:

engine = create_engine("mssql+pyodbc://scott:tiger@myhost:port/databasename?driver=SQL+Server+Native+Client+10.0")

SQLAlchemy以前硬编码的默认“SQL Server”在Windows上已经过时,SQLAlchemy不能根据操作系统/驱动程序检测来猜测最佳的驱动程序。使用ODBC来完全避免此问题时,始终首选使用DSN。

#3182 T0>

SQL Server 2012大型文本/二进制类型呈现为VARCHAR,NVARCHAR,VARBINARY

对于SQL Server 2012及更高版本,TextUnicodeTextLargeBinary类型的呈现方式已更改,并具有完全控制行为的选项,基于微软的弃用准则。有关详细信息,请参见Large Text/Binary Type Deprecation

方言的改进和改变 - Oracle

在Oracle 中改进了对CTE的支持

CTE support has been fixed up for Oracle, and there is also a new feature CTE.with_suffixes() that can assist with Oracle’s special directives:

included_parts = select([
    part.c.sub_part, part.c.part, part.c.quantity
]).where(part.c.part == "p1").\
    cte(name="included_parts", recursive=True).\
    suffix_with(
        "search depth first by part set ord1",
        "cycle part set y_cycle to 1 default 0", dialect='oracle')

#3220 T0>

DDL的新Oracle关键字

关键字如COMPRESS,ON COMMIT,BITMAP:

Oracle Table Options

Oracle Specific Index Options