What’s New in SQLAlchemy 1.2?

About this Document

This document describes changes between SQLAlchemy version 1.1and SQLAlchemy version 1.2.

Introduction

This guide introduces what’s new in SQLAlchemy version 1.2,and also documents changes which affect users migratingtheir applications from the 1.1 series of SQLAlchemy to 1.2.

Please carefully review the sections on behavioral changes forpotentially backwards-incompatible changes in behavior.

Platform Support

Targeting Python 2.7 and Up

SQLAlchemy 1.2 now moves the minimum Python version to 2.7, no longersupporting 2.6. New language features are expected to be mergedinto the 1.2 series that were not supported in Python 2.6. For Python 3 support,SQLAlchemy is currently tested on versions 3.5 and 3.6.

New Features and Improvements - ORM

“Baked” loading now the default for lazy loads

The sqlalchemy.ext.baked extension, first introduced in the 1.0 series,allows for the construction of a so-called BakedQuery object,which is an object that generates a Query object in conjunctionwith a cache key representing the structure of the query; this cache keyis then linked to the resulting string SQL statement so that subsequent useof another BakedQuery with the same structure will bypass all theoverhead of building the Query object, building the coreselect() object within, as well as the compilation of the select()into a string, cutting out well the majority of function call overhead normallyassociated with constructing and emitting an ORM Query object.

The BakedQuery is now used by default by the ORM when it generatesa “lazy” query for the lazy load of a relationship() construct, e.g.that of the default lazy="select" relationship loader strategy. Thiswill allow for a significant reduction in function calls within the scopeof an application’s use of lazy load queries to load collections and relatedobjects. Previously, this feature was availablein 1.0 and 1.1 through the use of a global API method or by using thebaked_select strategy, it’s now the only implementation for this behavior.The feature has also been improved such that the caching can still take placefor objects that have additional loader options in effect subsequentto the lazy load.

The caching behavior can be disabled on a per-relationship basis using therelationship.bake_queries flag, which is available forvery unusual cases, such as a relationship that uses a customQuery implementation that’s not compatible with caching.

#3954

New “selectin” eager loading, loads all collections at once using IN

A new eager loader called “selectin” loading is added, which in many waysis similar to “subquery” loading, however produces a simpler SQL statementthat is cacheable as well as more efficient.

Given a query as below:

q = session.query(User).\
    filter(User.name.like('%ed%')).\
    options(subqueryload(User.addresses))

The SQL produced would be the query against User followed by thesubqueryload for User.addresses (note the parameters are also listed):

SELECT users.id AS users_id, users.name AS users_name
FROM users
WHERE users.name LIKE ?
('%ed%',)
 
SELECT addresses.id AS addresses_id,
       addresses.user_id AS addresses_user_id,
       addresses.email_address AS addresses_email_address,
       anon_1.users_id AS anon_1_users_id
FROM (SELECT users.id AS users_id
FROM users
WHERE users.name LIKE ?) AS anon_1
JOIN addresses ON anon_1.users_id = addresses.user_id
ORDER BY anon_1.users_id
('%ed%',)

With “selectin” loading, we instead get a SELECT that refers to theactual primary key values loaded in the parent query:

q = session.query(User).\
    filter(User.name.like('%ed%')).\
    options(selectinload(User.addresses))

Produces:

SELECT users.id AS users_id, users.name AS users_name
FROM users
WHERE users.name LIKE ?
('%ed%',)
 
SELECT users_1.id AS users_1_id,
       addresses.id AS addresses_id,
       addresses.user_id AS addresses_user_id,
       addresses.email_address AS addresses_email_address
FROM users AS users_1
JOIN addresses ON users_1.id = addresses.user_id
WHERE users_1.id IN (?, ?)
ORDER BY users_1.id
(1, 3)

The above SELECT statement includes these advantages:

• It doesn’t use a subquery, just an INNER JOIN, meaning it will performmuch better on a database like MySQL that doesn’t like subqueries

• Its structure is independent of the original query; in conjunction with thenew expanding IN parameter system we can in most casesuse the “baked” query to cache the string SQL, reducing per-query overheadsignificantly

• Because the query only fetches for a given list of primary key identifiers,“selectin” loading is potentially compatible with Query.yield_per() tooperate on chunks of a SELECT result at a time, provided that thedatabase driver allows for multiple, simultaneous cursors (SQLite, PostgreSQL;not MySQL drivers or SQL Server ODBC drivers). Neither joined eagerloading nor subquery eager loading are compatible with Query.yield_per().

The disadvantages of selectin eager loading are potentially large SQLqueries, with large lists of IN parameters. The list of IN parameters themselvesare chunked in groups of 500, so a result set of more than 500 lead objectswill have more additional “SELECT IN” queries following. Also, supportfor composite primary keys depends on the database’s ability to usetuples with IN, e.g.(table.column_one, table_column_two) IN ((?, ?), (?, ?) (?, ?)).Currently, PostgreSQL and MySQL are known to be compatible with this syntax,SQLite is not.

See also

Select IN loading

#3944

“selectin” polymorphic loading, loads subclasses using separate IN queries

Along similar lines as the “selectin” relationship loading feature justdescribed at New “selectin” eager loading, loads all collections at once using IN is “selectin” polymorphic loading. Thisis a polymorphic loading feature tailored primarily towards joined eagerloading that allows the loading of the base entity to proceed with a simpleSELECT statement, but then the attributes of the additional subclassesare loaded with additional SELECT statements:

from sqlalchemy.orm import selectin_polymorphic
 
query = session.query(Employee).options(
    selectin_polymorphic(Employee, [Manager, Engineer])
)
 
query.all()
SELECT
    employee.id AS employee_id,
    employee.name AS employee_name,
    employee.type AS employee_type
FROM employee
()
SELECT
    engineer.id AS engineer_id,
    employee.id AS employee_id,
    employee.type AS employee_type,
    engineer.engineer_name AS engineer_engineer_name
FROM employee JOIN engineer ON employee.id = engineer.id
WHERE employee.id IN (?, ?) ORDER BY employee.id
(1, 2)
SELECT
    manager.id AS manager_id,
    employee.id AS employee_id,
    employee.type AS employee_type,
    manager.manager_name AS manager_manager_name
FROM employee JOIN manager ON employee.id = manager.id
WHERE employee.id IN (?) ORDER BY employee.id
(3,)

See also

Polymorphic Selectin Loading

#3948

ORM attributes that can receive ad-hoc SQL expressions

A new ORM attribute type orm.query_expression() is added whichis similar to orm.deferred(), except its SQL expressionis determined at query time using a new option orm.with_expression();if not specified, the attribute defaults to None:

