score.sa.orm

This module builds on top of score.sa.db and allows convenient configuration of your ORM layer.

Quickstart

Create a base class:

from score.sa.orm import create_base

Storable = create_base()

All persistable classes should derive from this class, they will now automatically receive an id column and a __tablename__ declaration. The only thing left to do is to add Columns:

from .storable import Storable
from sqlalchemy import Column, String

class User(Storable):
    username = Column(String(100), nullable=False)

The module will take care of inheritance mapping, too:

from .user import User
from sqlalchemy import Column, Boolean


class Blogger(User):
    may_publish = Column(Boolean, nullable=False, default=True)

You can then create your database by calling create.

>>> from score.init import init_from_file
>>> score = init_from_file('local.conf')
>>> score.orm.create()

If you are using the score.ctx module, you can access an sqlalchemy.orm.session.Session bound to the Context object’s transaction:

>>> ctx.orm.query(User).get(1)
<User: sirlancelot>

Configuration

score.sa.orm.init(confdict, db, ctx=None)

Initializes this module acoording to our module initialization guidelines with the following configuration keys:

base

The dotted python path to the base class to configure, as interpreted by score.init.parse_dotted_path().

ctx.member db

The name of the context member, that should be registered with the configured score.ctx module (if there is one). The default value allows you to always access a valid session within a score.ctx.Context like this:

>>> ctx.orm.query(User).first()

zope_transactions False

Whether the Session should include the zope transaction extension outside of score.ctx.Context objects. Note that sessions created as context members always include this extension, since the score.ctx module makes use of zope transactions.

Details

Base Class

Database classes should derive from a base class constructed by a call to score.sa.orm.create_base(). The class will automatically determine a table name and automatically establish an id and a _type column. The following class will be assigned the table name _user (as returned by cls2tbl()) and its id column as primary key:

from score.sa.orm import create_base

Storable = create_base()

class User(Storable):
    pass

The above is equvalent to the following sqlalchemy configuration:

from score.sa.orm import IdType
from sqlalchemy import Column, String

class User(Storable):

    __tablename__ = '_user'

    __mapper_args__ = {
        'polymorphic_identity': 'user',
        'polymorphic_on': '_type',
    }

    id = Column(IdType, primary_key=True, nullable=False)
    _type = Column(String(100), nullable=False)

Note that these are just defaults: You can still configure your ORM as if you weren’t using this module at all.

Sqlalchemy Defaults

The base class will add the following class attributes automatically, unless explicitly specified. Most of the values shown here assume that the default inheritance configuration—joined-table inheritance—is used:

• A __tablename__ attribute that is determined using cls2tbl(). A class called AdminUser would thus be translated to the table name _admin_user. The reason for the leading underscore is that the name without that prefix is reserved for the database VIEW aggregating all parent tables.

• An id column. This would look something like this, if it were written explicitly in a class:

  from score.sa.orm import create_base, IdType
  from sqlalchemy import Column
  
  Storable = create_base()
  
  class User(Storable):
      id = Column(IdType, nullable=False, primary_key=True)
  
  class AdminUser(User):
      id = Column(IdType, ForeignKey('_user.id'),
                  nullable=False, primary_key=True)
  

• A _type column storing the concrete type of a table entry. This allows sqlalchemy to determine which particular python class to use for an entry in the database:

  admin = AdminUser()
  session.add(admin)
  result = session.query(User).filter(User.id == admin.id).first()
  assert isinstance(result, AdminUser)
  

  In the example above, we received an object of the correct type AdminUser at the end, although we were actually querying for User objects. Sqlalchemy was able to determine which class to use by looking up the _type value in the database.

• A __score_sa_orm__ attribute containing the class configuration as seen by this module. Have a look at the documentation of __score_sa_orm__ for details.

All of these values can be overridden manually within the class declaration. If you want your _type column to be an enumeration, for example, you can set it manually:

from score.sa.orm import create_base
from sqlalchemy import Column, Enum

Storable = create_base()

