cs-sqlalchemy-utils 20220311

Assorted utility functions to support working with SQLAlchemy.

Assorted utility functions to support working with SQLAlchemy.

Latest release 20220311: Many updates and small fixes.

Function auto_session(function)

Decorator to run a function in a session if one is not presupplied. The function function runs within a transaction, nested if the session already exists.

See with_session for details.

Class BasicTableMixin

Useful methods for most tables.

Method BasicTableMixin.__getitem__(index, *, session): Index the table by its id column.

Method BasicTableMixin.by_id(index, *, session): Index the table by its id column.

Method BasicTableMixin.lookup(*, session, **criteria): Return an iterable Query of row entities matching criteria.

Method BasicTableMixin.lookup1(*, session, **criteria): Return the row entity matching criteria, or None if no match.

Function find_json_field(column_value, field_name, *, infill=False)

Descend a JSONable Python object column_value to field_name. Return column_value (possibly infilled), final_field, final_field_name.

This supports database row columns which are JSON columns.

Parameters:

• column_value: the original value of the column
• field_name: the field within the column to locate
• infill: optional keyword parameter, default False. If true, column_value and its innards will be filled in as dicts to allow deferencing the field_name.

The field_name is a str consisting of a period ('.') separated sequence of field parts. Each field part becomes a key to index the column mapping. These keys are split into the leading field parts and the final field part, which is returned as final_field_name above.

The final_field return value above is the mapping within which final_field_value may lie and where final_field_value may be set. Note: it may not be present.

If a leading key is missing and infill is true the corresponding part of the column_value is set to an empty dictionary in order to allow deferencing the leading key. This includes the case when column_value itself is None, which is why the column_value is part of the return.

If a leading key is missing and infill is false this function will raise a KeyError for the portion of the field_name which failed.

Examples:

>>> find_json_field({'a':{'b':{}}}, 'a.b')
({'a': {'b': {}}}, {'b': {}}, 'b')
>>> find_json_field({'a':{}}, 'a.b')
({'a': {}}, {}, 'b')
>>> find_json_field({'a':{'b':{}}}, 'a.b.c.d')
Traceback (most recent call last):
    ...
KeyError: 'a.b.c'
>>> find_json_field({'a':{'b':{}}}, 'a.b.c.d', infill=True)
({'a': {'b': {'c': {}}}}, {}, 'd')
>>> find_json_field(None, 'a.b.c.d')
Traceback (most recent call last):
    ...
KeyError: 'a'
>>> find_json_field(None, 'a.b.c.d', infill=True)
({'a': {'b': {'c': {}}}}, {}, 'd')

Function get_json_field(column_value, field_name, *, default=None)

Return the value of field_name from column_value or a defaault if the field is not present.

Parameters:

• column_value: the original value of the column
• field_name: the field within the column to locate
• default: default value to return if the field is not present, default: None

Examples:

>>> get_json_field({'a': 1}, 'a')
1
>>> get_json_field({'b': 1}, 'a')
>>> get_json_field({'a': {}}, 'a.b')
>>> get_json_field({'a': {'b': 2}}, 'a.b')
2

Class HasIdMixin

Include an "id" Column as the primary key.

Function json_column(*da, **dkw)

Class decorator to declare a virtual column name on a table where the value resides inside a JSON column of the table.

Parameters:

• cls: the class to annotate
• attr: the virtual column name to present as a row attribute
• json_field_name: the field within the JSON column used to store this value, default the same as attr
• json_column_name: the name of the associated JSON column, default 'info'
• default: the default value returned by the getter if the field is not present, default None

Example use:

Base = declarative_base()
...
@json_column('virtual_name', 'json.field.name')
class TableClass(Base):
  ...

This annotates the class with a .virtual_name property which can be accessed or set, accessing or modifying the associated JSON column (in this instance, the column info, accessing info['json']['field']['name']).

Function log_level(*da, **dkw)

Decorator for functions which wraps calls to the function in a context manager, optionally supplying the context as the first argument to the called function.

Class ORM(cs.resources.MultiOpenMixin, cs.context.ContextManagerMixin)

A convenience base class for an ORM class.

This defines a .Base attribute which is a new DeclarativeBase and provides various Session related convenience methods. It is also a MultiOpenMixin subclass supporting nested open/close sequences and use as a context manager.

Method ORM.__init__(self, *a, **kw): Initialise the ORM.

If serial_sessions is true (default False) then allocate a lock to serialise session allocation. This might be chosen with SQL backends which do not support concurrent sessions such as SQLite.

In the case of SQLite there's a small inbuilt timeout in an attempt to serialise transactions but it is possible to exceed it easily and recovery is usually infeasible. Instead we use the serial_sessions option to obtain a mutex before allocating a session.

Method ORM.arranged_session(self, *a, **kw): Arrange a new session for this Thread.

Method ORM.declare_schema(self): Declare the database schema / ORM mapping. This just defines the relation types etc. It does not act on the database itself. It is called automatically at the end of __init__.

Example:

def declare_schema(self):
  """ Define the database schema / ORM mapping.
  """
  orm = self
  Base = self.Base
  class Entities(
  ........
  self.entities = Entities

After this, methods can access the example Entities relation as self.entites.

Property ORM.default_session: The current per-Thread session.

Property ORM.engine: SQLAlchemy engine, made on demand.

Method ORM.startup_shutdown(self): Default startup/shutdown context manager.

This base method operates a lockfile to manage concurrent access by other programmes (which would also need to honour this file). If you actually expect this to be common you should try to keep the ORM "open" as briefly as possible. The lock file is only operated if self.db_fspath, current set only for filesystem SQLite database URLs.

Function orm_auto_session(method)

Decorator to run a method in a session derived from self.orm if a session is not presupplied. Intended to assist classes with a .orm attribute.

See with_session for details.

Function set_json_field(column_value, field_name, value, *, infill=False)

Set a new value for field_name of column_value. Return the new column_value.

Parameters:

• column_value: the original value of the column
• field_name: the field within the column to locate
• value: the value to store as field_name
• infill: optional keyword parameter, default False. If true, column_value and its innards will be filled in as dicts to allow deferencing the field_name.

As with find_json_field, a true infill may modify column_value to provide field_name which is why this function returns the new column_value.

Examples:

>>> set_json_field({'a': 2}, 'a', 3)
{'a': 3}
>>> set_json_field({'a': 2, 'b': {'c': 5}}, 'b.c', 4)
{'a': 2, 'b': {'c': 4}}
>>> set_json_field({'a': 2}, 'b.c', 4)
Traceback (most recent call last):
    ...
KeyError: 'b'
>>> set_json_field({'a': 2}, 'b.c', 4, infill=True)
{'a': 2, 'b': {'c': 4}}
>>> set_json_field(None, 'b.c', 4, infill=True)
{'b': {'c': 4}}

Class SQLAState(cs.threads.State, _thread._local)

Thread local state for SQLAlchemy ORM and session.

Method SQLAState.auto_session(self, *, orm=None): Context manager to use the current session if not None, otherwise to make one using orm or self.orm.

Method SQLAState.new_session(self, *, orm=None): Context manager to create a new session from orm or self.orm.

Function using_session(orm=None, session=None)

A context manager to prepare an SQLAlchemy session for use by a suite.

Parameters:

• orm: optional reference ORM, an object with a .session() method for creating a new session. Default: if needed, obtained from the global state.orm.
• session: optional existing session. Default: the global state.session if not None, otherwise created by orm.session().

If a new session is created, the new session and reference ORM are pushed onto the globals state.session and state.orm respectively.

If an existing session is reused, the suite runs within a savepoint from session.begin_nested().

Function with_orm(function, *a, orm=None, **kw)

Call function with the supplied orm in the shared state.

Function with_session(function, *a, orm=None, session=None, **kw)

Call function(*a,session=session,**kw), creating a session if required. The function function runs within a transaction, nested if the session already exists. If a new session is created it is set as the default session in the shared state.

This is the inner mechanism of @auto_session and ORM.auto_session.

Parameters:

• function: the function to call
• a: the positional parameters
• orm: optional ORM class with a .session() context manager method such as the ORM base class supplied by this module.
• session: optional existing ORM session
• kw: other keyword arguments, passed to function

One of orm or session must be not None; if session is None then one is made from orm.session() and used as a context manager.

The session is also passed to function as the keyword parameter session to support nested calls.

Release Log

Release 20220311: Many updates and small fixes.

Release 20210420:

• ORM: drop .Session from docstring, no longer used.
• Rename ORM.sessionmaker to ORM._sessionmaker, not for public use.
• ORM: replace session with arranged_session, which allocates a session in conformance with ORM.serial_sessions (serial sessions are used with SQLite).
• Drop @ORM.auto_session and @ORM.orm_method decorators, no longer used.
• SQLAState.new_session: use orm.arranged_session(), use begin_nested(); SQLAState.auto_session: use begin_nested().

Release 20210322: Delete escaped debug code which issued a RuntimeError.

Release 20210321:

• Default session support, particularly though an ORM's .sqla_state per-Thread state object - this allows removal of a lot of plumbing and @auto_session decoration.
• Support for serialised sessions, for db backend where only one session may be active at a time; this brings easy support for multithreaded SQLite access.

Release 20210306:

• Rename _state to state, making it public.
• Some other internal changes.

Release 20201025:

• New BasicTableMixin and HasIdMixin classes with useful methods and a typical id Column respectively.
• Assorted fixes and improvements.

Release 20190830.1: Have the decorators set .module.

Release 20190830: @json_column: small docstring improvement.

Release 20190829:

• Bugfix @json_column setter: mark the column as modified for the ORM.
• New push_log_level context manager and @log_level decorator to temporarily change the SQLAlchemy logging handler level.

Release 20190812:

• Make ORM a MultiOpenMixin.
• get_json_field: use forgotten default parameter.
• Other minor changes.

Release 20190526:

• Support for virtual columns mapped to a JSON column interior value:
• New functions find_json_field, get_json_field, set_json_field.
• New decorator @json_column for declaritive_base tables.

Release 20190517:

• Make ORM._Session private session factory the public ORM.Session factory for external use.
• with_session: preexisting sessions still trigger a session.begin_nested, removes flush/commit tension elsewhere.

Release 20190403:

• Rename @ORM.orm_auto_session to @ORM.auto_session.
• New @orm_auto_session decorator for methods of objects with a .orm attribute.

Release 20190319.1: Initial release. ORM base class, @auto_session decorator.