from sqlalchemy.orm import query_expression
from sqlalchemy.orm import with_expression
 
class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    x = Column(Integer)
    y = Column(Integer)
 
    # will be None normally...
    expr = query_expression()
 
# but let's give it x + y
a1 = session.query(A).options(
    with_expression(A.expr, A.x + A.y)).first()
print(a1.expr)

See also

Query-time SQL expressions as mapped attributes

#3058

ORM Support of multiple-table deletes

The ORM Query.delete() method supports multiple-table criteriafor DELETE, as introduced in Multiple-table criteria support for DELETE. The feature worksin the same manner as multiple-table criteria for UPDATE, firstintroduced in 0.8 and described at Query.update() supports UPDATE..FROM.

Below, we emit a DELETE against SomeEntity, addinga FROM clause (or equivalent, depending on backend)against SomeOtherEntity:

query(SomeEntity).\
    filter(SomeEntity.id==SomeOtherEntity.id).\
    filter(SomeOtherEntity.foo=='bar').\
    delete()

See also

Multiple-table criteria support for DELETE

#959

Support for bulk updates of hybrids, composites

Both hybrid attributes (e.g. sqlalchemy.ext.hybrid) as well as compositeattributes (Composite Column Types) now support being used in theSET clause of an UPDATE statement when using Query.update().

For hybrids, simple expressions can be used directly, or the new decoratorhybrid_property.update_expression() can be used to break a valueinto multiple columns/expressions:

class Person(Base):
    # ...
 
    first_name = Column(String(10))
    last_name = Column(String(10))
 
    @hybrid.hybrid_property
    def name(self):
        return self.first_name + ' ' + self.last_name
 
    @name.expression
    def name(cls):
        return func.concat(cls.first_name, ' ', cls.last_name)
 
    @name.update_expression
    def name(cls, value):
        f, l = value.split(' ', 1)
        return [(cls.first_name, f), (cls.last_name, l)]

Above, an UPDATE can be rendered using:

session.query(Person).filter(Person.id == 5).update(
    {Person.name: "Dr. No"})

Similar functionality is available for composites, where composite valueswill be broken out into their individual columns for bulk UPDATE:

session.query(Vertex).update({Edge.start: Point(3, 4)})

See also

Allowing Bulk ORM Update

Hybrid attributes support reuse among subclasses, redefinition of @getter

The sqlalchemy.ext.hybrid.hybrid_property class now supportscalling mutators like @setter, @expression etc. multiple timesacross subclasses, and now provides a @getter mutator, so thata particular hybrid can be repurposed across subclasses or otherclasses. This now is similar to the behavior of @property in standardPython:

class FirstNameOnly(Base):
    # ...
 
    first_name = Column(String)
 
    @hybrid_property
    def name(self):
        return self.first_name
 
    @name.setter
    def name(self, value):
        self.first_name = value
 
class FirstNameLastName(FirstNameOnly):
    # ...
 
    last_name = Column(String)
 
    @FirstNameOnly.name.getter
    def name(self):
        return self.first_name + ' ' + self.last_name
 
    @name.setter
    def name(self, value):
        self.first_name, self.last_name = value.split(' ', maxsplit=1)
 
    @name.expression
    def name(cls):
        return func.concat(cls.first_name, ' ', cls.last_name)

Above, the FirstNameOnly.name hybrid is referenced by theFirstNameLastName subclass in order to repurpose it specifically to thenew subclass. This is achieved by copying the hybrid object to a new onewithin each call to @getter, @setter, as well as in all othermutator methods like @expression, leaving the previous hybrid’s definitionintact. Previously, methods like @setter would modify the existinghybrid in-place, interfering with the definition on the superclass.

Note

Be sure to read the documentation at Reusing Hybrid Properties across Subclassesfor important notes regarding how to overridehybrid_property.expression()and hybrid_property.comparator(), as a special qualifierhybrid_property.overrides may be necessary to avoid nameconflicts with QueryableAttribute in some cases.

Note

This change in @hybrid_property implies that when adding setters andother state to a @hybrid_property, the methods must retain the nameof the original hybrid, else the new hybrid with the additional state willbe present on the class as the non-matching name. This is the same behavioras that of the @property construct that is part of standard Python:

class FirstNameOnly(Base):
    @hybrid_property
    def name(self):
        return self.first_name
 
    # WRONG - will raise AttributeError: can't set attribute when
    # assigning to .name
    @name.setter
    def _set_name(self, value):
        self.first_name = value
 
class FirstNameOnly(Base):
    @hybrid_property
    def name(self):
        return self.first_name
 
    # CORRECT - note regular Python @property works the same way
    @name.setter
    def name(self, value):
        self.first_name = value

#3911

#3912

New bulk_replace event

To suit the validation use case described in A @validates method receives all values on bulk-collection set before comparison,a new AttributeEvents.bulk_replace() method is added, which iscalled in conjunction with the AttributeEvents.append() andAttributeEvents.remove() events. “bulk_replace” is called before“append” and “remove” so that the collection can be modified ahead of comparisonto the existing collection. After that, individual itemsare appended to a new target collection, firing off the “append”event for items new to the collection, as was the previous behavior.Below illustrates both “bulk_replace” and“append” at the same time, including that “append” will receive an objectalready handled by “bulk_replace” if collection assignment is used.A new symbol OP_BULK_REPLACE may be used to determineif this “append” event is the second part of a bulk replace:

from sqlalchemy.orm.attributes import OP_BULK_REPLACE
 
@event.listens_for(SomeObject.collection, "bulk_replace")
def process_collection(target, values, initiator):
    values[:] = [_make_value(value) for value in values]
 
@event.listens_for(SomeObject.collection, "append", retval=True)
def process_collection(target, value, initiator):
    # make sure bulk_replace didn't already do it
    if initiator is None or initiator.op is not OP_BULK_REPLACE:
        return _make_value(value)
    else:
        return value

#3896

New “modified” event handler for sqlalchemy.ext.mutable

A new event handler AttributeEvents.modified() is added, which istriggered corresponding to calls to the attributes.flag_modified()method, which is normally called from the sqlalchemy.ext.mutableextension:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.mutable import MutableDict
from sqlalchemy import event
 
Base = declarative_base()
 
class MyDataClass(Base):
    __tablename__ = 'my_data'
    id = Column(Integer, primary_key=True)
    data = Column(MutableDict.as_mutable(JSONEncodedDict))
 
@event.listens_for(MyDataClass.data, "modified")
def modified_json(instance):
    print("json value modified:", instance.data)

