│ │ │ │
Large Collections
│ │ │ │
Large collection example.
│ │ │ │
Illustrates the options to use with
│ │ │ │ @@ -389,33 +389,33 @@
│ │ │ │
│ │ │ │
│ │ │ │
File Listing
│ │ │ │
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_updates.py - This series of tests illustrates different ways to UPDATE a large number
│ │ │ │ of rows in bulk.
│ │ │ │ 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.
│ │ │ │ -bulk_inserts.py - This series of tests illustrates different ways to INSERT a large number
│ │ │ │ of rows in bulk.
│ │ │ │ __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.
│ │ │ │ large_resultsets.py - In this series of tests, we are looking at time to load a large number
│ │ │ │ -of very small and simple rows.
│ │ │ │ -
│ │ │ │
│ │ │ │
│ │ │ │
│ │ │ │
Running all tests with time
│ │ │ │
This is the default form of run:
│ │ │ │
$ python -m examples.performance single_inserts
│ │ │ │ @@ -557,22 +557,22 @@
│ │ │ │
│ │ │ │
│ │ │ │
Relationship Join Conditions
│ │ │ │
Examples of various relationship() configurations,
│ │ │ │ which make use of the primaryjoin argument to compose special types
│ │ │ │ of join conditions.
│ │ │ │
Listing of files:
│ │ │ │ -threeway.py - Illustrate a “three way join” - where a primary table joins to a remote
│ │ │ │ -table via an association table, but then the primary table also needs
│ │ │ │ -to refer to some columns in the remote table directly.
│ │ │ │ -cast.py - Illustrate a relationship() that joins two columns where those
│ │ │ │ columns are not of the same type, and a CAST must be used on the SQL
│ │ │ │ side in order to match them.
│ │ │ │ threeway.py - Illustrate a “three way join” - where a primary table joins to a remote
│ │ │ │ +table via an association table, but then the primary table also needs
│ │ │ │ +to refer to some columns in the remote table directly.
│ │ │ │ +
│ │ │ │
│ │ │ │
│ │ │ │
│ │ │ │
Space Invaders
│ │ │ │
A Space Invaders game using SQLite as the state machine.
│ │ │ │
Originally developed in 2012. Adapted to work in Python 3.
│ │ │ │ @@ -702,48 +702,48 @@
│ │ │ │
assert type(other) is SomeClass and other.id == self.id
│ │ │ │
│ │ │ │
Above, if two instance of SomeClass with the same version identifier
│ │ │ │ are updated and sent to the database for UPDATE concurrently, if the database
│ │ │ │ isolation level allows the two UPDATE statements to proceed, one will fail
│ │ │ │ because it no longer is against the last known version identifier.
│ │ │ │
Listing of files:
│ │ │ │ -history_meta.py - Versioned mixin class and other utilities.
│ │ │ │ -test_versioning.py - Unit tests illustrating usage of the history_meta.py
│ │ │ │ module functions.
│ │ │ │ history_meta.py - Versioned mixin class and other utilities.
│ │ │ │ +
│ │ │ │
│ │ │ │
│ │ │ │
│ │ │ │
Versioning using Temporal Rows
│ │ │ │
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_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_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_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_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 QueryEvents.before_compile() 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_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.
│ │ │ │ -
│ │ │ │
│ │ │ │
│ │ │ │
│ │ │ │
Special APIs
│ │ │ │
│ │ │ │
Attribute Instrumentation
│ │ │ │
Examples illustrating modifications to SQLAlchemy’s attribute management
│ │ │ │ system.
│ │ │ │
Listing of files:
│ │ │ │ +listen_for_events.py - Illustrates how to attach events to all instrumented attributes
│ │ │ │ +and listen for change events.
│ │ │ │ +active_column_defaults.py - Illustrates use of the AttributeEvents.init_scalar()
│ │ │ │ event, in conjunction with Core column defaults to provide
│ │ │ │ ORM objects that automatically produce the default value
│ │ │ │ when an un-set attribute is accessed.
│ │ │ │ listen_for_events.py - Illustrates how to attach events to all instrumented attributes
│ │ │ │ -and listen for change events.
│ │ │ │ -custom_management.py - Illustrates customized class instrumentation, using
│ │ │ │ the sqlalchemy.ext.instrumentation extension package.
│ │ │ │
│ │ │ │
│ │ │ │
│ │ │ │
│ │ │ │ ├── html2text {}
│ │ │ │ │ @@ -75,24 +75,24 @@
│ │ │ │ │ Listing of files:
│ │ │ │ │ * adjacency_list.py
│ │ │ │ │ **** Associations¶ ****
│ │ │ │ │ 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:
│ │ │ │ │ - * proxied_association.py - Same example as basic_association, adding in
│ │ │ │ │ - usage of sqlalchemy.ext.associationproxy to make explicit references to
│ │ │ │ │ - OrderItem optional.
│ │ │ │ │ + * 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.
│ │ │ │ │ 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.
│ │ │ │ │ **** Directed Graphs¶ ****
│ │ │ │ │ An example of persistence for a directed graph structure. The graph is stored
│ │ │ │ │ as a collection of edges, each referencing both a “lower” and an “upper” node
│ │ │ │ │ in a table of nodes. Basic persistence and querying for lower- and upper-
│ │ │ │ │ neighbors are illustrated:
│ │ │ │ │ n2 = Node(2)
│ │ │ │ │ n5 = Node(5)
│ │ │ │ │ @@ -114,31 +114,31 @@
│ │ │ │ │ Supplier, both 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:
│ │ │ │ │ - * 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.
│ │ │ │ │ -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.
│ │ │ │ │ + * 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.
│ │ │ │ │ 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.
│ │ │ │ │ +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.
│ │ │ │ │ **** Large Collections¶ ****
│ │ │ │ │ Large collection example.
│ │ │ │ │ Illustrates the options to use with relationship() when the list of related
│ │ │ │ │ objects is very large, including:
│ │ │ │ │ * “dynamic” relationships which query slices of data as accessed
│ │ │ │ │ * how to use ON DELETE CASCADE in conjunction with passive_deletes=True to
│ │ │ │ │ greatly improve the performance of related collection deletion.
│ │ │ │ │ @@ -196,27 +196,27 @@
│ │ │ │ │ $ python -m examples.performance bulk_inserts \
│ │ │ │ │ --dburl mysql+mysqldb://scott:tiger@localhost/test \
│ │ │ │ │ --profile --num 1000
│ │ │ │ │ See also
│ │ │ │ │ How_can_I_profile_a_SQLAlchemy_powered_application?
│ │ │ │ │ *** File Listing¶ ***
│ │ │ │ │ Listing of files:
│ │ │ │ │ - * bulk_updates.py - This series of tests illustrates different ways to
│ │ │ │ │ - UPDATE 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 illustrates different ways to UPDATE a
│ │ │ │ │ +large number of rows in bulk.
│ │ │ │ │ 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.
│ │ │ │ │ bulk_inserts.py - This series of tests illustrates different ways to INSERT a
│ │ │ │ │ large number of rows in bulk.
│ │ │ │ │ +__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.
│ │ │ │ │ -large_resultsets.py - In this series of tests, we are looking at time to load a
│ │ │ │ │ -large number of very small and simple rows.
│ │ │ │ │ *** Running all tests with time¶ ***
│ │ │ │ │ This is the default form of run:
│ │ │ │ │ $ python -m examples.performance single_inserts
│ │ │ │ │ Tests to run: test_orm_commit, test_bulk_save,
│ │ │ │ │ test_bulk_insert_dictionaries, test_core,
│ │ │ │ │ test_core_query_caching, test_dbapi_raw_w_connect,
│ │ │ │ │ test_dbapi_raw_w_pool
│ │ │ │ │ @@ -353,20 +353,20 @@
│ │ │ │ │ total time 2.754592 sec
│ │ │ │ │ test_subqueryload : load everything, subquery eager loading. (1000 iterations);
│ │ │ │ │ total time 2.977696 sec
│ │ │ │ │ **** Relationship Join Conditions¶ ****
│ │ │ │ │ Examples of various relationship() configurations, which make use of the
│ │ │ │ │ primaryjoin argument to compose special types of join conditions.
│ │ │ │ │ Listing of files:
│ │ │ │ │ - * threeway.py - Illustrate a “three way join” - where a primary table joins
│ │ │ │ │ - to a remote table via an association table, but then the primary table
│ │ │ │ │ - also needs to refer to some columns in the remote table directly.
│ │ │ │ │ -cast.py - Illustrate a relationship() that joins two columns where those
│ │ │ │ │ -columns are not of the same type, and a CAST must be used on the SQL side in
│ │ │ │ │ -order to match them.
│ │ │ │ │ + * cast.py - Illustrate a relationship() that joins two columns where those
│ │ │ │ │ + columns are not of the same type, and a CAST must be used on the SQL side
│ │ │ │ │ + in order to match them.
│ │ │ │ │ +threeway.py - Illustrate a “three way join” - where a primary table joins to a
│ │ │ │ │ +remote table via an association table, but then the primary table also needs to
│ │ │ │ │ +refer to some columns in the remote table directly.
│ │ │ │ │ **** Space Invaders¶ ****
│ │ │ │ │ A Space Invaders game using SQLite as the state machine.
│ │ │ │ │ Originally developed in 2012. Adapted to work in Python 3.
│ │ │ │ │ Runs in a textual console using ASCII art.
│ │ │ │ │ [orm/space_invaders.jpg]
│ │ │ │ │ To run:
│ │ │ │ │ python -m examples.space_invaders.space_invaders
│ │ │ │ │ @@ -469,38 +469,39 @@
│ │ │ │ │ def __eq__(self, other):
│ │ │ │ │ assert type(other) is SomeClass and other.id == self.id
│ │ │ │ │ Above, if two instance of SomeClass with the same version identifier are
│ │ │ │ │ updated and sent to the database for UPDATE concurrently, if the database
│ │ │ │ │ isolation level allows the two UPDATE statements to proceed, one will fail
│ │ │ │ │ because it no longer is against the last known version identifier.
│ │ │ │ │ Listing of files:
│ │ │ │ │ - * history_meta.py - Versioned mixin class and other utilities.
│ │ │ │ │ -test_versioning.py - Unit tests illustrating usage of the history_meta.py
│ │ │ │ │ -module functions.
│ │ │ │ │ + * test_versioning.py - Unit tests illustrating usage of the history_meta.py
│ │ │ │ │ + module functions.
│ │ │ │ │ +history_meta.py - Versioned mixin class and other utilities.
│ │ │ │ │ *** Versioning using Temporal Rows¶ ***
│ │ │ │ │ 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_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_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_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_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 QueryEvents.before_compile() 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_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.
│ │ │ │ │ **** Vertical Attribute Mapping¶ ****
│ │ │ │ │ Illustrates “vertical table” mappings.
│ │ │ │ │ A “vertical table” refers to a technique where individual attributes of an
│ │ │ │ │ object are stored as distinct rows in a table. The “vertical table” technique
│ │ │ │ │ is used to persist objects which can have a varied set of attributes, at the
│ │ │ │ │ expense of simple query control and brevity. It is commonly found in content/
│ │ │ │ │ document management systems in order to represent user-created structures
│ │ │ │ │ @@ -519,36 +520,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.py - Mapping a vertical table as a dictionary.
│ │ │ │ │ +dictlike-polymorphic.py - Mapping a polymorphic-valued vertical table as a
│ │ │ │ │ +dictionary.
│ │ │ │ │ ***** Inheritance Mapping Recipes¶ *****
│ │ │ │ │ **** Basic Inheritance Mappings¶ ****
│ │ │ │ │ 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.
│ │ │ │ │ ***** Special APIs¶ *****
│ │ │ │ │ **** Attribute Instrumentation¶ ****
│ │ │ │ │ Examples illustrating modifications to SQLAlchemy’s attribute management
│ │ │ │ │ system.
│ │ │ │ │ Listing of files:
│ │ │ │ │ - * active_column_defaults.py - Illustrates use of the
│ │ │ │ │ - AttributeEvents.init_scalar() event, in conjunction with Core column
│ │ │ │ │ - defaults to provide ORM objects that automatically produce the default
│ │ │ │ │ - value when an un-set attribute is accessed.
│ │ │ │ │ -listen_for_events.py - Illustrates how to attach events to all instrumented
│ │ │ │ │ -attributes and listen for change events.
│ │ │ │ │ + * listen_for_events.py - Illustrates how to attach events to all
│ │ │ │ │ + instrumented attributes and listen for change events.
│ │ │ │ │ +active_column_defaults.py - Illustrates use of the AttributeEvents.init_scalar
│ │ │ │ │ +() event, in conjunction with Core column defaults to provide ORM objects that
│ │ │ │ │ +automatically produce the default value when an un-set attribute is accessed.
│ │ │ │ │ custom_management.py - Illustrates customized class instrumentation, using the
│ │ │ │ │ sqlalchemy.ext.instrumentation extension package.
│ │ │ │ │ **** Horizontal Sharding¶ ****
│ │ │ │ │ A basic example of using the SQLAlchemy Sharding API. Sharding refers to
│ │ │ │ │ horizontally scaling data across multiple databases.
│ │ │ │ │ The basic components of a “sharded” mapping are:
│ │ │ │ │ * multiple databases, each assigned a ‘shard id’