[orm] [bug] Fixed regression caused by #2677 whereby calling
Session.delete()
on an object that was already flushed as
deleted in that session would fail to set up the object in the
identity map (or reject the object), causing flush errors as the
object were in a state not accommodated by the unit of work.
The pre-1.1 behavior in this case has been restored, which is that
the object is put back into the identity map so that the DELETE
statement will be attempted again, which emits a warning that the number
of expected rows was not matched (unless the row were restored outside
of the session).¶
References: #3839
[orm] [bug] Fixed regression where some Query
methods like
Query.update()
and others would fail if the Query
were against a series of mapped columns, rather than the mapped
entity as a whole.¶
References: #3836
[sql] [bug] Fixed bug involving new value translation and validation feature
in Enum
whereby using the enum object in a string
concatenation would maintain the Enum
type as the type
of the expression overall, producing missing lookups. A string
concatenation against an Enum
-typed column now uses
String
as the datatype of the expression itself.¶
References: #3833
[sql] [bug] Fixed regression which occurred as a side effect of #2919,
which in the less typical case of a user-defined
TypeDecorator
that was also itself an instance of
SchemaType
(rather than the implementation being such)
would cause the column attachment events to be skipped for the
type itself.¶
References: #3832
[postgresql] [bug] Postgresql table reflection will ensure that the
Column.autoincrement
flag is set to False when reflecting
a primary key column that is not of an Integer
datatype,
even if the default is related to an integer-generating sequence.
This can happen if a column is created as SERIAL and the datatype
is changed. The autoincrement flag can only be True if the datatype
is of integer affinity in the 1.1 series.¶
References: #3835
[orm] [bug] Fixed bug involving the rule to disable a joined collection eager loader on the other side of a many-to-one lazy loader, first added in #1495, where the rule would fail if the parent object had some other lazyloader-bound query options associated with it.¶
References: #3824
[orm] [bug] Fixed self-referential entity, deferred column loading issue in a similar style as that of #3431, #3811 where an entity is present in multiple positions within the row due to self-referential eager loading; when the deferred loader only applies to one of the paths, the “present” column loader will now override the deferred non- load for that entity regardless of row ordering.¶
References: #3822
[sql] [bug] Fixed a regression caused by a newly added function that performs the
“wrap callable” function of sql DefaultGenerator
objects,
an attribute error raised for __module__
when the default callable
was a functools.partial
or other object that doesn’t have a
__module__
attribute.¶
References: #3823
[sql] [bug] [postgresql] Fixed regression in Enum
type where event handlers were not
transferred in the case of the type object being copied, due to a
conflicting copy() method added as part of #3250. This copy
occurs normally in situations when the column is copied, such as
in tometadata() or when using declarative mixins with columns. The
event handler not being present would impact the constraint being
created for a non-native enumerated type, but more critically the
ENUM object on the PostgreSQL backend.¶
References: #3827
[postgresql] [bug] [sql] Changed the naming convention used when generating bound parameters for a multi-VALUES insert statement, so that the numbered parameter names don’t conflict with the anonymized parameters of a WHERE clause, as is now common in a PostgreSQL ON CONFLICT construct.¶
References: #3828
[bug] [core] Changed the CompileError raised when various primary key missing situations are detected to a warning. The statement is again passed to the database where it will fail and the DBAPI error (usually IntegrityError) raises as usual.
See also
The .autoincrement directive is no longer implicitly enabled for a composite primary key column
References: #3216
[orm] [feature] Enhanced the new “raise” lazy loader strategy to also include a
“raise_on_sql” variant, available both via orm.relationship.lazy
as well as orm.raiseload()
. This variant only raises if the
lazy load would actually emit SQL, vs. raising if the lazy loader
mechanism is invoked at all.¶
References: #3812
[orm] [feature] The Query.group_by()
method now resets the group by collection
if an argument of None
is passed, in the same way that
Query.order_by()
has worked for a long time. Pull request
courtesy Iuri Diniz.¶
[orm] [bug] Fixed bug where joined eager loading would fail for a polymorphically- loaded mapper, where the polymorphic_on was set to an un-mapped expression such as a CASE expression.¶
This change is also backported to: 1.0.16
References: #3800
[orm] [bug] Fixed bug where the ArgumentError raised for an invalid bind
sent to a Session via Session.bind_mapper()
,
Session.bind_table()
,
or the constructor would fail to be correctly raised.¶
This change is also backported to: 1.0.16
References: #3798
[orm] [bug] Fixed bug in subquery eager loading where a subqueryload of an “of_type()” object linked to a second subqueryload of a plain mapped class, or a longer chain of several “of_type()” attributes, would fail to link the joins correctly.¶
This change is also backported to: 1.0.15
[orm] [bug] ORM attributes can now be assigned any object that is has a
__clause_element__()
attribute, which will result in inline
SQL the way any ClauseElement
class does. This covers other
mapped attributes not otherwise transformed by further expression
constructs.¶
References: #3802
[orm] [bug] Made an adjustment to the bug fix first introduced in [ticket:3431] that involves an object appearing in multiple contexts in a single result set, such that an eager loader that would set the related object value to be None will still fire off, thus satisfying the load of that attribute. Previously, the adjustment only honored a non-None value arriving for an eagerly loaded attribute in a secondary row.¶
References: #3811
[orm] [bug] Fixed bug in new SessionEvents.persistent_to_deleted()
event
where the target object could be garbage collected before the event
is fired off.¶
References: #3808
[orm] [bug] The primaryjoin of a relationship()
construct can now include
a bindparam()
object that includes a callable function to
generate values. Previously, the lazy loader strategy would
be incompatible with this use, and additionally would fail to correctly
detect if the “use_get” criteria should be used if the primary key
were involved with the bound parameter.¶
References: #3767
[orm] [bug] An UPDATE emitted from the ORM flush process can now accommodate a SQL expression element for a column within the primary key of an object, if the target database supports RETURNING in order to provide the new value, or if the PK value is set “to itself” for the purposes of bumping some other trigger / onupdate on the column.¶
References: #3801
[orm] [bug] Fixed bug where the “simple many-to-one” condition that allows lazy loading to use get() from identity map would fail to be invoked if the primaryjoin of the relationship had multiple clauses separated by AND which were not in the same order as that of the primary key columns being compared in each clause. This ordering difference occurs for a composite foreign key where the table-bound columns on the referencing side were not in the same order in the .c collection as the primary key columns on the referenced side....which in turn occurs a lot if one is using declarative mixins and/or declared_attr to set up columns.¶
References: #3788
[orm] [bug] An exception is raised when two @validates
decorators on a mapping
make use of the same name. Only one validator of a certain name
at a time is supported, there’s no mechanism to chain these together,
as the order of the validators at the level of function decorator
can’t be made deterministic.
References: #3776
[orm] [bug] Mapper errors raised during configure_mappers()
now explicitly
include the name of the originating mapper in the exception message
to help in those situations where the wrapped exception does not
itself include the source mapper. Pull request courtesy
John Perkins.¶
[orm] [change] Passing False to Query.order_by()
in order to cancel
all order by’s is deprecated; there is no longer any difference
between calling this method with False or with None.¶
[orm] [declarative] [change] Constructing a declarative base class that inherits from another class
will also inherit its docstring. This means
as_declarative()
acts more like a normal class
decorator.¶
[sql] [bug] Fixed bug in Table
where the internal method
_reset_exported()
would corrupt the state of the object. This
method is intended for selectable objects and is called by the ORM
in some cases; an erroneous mapper configuration would could lead the
ORM to call this on on a Table
object.¶
This change is also backported to: 1.0.15
References: #3755
[sql] [bug] Execution options can now be propagated from within a statement at compile time to the outermost statement, so that if an embedded element wants to set “autocommit” to be True for example, it can propagate this to the enclosing statement. Currently, this feature is enabled for a DML-oriented CTE embedded inside of a SELECT statement, e.g. INSERT/UPDATE/DELETE inside of SELECT.¶
References: #3805
[sql] [bug] A string sent as a column default via the
Column.server_default
parameter is now escaped for quotes.
References: #3809
[sql] [bug] [postgresql] Added compiler-level flags used by PostgreSQL to place additional parenthesis than would normally be generated by precedence rules around operations involving JSON, HSTORE indexing operators as well as within their operands since it has been observed that PostgreSQL’s precedence rules for at least the HSTORE indexing operator is not consistent between 9.4 and 9.5.¶
References: #3806
[sql] [bug] [mysql] The BaseException
exception class is now intercepted by the
exception-handling routines of Connection
, and includes
handling by the handle_error()
event. The Connection
is now invalidated by default in
the case of a system level exception that is not a subclass of
Exception
, including KeyboardInterrupt
and the greenlet
GreenletExit
class, to prevent further operations from occurring
upon a database connection that is in an unknown and possibly
corrupted state. The MySQL drivers are most targeted by this change
however the change is across all DBAPIs.
References: #3803
[sql] [bug] The “eq” and “ne” operators are no longer part of the list of
“associative” operators, while they remain considered to be
“commutative”. This allows an expression like (x == y) == z
to be maintained at the SQL level with parenthesis. Pull request
courtesy John Passaro.¶
References: #3799
[sql] [bug] Stringify of expression with unnamed Column
objects, as
occurs in lots of situations including ORM error reporting,
will now render the name in string context as “<name unknown>”
rather than raising a compile error.¶
References: #3789
[sql] [bug] Raise a more descriptive exception / message when ClauseElement
or non-SQLAlchemy objects that are not “executable” are erroneously
passed to .execute()
; a new exception ObjectNotExecutableError
is raised consistently in all cases.¶
References: #3786
[sql] [bug] [postgresql] [mysql] Fixed regression in JSON datatypes where the “literal processor” for a JSON index value would not be invoked. The native String and Integer datatypes are now called upon from within the JSONIndexType and JSONPathType. This is applied to the generic, PostgreSQL, and MySQL JSON types and also has a dependency on #3766.¶
References: #3765
[sql] [bug] Fixed bug where Index
would fail to extract columns from
compound SQL expressions if those SQL expressions were wrapped inside
of an ORM-style __clause_element__()
construct. This bug
exists in 1.0.x as well, however in 1.1 is more noticeable as
hybrid_property @expression now returns a wrapped element.¶
References: #3763
[postgresql] [bug] An adjustment to ON CONFLICT such that the “inserted_primary_key” logic is able to accommodate the case where there’s no INSERT or UPDATE and there’s no net change. The value comes out as None in this case, rather than failing on an exception.¶
References: #3813
[postgresql] [bug]
¶Fixed issue in new PG “on conflict” construct where columns including those of the “excluded” namespace would not be table-qualified in the WHERE clauses in the statement.
References: #3807
[mysql] [bug] Added support for parsing MySQL/Connector boolean and integer arguments within the URL query string: connection_timeout, connect_timeout, pool_size, get_warnings, raise_on_warnings, raw, consume_results, ssl_verify_cert, force_ipv6, pool_reset_session, compress, allow_local_infile, use_pure.¶
This change is also backported to: 1.0.15
References: #3787
[mysql] [bug] Fixed bug where the “literal_binds” flag would not be propagated to a CAST expression under MySQL.¶
References: #3766
[mssql] [bug] Changed the query used to get “default schema name”, from one that queries the database principals table to using the “schema_name()” function, as issues have been reported that the former system was unavailable on the Azure Data Warehouse edition. It is hoped that this will finally work across all SQL Server versions and authentication styles.¶
This change is also backported to: 1.0.16
References: #3810
[mssql] [bug] Updated the server version info scheme for pyodbc to use SQL Server SERVERPROPERTY(), rather than relying upon pyodbc.SQL_DBMS_VER, which continues to be unreliable particularly with FreeTDS.¶
This change is also backported to: 1.0.16
References: #3814
[mssql] [bug] Added error code 20017 “unexpected EOF from the server” to the list of disconnect exceptions that result in a connection pool reset. Pull request courtesy Ken Robbins.¶
This change is also backported to: 1.0.16
[bug] [orm.declarative] Fixed bug where setting up a single-table inh subclass of a joined-table subclass which included an extra column would corrupt the foreign keys collection of the mapped table, thereby interfering with the initialization of relationships.¶
This change is also backported to: 1.0.16
References: #3797
[sql] [bug] Fixed bug in new CTE feature for update/insert/delete stated as a CTE inside of an enclosing statement (typically SELECT) whereby oninsert and onupdate values weren’t called upon for the embedded statement.¶
References: #3745
[sql] [bug] Fixed bug in new CTE feature for update/insert/delete whereby
an anoymous (e.g. no name passed) CTE
construct around
the statement would fail.¶
References: #3744
[postgresql] [bug] Fixed bug whereby TypeDecorator
and Variant
types were not deeply inspected enough by the PostgreSQL dialect
to determine if SMALLSERIAL or BIGSERIAL needed to be rendered
rather than SERIAL.¶
This change is also backported to: 1.0.14
References: #3739
[oracle] [bug] Fixed bug in Select.with_for_update.of
, where the Oracle
“rownum” approach to LIMIT/OFFSET would fail to accommodate for the
expressions inside the “OF” clause, which must be stated at the topmost
level referring to expression within the subquery. The expressions are
now added to the subquery if needed.¶
This change is also backported to: 1.0.14
References: #3741
[feature] [ext] Added a “default” parameter to the new sqlalchemy.ext.indexable extension.¶
[bug] [ext] Fixed bug in sqlalchemy.ext.baked
where the unbaking of a
subquery eager loader query would fail due to a variable scoping
issue, when multiple subquery loaders were involved. Pull request
courtesy Mark Hahnenberg.¶
This change is also backported to: 1.0.15
References: #3743
[bug] [ext] sqlalchemy.ext.indexable will intercept IndexError as well as KeyError when raising as AttributeError.¶
[sql] [bug] Fixed issue in SQL math negation operator where the type of the expression would no longer be the numeric type of the original. This would cause issues where the type determined result set behaviors.¶
This change is also backported to: 1.0.14
References: #3735
[sql] [bug] Fixed bug whereby the __getstate__
/ __setstate__
methods for sqlalchemy.util.Properties were
non-working due to the transition in the 1.0 series to __slots__
.
The issue potentially impacted some third-party applications.
Pull request courtesy Pieter Mulder.¶
This change is also backported to: 1.0.14
References: #3728
[sql] [bug] The processing performed by the Boolean
datatype for backends
that only feature integer types has been made consistent between the
pure Python and C-extension versions, in that the C-extension version
will accept any integer value from the database as a boolean, not just
zero and one; additionally, non-boolean integer values being sent to
the database are coerced to exactly zero or one, instead of being
passed as the original integer value.
References: #3730
[sql] [bug] Rolled back the validation rules a bit in Enum
to allow
unknown string values to pass through, unless the flag
validate_string=True
is passed to the Enum; any other kind of object is
still of course rejected. While the immediate use
is to allow comparisons to enums with LIKE, the fact that this
use exists indicates there may be more unknown-string-comparison use
cases than we expected, which hints that perhaps there are some
unknown string-INSERT cases too.¶
References: #3725
[postgresql] [bug] [ext] Made a slight behavioral change in the sqlalchemy.ext.compiler
extension, whereby the existing compilation schemes for an established
construct would be removed if that construct was itself didn’t already
have its own dedicated __visit_name__
. This was a
rare occurrence in 1.0, however in 1.1 postgresql.ARRAY
subclasses sqltypes.ARRAY
and has this behavior.
As a result, setting up a compilation handler for another dialect
such as SQLite would render the main postgresql.ARRAY
object no longer compilable.¶
References: #3732
[mysql] [bug] Dialed back the “order the primary key columns per auto-increment”
described in No more generation of an implicit KEY for composite primary key w/ AUTO_INCREMENT a bit, so that if the
PrimaryKeyConstraint
is explicitly defined, the order
of columns is maintained exactly, allowing control of this behavior
when necessary.¶
References: #3726
[orm] [feature] [ext] A new ORM extension Indexable is added, which allows construction of Python attributes which refer to specific elements of “indexed” structures such as arrays and JSON fields. Pull request courtesy Jeong YunWon.
See also
[orm] [feature] Added new flag Session.bulk_insert_mappings.render_nulls
which allows an ORM bulk INSERT to occur with NULL values rendered;
this bypasses server side defaults, however allows all statements
to be formed with the same set of columns, allowing them to be
batched. Pull request courtesy Tobias Sauerwein.¶
[orm] [feature] Added new event AttributeEvents.init_scalar()
, as well
as a new example suite illustrating its use. This event can be used
to provide a Core-generated default value to a Python-side attribute
before the object is persisted.
References: #1311
[orm] [feature] Added AutomapBase.prepare.schema
to the
AutomapBase.prepare()
method, to indicate which schema
tables should be reflected from if not the default schema.
Pull request courtesy Josh Marlow.¶
References: pull request github:237
[orm] [feature] Added new parameter orm.mapper.passive_deletes
to
available mapper options. This allows a DELETE to proceed
for a joined-table inheritance mapping against the base table only,
while allowing for ON DELETE CASCADE to handle deleting the row
from the subclass tables.
References: #2349
[orm] [feature] Calling str() on a core SQL construct has been made more “friendly”, when the construct contains non-standard SQL elements such as RETURNING, array index operations, or dialect-specific or custom datatypes. A string is now returned in these cases rendering an approximation of the construct (typically the PostgreSQL-style version of it) rather than raising an error.
¶References: #3631
[orm] [feature] The str()
call for Query
will now take into account
the Engine
to which the Session
is bound, when
generating the string form of the SQL, so that the actual SQL
that would be emitted to the database is shown, if possible. Previously,
only the engine associated with the MetaData
to which the
mappings are associated would be used, if present. If
no bind can be located either on the Session
or on
the MetaData
to which the mappings are associated, then
the “default” dialect is used to render the SQL, as was the case
previously.
References: #3081
[orm] [feature] The SessionEvents
suite now includes events to allow
unambiguous tracking of all object lifecycle state transitions
in terms of the Session
itself, e.g. pending,
transient, persistent, detached. The state of the object
within each event is also defined.
See also
References: #2677
[orm] [feature] Added a new session lifecycle state deleted. This new state
represents an object that has been deleted from the persistent
state and will move to the detached state once the transaction
is committed. This resolves the long-standing issue that objects
which were deleted existed in a gray area between persistent and
detached. The InstanceState.persistent
accessor will
no longer report on a deleted object as persistent; the
InstanceState.deleted
accessor will instead be True for
these objects, until they become detached.
See also
References: #2677
[orm] [feature] Added new checks for the common error case of passing mapped classes or mapped instances into contexts where they are interpreted as SQL bound parameters; a new exception is raised for this.
¶References: #3321
[orm] [feature] Added new relationship loading strategy orm.raiseload()
(also
accessible via lazy='raise'
). This strategy behaves almost like
orm.noload()
but instead of returning None
it raises an
InvalidRequestError. Pull request courtesy Adrian Moennich.
References: #3512, pull request github:193
[orm] [bug] Fixed an issue where a many-to-one change of an object from one parent to another could work inconsistently when combined with an un-flushed modification of the foreign key attribute. The attribute move now considers the database-committed value of the foreign key in order to locate the “previous” parent of the object being moved. This allows events to fire off correctly including backref events. Previously, these events would not always fire. Applications which may have relied on the previously broken behavior may be affected.
¶References: #3708
[orm] [bug] Fixed bug where deferred columns would inadvertently be set up
for database load on the next object-wide unexpire, when the object
were merged into the session with session.merge(obj, load=False)
.¶
References: #3488
[orm] [bug] [mysql] Further continuing on the common MySQL exception case of
a savepoint being cancelled first covered in #2696,
the failure mode in which the Session
is placed when a
SAVEPOINT vanishes before rollback has been improved to allow the
Session
to still function outside of that savepoint.
It is assumed that the savepoint operation failed and was cancelled.
References: #3680
[orm] [bug] Fixed bug where a newly inserted instance that is rolled back would still potentially cause persistence conflicts on the next transaction, because the instance would not be checked that it was expired. This fix will resolve a large class of cases that erroneously cause the “New instance with identity X conflicts with persistent instance Y” error.
¶References: #3677
[orm] [bug] An improvement to the workings of Query.correlate()
such
that when a “polymorphic” entity is used which represents a straight
join of several tables, the statement will ensure that all the
tables within the join are part of what’s correlating.
References: #3662
[orm] [bug] Fixed bug which would cause an eagerly loaded many-to-one attribute to not be loaded, if the joined eager load were from a row where the same entity were present multiple times, some calling for the attribute to be eagerly loaded and others not. The logic here is revised to take in the attribute even though a different loader path has handled the parent entity already.
¶References: #3431
[orm] [bug] A refinement to the logic which adds columns to the resulting SQL when
Query.distinct()
is combined with Query.order_by()
such
that columns which are already present will not be added
a second time, even if they are labeled with a different name.
Regardless of this change, the extra columns added to the SQL have
never been returned in the final result, so this change only impacts
the string form of the statement as well as its behavior when used in
a Core execution context. Additionally, columns are no longer added
when the DISTINCT ON format is used, provided the query is not
wrapped inside a subquery due to joined eager loading.
References: #3641
[orm] [bug] Fixed issue where two same-named relationships that refer to a base class and a concrete-inherited subclass would raise an error if those relationships were set up using “backref”, while setting up the identical configuration using relationship() instead with the conflicting names would succeed, as is allowed in the case of a concrete mapping.
See also
Same-named backrefs will not raise an error when applied to concrete inheritance subclasses
References: #3630
[orm] [bug] The Session.merge()
method now tracks pending objects by
primary key before emitting an INSERT, and merges distinct objects with
duplicate primary keys together as they are encountered, which is
essentially semi-deterministic at best. This behavior
matches what happens already with persistent objects.
References: #3601
[orm] [bug] Fixed bug where the “single table inheritance” criteria would be added onto the end of a query in some inappropriate situations, such as when querying from an exists() of a single-inheritance subclass.
¶References: #3582
[orm] [bug] Added a new type-level modifier TypeEngine.evaluates_none()
which indicates to the ORM that a positive set of None should be
persisted as the value NULL, instead of omitting the column from
the INSERT statement. This feature is used both as part of the
implementation for #3514 as well as a standalone feature
available on any type.
References: #3250
[orm] [bug] Internal calls to “bookkeeping” functions within
Session.bulk_save_objects()
and related bulk methods have
been scaled back to the extent that this functionality is not
currently used, e.g. checks for column default values to be
fetched after an INSERT or UPDATE statement.¶
References: #3526
[orm] [bug] [postgresql] Additional fixes have been made regarding the value of None
in conjunction with the PostgreSQL postgresql.JSON
type. When
the JSON.none_as_null
flag is left at its default
value of False
, the ORM will now correctly insert the JSON
“‘null’” string into the column whenever the value on the ORM
object is set to the value None
or when the value None
is used with Session.bulk_insert_mappings()
,
including if the column has a default or server default on it.
See also
JSON “null” is inserted as expected with ORM operations, omitted when not present
New options allowing explicit persistence of NULL over a default
References: #3514
[orm] [change] The Mapper.order_by
parameter is deprecated.
This is an old parameter no longer relevant to how SQLAlchemy
works, once the Query object was introduced. By deprecating it
we establish that we aren’t supporting non-working use cases
and that we encourage applications to move off of the use of this
parameter.
See also
References: #3394
[orm] [change] The Session.weak_identity_map
parameter is deprecated.
See the new recipe at Session Referencing Behavior for
an event-based approach to maintaining strong identity map behavior.
See also
References: #2677
[engine] [feature] Added connection pool events ConnectionEvents.close()
,
ConnectionEvents.detach()
,
ConnectionEvents.close_detached()
.¶
[engine] [feature] All string formatting of bound parameter sets and result rows for
logging, exception, and repr()
purposes now truncate very large
scalar values within each collection, including an
“N characters truncated”
notation, similar to how the display for large multiple-parameter sets
are themselves truncated.
References: #2837
[engine] [feature] Multi-tenancy schema translation for Table
objects is added.
This supports the use case of an application that uses the same set of
Table
objects in many schemas, such as schema-per-user.
A new execution option
Connection.execution_options.schema_translate_map
is
added.
References: #2685
[engine] [feature] Added a new entrypoint system to the engine to allow “plugins” to
be stated in the query string for a URL. Custom plugins can
be written which will be given the chance up front to alter and/or
consume the engine’s URL and keyword arguments, and then at engine
create time will be given the engine itself to allow additional
modifications or event registration. Plugins are written as a
subclass of CreateEnginePlugin
; see that class for
details.¶
References: #3536
[sql] [feature] Added TABLESAMPLE support via the new FromClause.tablesample()
method and standalone function. Pull request courtesy Ilja Everilä.
See also
References: #3718
[sql] [feature] Added support for ranges in window functions, using the
expression.over.range_
and
expression.over.rows
parameters.
References: #3049
[sql] [feature] Implemented reflection of CHECK constraints for SQLite and PostgreSQL.
This is available via the new inspector method
Inspector.get_check_constraints()
as well as when reflecting
Table
objects in the form of CheckConstraint
objects present in the constraints collection. Pull request courtesy
Alex Grönholm.¶
References: pull request bitbucket:80
[sql] [feature] New ColumnOperators.is_distinct_from()
and
ColumnOperators.isnot_distinct_from()
operators; pull request
courtesy Sebastian Bank.
[sql] [feature] Added a hook in DDLCompiler.visit_create_table()
called
DDLCompiler.create_table_suffix()
, allowing custom dialects
to add keywords after the “CREATE TABLE” clause. Pull request
courtesy Mark Sandan.¶
References: pull request github:275
[sql] [feature] Negative integer indexes are now accommodated by rows
returned from a ResultProxy
. Pull request courtesy
Emanuele Gaifas.
References: pull request github:231
[sql] [feature] Added Select.lateral()
and related constructs to allow
for the SQL standard LATERAL keyword, currently only supported
by PostgreSQL.
See also
References: #2857
[sql] [feature] Added support for rendering “FULL OUTER JOIN” to both Core and ORM. Pull request courtesy Stefan Urbanek.
¶References: #1957, pull request github:209
[sql] [feature] CTE functionality has been expanded to support all DML, allowing INSERT, UPDATE, and DELETE statements to both specify their own WITH clause, as well as for these statements themselves to be CTE expressions when they include a RETURNING clause.
¶References: #2551
[sql] [feature] Added support for PEP-435-style enumerated classes, namely
Python 3’s enum.Enum
class but also including compatible
enumeration libraries, to the types.Enum
datatype.
The types.Enum
datatype now also performs in-Python validation
of incoming values, and adds an option to forego creating the
CHECK constraint Enum.create_constraint
.
Pull request courtesy Alex Grönholm.
[sql] [feature] A deep improvement to the recently added TextClause.columns()
method, and its interaction with result-row processing, now allows
the columns passed to the method to be positionally matched with the
result columns in the statement, rather than matching on name alone.
The advantage to this includes that when linking a textual SQL statement
to an ORM or Core table model, no system of labeling or de-duping of
common column names needs to occur, which also means there’s no need
to worry about how label names match to ORM columns and so-forth. In
addition, the ResultProxy
has been further enhanced to
map column and string keys to a row with greater precision in some
cases.
See also
ResultSet column matching enhancements; positional column setup for textual SQL - feature overview
TextClause.columns() will match columns positionally, not by name, when passed positionally - backwards compatibility remarks
References: #3501
[sql] [feature] Added a new type to core types.JSON
. This is the
base of the PostgreSQL postgresql.JSON
type as well as that
of the new mysql.JSON
type, so that a PG/MySQL-agnostic
JSON column may be used. The type features basic index and path
searching support.
See also
References: #3619
[sql] [feature] Added support for “set-aggregate” functions of the form
<function> WITHIN GROUP (ORDER BY <criteria>)
, using the
method FunctionElement.within_group()
. A series of common
set-aggregate functions with return types derived from the set have
been added. This includes functions like percentile_cont
,
dense_rank
and others.
References: #1370
[sql] [feature] [postgresql] Added support for the SQL-standard function array_agg
,
which automatically returns an postgresql.ARRAY
of the correct type
and supports index / slice operations, as well as
postgresql.array_agg()
, which returns a postgresql.ARRAY
with additional comparison features. As arrays are only
supported on PostgreSQL at the moment, only actually works on
PostgreSQL. Also added a new construct
postgresql.aggregate_order_by
in support of PG’s
“ORDER BY” extension.
References: #3132
[sql] [feature] Added a new type to core types.ARRAY
. This is the
base of the PostgreSQL postgresql.ARRAY
type, and is now part of Core
to begin supporting various SQL-standard array-supporting features
including some functions and eventual support for native arrays
on other databases that have an “array” concept, such as DB2 or Oracle.
Additionally, new operators expression.any_()
and
expression.all_()
have been added. These support not just
array constructs on PostgreSQL, but also subqueries that are usable
on MySQL (but sadly not on PostgreSQL).
References: #3516
[sql] [bug] FromClause.count()
is deprecated. This function makes use of
an arbitrary column in the table and is not reliable; for Core use,
func.count()
should be preferred.¶
References: #3724
[sql] [bug] Fixed an assertion that would raise somewhat inappropriately
if a Index
were associated with a Column
that
is associated with a lower-case-t TableClause
; the
association should be ignored for the purposes of associating
the index with a Table
.¶
References: #3616
[sql] [bug] The type_coerce()
construct is now a fully fledged Core
expression element which is late-evaluated at compile time. Previously,
the function was only a conversion function which would handle different
expression inputs by returning either a Label
of a column-oriented
expression or a copy of a given BindParameter
object,
which in particular prevented the operation from being logically
maintained when an ORM-level expression transformation would convert
a column to a bound parameter (e.g. for lazy loading).
References: #3531
[sql] [bug] The TypeDecorator
type extender will now work in conjunction
with a SchemaType
implementation, typically Enum
or Boolean
with regards to ensuring that the per-table
events are propagated from the implementation type to the outer type.
These events are used
to ensure that the constraints or PostgreSQL types (e.g. ENUM)
are correctly created (and possibly dropped) along with the parent
table.
References: #2919
[sql] [bug] The behavior of the union()
construct and related constructs
such as Query.union()
now handle the case where the embedded
SELECT statements need to be parenthesized due to the fact that they
include LIMIT, OFFSET and/or ORDER BY. These queries do not work
on SQLite, and will fail on that backend as they did before, but
should now work on all other backends.
See also
A UNION or similar of SELECTs with LIMIT/OFFSET/ORDER BY now parenthesizes the embedded selects
References: #2528
[sql] [mysql] [change] The system by which a Column
considers itself to be an
“auto increment” column has been changed, such that autoincrement
is no longer implicitly enabled for a Table
that has a
composite primary key. In order to accommodate being able to enable
autoincrement for a composite PK member column while at the same time
maintaining SQLAlchemy’s long standing behavior of enabling
implicit autoincrement for a single integer primary key, a third
state has been added to the Column.autoincrement
parameter
"auto"
, which is now the default.
See also
The .autoincrement directive is no longer implicitly enabled for a composite primary key column
No more generation of an implicit KEY for composite primary key w/ AUTO_INCREMENT
References: #3216
[schema] [enhancement] The default generation functions passed to Column
objects
are now run through “update_wrapper”, or an equivalent function
if a callable non-function is passed, so that introspection tools
preserve the name and docstring of the wrapped function. Pull
request courtesy hsum.¶
References: pull request github:204
[postgresql] [feature] Added support for PostgreSQL’s INSERT..ON CONFLICT using a new
PostgreSQL-specific postgresql.dml.Insert
object.
Pull request and extensive efforts here by Robin Thomas.
References: #3529
[postgresql] [feature] The DDL for DROP INDEX will emit “CONCURRENTLY” if the
postgresql_concurrently
flag is set upon the
Index
and if the database in use is detected as
PostgreSQL version 9.2 or greater. For CREATE INDEX, database
version detection is also added which will omit the clause if
PG version is less than 8.2. Pull request courtesy Iuri de Silvio.¶
References: pull request bitbucket:84
[postgresql] [feature] Added new parameter PGInspector.get_view_names.include
,
allowing specification for what kinds of views should be returned.
Currently “plain” and “materialized” views are included. Pull
request courtesy Sebastian Bank.¶
References: #3588
[postgresql] [feature] Added postgresql_tablespace
as an argument to Index
to allow specification of TABLESPACE for an index in PostgreSQL.
Complements the same-named parameter on Table
. Pull
request courtesy Benjamin Bertrand.¶
References: #3720
[postgresql] [feature] Added new parameter
GenerativeSelect.with_for_update.key_share
, which
will render the FOR NO KEY UPDATE
version of FOR UPDATE
and FOR KEY SHARE
instead of FOR SHARE
on the PostgreSQL backend. Pull request courtesy Sergey Skopin.¶
References: pull request github:297
[postgresql] [feature] [oracle] Added new parameter
GenerativeSelect.with_for_update.skip_locked
, which
will render the SKIP LOCKED
phrase for a FOR UPDATE
or
FOR SHARE
lock on the PostgreSQL and Oracle backends. Pull
request courtesy Jack Zhou.¶
References: pull request bitbucket:86
[postgresql] [feature] Added a new dialect for the PyGreSQL PostgreSQL dialect. Thanks to Christoph Zwerschke and Kaolin Imago Fire for their efforts.¶
[postgresql] [feature] Added a new constant postgresql.JSON.NULL
, indicating
that the JSON NULL value should be used for a value
regardless of other settings.
See also
References: #3514
[postgresql] [bug] Added support for reflecting the source of materialized views
to the PostgreSQL version of the Inspector.get_view_definition()
method.¶
References: #3587
[postgresql] [bug] The use of a postgresql.ARRAY
object that refers
to a types.Enum
or postgresql.ENUM
subtype
will now emit the expected “CREATE TYPE” and “DROP TYPE” DDL when
the type is used within a “CREATE TABLE” or “DROP TABLE”.
References: #2729
[postgresql] [bug] The “hashable” flag on special datatypes such as postgresql.ARRAY
,
postgresql.JSON
and postgresql.HSTORE
is now
set to False, which allows these types to be fetchable in ORM
queries that include entities within the row.
See also
References: #3499
[postgresql] [bug] The PostgreSQL postgresql.ARRAY
type now supports multidimensional
indexed access, e.g. expressions such as somecol[5][6]
without
any need for explicit casts or type coercions, provided
that the postgresql.ARRAY.dimensions
parameter is set to the
desired number of dimensions.
References: #3487
[postgresql] [bug] The return type for the postgresql.JSON
and postgresql.JSONB
when using indexed access has been fixed to work like PostgreSQL itself,
and returns an expression that itself is of type postgresql.JSON
or postgresql.JSONB
. Previously, the accessor would return
NullType
which disallowed subsequent JSON-like operators to be
used.
References: #3503
[postgresql] [bug] The postgresql.JSON
, postgresql.JSONB
and
postgresql.HSTORE
datatypes now allow full control over the
return type from an indexed textual access operation, either column[someindex].astext
for a JSON type or column[someindex]
for an HSTORE type,
via the postgresql.JSON.astext_type
and
postgresql.HSTORE.text_type
parameters.
References: #3503
[postgresql] [bug] The postgresql.JSON.Comparator.astext
modifier no longer
calls upon ColumnElement.cast()
implicitly, as PG’s JSON/JSONB
types allow cross-casting between each other as well. Code that
makes use of ColumnElement.cast()
on JSON indexed access,
e.g. col[someindex].cast(Integer)
, will need to be changed
to call postgresql.JSON.Comparator.astext
explicitly.
References: #3503
[postgresql] [change] The sqlalchemy.dialects.postgres
module, long deprecated, is
removed; this has emitted a warning for many years and projects
should be calling upon sqlalchemy.dialects.postgresql
.
Engine URLs of the form postgres://
will still continue to function,
however.¶
[mysql] [feature] Added support for “autocommit” on MySQL drivers, via the AUTOCOMMIT isolation level setting. Pull request courtesy Roman Podoliaka.
¶References: #3332
[mysql] [feature] Added mysql.JSON
for MySQL 5.7. The JSON type provides
persistence of JSON values in MySQL as well as basic operator support
of “getitem” and “getpath”, making use of the JSON_EXTRACT
function in order to refer to individual paths in a JSON structure.
See also
References: #3547
[mysql] [change] The MySQL dialect no longer generates an extra “KEY” directive when generating CREATE TABLE DDL for a table using InnoDB with a composite primary key with AUTO_INCREMENT on a column that isn’t the first column; to overcome InnoDB’s limitation here, the PRIMARY KEY constraint is now generated with the AUTO_INCREMENT column placed first in the list of columns.
See also
No more generation of an implicit KEY for composite primary key w/ AUTO_INCREMENT
The .autoincrement directive is no longer implicitly enabled for a composite primary key column
References: #3216
[sqlite] [feature] The SQLite dialect now reflects ON UPDATE and ON DELETE phrases within foreign key constraints. Pull request courtesy Michal Petrucha.¶
References: pull request github:244
[sqlite] [feature] The SQLite dialect now reflects the names of primary key constraints. Pull request courtesy Diana Clarke.
¶References: #3629
[sqlite] [bug] The workaround for right-nested joins on SQLite, where they are rewritten as subqueries in order to work around SQLite’s lack of support for this syntax, is lifted when SQLite version 3.7.16 or greater is detected.
¶References: #3634
[sqlite] [bug] The workaround for SQLite’s unexpected delivery of column names as
tablename.columnname
for some kinds of queries is now disabled
when SQLite version 3.10.0 or greater is detected.
References: #3633
[sqlite] [change] Added support to the SQLite dialect for the
Inspector.get_schema_names()
method to work with SQLite;
pull request courtesy Brian Van Klaveren. Also repaired support
for creation of indexes with schemas as well as reflection of
foreign key constraints in schema-bound tables.
See also
References: pull request github:198
[mssql] [feature] The mssql_clustered
flag available on UniqueConstraint
,
PrimaryKeyConstraint
, Index
now defaults to
None
, and can be set to False which will render the NONCLUSTERED
keyword in particular for a primary key, allowing a different index to
be used as “clustered”. Pull request courtesy Saulius Žemaitaitis.¶
[mssql] [feature] Added basic isolation level support to the SQL Server dialects
via create_engine.isolation_level
and
Connection.execution_options.isolation_level
parameters.
References: #3534
[mssql] [bug] Adjustments to the mxODBC dialect to make use of the BinaryNull
symbol when appropriate in conjunction with the VARBINARY
data type. Pull request courtesy Sheila Allen.¶
References: pull request bitbucket:58
[mssql] [bug] Fixed issue where the SQL Server dialect would reflect a string-
or other variable-length column type with unbounded length
by assigning the token "max"
to the
length attribute of the string. While using the "max"
token
explicitly is supported by the SQL Server dialect, it isn’t part
of the normal contract of the base string types, and instead the
length should just be left as None. The dialect now assigns the
length to None on reflection of the type so that the type behaves
normally in other contexts.
References: #3504
[mssql] [change] The legacy_schema_aliasing
flag, introduced in version 1.0.5
as part of #3424 to allow disabling of the MSSQL dialect’s
attempts to create aliases for schema-qualified tables, now defaults
to False; the old behavior is now disabled unless explicitly turned on.
References: #3434
[feature] [ext] Added MutableSet
and MutableList
helper classes
to the Mutation Tracking extension. Pull request courtesy
Jeong YunWon.¶
References: #3297
[bug] [ext] The docstring specified on a hybrid property or method is now honored at the class level, allowing it to work with tools like Sphinx autodoc. The mechanics here necessarily involve some wrapping of expressions to occur for hybrid properties, which may cause them to appear differently using introspection.
¶References: #3653
[bug] [sybase] The unsupported Sybase dialect now raises NotImplementedError
when attempting to compile a query that includes “offset”; Sybase
has no straightforward “offset” feature.¶
References: #2278