Above, the event handler will be triggered when an in-place change to the.data dictionary occurs.

#3303

Added “for update” arguments to Session.refresh

Added new argument Session.refresh.with_for_update to theSession.refresh() method. When the Query.with_lockmode()method were deprecated in favor of Query.with_for_update(),the Session.refresh() method was never updated to reflectthe new option:

session.refresh(some_object, with_for_update=True)

The Session.refresh.with_for_update argument accepts a dictionaryof options that will be passed as the same arguments which are sent toQuery.with_for_update():

session.refresh(some_objects, with_for_update={"read": True})

The new parameter supersedes the Session.refresh.lockmodeparameter.

#3991

In-place mutation operators work for MutableSet, MutableList

Implemented the in-place mutation operators ior, iand,ixor and isub for mutable.MutableSet and iaddfor mutable.MutableList. While thesemethods would successfully update the collection previously, they wouldnot correctly fire off change events. The operators mutate the collectionas before but additionally emit the correct change event so that the changebecomes part of the next flush process:

model = session.query(MyModel).first()
model.json_set &= {1, 3}

#3853

AssociationProxy any(), has(), contains() work with chained association proxies

The AssociationProxy.any(), AssociationProxy.has()and AssociationProxy.contains() comparison methods now supportlinkage to an attribute that isitself also an AssociationProxy, recursively. Below, A.b_valuesis an association proxy that links to AtoB.bvalue, which isitself an association proxy onto B:

class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
 
    b_values = association_proxy("atob", "b_value")
    c_values = association_proxy("atob", "c_value")
 
 
class B(Base):
    __tablename__ = 'b'
    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey('a.id'))
    value = Column(String)
 
    c = relationship("C")
 
 
class C(Base):
    __tablename__ = 'c'
    id = Column(Integer, primary_key=True)
    b_id = Column(ForeignKey('b.id'))
    value = Column(String)
 
 
class AtoB(Base):
    __tablename__ = 'atob'
 
    a_id = Column(ForeignKey('a.id'), primary_key=True)
    b_id = Column(ForeignKey('b.id'), primary_key=True)
 
    a = relationship("A", backref="atob")
    b = relationship("B", backref="atob")
 
    b_value = association_proxy("b", "value")
    c_value = association_proxy("b", "c")

We can query on A.b_values using AssociationProxy.contains() toquery across the two proxies A.b_values, AtoB.b_value:

>>> s.query(A).filter(A.b_values.contains('hi')).all()
SELECT a.id AS a_id
FROM a
WHERE EXISTS (SELECT 1
FROM atob
WHERE a.id = atob.a_id AND (EXISTS (SELECT 1
FROM b
WHERE b.id = atob.b_id AND b.value = :value_1)))

Similarly, we can query on A.c_values using AssociationProxy.any()to query across the two proxies A.c_values, AtoB.c_value:

>>> s.query(A).filter(A.c_values.any(value='x')).all()
SELECT a.id AS a_id
FROM a
WHERE EXISTS (SELECT 1
FROM atob
WHERE a.id = atob.a_id AND (EXISTS (SELECT 1
FROM b
WHERE b.id = atob.b_id AND (EXISTS (SELECT 1
FROM c
WHERE b.id = c.b_id AND c.value = :value_1)))))

#3769

Identity key enhancements to support sharding

The identity key structure used by the ORM now contains an additionalmember, so that two identical primary keys that originate from differentcontexts can co-exist within the same identity map.

The example at Horizontal Sharding has been updated to illustrate thisbehavior. The example shows a sharded class WeatherLocation thatrefers to a dependent WeatherReport object, where the WeatherReportclass is mapped to a table that stores a simple integer primary key. TwoWeatherReport objects from different databases may have the sameprimary key value. The example now illustrates that a new identity_tokenfield tracks this difference so that the two objects can co-exist in thesame identity map:

tokyo = WeatherLocation('Asia', 'Tokyo')
newyork = WeatherLocation('North America', 'New York')
 
tokyo.reports.append(Report(80.0))
newyork.reports.append(Report(75))
 
sess = create_session()
 
sess.add_all([tokyo, newyork, quito])
 
sess.commit()
 
# the Report class uses a simple integer primary key.  So across two
# databases, a primary key will be repeated.  The "identity_token" tracks
# in memory that these two identical primary keys are local to different
# databases.
 
newyork_report = newyork.reports[0]
tokyo_report = tokyo.reports[0]
 
assert inspect(newyork_report).identity_key == (Report, (1, ), "north_america")
assert inspect(tokyo_report).identity_key == (Report, (1, ), "asia")
 
# the token representing the originating shard is also available directly
 
assert inspect(newyork_report).identity_token == "north_america"
assert inspect(tokyo_report).identity_token == "asia"

#4137

New Features and Improvements - Core

Boolean datatype now enforces strict True/False/None values

In version 1.1, the change described in Non-native boolean integer values coerced to zero/one/None in all cases produced anunintended side effect of altering the way Boolean behaves whenpresented with a non-integer value, such as a string. In particular, thestring value "0", which would previously result in the value Falsebeing generated, would now produce True. Making matters worse, the changein behavior was only for some backends and not others, meaning code that sendsstring "0" values to Boolean would break inconsistently acrossbackends.

The ultimate solution to this problem is that string values are not supportedwith Boolean, so in 1.2 a hard TypeError is raised if a non-integer /True/False/None value is passed. Additionally, only the integer values0 and 1 are accepted.

To accommodate for applications that wish to have more liberal interpretationof boolean values, the TypeDecorator should be used. Belowillustrates a recipe that will allow for the “liberal” behavior of the pre-1.1Boolean datatype:

from sqlalchemy import Boolean
from sqlalchemy import TypeDecorator
 
class LiberalBoolean(TypeDecorator):
    impl = Boolean
 
    def process_bind_param(self, value, dialect):
        if value is not None:
            value = bool(int(value))
        return value

#4102

Pessimistic disconnection detection added to the connection pool

The connection pool documentation has long featured a recipe for usingthe ConnectionEvents.engine_connect() engine event to emit a simplestatement on a checked-out connection to test it for liveness. Thefunctionality of this recipe has now been added into the connection poolitself, when used in conjunction with an appropriate dialect. Usingthe new parameter create_engine.pool_pre_ping, each connectionchecked out will be tested for freshness before being returned:

engine = create_engine("mysql+pymysql://", pool_pre_ping=True)

