│ │ │ │
Large Collections
│ │ │ │
Large collection example.
│ │ │ │ @@ -389,24 +389,24 @@
│ │ │ │
│ │ │ │
│ │ │ │
File Listing
│ │ │ │
Listing of files:
│ │ │ │ -__main__.py - Allows the examples/performance package to be run as a script.
│ │ │ │ -short_selects.py - This series of tests illustrates different ways to SELECT a single
│ │ │ │ -record by primary key
│ │ │ │ -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.
│ │ │ │ +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 illustrates different ways to UPDATE a large number
│ │ │ │ @@ -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:
│ │ │ │ -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.
│ │ │ │ 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.
│ │ │ │ +
│ │ │ │
│ │ │ │
│ │ │ │
│ │ │ │
Space Invaders
│ │ │ │
A Space Invaders game using SQLite as the state machine.
│ │ │ │
Originally developed in 2012. Adapted to work in Python 3.
│ │ │ │ @@ -719,31 +719,31 @@
│ │ │ │
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.py - Illustrates a method to intercept changes on objects, turning
│ │ │ │ +
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_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
│ │ │ │ +
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 QueryEvents.before_compile() hook to limit queries
│ │ │ │ -to only the most recent version.
│ │ │ │ -
│ │ │ │
│ │ │ │
│ │ │ │
│ │ │ │
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,21 +75,21 @@
│ │ │ │ │ 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:
│ │ │ │ │ - * 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”
│ │ │ │ │ + * 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-
│ │ │ │ │ @@ -119,26 +119,26 @@
│ │ │ │ │ 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_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.
│ │ │ │ │ +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.
│ │ │ │ │ -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.
│ │ │ │ │ +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.
│ │ │ │ │ **** 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,22 +196,22 @@
│ │ │ │ │ $ 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:
│ │ │ │ │ - * __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.
│ │ │ │ │ short_selects.py - This series of tests illustrates different ways to SELECT a
│ │ │ │ │ single record by primary key
│ │ │ │ │ -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.
│ │ │ │ │ +__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.
│ │ │ │ │ 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.
│ │ │ │ │ *** Running all tests with time¶ ***
│ │ │ │ │ @@ -354,20 +354,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:
│ │ │ │ │ - * 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.
│ │ │ │ │ + * 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.
│ │ │ │ │ **** 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
│ │ │ │ │ @@ -481,27 +481,28 @@
│ │ │ │ │ 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.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_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_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.
│ │ │ │ │ **** 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
│ │ │ │ │ @@ -520,36 +521,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’