class User(Storable):
    _type = Column(Enum('user', 'admin_user'), nullable=False)

Flexible ID Type

This module also provides an sqlalchemy type to use for referencing other tables. The primary reason for this feature is a work-around of a limitation of SQLite: it only supports Integer fields as auto-incrementing ids. All other databases use the much larger BigInteger.

This means that the preferred way of referencing objets is the following:

from score.sa.orm import IdType
from sqlalchemy import Column

class User(Base):
    pass

class Article(Base):
    author_id = Column(IdType, ForeignKey('_user.id'), nullable=False)

Inheritance

Sqlalchemy supports various ways of configuring the inheritance in the database. The full list of options can be found in sqlalchemy’s documentation on inheritance mapping. But since we value programmer time over CPU time and want to avoid unnecessery optimization attempts at the early stages of a project, we would rather recommend just using joined table inheritance at the beginning—which is also the default:

  from score.sa.orm import create_base
  from sqlalchemy import Column, String

  Storable = create_base()

  class User(Storable):
      pass

  class RegisteredUser(User):
      name = Column(String, nullable=False)
      email = Column(String(200), nullable=False)

  user = session.query(RegisteredUser)\
      .filter(User.id == 18).first()

This will automatically create the member User._type, which contains the name of the table of the concrete class. If we create a RegisteredUser, the two tables will contain the following values:

> SELECT * FROM _user;
      _type      | id
-----------------+----
 user            |  1
 registered_user |  2

> SELECT * FROM _registered_user;
 id |   name  |     email
----+---------+-----------------
  2 | Mrs Bun | nospam@bun.name

If you really want to change the way inheritance is configured, you can do so using the class member __score_sa_orm__:

class User(Storable):
    __score_sa_orm__ = {
        'inheritance': 'single-table'
    }

class RegisteredUser(User):
    name = Column(String)
    email = Column(String(200))

The inheritance configuration in line 3 will instruct sqlalchemy to create a single table for all sub-classes. Note that you must not have any columns with NOT NULL constraints in any child table. Otherwise the database will raise an error for attempts to create a different type!

This configuration will now create a single table in the database containing all members:

> SELECT * FROM _user;
      _type      | id |   name  |     email
-----------------+----+---------+-----------------
 user            |  1 | NULL    | NULL
 registered_user |  2 | Mrs Bun | nospam@bun.name

It is also possible to configure a class to not support subclassing at all. This is done by assigning None as its inheritance configuration:

class User(Storable):
    __score_sa_orm__ = {
        'inheritance': None
    }

class RegisteredUser(User):  # invalid!
    name = Column(String)
    email = Column(String(200))

In this case, the second class declaration will raise an exception in line #6.

Automatic VIEWs

During creation of database tables, this module will also create a view for each class. The aim of the view is to aggregate the members of all parent classes. These views have the same name as the table, but omit the leading underscore:

> SELECT * FROM _registered_user;
 id |   name  |     email
----+---------+-----------------
  2 | Mrs Bun | nospam@bun.name

> SELECT * FROM registered_user;
      _type     | id |   name  |     email
----------------+----+---------+-----------------
registered_user |  2 | Mrs Bun | nospam@bun.name

Note that there is no member called _type in RegisteredUser, the view just joins the parent table and allows convenient access to the members as one would see them in python. The DDL statement is something like the following:

> CREATE VIEW registered_user AS
…   SELECT * FROM _user u
…   INNER JOIN _registered_user r ON u.id == r.id;

This statement depends on the inheritance configuration, of course. If we had configured single table inheritance, it would look different:

> CREATE VIEW registered_user AS
…   SELECT * FROM _user
…   WHERE _user._type == 'registered_user';

These views are strictly for humans. The ORM layer (provided by sqlalchemy) is, of course, smart enough to make as few joins as possible during queries. This shouldn’t come as a surprise, as sqlalchemy doesn’t have a clue about these views.

Fine-Grained Configuration

We have already seen that inheritance can be configured via a special class member called __score_sa_orm__. There are a few more available options while configuring the class:

• inheritance: Determines how inheritance should be configured. Valid values are:
  • joined-table - creates a table for each sub-class and joins them whenever necessary. This is the default.
  • single-table - creates a single table containing all members of all sub-classes.
  • None - the class does not support sub-classing.
• type_column: Name of the column to use to determine the class’s actual type. The column will be created automatically if it does not already exist. Defaults to _type.
• type_name: How this class should be called in the type_column. Defaults to this class’s view name.
• parent: The parent class of this class in the inheritance chain toward the base class. Note that classes deriving from the base class directly will have None. This will be determined automatically.
• base: Reference to the base class.

Note that there are very few cases where one might want to set any of these values. The safest to configure manually, and the one where deviating from the default makes any sense at all, is the inheritance configuration.

The base class will make sure that all these values are actually present in this class nonetheless:

class User(Storable):
    pass

assert User.__score_sa_orm__['inheritance'] == 'joined-table'
assert User.__score_sa_orm__['type_column'] == '_type'
assert User.__score_sa_orm__['type_name'] == 'user'
assert User.__score_sa_orm__['parent'] == None
assert User.__score_sa_orm__['base'] is Storable

Data Loading

When starting a new project, it is quite convenient to have some test data in the database. score.sa.orm addresses this need by providing a data loader, that is capable of reading yaml files. You can have a look at the example file used during the tutorial.

The format of the file is very simple: - define a section for each class:

moswblog.db.InternalUser:

• add objects to this section, giving each one a unique name:

  moswblog.db.InternalUser:
      MrTeabag:
  

• add members to each object to your liking:

  moswblog.db.InternalUser:
      MrTeabag:
          name: John Cleese
  

• since relationships are already configured via SQLAlchemy, you can reference other objects using the unique name you gave earlier:

  moswblog.db.content.Blog:
      News:
          name: News-Blog!
          owner: MrTeabag
  

That’s it! You can load the data using load_data().

Session Extensions

It is possible to extend the SQLAlchemy Session provided by this module with custom mixins. Mixin classes need to be registered via score.sa.orm.ConfiguredSaOrmModule.add_session_mixin().

One mixin is provided by this module as reference, the QueryIdsMixin.

Relationship Helpers

A common need during initial application development is the implementation of relationships. Although SQLAlchemy provides various features to support this, it provides no ready-to-use class or function for implementing m:n relationships, for example. That’s why we provide our own:

class User(Storable):
    name = Column(String, nullable=False)

class Group(Storable):
    name = Column(String, nullable=False)

UserGroup = create_relationship_class(
    User, Group, 'groups', sorted=False, duplicates=False, backref='users')

user = User('Mousebender')
group = Group('Customer')
user.groups.append(group)
session.flush()
# the database now contains an entry in the intermidiate table
# _user_group linking the objects.

API

Configuration

score.sa.orm.init(confdict, db, ctx=None)

Initializes this module acoording to our module initialization guidelines with the following configuration keys:

base

The dotted python path to the base class to configure, as interpreted by score.init.parse_dotted_path().

ctx.member db

The name of the context member, that should be registered with the configured score.ctx module (if there is one). The default value allows you to always access a valid session within a score.ctx.Context like this:

>>> ctx.orm.query(User).first()

zope_transactions False

Whether the Session should include the zope transaction extension outside of score.ctx.Context objects. Note that sessions created as context members always include this extension, since the score.ctx module makes use of zope transactions.

class score.sa.orm.ConfiguredSaOrmModule(db, ctx, Base, ctx_member, zope_transactions)

This module’s configuration class.

Base

The configured base class. Can be None if no base class was configured.

destroyable

Whether destructive operations may be performed on the database. This value will be consulted before any such operations are performed. Application developers are also advised to make use of this value appropriately.

engine

An SQLAlchemy Engine.

add_session_mixin(mixin)

Adds a mixin class to the Session obejct.

You can use this function to add arbitrary features to your database sessions:

class RestaurantSketch:

    def __init__(self, *args, **kwargs):
        # this function will receive the same arguments as the
        # base Session class (sqlalchemy.orm.session.Session)
        pass

    def dirty_fork(self):
        try:
            raise Exception('The waiter has commited suicide!')
        except Exception as e:
            raise BadPunchLineException() from e

After registering this mixin through this function, you can access its functions in every session instance:

try:
    # prepare for a bad punch line
    ctx.orm.dirty_fork()
except BadPunchLineException:
    pass  # out

This function must be called before this object is finalized.

Session

An SQLAlchemy Session class. Can be instanciated without arguments:

>>> session = dbconf.Session()
>>> session.execute('SELECT 1 FROM DUAL')

create()

Generates all necessary tables, views, triggers, sequences, etc.

Helper Functions

score.sa.orm.cls2tbl(cls)

Converts a class (or a class name) to a table name. The class name is expected to be in CamelCase. The return value will be seperated_by_underscores and prefixed with an underscore. Omitting the underscore will yield the name of the class’s view.

score.sa.orm.tbl2cls(tbl)

Inverse of cls2tbl(). Returns the name of a class.

score.sa.orm.create_base()

Returns a base class for database access objects.

score.sa.orm.create_relationship_class(cls1, cls2, member, *, classname=None, sorted=False, duplicates=True, backref=None)

Creates a class linking two given models and adds appropriate relationship properties to the classes.

At its minimum, this function requires two classes cls1 and cls2 to be linked—where cls1 is assumed to be the owning part of the relation—and the name of the member to be added to the owning class:

>>> UserGroup = create_relationship_class(User, Group, 'groups')

By default, this will create a class called UserGroup, which looks like the following:

>>> class UserGroup(Storable):
...     __score_db__: {
...         'inheritance': None
...     }
...     index = Column(Integer, nullable=False)
...     user_id = Column(IdType, nullable=False, ForeignKey('_user.id'))
...     user = relationship(Group, foreign_keys=[user_id])
...     group_id = Column(IdType, nullable=False, ForeignKey('_group.id'))
...     group = relationship(Group, foreign_keys=[group_id])

You can choose the name of the new class by passing it as the classname argument, which also has an effect on the table name.

It will also add a new member ‘groups’ to the User class, which is of type sqlalchemy.orm.properties.RelationshipProperty.

The parameter sorted decides whether the relationship is stored with a sorting ‘index’ column.

It is possible to declare that the relationship does not accept duplicates, in which case the table will also have a UniqueConstraint on [user_id, group_id]

Providing a backref member, will also add a relationship property to the second class with the given name.

score.sa.orm.create_collection_class(owner, member, column, *, sorted=True, duplicates=True)

Creates a class for holding the values of a collection in given owner class.

The given owner class will be updated to have a new member with given name, which is a list containing elements as described by column:

>>> create_collection_class(Group, 'permissions',
...                         Column(PermissionEnum.db_type(), nullable=False)

Group objects will now have a member called ‘permissions’, which contain a sorted list of PermissionEnum values.

See create_relationship_class() for the description of the keyword arguments.

class score.sa.orm.QueryIdsMixin(*args, **kwargs)

A mixin for sqlalchemy session classes that adds some convenience features.

by_ids(type, ids, *, ignore_missing=True)

Yields objects of type with given ids. The function will return the objects in the order of their id in the ids parameter. The following code will print the User with id #4 first, followed by the users #2 and #5:

for user in session.by_ids(User, [4,2,5]):
  print(user)

If ignore_missing evaluates to False, the function will raise an IdsNotFound exception if any of the ids were not present in the database.

The main use case of this function is retrieval of objects, that were found through queries on external resources, such as full text indexing services like elasticsearch.

Data Loading

score.sa.orm.load_data(thing, objects=None)

Loads data from given thing, i.e. a file, a file-like object or a URL. If the source contains references to other objects loaded in an earlier call, you can pass them as objects. Usual usage:

objects = load_data('base.yaml')
if generate_dummy_data:
    objects = load_data('dummy.yaml', objects)