While the “pre-ping” approach adds a small amount of latency to the connectionpool checkout, for a typical application that is transactionally-oriented(which includes most ORM applications), this overhead is minimal, andeliminates the problem of acquiring a stale connection that will raisean error, requiring that the application either abandon or retry the operation.

The feature does not accommodate for connections dropped withinan ongoing transaction or SQL operation. If an application must recoverfrom these as well, it would need to employ its own operation retry logicto anticipate these errors.

See also

Disconnect Handling - Pessimistic

#3919

The IN / NOT IN operator’s empty collection behavior is now configurable; default expression simplified

An expression such as column.in_([]), which is assumed to be false,now produces the expression 1 != 1by default, instead of column != column. This will change the resultof a query that is comparing a SQL expression or column that evaluates toNULL when compared to an empty set, producing a boolean value false or true(for NOT IN) rather than NULL. The warning that would emit underthis condition is also removed. The old behavior is available using thecreate_engine.empty_in_strategy parameter tocreate_engine().

In SQL, the IN and NOT IN operators do not support comparison to acollection of values that is explicitly empty; meaning, this syntax isillegal:

mycolumn IN ()

To work around this, SQLAlchemy and other database libraries detect thiscondition and render an alternative expression that evaluates to false, orin the case of NOT IN, to true, based on the theory that “col IN ()” is alwaysfalse since nothing is in “the empty set”. Typically, in order toproduce a false/true constant that is portable across databases and worksin the context of the WHERE clause, a simple tautology such as 1 != 1 isused to evaluate to false and 1 = 1 to evaluate to true (a simple constant“0” or “1” often does not work as the target of a WHERE clause).

SQLAlchemy in its early days began with this approach as well, but soon itwas theorized that the SQL expression column IN () would not evaluate tofalse if the “column” were NULL; instead, the expression would produce NULL,since “NULL” means “unknown”, and comparisons to NULL in SQL usually produceNULL.

To simulate this result, SQLAlchemy changed from using 1 != 1 toinstead use th expression expr != expr for empty “IN” and expr = exprfor empty “NOT IN”; that is, instead of using a fixed value we use theactual left-hand side of the expression. If the left-hand side ofthe expression passed evaluates to NULL, then the comparison overallalso gets the NULL result instead of false or true.

Unfortunately, users eventually complained that this expression had a verysevere performance impact on some query planners. At that point, a warningwas added when an empty IN expression was encountered, favoring that SQLAlchemycontinues to be “correct” and urging users to avoid code that generates emptyIN predicates in general, since typically they can be safely omitted. However,this is of course burdensome in the case of queries that are built up dynamicallyfrom input variables, where an incoming set of values might be empty.

In recent months, the original assumptions of this decision have beenquestioned. The notion that the expression “NULL IN ()” should return NULL wasonly theoretical, and could not be tested since databases don’t support thatsyntax. However, as it turns out, you can in fact ask a relational databasewhat value it would return for “NULL IN ()” by simulating the empty set asfollows:

SELECT NULL IN (SELECT 1 WHERE 1 != 1)

With the above test, we see that the databases themselves can’t agree onthe answer. PostgreSQL, considered by most to be the most “correct” database,returns False; because even though “NULL” represents “unknown”, the “empty set”means nothing is present, including all unknown values. On theother hand, MySQL and MariaDB return NULL for the above expression, defaultingto the more common behavior of “all comparisons to NULL return NULL”.

SQLAlchemy’s SQL architecture is more sophisticated than it was when thisdesign decision was first made, so we can now allow either behavior tobe invoked at SQL string compilation time. Previously, the conversion to acomparison expression were done at construction time, that is, the momentthe ColumnOperators.in_() or ColumnOperators.notin_() operators were invoked.With the compilation-time behavior, the dialect itself can be instructedto invoke either approach, that is, the “static” 1 != 1 comparison or the“dynamic” expr != expr comparison. The default has been changedto be the “static” comparison, since this agrees with the behavior thatPostgreSQL would have in any case and this is also what the vast majorityof users prefer. This will change the result of a query that is comparinga null expression to the empty set, particularly one that is queryingfor the negation where(~nullexpr.in([])), since this now evaluates to trueand not NULL.

The behavior can now be controlled using the flagcreate_engine.empty_in_strategy, which defaults to the"static" setting, but may also be set to "dynamic" or"dynamic_warn", where the "dynamic_warn" setting is equivalent to theprevious behavior of emitting expr != expr as well as a performancewarning. However, it is anticipated that most users will appreciate the“static” default.

#3907

Late-expanded IN parameter sets allow IN expressions with cached statements

Added a new kind of bindparam() called “expanding”. This isfor use in IN expressions where the list of elements is renderedinto individual bound parameters at statement execution time, ratherthan at statement compilation time. This allows both a single boundparameter name to be linked to an IN expression of multiple elements,as well as allows query caching to be used with IN expressions. Thenew feature allows the related features of “select in” loading and“polymorphic in” loading to make use of the baked query extensionto reduce call overhead:

