[sql] [bug] ¶
Fixed caching issue where the │ │ │ │ +
[sql] [bug] ¶
Fixed caching issue where using the TextualSelect.add_cte() method
│ │ │ │ +of the TextualSelect construct would not set a correct cache key
│ │ │ │ +which distinguished between different CTE expressions.
References: #11471
│ │ │ │ + │ │ │ │ +[sql] [bug] ¶
Fixed caching issue where the
│ │ │ │ Select.with_for_update.key_share element of
│ │ │ │ Select.with_for_update() was not considered as part of the cache
│ │ │ │ key, leading to incorrect caching if different variations of this parameter
│ │ │ │ were used with an otherwise identical statement.
References: #11544
│ │ │ │ │ │ │ │[sql] [bug] ¶
Fixed caching issue where using the TextualSelect.add_cte() method
│ │ │ │ -of the TextualSelect construct would not set a correct cache key
│ │ │ │ -which distinguished between different CTE expressions.
References: #11471
│ │ │ │ - │ │ │ │ -[mypy] [bug] ¶
The deprecated mypy plugin is no longer fully functional with the latest │ │ │ │ series of mypy 1.11.0, as changes in the mypy interpreter are no longer │ │ │ │ ├── html2text {} │ │ │ │ │ @@ -808,24 +808,24 @@ │ │ │ │ │ sqlalchemy.util.await_only() directly. │ │ │ │ │ [[eennggiinnee]] [[bbuugg]] _¶ │ │ │ │ │ Adjustments to the C extensions, which are specific to the SQLAlchemy 1.x │ │ │ │ │ series, to work under Python 3.13. Pull request courtesy Ben Beasley. │ │ │ │ │ References: _#_1_1_4_9_9 │ │ │ │ │ ******** ssqqll_?¶ ******** │ │ │ │ │ * [[ssqqll]] [[bbuugg]] _¶ │ │ │ │ │ - Fixed caching issue where the _S_e_l_e_c_t_._w_i_t_h___f_o_r___u_p_d_a_t_e_._k_e_y___s_h_a_r_e element of │ │ │ │ │ - _S_e_l_e_c_t_._w_i_t_h___f_o_r___u_p_d_a_t_e_(_) was not considered as part of the cache key, │ │ │ │ │ - leading to incorrect caching if different variations of this parameter │ │ │ │ │ - were used with an otherwise identical statement. │ │ │ │ │ - References: _#_1_1_5_4_4 │ │ │ │ │ + Fixed caching issue where using the _T_e_x_t_u_a_l_S_e_l_e_c_t_._a_d_d___c_t_e_(_) method of the │ │ │ │ │ + _T_e_x_t_u_a_l_S_e_l_e_c_t construct would not set a correct cache key which │ │ │ │ │ + distinguished between different CTE expressions. │ │ │ │ │ + References: _#_1_1_4_7_1 │ │ │ │ │ [[ssqqll]] [[bbuugg]] _¶ │ │ │ │ │ -Fixed caching issue where using the _T_e_x_t_u_a_l_S_e_l_e_c_t_._a_d_d___c_t_e_(_) method of the │ │ │ │ │ -_T_e_x_t_u_a_l_S_e_l_e_c_t construct would not set a correct cache key which distinguished │ │ │ │ │ -between different CTE expressions. │ │ │ │ │ -References: _#_1_1_4_7_1 │ │ │ │ │ +Fixed caching issue where the _S_e_l_e_c_t_._w_i_t_h___f_o_r___u_p_d_a_t_e_._k_e_y___s_h_a_r_e element of │ │ │ │ │ +_S_e_l_e_c_t_._w_i_t_h___f_o_r___u_p_d_a_t_e_(_) was not considered as part of the cache key, leading │ │ │ │ │ +to incorrect caching if different variations of this parameter were used with │ │ │ │ │ +an otherwise identical statement. │ │ │ │ │ +References: _#_1_1_5_4_4 │ │ │ │ │ ******** mmyyppyy_?¶ ******** │ │ │ │ │ * [[mmyyppyy]] [[bbuugg]] _¶ │ │ │ │ │ The deprecated mypy plugin is no longer fully functional with the latest │ │ │ │ │ series of mypy 1.11.0, as changes in the mypy interpreter are no longer │ │ │ │ │ compatible with the approach used by the plugin. If code is dependent on │ │ │ │ │ the mypy plugin with sqlalchemy2-stubs, it’s recommended to pin mypy to │ │ │ │ │ be below the 1.11.0 series. Seek upgrading to the 2.0 series of │ │ │ ├── ./usr/share/doc/python-sqlalchemy-doc/html/changelog/changelog_20.html │ │ │ │ @@ -7935,31 +7935,31 @@ │ │ │ │
│ │ │ │[sqlite] [usecase] ¶
Added RETURNING support for the SQLite dialect. SQLite supports RETURNING │ │ │ │ since version 3.35.
│ │ │ │References: #6195
│ │ │ │ │ │ │ │[sqlite] [usecase] [performance] ¶
SQLite datetime, date, and time datatypes now use Python standard lib │ │ │ │ +
[sqlite] [usecase] ¶
The SQLite dialect now supports UPDATE..FROM syntax, for UPDATE statements
│ │ │ │ +that may refer to additional tables within the WHERE criteria of the
│ │ │ │ +statement without the need to use subqueries. This syntax is invoked
│ │ │ │ +automatically when using the Update construct when more than
│ │ │ │ +one table or other entity or selectable is used.
References: #7185
│ │ │ │ + │ │ │ │ +[sqlite] [performance] [usecase] ¶
SQLite datetime, date, and time datatypes now use Python standard lib
│ │ │ │ fromisoformat() methods in order to parse incoming datetime, date, and
│ │ │ │ time string values. This improves performance vs. the previous regular
│ │ │ │ expression-based approach, and also automatically accommodates for datetime
│ │ │ │ and time formats that contain either a six-digit “microseconds” format or a
│ │ │ │ three-digit “milliseconds” format.
References: #7029
│ │ │ │ │ │ │ │[sqlite] [usecase] ¶
The SQLite dialect now supports UPDATE..FROM syntax, for UPDATE statements
│ │ │ │ -that may refer to additional tables within the WHERE criteria of the
│ │ │ │ -statement without the need to use subqueries. This syntax is invoked
│ │ │ │ -automatically when using the Update construct when more than
│ │ │ │ -one table or other entity or selectable is used.
References: #7185
│ │ │ │ - │ │ │ │ -[sqlite] [bug] ¶
Removed the warning that emits from the Numeric type about
│ │ │ │ DBAPIs not supporting Decimal values natively. This warning was oriented
│ │ │ │ towards SQLite, which does not have any real way without additional
│ │ │ │ extensions or workarounds of handling precision numeric values more than 15
│ │ │ │ significant digits as it only uses floating point math to represent
│ │ │ │ numbers. As this is a known and documented limitation in SQLite itself, and
│ │ │ │ not a quirk of the pysqlite driver, there’s no need for SQLAlchemy to warn
│ │ │ │ ├── html2text {}
│ │ │ │ │ @@ -5471,29 +5471,29 @@
│ │ │ │ │ See also
│ │ │ │ │ _R_e_f_l_e_c_t_i_n_g_ _i_n_t_e_r_n_a_l_ _s_c_h_e_m_a_ _t_a_b_l_e_s
│ │ │ │ │ References: _#_8_2_3_4
│ │ │ │ │ [[ssqqlliittee]] [[uusseeccaassee]] _¶
│ │ │ │ │ Added RETURNING support for the SQLite dialect. SQLite supports RETURNING since
│ │ │ │ │ version 3.35.
│ │ │ │ │ References: _#_6_1_9_5
│ │ │ │ │ -[[ssqqlliittee]] [[uusseeccaassee]] [[ppeerrffoorrmmaannccee]] _¶
│ │ │ │ │ -SQLite datetime, date, and time datatypes now use Python standard lib
│ │ │ │ │ -fromisoformat() methods in order to parse incoming datetime, date, and time
│ │ │ │ │ -string values. This improves performance vs. the previous regular expression-
│ │ │ │ │ -based approach, and also automatically accommodates for datetime and time
│ │ │ │ │ -formats that contain either a six-digit “microseconds” format or a three-digit
│ │ │ │ │ -“milliseconds” format.
│ │ │ │ │ -References: _#_7_0_2_9
│ │ │ │ │ [[ssqqlliittee]] [[uusseeccaassee]] _¶
│ │ │ │ │ The SQLite dialect now supports UPDATE..FROM syntax, for UPDATE statements that
│ │ │ │ │ may refer to additional tables within the WHERE criteria of the statement
│ │ │ │ │ without the need to use subqueries. This syntax is invoked automatically when
│ │ │ │ │ using the _U_p_d_a_t_e construct when more than one table or other entity or
│ │ │ │ │ selectable is used.
│ │ │ │ │ References: _#_7_1_8_5
│ │ │ │ │ +[[ssqqlliittee]] [[ppeerrffoorrmmaannccee]] [[uusseeccaassee]] _¶
│ │ │ │ │ +SQLite datetime, date, and time datatypes now use Python standard lib
│ │ │ │ │ +fromisoformat() methods in order to parse incoming datetime, date, and time
│ │ │ │ │ +string values. This improves performance vs. the previous regular expression-
│ │ │ │ │ +based approach, and also automatically accommodates for datetime and time
│ │ │ │ │ +formats that contain either a six-digit “microseconds” format or a three-digit
│ │ │ │ │ +“milliseconds” format.
│ │ │ │ │ +References: _#_7_0_2_9
│ │ │ │ │ [[ssqqlliittee]] [[bbuugg]] _¶
│ │ │ │ │ Removed the warning that emits from the _N_u_m_e_r_i_c type about DBAPIs not
│ │ │ │ │ supporting Decimal values natively. This warning was oriented towards SQLite,
│ │ │ │ │ which does not have any real way without additional extensions or workarounds
│ │ │ │ │ of handling precision numeric values more than 15 significant digits as it only
│ │ │ │ │ uses floating point math to represent numbers. As this is a known and
│ │ │ │ │ documented limitation in SQLite itself, and not a quirk of the pysqlite driver,
│ │ │ ├── ./usr/share/doc/python-sqlalchemy-doc/html/orm/examples.html
│ │ │ │┄ Ordering differences only
│ │ │ │ @@ -299,49 +299,49 @@
│ │ │ │
Examples illustrating the usage of the “association object” pattern, │ │ │ │ where an intermediary class mediates the relationship between two │ │ │ │ classes that are associated in a many-to-many pattern.
│ │ │ │Listing of files:
basic_association.py - Illustrate a many-to-many relationship between an │ │ │ │ +“Order” and a collection of “Item” objects, associating a purchase price │ │ │ │ +with each via an association object called “OrderItem”
│ │ │ │ +dict_of_sets_with_default.py - An advanced association proxy example which │ │ │ │ illustrates nesting of association proxies to produce multi-level Python │ │ │ │ collections, in this case a dictionary with string keys and sets of integers │ │ │ │ as values, which conceal the underlying mapped classes.
│ │ │ │proxied_association.py - Same example as basic_association, adding in
│ │ │ │ usage of sqlalchemy.ext.associationproxy to make explicit references
│ │ │ │ to OrderItem optional.
basic_association.py - Illustrate a many-to-many relationship between an │ │ │ │ -“Order” and a collection of “Item” objects, associating a purchase price │ │ │ │ -with each via an association object called “OrderItem”
│ │ │ │ -Examples illustrating the asyncio engine feature of SQLAlchemy.
│ │ │ │Listing of files:
async_orm_writeonly.py - Illustrates using write only relationships for simpler handling │ │ │ │ of ORM collections under asyncio.
│ │ │ │async_orm.py - Illustrates use of the sqlalchemy.ext.asyncio.AsyncSession object
│ │ │ │ +for asynchronous ORM use.
greenlet_orm.py - Illustrates use of the sqlalchemy.ext.asyncio.AsyncSession object │ │ │ │ for asynchronous ORM use, including the optional run_sync() method.
│ │ │ │basic.py - Illustrates the asyncio engine / connection interface.
│ │ │ │ +gather_orm_statements.py - Illustrates how to run many statements concurrently using asyncio.gather()
│ │ │ │ along many asyncio database connections, merging ORM results into a single
│ │ │ │ AsyncSession.
basic.py - Illustrates the asyncio engine / connection interface.
│ │ │ │ -async_orm.py - Illustrates use of the sqlalchemy.ext.asyncio.AsyncSession object
│ │ │ │ -for asynchronous ORM use.
An example of persistence for a directed graph structure. The
│ │ │ │ graph is stored as a collection of edges, each referencing both a
│ │ │ │ @@ -378,32 +378,32 @@
│ │ │ │ subclassing the HasAddresses mixin, which ensures that the
│ │ │ │ parent class is provided with an addresses collection
│ │ │ │ which contains Address objects.
The discriminator_on_association.py and generic_fk.py scripts │ │ │ │ are modernized versions of recipes presented in the 2007 blog post │ │ │ │ Polymorphic Associations with SQLAlchemy.
│ │ │ │Listing of files:
discriminator_on_association.py - Illustrates a mixin which provides a generic association │ │ │ │ +using a single target table and a single association table, │ │ │ │ +referred to by all parent tables. The association table │ │ │ │ +contains a “discriminator” column which determines what type of │ │ │ │ +parent object associates to each particular row in the association │ │ │ │ +table.
│ │ │ │ +table_per_related.py - Illustrates a generic association which persists association │ │ │ │ objects within individual tables, each one generated to persist │ │ │ │ those objects on behalf of a particular parent class.
│ │ │ │generic_fk.py - Illustrates a so-called “generic foreign key”, in a similar fashion │ │ │ │ to that of popular frameworks such as Django, ROR, etc. This │ │ │ │ approach bypasses standard referential integrity │ │ │ │ practices, in that the “foreign key” column is not actually │ │ │ │ constrained to refer to any particular table; instead, │ │ │ │ in-application logic is used to determine which table is referenced.
│ │ │ │discriminator_on_association.py - Illustrates a mixin which provides a generic association │ │ │ │ -using a single target table and a single association table, │ │ │ │ -referred to by all parent tables. The association table │ │ │ │ -contains a “discriminator” column which determines what type of │ │ │ │ -parent object associates to each particular row in the association │ │ │ │ -table.
│ │ │ │ -table_per_association.py - Illustrates a mixin which provides a generic association │ │ │ │ via a individually generated association tables for each parent class. │ │ │ │ The associated objects themselves are persisted in a single table │ │ │ │ shared among all parents.
│ │ │ │See also
│ │ │ │ │ │ │ │Listing of files:
large_resultsets.py - In this series of tests, we are looking at time to load a large number │ │ │ │ -of very small and simple rows.
│ │ │ │ -bulk_inserts.py - This series of tests illustrates different ways to INSERT a large number │ │ │ │ -of rows in bulk.
│ │ │ │ +single_inserts.py - In this series of tests, we’re looking at a method that inserts a row │ │ │ │ +within a distinct transaction, and afterwards returns to essentially a │ │ │ │ +“closed” state. This would be analogous to an API call that starts up │ │ │ │ +a database connection, inserts the row, commits and closes.
│ │ │ │short_selects.py - This series of tests illustrates different ways to SELECT a single │ │ │ │ record by primary key
│ │ │ │__main__.py - Allows the examples/performance package to be run as a script.
│ │ │ │single_inserts.py - In this series of tests, we’re looking at a method that inserts a row │ │ │ │ -within a distinct transaction, and afterwards returns to essentially a │ │ │ │ -“closed” state. This would be analogous to an API call that starts up │ │ │ │ -a database connection, inserts the row, commits and closes.
│ │ │ │ +bulk_inserts.py - This series of tests illustrates different ways to INSERT a large number │ │ │ │ +of rows in bulk.
│ │ │ │ +large_resultsets.py - In this series of tests, we are looking at time to load a large number │ │ │ │ +of very small and simple rows.
│ │ │ │bulk_updates.py - This series of tests will illustrate different ways to UPDATE a large number │ │ │ │ of rows in bulk (under construction! there’s just one test at the moment)
│ │ │ │Several examples that illustrate the technique of intercepting changes │ │ │ │ that would be first interpreted as an UPDATE on a row, and instead turning │ │ │ │ it into an INSERT of a new row, leaving the previous row intact as │ │ │ │ a historical version.
│ │ │ │Compare to the Versioning with a History Table example which writes a │ │ │ │ history row to a separate history table.
│ │ │ │Listing of files:
versioned_map.py - A variant of the versioned_rows example built around the │ │ │ │ -concept of a “vertical table” structure, like those illustrated in │ │ │ │ -Vertical Attribute Mapping examples.
│ │ │ │ -versioned_rows.py - Illustrates a method to intercept changes on objects, turning │ │ │ │ -an UPDATE statement on a single row into an INSERT statement, so that a new │ │ │ │ -row is inserted with the new data, keeping the old row intact.
│ │ │ │ -versioned_update_old_row.py - Illustrates the same UPDATE into INSERT technique of versioned_rows.py,
│ │ │ │ but also emits an UPDATE on the old row to affect a change in timestamp.
│ │ │ │ Also includes a SessionEvents.do_orm_execute() hook to limit queries
│ │ │ │ to only the most recent version.
versioned_rows_w_versionid.py - Illustrates a method to intercept changes on objects, turning │ │ │ │ an UPDATE statement on a single row into an INSERT statement, so that a new │ │ │ │ row is inserted with the new data, keeping the old row intact.
│ │ │ │versioned_map.py - A variant of the versioned_rows example built around the │ │ │ │ +concept of a “vertical table” structure, like those illustrated in │ │ │ │ +Vertical Attribute Mapping examples.
│ │ │ │ +versioned_rows.py - Illustrates a method to intercept changes on objects, turning │ │ │ │ +an UPDATE statement on a single row into an INSERT statement, so that a new │ │ │ │ +row is inserted with the new data, keeping the old row intact.
│ │ │ │ +Illustrates “vertical table” mappings.
│ │ │ │ @@ -800,35 +800,35 @@ │ │ │ │ q = (session.query(Animal). │ │ │ │ filter(Animal.facts.any( │ │ │ │ and_(AnimalFact.key == u'weasel-like', │ │ │ │ AnimalFact.value == True)))) │ │ │ │ print('weasel-like animals', q.all()) │ │ │ │ │ │ │ │Listing of files:
dictlike-polymorphic.py - Mapping a polymorphic-valued vertical table as a dictionary.
│ │ │ │ -dictlike.py - Mapping a vertical table as a dictionary.
│ │ │ │dictlike-polymorphic.py - Mapping a polymorphic-valued vertical table as a dictionary.
│ │ │ │ +Working examples of single-table, joined-table, and concrete-table │ │ │ │ inheritance as described in Mapping Class Inheritance Hierarchies.
│ │ │ │Listing of files:
concrete.py - Concrete-table (table-per-class) inheritance example.
│ │ │ │single.py - Single-table (table-per-hierarchy) inheritance example.
│ │ │ │ -joined.py - Joined-table (table-per-subclass) inheritance example.
│ │ │ │single.py - Single-table (table-per-hierarchy) inheritance example.
│ │ │ │ +