SQLAlchemy is a Python SQL toolkit and a database object mapping framework. It includes a complete enterprise-class persistence model, specifically for efficient and high-performance database access.
SQL databases behave less like object collections the more size and performance start to matter; object collections behave less like tables and rows the more abstraction starts to matter. SQLAlchemy aims to accommodate both of these principles.
SQLAlchemy considers the database to be a relational algebra engine, not just a collection of tables. Rows can be selected from not only tables but also joins and other select statements; any of these units can be composed into a larger structure. SQLAlchemy’s expression language builds on this concept from its core.
SQLAlchemy is most famous for its object-relational mapper (ORM), an optional component that provides the data mapper pattern, where classes can be mapped to the database in open ended, multiple ways – allowing the object model and database schema to develop in a cleanly decoupled way from the beginning.
SQLAlchemy’s overall approach to these problems is entirely different from that of most other SQL / ORM tools, rooted in a so-called complimentarity- oriented approach; instead of hiding away SQL and object relational details behind a wall of automation, all processes are fully exposed within a series of composable, transparent tools. The library takes on the job of automating redundant tasks while the developer remains in control of how the database is organized and how SQL is constructed.
The main goal of SQLAlchemy is to change the way you think about databases and SQL!
SQLAlchemy 1.3.12 has been released, this release contains various fixes.
Fixed issue involving
lazy="raise"strategy where an ORM delete of an object would raise for a simple “use-get” style many-to-one relationship that had lazy=”raise” configured. This is inconsistent vs. the change introduced in 1.3 as part of #4353, where it was established that a history operation that does not expect emit SQL should bypass the
lazy="raise"check, and instead effectively treat it as
lazy="raise_on_sql"for this case. The fix adjusts the lazy loader strategy to not raise for the case where the lazy load was instructed that it should not emit SQL if the object were not present.
Setting persistence-related flags on
relationship()while also setting viewonly=True will now emit a regular warning, as these flags do not make sense for a viewonly=True relationship. In particular, the “cascade” settings have their own warning that is generated based on the individual values, such as “delete, delete-orphan”, that should not apply to a viewonly relationship. Note however that in the case of “cascade”, these settings are still erroneously taking effect even though the relationship is set up as “viewonly”. In 1.4, all persistence-related cascade settings will be disallowed on a viewonly=True relationship in order to resolve this issue.
[orm] [bug] [py3k]
Fixed issue where when assigning a collection to itself as a slice, the mutation operation would fail as it would first erase the assigned collection inadvertently. As an assignment that does not change the contents should not generate events, the operation is now a no-op. Note that the fix only applies to Python 3; in Python 2, the
__setitem__hook isn’t called in this case;
__setslice__is used instead which recreates the list item-by-item in all cases.
Fixed issue where by if the “begin” of a transaction failed at the Core engine/connection level, such as due to network error or database is locked for some transactional recipes, within the context of the
Sessionprocuring that connection from the conneciton pool and then immediately returning it, the ORM
Sessionwould not close the connection despite this connection not being stored within the state of that
Session. This would lead to the connection being cleaned out by the connection pool weakref handler within garbage collection which is an unpreferred codepath that in some special configurations can emit errors in standard error.
Fixed bug where “distinct” keyword passed to
select()would not treat a string value as a “label reference” in the same way that the
select.distinct()does; it would instead raise unconditionally. This keyword argument and the others passed to
select()will ultimately be deprecated for SQLAlchemy 2.0.
Changed the text of the exception for “Can’t resolve label reference” to include other kinds of label coercions, namely that “DISTINCT” is also in this category under the PostgreSQL dialect.
Fixed issue to workaround SQLite’s behavior of assigning “numeric” affinity to JSON datatypes, first described at Support for SQLite JSON Added, which returns scalar numeric JSON values as a number and not as a string that can be JSON deserialized. The SQLite-specific JSON deserializer now gracefully degrades for this case as an exception and bypasses deserialization for single numeric values, as from a JSON perspective they are already deserialized.
Repaired support for the
mssql.DATETIMEOFFSETdatatype on PyODBC, by adding PyODBC-level result handlers as it does not include native support for this datatype. This includes usage of the Python 3 “timezone” tzinfo subclass in order to set up a timezone, which on Python 2 makes use of a minimal backport of “timezone” in sqlalchemy.util.