$ python -m examples.performance single_inserts
│ │ │ │ @@ -751,31 +751,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
│ │ │ │ +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.
│ │ │ │ -
│ │ │ │
│ │ │ │
│ │ │ │
│ │ │ │
│ │ │ │ Vertical Attribute Mapping
│ │ │ │ Illustrates “vertical table” mappings.
│ │ │ │ @@ -837,20 +837,20 @@
│ │ │ │ system.
│ │ │ │ Listing of files:
│ │ │ │
│ │ │ │
│ │ │ │
│ │ │ │ Horizontal Sharding
│ │ │ │ A basic example of using the SQLAlchemy Sharding API.
│ │ │ │ Sharding refers to horizontally scaling data across multiple
│ │ │ │ @@ -881,22 +881,22 @@
│ │ │ │ more plain-spoken alternative, the “distinct entity” approach
│ │ │ │ is a simple method of assigning objects to different tables (and potentially
│ │ │ │ database nodes) in an explicit way - described on the wiki at
│ │ │ │ EntityName.
│ │ │ │ Listing of files:
│ │ │ │ separate_databases.py - Illustrates sharding using distinct SQLite databases.
│ │ │ │ separate_tables.py - Illustrates sharding using a single SQLite database, that will however
│ │ │ │ +have multiple tables using a naming convention.
│ │ │ │ +separate_schema_translates.py - Illustrates sharding using a single database with multiple schemas,
│ │ │ │ where a different “schema_translates_map” can be used for each shard.
│ │ │ │ asyncio.py - Illustrates sharding API used with asyncio.
│ │ │ │ separate_tables.py - Illustrates sharding using a single SQLite database, that will however
│ │ │ │ -have multiple tables using a naming convention.
│ │ │ │ -
│ │ │ │
│ │ │ │
│ │ │ │
│ │ │ │
│ │ │ │ Extending the ORM
│ │ │ │
│ │ │ │ ├── html2text {}
│ │ │ │ │ @@ -109,25 +109,24 @@
│ │ │ │ │ each via an association object called “OrderItem”
│ │ │ │ │ _p_r_o_x_i_e_d___a_s_s_o_c_i_a_t_i_o_n_._p_y - Same example as basic_association, adding in usage of
│ │ │ │ │ _s_q_l_a_l_c_h_e_m_y_._e_x_t_._a_s_s_o_c_i_a_t_i_o_n_p_r_o_x_y to make explicit references to OrderItem
│ │ │ │ │ optional.
│ │ │ │ │ ******** AAssyynncciioo IInntteeggrraattiioonn_?¶ ********
│ │ │ │ │ Examples illustrating the asyncio engine feature of SQLAlchemy.
│ │ │ │ │ Listing of files:
│ │ │ │ │ - * _g_r_e_e_n_l_e_t___o_r_m_._p_y - Illustrates use of the
│ │ │ │ │ - sqlalchemy.ext.asyncio.AsyncSession object for asynchronous ORM use,
│ │ │ │ │ - including the optional run_sync() method.
│ │ │ │ │ -_a_s_y_n_c___o_r_m_._p_y - Illustrates use of the sqlalchemy.ext.asyncio.AsyncSession
│ │ │ │ │ -object for asynchronous ORM use.
│ │ │ │ │ -_b_a_s_i_c_._p_y - Illustrates the asyncio engine / connection interface.
│ │ │ │ │ + * _a_s_y_n_c___o_r_m_._p_y - Illustrates use of the sqlalchemy.ext.asyncio.AsyncSession
│ │ │ │ │ + object for asynchronous ORM use.
│ │ │ │ │ +_g_r_e_e_n_l_e_t___o_r_m_._p_y - Illustrates use of the sqlalchemy.ext.asyncio.AsyncSession
│ │ │ │ │ +object for asynchronous ORM use, including the optional run_sync() method.
│ │ │ │ │ _g_a_t_h_e_r___o_r_m___s_t_a_t_e_m_e_n_t_s_._p_y - Illustrates how to run many statements concurrently
│ │ │ │ │ using asyncio.gather() along many asyncio database connections, merging ORM
│ │ │ │ │ results into a single AsyncSession.
│ │ │ │ │ _a_s_y_n_c___o_r_m___w_r_i_t_e_o_n_l_y_._p_y - Illustrates using wwrriittee oonnllyy rreellaattiioonnsshhiippss for simpler
│ │ │ │ │ handling of ORM collections under asyncio.
│ │ │ │ │ +_b_a_s_i_c_._p_y - Illustrates the asyncio engine / connection interface.
│ │ │ │ │ ******** DDiirreecctteedd GGrraapphhss_?¶ ********
│ │ │ │ │ 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)
│ │ │ │ │ @@ -149,31 +148,31 @@
│ │ │ │ │ Supplier, both subclassing the HasAddresses mixin, which ensures that the
│ │ │ │ │ parent class is provided with an addresses collection which contains Address
│ │ │ │ │ objects.
│ │ │ │ │ The _d_i_s_c_r_i_m_i_n_a_t_o_r___o_n___a_s_s_o_c_i_a_t_i_o_n_._p_y and _g_e_n_e_r_i_c___f_k_._p_y scripts are modernized
│ │ │ │ │ versions of recipes presented in the 2007 blog post _P_o_l_y_m_o_r_p_h_i_c_ _A_s_s_o_c_i_a_t_i_o_n_s
│ │ │ │ │ _w_i_t_h_ _S_Q_L_A_l_c_h_e_m_y.
│ │ │ │ │ Listing of files:
│ │ │ │ │ - * _t_a_b_l_e___p_e_r___r_e_l_a_t_e_d_._p_y - 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.
│ │ │ │ │ -_d_i_s_c_r_i_m_i_n_a_t_o_r___o_n___a_s_s_o_c_i_a_t_i_o_n_._p_y - 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.
│ │ │ │ │ -_t_a_b_l_e___p_e_r___a_s_s_o_c_i_a_t_i_o_n_._p_y - 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.
│ │ │ │ │ + * _d_i_s_c_r_i_m_i_n_a_t_o_r___o_n___a_s_s_o_c_i_a_t_i_o_n_._p_y - 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.
│ │ │ │ │ _g_e_n_e_r_i_c___f_k_._p_y - 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.
│ │ │ │ │ +_t_a_b_l_e___p_e_r___r_e_l_a_t_e_d_._p_y - 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.
│ │ │ │ │ +_t_a_b_l_e___p_e_r___a_s_s_o_c_i_a_t_i_o_n_._p_y - 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.
│ │ │ │ │ ******** MMaatteerriiaalliizzeedd PPaatthhss_?¶ ********
│ │ │ │ │ Illustrates the “materialized paths” pattern for hierarchical data using the
│ │ │ │ │ SQLAlchemy ORM.
│ │ │ │ │ Listing of files:
│ │ │ │ │ * _m_a_t_e_r_i_a_l_i_z_e_d___p_a_t_h_s_._p_y - Illustrates the “materialized paths” pattern.
│ │ │ │ │ ******** NNeesstteedd SSeettss_?¶ ********
│ │ │ │ │ Illustrates a rudimentary way to implement the “nested sets” pattern for
│ │ │ │ │ @@ -221,29 +220,29 @@
│ │ │ │ │ $ python -m examples.performance bulk_inserts \
│ │ │ │ │ --dburl mysql+mysqldb://scott:tiger@localhost/test \
│ │ │ │ │ --profile --num 1000
│ │ │ │ │ See also
│ │ │ │ │ _H_o_w_ _c_a_n_ _I_ _p_r_o_f_i_l_e_ _a_ _S_Q_L_A_l_c_h_e_m_y_ _p_o_w_e_r_e_d_ _a_p_p_l_i_c_a_t_i_o_n_?
│ │ │ │ │ ****** FFiillee LLiissttiinngg_?¶ ******
│ │ │ │ │ Listing of files:
│ │ │ │ │ - * _____m_a_i_n_____._p_y - Allows the examples/performance package to be run as a
│ │ │ │ │ - script.
│ │ │ │ │ -_l_a_r_g_e___r_e_s_u_l_t_s_e_t_s_._p_y - In this series of tests, we are looking at time to load a
│ │ │ │ │ -large number of very small and simple rows.
│ │ │ │ │ -_s_i_n_g_l_e___i_n_s_e_r_t_s_._p_y - 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.
│ │ │ │ │ + * _s_i_n_g_l_e___i_n_s_e_r_t_s_._p_y - 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.
│ │ │ │ │ _s_h_o_r_t___s_e_l_e_c_t_s_._p_y - This series of tests illustrates different ways to SELECT a
│ │ │ │ │ single record by primary key
│ │ │ │ │ _b_u_l_k___i_n_s_e_r_t_s_._p_y - This series of tests illustrates different ways to INSERT a
│ │ │ │ │ large number of rows in bulk.
│ │ │ │ │ _b_u_l_k___u_p_d_a_t_e_s_._p_y - 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)
│ │ │ │ │ +_____m_a_i_n_____._p_y - Allows the examples/performance package to be run as a script.
│ │ │ │ │ +_l_a_r_g_e___r_e_s_u_l_t_s_e_t_s_._p_y - In this series of tests, we are looking at time to load a
│ │ │ │ │ +large number of very small and simple rows.
│ │ │ │ │ ****** RRuunnnniinngg aallll tteessttss wwiitthh ttiimmee_?¶ ******
│ │ │ │ │ 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
│ │ │ │ │ @@ -469,28 +468,27 @@
│ │ │ │ │ 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 _V_e_r_s_i_o_n_i_n_g_ _w_i_t_h_ _a_ _H_i_s_t_o_r_y_ _T_a_b_l_e example which writes a history
│ │ │ │ │ row to a separate history table.
│ │ │ │ │ Listing of files:
│ │ │ │ │ - * _v_e_r_s_i_o_n_e_d___u_p_d_a_t_e___o_l_d___r_o_w_._p_y - Illustrates the same UPDATE into INSERT
│ │ │ │ │ - technique of versioned_rows.py, but also emits an UPDATE on the oolldd row
│ │ │ │ │ - to affect a change in timestamp. Also includes a
│ │ │ │ │ - _S_e_s_s_i_o_n_E_v_e_n_t_s_._d_o___o_r_m___e_x_e_c_u_t_e_(_) hook to limit queries to only the most
│ │ │ │ │ - recent version.
│ │ │ │ │ + * _v_e_r_s_i_o_n_e_d___r_o_w_s_._p_y - 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.
│ │ │ │ │ +_v_e_r_s_i_o_n_e_d___u_p_d_a_t_e___o_l_d___r_o_w_._p_y - Illustrates the same UPDATE into INSERT technique
│ │ │ │ │ +of versioned_rows.py, but also emits an UPDATE on the oolldd row to affect a
│ │ │ │ │ +change in timestamp. Also includes a _S_e_s_s_i_o_n_E_v_e_n_t_s_._d_o___o_r_m___e_x_e_c_u_t_e_(_) hook to
│ │ │ │ │ +limit queries to only the most recent version.
│ │ │ │ │ _v_e_r_s_i_o_n_e_d___r_o_w_s___w___v_e_r_s_i_o_n_i_d_._p_y - 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.
│ │ │ │ │ _v_e_r_s_i_o_n_e_d___m_a_p_._p_y - A variant of the versioned_rows example built around the
│ │ │ │ │ concept of a “vertical table” structure, like those illustrated in _V_e_r_t_i_c_a_l
│ │ │ │ │ _A_t_t_r_i_b_u_t_e_ _M_a_p_p_i_n_g examples.
│ │ │ │ │ -_v_e_r_s_i_o_n_e_d___r_o_w_s_._p_y - 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.
│ │ │ │ │ ******** VVeerrttiiccaall AAttttrriibbuuttee MMaappppiinngg_?¶ ********
│ │ │ │ │ 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
│ │ │ │ │ @@ -529,18 +527,18 @@
│ │ │ │ │ Examples illustrating modifications to SQLAlchemy’s attribute management
│ │ │ │ │ system.
│ │ │ │ │ Listing of files:
│ │ │ │ │ * _a_c_t_i_v_e___c_o_l_u_m_n___d_e_f_a_u_l_t_s_._p_y - Illustrates use of the
│ │ │ │ │ _A_t_t_r_i_b_u_t_e_E_v_e_n_t_s_._i_n_i_t___s_c_a_l_a_r_(_) event, in conjunction with Core column
│ │ │ │ │ defaults to provide ORM objects that automatically produce the default
│ │ │ │ │ value when an un-set attribute is accessed.
│ │ │ │ │ -_c_u_s_t_o_m___m_a_n_a_g_e_m_e_n_t_._p_y - Illustrates customized class instrumentation, using the
│ │ │ │ │ -_s_q_l_a_l_c_h_e_m_y_._e_x_t_._i_n_s_t_r_u_m_e_n_t_a_t_i_o_n extension package.
│ │ │ │ │ _l_i_s_t_e_n___f_o_r___e_v_e_n_t_s_._p_y - Illustrates how to attach events to all instrumented
│ │ │ │ │ attributes and listen for change events.
│ │ │ │ │ +_c_u_s_t_o_m___m_a_n_a_g_e_m_e_n_t_._p_y - Illustrates customized class instrumentation, using the
│ │ │ │ │ +_s_q_l_a_l_c_h_e_m_y_._e_x_t_._i_n_s_t_r_u_m_e_n_t_a_t_i_o_n extension package.
│ │ │ │ │ ******** HHoorriizzoonnttaall SShhaarrddiinngg_?¶ ********
│ │ │ │ │ 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 _E_n_g_i_n_e instances, each assigned a “shard id”. These _E_n_g_i_n_e
│ │ │ │ │ instances may refer to different databases, or different schemas /
│ │ │ │ │ accounts within the same database, or they can even be differentiated
│ │ │ │ │ @@ -563,20 +561,20 @@
│ │ │ │ │ issue of organizing instances among multiple databases. For a more plain-spoken
│ │ │ │ │ alternative, the “distinct entity” approach is a simple method of assigning
│ │ │ │ │ objects to different tables (and potentially database nodes) in an explicit way
│ │ │ │ │ - described on the wiki at _E_n_t_i_t_y_N_a_m_e.
│ │ │ │ │ Listing of files:
│ │ │ │ │ * _s_e_p_a_r_a_t_e___d_a_t_a_b_a_s_e_s_._p_y - Illustrates sharding using distinct SQLite
│ │ │ │ │ databases.
│ │ │ │ │ +_s_e_p_a_r_a_t_e___t_a_b_l_e_s_._p_y - Illustrates sharding using a single SQLite database, that
│ │ │ │ │ +will however have multiple tables using a naming convention.
│ │ │ │ │ _s_e_p_a_r_a_t_e___s_c_h_e_m_a___t_r_a_n_s_l_a_t_e_s_._p_y - Illustrates sharding using a single database
│ │ │ │ │ with multiple schemas, where a different “schema_translates_map” can be used
│ │ │ │ │ for each shard.
│ │ │ │ │ _a_s_y_n_c_i_o_._p_y - Illustrates sharding API used with asyncio.
│ │ │ │ │ -_s_e_p_a_r_a_t_e___t_a_b_l_e_s_._p_y - Illustrates sharding using a single SQLite database, that
│ │ │ │ │ -will however have multiple tables using a naming convention.
│ │ │ │ │ ********** EExxtteennddiinngg tthhee OORRMM_?¶ **********
│ │ │ │ │ ******** OORRMM QQuueerryy EEvveennttss_?¶ ********
│ │ │ │ │ Recipes which illustrate augmentation of ORM SELECT behavior as used by
│ │ │ │ │ _S_e_s_s_i_o_n_._e_x_e_c_u_t_e_(_) with _2_._0_ _s_t_y_l_e use of _s_e_l_e_c_t_(_), as well as the _1_._x_ _s_t_y_l_e
│ │ │ │ │ _Q_u_e_r_y object.
│ │ │ │ │ Examples include demonstrations of the _w_i_t_h___l_o_a_d_e_r___c_r_i_t_e_r_i_a_(_) option as well as
│ │ │ │ │ the _S_e_s_s_i_o_n_E_v_e_n_t_s_._d_o___o_r_m___e_x_e_c_u_t_e_(_) hook.