stmt = select([table]).where(
    table.c.col.in_(bindparam('foo', expanding=True))
conn.execute(stmt, {"foo": [1, 2, 3]})

The feature should be regarded as experimental within the 1.2 series.

#3953

Flattened operator precedence for comparison operators

The operator precedence for operators like IN, LIKE, equals, IS, MATCH, andother comparison operators has been flattened into one level. This willhave the effect of more parenthesization being generated when comparisonoperators are combined together, such as:

(column('q') == null()) != (column('y') == null())

Will now generate (q IS NULL) != (y IS NULL) rather thanq IS NULL != y IS NULL.

#3999

Support for SQL Comments on Table, Column, includes DDL, reflection

The Core receives support for string comments associated with tablesand columns. These are specified via the Table.comment andColumn.comment arguments:

Table(
    'my_table', metadata,
    Column('q', Integer, comment="the Q value"),
    comment="my Q table"
)

Above, DDL will be rendered appropriately upon table create to associatethe above comments with the table/ column within the schema. Whenthe above table is autoloaded or inspected with Inspector.get_columns(),the comments are included. The table comment is also available independentlyusing the Inspector.get_table_comment() method.

Current backend support includes MySQL, PostgreSQL, and Oracle.

#1546

Multiple-table criteria support for DELETE

The Delete construct now supports multiple-table criteria,implemented for those backends which support it, currently these arePostgreSQL, MySQL and Microsoft SQL Server (support is also added to thecurrently non-working Sybase dialect). The feature works in the samewas as that of multiple-table criteria for UPDATE, first introduced inthe 0.7 and 0.8 series.

Given a statement as:

stmt = users.delete().\
        where(users.c.id == addresses.c.id).\
        where(addresses.c.email_address.startswith('ed%'))
conn.execute(stmt)

The resulting SQL from the above statement on a PostgreSQL backendwould render as:

DELETE FROM users USING addresses
WHERE users.id = addresses.id
AND (addresses.email_address LIKE %(email_address_1)s || '%%')

See also

Multiple Table Deletes

#959

New “autoescape” option for startswith(), endswith()

The “autoescape” parameter is added to ColumnOperators.startswith(),ColumnOperators.endswith(), ColumnOperators.contains().This parameter when set to True will automatically escape all occurrencesof %, _ with an escape character, which defaults to a forwards slash /;occurrences of the escape character itself are also escaped. The forwards slashis used to avoid conflicts with settings like PostgreSQL’sstandard_confirming_strings, whose default value changed as of PostgreSQL9.1, and MySQL’s NO_BACKSLASH_ESCAPES settings. The existing “escape” parametercan now be used to change the autoescape character, if desired.

Note

This feature has been changed as of 1.2.0 from its initialimplementation in 1.2.0b2 such that autoescape is now passed as a booleanvalue, rather than a specific character to use as the escape character.

An expression such as:

>>> column('x').startswith('total%score', autoescape=True)

Renders as:

x LIKE :x_1 || '%' ESCAPE '/'

Where the value of the parameter “x_1” is 'total/%score'.

Similarly, an expression that has backslashes:

>>> column('x').startswith('total/score', autoescape=True)

Will render the same way, with the value of the parameter “x_1” as'total//score'.

#2694

Stronger typing added to “float” datatypes

A series of changes allow for use of the Float datatype to morestrongly link itself to Python floating point values, instead of the moregeneric Numeric. The changes are mostly related to ensuringthat Python floating point values are not erroneously coerced toDecimal(), and are coerced to float if needed, on the result side,if the application is working with plain floats.

• A plain Python “float” value passed to a SQL expression will now bepulled into a literal parameter with the type Float; previously,the type was Numeric, with the default “asdecimal=True” flag, whichmeant the result type would coerce to Decimal(). In particular,this would emit a confusing warning on SQLite:

float_value = connection.scalar(
    select([literal(4.56)])   # the "BindParameter" will now be
                              # Float, not Numeric(asdecimal=True)
)

• Math operations between Numeric, Float, andInteger will now preserve the Numeric or Floattype in the resulting expression’s type, including the asdecimal flagas well as if the type should be Float:

# asdecimal flag is maintained
expr = column('a', Integer) * column('b', Numeric(asdecimal=False))
assert expr.type.asdecimal == False
 
# Float subclass of Numeric is maintained
expr = column('a', Integer) * column('b', Float())
assert isinstance(expr.type, Float)

• The Float datatype will apply the float() processor toresult values unconditionally if the DBAPI is known to support nativeDecimal() mode. Some backends do not always guarantee that a floatingpoint number comes back as plain float and not precision numeric suchas MySQL.

#4017

#4018

#4020

Support for GROUPING SETS, CUBE, ROLLUP

All three of GROUPING SETS, CUBE, ROLLUP are available via thefunc namespace. In the case of CUBE and ROLLUP, these functionsalready work in previous versions, however for GROUPING SETS, a placeholderis added to the compiler to allow for the space. All three functionsare named in the documentation now:

>>> from sqlalchemy import select, table, column, func, tuple_
>>> t = table('t',
...           column('value'), column('x'),
...           column('y'), column('z'), column('q'))
>>> stmt = select([func.sum(t.c.value)]).group_by(
...     func.grouping_sets(
...         tuple_(t.c.x, t.c.y),
...         tuple_(t.c.z, t.c.q),
...     )
... )
>>> print(stmt)
SELECT sum(t.value) AS sum_1
FROM t GROUP BY GROUPING SETS((t.x, t.y), (t.z, t.q))

#3429

Parameter helper for multi-valued INSERT with contextual default generator

A default generation function, e.g. that described atContext-Sensitive Default Functions, can look at the current parameters relevantto the statement via the DefaultExecutionContext.current_parametersattribute. However, in the case of a Insert construct that specifiesmultiple VALUES clauses via the Insert.values() method, the user-definedfunction is called multiple times, once for each parameter set, however therewas no way to know which subset of keys inDefaultExecutionContext.current_parameters apply to that column. Anew function DefaultExecutionContext.get_current_parameters() is added,which includes a keyword argumentDefaultExecutionContext.get_current_parameters.isolate_multiinsert_groupsdefaulting to True, which performs the extra work of delivering a sub-dictionary ofDefaultExecutionContext.current_parameters which has the nameslocalized to the current VALUES clause being processed:

def mydefault(context):
    return context.get_current_parameters()['counter'] + 12
 
mytable = Table('mytable', meta,
    Column('counter', Integer),
    Column('counter_plus_twelve',
           Integer, default=mydefault, onupdate=mydefault)
)
 
stmt = mytable.insert().values(
    [{"counter": 5}, {"counter": 18}, {"counter": 20}])
 
conn.execute(stmt)

#4075

Key Behavioral Changes - ORM

The after_rollback() Session event now emits before the expiration of objects

The SessionEvents.after_rollback() event now has access to the attributestate of objects before their state has been expired (e.g. the “snapshotremoval”). This allows the event to be consistent with the behaviorof the SessionEvents.after_commit() event which also emits before the“snapshot” has been removed:

sess = Session()
 
user = sess.query(User).filter_by(name='x').first()
 
@event.listens_for(sess, "after_rollback")
def after_rollback(session):
    # 'user.name' is now present, assuming it was already
    # loaded.  previously this would raise upon trying
    # to emit a lazy load.
    print("user name: %s" % user.name)
 
@event.listens_for(sess, "after_commit")
def after_commit(session):
    # 'user.name' is present, assuming it was already
    # loaded.  this is the existing behavior.
    print("user name: %s" % user.name)
 
if should_rollback:
    sess.rollback()
else:
    sess.commit()

Note that the Session will still disallow SQL from being emittedwithin this event; meaning that unloaded attributes will still not beable to load within the scope of the event.

#3934

Fixed issue involving single-table inheritance with select_from()

The Query.select_from() method now honors the single-table inheritancecolumn discriminator when generating SQL; previously, only the expressionsin the query column list would be taken into account.

Supposing Manager is a subclass of Employee. A query like the following:

sess.query(Manager.id)

Would generate SQL as:

SELECT employee.id FROM employee WHERE employee.type IN ('manager')

However, if Manager were only specified by Query.select_from()and not in the columns list, the discriminator would not be added:

sess.query(func.count(1)).select_from(Manager)

would generate:

SELECT count(1) FROM employee

With the fix, Query.select_from() now works correctly and we get:

SELECT count(1) FROM employee WHERE employee.type IN ('manager')

Applications that may have been working around this by supplying theWHERE clause manually may need to be adjusted.

#3891

Previous collection is no longer mutated upon replacement

The ORM emits events whenever the members of a mapped collection change.In the case of assigning a collection to an attribute that would replacethe previous collection, a side effect of this was that the collectionbeing replaced would also be mutated, which is misleading and unnecessary:

>>> a1, a2, a3 = Address('a1'), Address('a2'), Address('a3')
>>> user.addresses = [a1, a2]
 
>>> previous_collection = user.addresses
 
# replace the collection with a new one
>>> user.addresses = [a2, a3]
 
>>> previous_collection
[Address('a1'), Address('a2')]

Above, prior to the change, the previous_collection would have had the“a1” member removed, corresponding to the member that’s no longer in thenew collection.

#3913

A @validates method receives all values on bulk-collection set before comparison

A method that uses @validates will now receive all members of a collectionduring a “bulk set” operation, before comparison is applied against theexisting collection.

Given a mapping as:

class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    bs = relationship("B")
 
    @validates('bs')
    def convert_dict_to_b(self, key, value):
        return B(data=value['data'])
 
class B(Base):
    __tablename__ = 'b'
    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey('a.id'))
    data = Column(String)

Above, we could use the validator as follows, to convert from an incomingdictionary to an instance of B upon collection append:

a1 = A()
a1.bs.append({"data": "b1"})

However, a collection assignment would fail, since the ORM would assumeincoming objects are already instances of B as it attempts to compare themto the existing members of the collection, before doing collection appendswhich actually invoke the validator. This would make it impossible for bulkset operations to accommodate non-ORM objects like dictionaries that neededup-front modification:

a1 = A()
a1.bs = [{"data": "b1"}]

The new logic uses the new AttributeEvents.bulk_replace() event to ensurethat all values are sent to the @validates function up front.

As part of this change, this means that validators will now receiveall members of a collection upon bulk set, not just the members thatare new. Supposing a simple validator such as:

class A(Base):
    # ...
 
    @validates('bs')
    def validate_b(self, key, value):
        assert value.data is not None
        return value

Above, if we began with a collection as:

a1 = A()
 
b1, b2 = B(data="one"), B(data="two")
a1.bs = [b1, b2]

And then, replaced the collection with one that overlaps the first:

b3 = B(data="three")
a1.bs = [b2, b3]

Previously, the second assignment would trigger the A.validate_bmethod only once, for the b3 object. The b2 object would be seenas being already present in the collection and not validated. With the newbehavior, both b2 and b3 are passed to A.validate_b before passingonto the collection. It is thus important that validation methods employidempotent behavior to suit such a case.

See also

New bulk_replace event

#3896

Use flag_dirty() to mark an object as “dirty” without any attribute changing

An exception is now raised if the attributes.flag_modified() functionis used to mark an attribute as modified that isn’t actually loaded:

a1 = A(data='adf')
s.add(a1)
 
s.flush()
 
# expire, similarly as though we said s.commit()
s.expire(a1, 'data')
 
# will raise InvalidRequestError
attributes.flag_modified(a1, 'data')

This because the flush process will most likely fail in any case if theattribute remains un-present by the time flush occurs. To mark an objectas “modified” without referring to any attribute specifically, so that itis considered within the flush process for the purpose of custom event handlerssuch as SessionEvents.before_flush(), use the newattributes.flag_dirty() function:

from sqlalchemy.orm import attributes
 
attributes.flag_dirty(a1)

#3753

“scope” keyword removed from scoped_session

A very old and undocumented keyword argument scope has been removed:

from sqlalchemy.orm import scoped_session
Session = scoped_session(sessionmaker())
 
session = Session(scope=None)

The purpose of this keyword was an attempt to allow for variable“scopes”, where None indicated “no scope” and would therefore returna new Session. The keyword has never been documented and willnow raise TypeError if encountered. It is not anticipated that thiskeyword is in use, however if users report issues related to this duringbeta testing, it can be restored with a deprecation.

#3796

Refinements to post_update in conjunction with onupdate

A relationship that uses the relationship.post_update featurewill now interact better with a column that has an Column.onupdatevalue set. If an object is inserted with an explicit value for the column,it is re-stated during the UPDATE so that the “onupdate” rule does notoverwrite it:

class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    favorite_b_id = Column(ForeignKey('b.id', name="favorite_b_fk"))
    bs = relationship("B", primaryjoin="A.id == B.a_id")
    favorite_b = relationship(
        "B", primaryjoin="A.favorite_b_id == B.id", post_update=True)
    updated = Column(Integer, onupdate=my_onupdate_function)
 
class B(Base):
    __tablename__ = 'b'
    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey('a.id', name="a_fk"))
 
a1 = A()
b1 = B()
 
a1.bs.append(b1)
a1.favorite_b = b1
a1.updated = 5
s.add(a1)
s.flush()

Above, the previous behavior would be that an UPDATE would emit after theINSERT, thus triggering the “onupdate” and overwriting the value“5”. The SQL now looks like:

INSERT INTO a (favorite_b_id, updated) VALUES (?, ?)
(None, 5)
INSERT INTO b (a_id) VALUES (?)
(1,)
UPDATE a SET favorite_b_id=?, updated=? WHERE a.id = ?
(1, 5, 1)

Additionally, if the value of “updated” is not set, then we correctlyget back the newly generated value on a1.updated; previously, the logicthat refreshes or expires the attribute to allow the generated valueto be present would not fire off for a post-update. TheInstanceEvents.refresh_flush() event is also emitted when a refreshwithin flush occurs in this case.

#3471

#3472

post_update integrates with ORM versioning

The post_update feature, documented at Rows that point to themselves / Mutually Dependent Rows, involves that anUPDATE statement is emitted in response to changes to a particularrelationship-bound foreign key, in addition to the INSERT/UPDATE/DELETE thatwould normally be emitted for the target row. This UPDATE statementnow participates in the versioning feature, documented atConfiguring a Version Counter.

Given a mapping:

class Node(Base):
    __tablename__ = 'node'
    id = Column(Integer, primary_key=True)
    version_id = Column(Integer, default=0)
    parent_id = Column(ForeignKey('node.id'))
    favorite_node_id = Column(ForeignKey('node.id'))
 
    nodes = relationship("Node", primaryjoin=remote(parent_id) == id)
    favorite_node = relationship(
        "Node", primaryjoin=favorite_node_id == remote(id),
        post_update=True
    )
 
    __mapper_args__ = {
        'version_id_col': version_id
    }

An UPDATE of a node that associates another node as “favorite” willnow increment the version counter as well as match the current version:

node = Node()
session.add(node)
session.commit()  # node is now version #1
 
node = session.query(Node).get(node.id)
node.favorite_node = Node()
session.commit()  # node is now version #2

Note that this means an object that receives an UPDATE in response toother attributes changing, and a second UPDATE due to a post_updaterelationship change, will now receivetwo version counter updates for one flush. However, if the objectis subject to an INSERT within the current flush, the version counterwill not be incremented an additional time, unless a server-sideversioning scheme is in place.

The reason post_update emits an UPDATE even for an UPDATE is now discussed atWhy does post_update emit UPDATE in addition to the first UPDATE?.

See also

Rows that point to themselves / Mutually Dependent Rows

Why does post_update emit UPDATE in addition to the first UPDATE?

#3496

Key Behavioral Changes - Core

The typing behavior of custom operators has been made consistent

User defined operators can be made on the fly using theOperators.op() function. Previously, the typing behavior ofan expression against such an operator was inconsistent and also notcontrollable.

Whereas in 1.1, an expression such as the following would producea result with no return type (assume -%> is some special operatorsupported by the database):

>>> column('x', types.DateTime).op('-%>')(None).type
NullType()

Other types would use the default behavior of using the left-hand typeas the return type:

>>> column('x', types.String(50)).op('-%>')(None).type
String(length=50)

These behaviors were mostly by accident, so the behavior has been madeconsistent with the second form, that is the default return type is thesame as the left-hand expression:

>>> column('x', types.DateTime).op('-%>')(None).type
DateTime()

As most user-defined operators tend to be “comparison” operators, oftenone of the many special operators defined by PostgreSQL, theOperators.op.is_comparison flag has been repaired to followits documented behavior of allowing the return type to be Booleanin all cases, including for ARRAY and JSON:

>>> column('x', types.String(50)).op('-%>', is_comparison=True)(None).type
Boolean()
>>> column('x', types.ARRAY(types.Integer)).op('-%>', is_comparison=True)(None).type
Boolean()
>>> column('x', types.JSON()).op('-%>', is_comparison=True)(None).type
Boolean()

To assist with boolean comparison operators, a new shorthand methodOperators.bool_op() has been added. This method should be preferredfor on-the-fly boolean operators:

>>> print(column('x', types.Integer).bool_op('-%>')(5))
x -%> :x_1

Percent signs in literal_column() now conditionally escaped

The literal_column construct now escapes percent sign charactersconditionally, based on whether or not the DBAPI in use makes use of apercent-sign-sensitive paramstyle or not (e.g. ‘format’ or ‘pyformat’).

Previously, it was not possible to produce a literal_columnconstruct that stated a single percent sign:

>>> from sqlalchemy import literal_column
>>> print(literal_column('some%symbol'))
some%%symbol

The percent sign is now unaffected for dialects that are not set touse the ‘format’ or ‘pyformat’ paramstyles; dialects such most MySQLdialects which do state one of these paramstyles will continue to escapeas is appropriate:

>>> from sqlalchemy import literal_column
>>> print(literal_column('some%symbol'))
some%symbol
>>> from sqlalchemy.dialects import mysql
>>> print(literal_column('some%symbol').compile(dialect=mysql.dialect()))
some%%symbol

As part of this change, the doubling that has been present when usingoperators like ColumnOperators.contains(),ColumnOperators.startswith() and ColumnOperators.endswith()is also refined to only occur when appropriate.

#3740

The column-level COLLATE keyword now quotes the collation name

A bug in the expression.collate() and ColumnOperators.collate()functions, used to supply ad-hoc column collations at the statement level,is fixed, where a case sensitive name would not be quoted:

stmt = select([mytable.c.x, mytable.c.y]).\
    order_by(mytable.c.somecolumn.collate("fr_FR"))

now renders:

SELECT mytable.x, mytable.y,
FROM mytable ORDER BY mytable.somecolumn COLLATE "fr_FR"

Previously, the case sensitive name “fr_FR” would not be quoted. Currently,manual quoting of the “fr_FR” name is not detected, so applications thatare manually quoting the identifier should be adjusted. Note that this changedoes not impact the use of collations at the type level (e.g. specifiedon the datatype like String at the table level), where quotingis already applied.

#3785

Dialect Improvements and Changes - PostgreSQL

Support for Batch Mode / Fast Execution Helpers

The psycopg2 cursor.executemany() method has been identified as performingpoorly, particularly with INSERT statements. To alleviate this, psycopg2has added Fast Execution Helperswhich rework statements into fewer server round trips by sending multipleDML statements in batch. SQLAlchemy 1.2 now includes support for thesehelpers to be used transparently whenever the Engine makes useof cursor.executemany() to invoke a statement against multiple parametersets. The feature is off by default and can be enabled using theuse_batch_mode argument on create_engine():

engine = create_engine(
    "postgresql+psycopg2://scott:tiger@host/dbname",
    use_batch_mode=True)

The feature is considered to be experimental for the moment but may becomeon by default in a future release.

See also

psycopg2_batch_mode

#4109

Support for fields specification in INTERVAL, including full reflection

The “fields” specifier in PostgreSQL’s INTERVAL datatype allows specificationof which fields of the interval to store, including such values as “YEAR”,“MONTH”, “YEAR TO MONTH”, etc. The postgresql.INTERVAL datatypenow allows these values to be specified:

from sqlalchemy.dialects.postgresql import INTERVAL
 
Table(
    'my_table', metadata,
    Column("some_interval", INTERVAL(fields="DAY TO SECOND"))
)

Additionally, all INTERVAL datatypes can now be reflected independentlyof the “fields” specifier present; the “fields” parameter in the datatypeitself will also be present:

>>> inspect(engine).get_columns("my_table")
[{'comment': None,
  'name': u'some_interval', 'nullable': True,
  'default': None, 'autoincrement': False,
  'type': INTERVAL(fields=u'day to second')}]

#3959

Dialect Improvements and Changes - MySQL

Support for INSERT..ON DUPLICATE KEY UPDATE

The ON DUPLICATE KEY UPDATE clause of INSERT supported by MySQLis now supported using a MySQL-specific version of theInsert object, via sqlalchemy.dialects.mysql.dml.insert().This Insert subclass adds a new methodon_duplicate_key_update() that implements MySQL’s syntax:

from sqlalchemy.dialects.mysql import insert
 
insert_stmt = insert(my_table). \
    values(id='some_id', data='some data to insert')
 
on_conflict_stmt = insert_stmt.on_duplicate_key_update(
    data=insert_stmt.inserted.data,
    status='U'
)
 
conn.execute(on_conflict_stmt)

The above will render:

INSERT INTO my_table (id, data)
VALUES (:id, :data)
ON DUPLICATE KEY UPDATE data=VALUES(data), status=:status_1

See also

INSERT…ON DUPLICATE KEY UPDATE (Upsert)

#4009

Dialect Improvements and Changes - Oracle

Major Refactor to cx_Oracle Dialect, Typing System

With the introduction of the 6.x series of the cx_Oracle DBAPI, SQLAlchemy’scx_Oracle dialect has been reworked and simplified to take advantage of recentimprovements in cx_Oracle as well as dropping support for patterns that weremore relevant before the 5.x series of cx_Oracle.

• The minimum cx_Oracle version supported is now 5.1.3; 5.3 or the most recent6.x series are recommended.

• The handling of datatypes has been refactored. The cursor.setinputsizes()method is no longer used for any datatype except LOB types, per advice fromcx_Oracle’s developers. As a result, the parameters auto_setinputsizesand exclude_setinputsizes are deprecated and no longer have any effect.

• The coerce_to_decimal flag, when set to False to indicate that coercionof numeric types with precision and scale to Decimal should not occur,only impacts untyped (e.g. plain string with no TypeEngine objects)statements. A Core expression that includes a Numeric type orsubtype will now follow the decimal coercion rules of that type.

• The “two phase” transaction support in the dialect, already dropped for the6.x series of cx_Oracle, has now been removed entirely as this feature hasnever worked correctly and is unlikely to have been in production use.As a result, the allow_twophase dialect flag is deprecated and also has noeffect.

• Fixed a bug involving the column keys present with RETURNING. Givena statement as follows:

result = conn.execute(table.insert().values(x=5).returning(table.c.a, table.c.b))

Previously, the keys in each row of the result would be ret_0 and ret_1,which are identifiers internal to the cx_Oracle RETURNING implementation.The keys will now be a and b as is expected for other dialects.

• cx_Oracle’s LOB datatype represents return values as a cx_Oracle.LOBobject, which is a cursor-associated proxy that returns the ultimate datavalue via a .read() method. Historically, if more rows were read beforethese LOB objects were consumed (specifically, more rows than the value ofcursor.arraysize which causes a new batch of rows to be read), these LOBobjects would raise the error “LOB variable no longer valid after subsequentfetch”. SQLAlchemy worked around this by both automatically calling.read() upon these LOBs within its typing system, as well as using aspecial BufferedColumnResultSet which would ensure this data was bufferedin case a call like cursor.fetchmany() or cursor.fetchall() wereused.

The dialect now makes use of a cx_Oracle outputtypehandler to handle these.read() calls, so that they are always called up front regardless of howmany rows are being fetched, so that this error can no longer occur. As aresult, the use of the BufferedColumnResultSet, as well as some otherinternals to the Core ResultSet that were specific to this use case,have been removed. The type objects are also simplified as they no longerneed to process a binary column result.

Additionally, cx_Oracle 6.x has removed the conditions under which this erroroccurs in any case, so the error is no longer possible. The errorcan occur on SQLAlchemy in the case that the seldom (if ever) usedauto_convert_lobs=False option is in use, in conjunction with theprevious 5.x series of cx_Oracle, and more rows are read before the LOBobjects can be consumed. Upgrading to cx_Oracle 6.x will resolve that issue.

Oracle Unique, Check constraints now reflected

UNIQUE and CHECK constraints now reflect viaInspector.get_unique_constraints() andInspector.get_check_constraints(). A Table object that’sreflected will now include CheckConstraint objects as well.See the notes at Constraint Reflection for informationon behavioral quirks here, including that most Table objectswill still not include any UniqueConstraint objects as theseusually represent via Index.

See also

Constraint Reflection

#4003

Oracle foreign key constraint names are now “name normalized”

The names of foreign key constraints as delivered to aForeignKeyConstraint object during table reflection as well aswithin the Inspector.get_foreign_keys() method will now be“name normalized”, that is, expressed as lower case for a case insensitivename, rather than the raw UPPERCASE format that Oracle uses:

>>> insp.get_indexes("addresses")
[{'unique': False, 'column_names': [u'user_id'],
  'name': u'address_idx', 'dialect_options': {}}]
 
>>> insp.get_pk_constraint("addresses")
{'name': u'pk_cons', 'constrained_columns': [u'id']}
 
>>> insp.get_foreign_keys("addresses")
[{'referred_table': u'users', 'referred_columns': [u'id'],
  'referred_schema': None, 'name': u'user_id_fk',
  'constrained_columns': [u'user_id']}]

Previously, the foreign keys result would look like:

[{'referred_table': u'users', 'referred_columns': [u'id'],
  'referred_schema': None, 'name': 'USER_ID_FK',
  'constrained_columns': [u'user_id']}]

Where the above could create problems particularly with Alembic autogenerate.

#3276

Dialect Improvements and Changes - SQL Server

SQL Server schema names with embedded dots supported

The SQL Server dialect has a behavior such that a schema name with a dot insideof it is assumed to be a “database”.”owner” identifier pair, which isnecessarily split up into these separate components during table and componentreflection operations, as well as when rendering quoting for the schema name sothat the two symbols are quoted separately. The schema argument cannow be passed using brackets to manually specify where this splitoccurs, allowing database and/or owner names that themselves contain oneor more dots:

Table(
    "some_table", metadata,
    Column("q", String(50)),
    schema="[MyDataBase.dbo]"
)

The above table will consider the “owner” to be MyDataBase.dbo, whichwill also be quoted upon render, and the “database” as None. To individuallyrefer to database name and owner, use two pairs of brackets:

Table(
    "some_table", metadata,
    Column("q", String(50)),
    schema="[MyDataBase.SomeDB].[MyDB.owner]"
)

Additionally, the quoted_name construct is now honored whenpassed to “schema” by the SQL Server dialect; the given symbol willnot be split on the dot if the quote flag is True and will be interpretedas the “owner”.

See also

Multipart Schema Names

#2626

AUTOCOMMIT isolation level support

Both the PyODBC and pymssql dialects now support the “AUTOCOMMIT” isolationlevel as set by Connection.execution_options() which will establishthe correct flags on the DBAPI connection object.