bag.sqlalchemy.tricks module

Functions that help define SQLAlchemy models.

class bag.sqlalchemy.tricks.AddressBase[source]

Bases: object

Base class for addresses.

In subclasses you can just define __tablename__, id, the foreign key, and maybe indexes.

city = Column('city', Unicode(length=80), table=None, default=ColumnDefault(''))
country_code = Column('country_code', Unicode(length=2), table=None, default=ColumnDefault(''))
district = Column('district', Unicode(length=80), table=None, default=ColumnDefault(''))
postal_code = Column('postal_code', Unicode(length=16), table=None, default=ColumnDefault(''))
province = Column('province', Unicode(length=40), table=None, default=ColumnDefault(''))
street = Column('street', Unicode(length=160), table=None, default=ColumnDefault(''))
class bag.sqlalchemy.tricks.CreatedChanged[source]

Bases: object

Mixin; updates created and changed columns automatically.

If you define __mapper_args__ in your model, you have to readd the mapper extension:

__mapper_args__ = dict(order_by=name,
    extension=CreatedChanged.MapperExt())
class MapperExt[source]

Bases: sqlalchemy.orm.deprecated_interfaces.MapperExtension

before_insert(mapper, connection, instance)[source]

Receive an object instance before that instance is inserted into its table.

This is a good place to set up primary key values and such that aren’t handled otherwise.

Column-based attributes can be modified within this method which will result in the new value being inserted. However no changes to the overall flush plan can be made, and manipulation of the Session will not have the desired effect. To manipulate the Session within an extension, use SessionExtension.

The return value is only significant within the MapperExtension chain; the parent mapper’s behavior isn’t modified by this method.

before_update(mapper, connection, instance)[source]

Receive an object instance before that instance is updated.

Note that this method is called for all instances that are marked as “dirty”, even those which have no net changes to their column-based attributes. An object is marked as dirty when any of its column-based attributes have a “set attribute” operation called or when any of its collections are modified. If, at update time, no column-based attributes have any net changes, no UPDATE statement will be issued. This means that an instance being sent to before_update is not a guarantee that an UPDATE statement will be issued (although you can affect the outcome here).

To detect if the column-based attributes on the object have net changes, and will therefore generate an UPDATE statement, use object_session(instance).is_modified(instance, include_collections=False).

Column-based attributes can be modified within this method which will result in the new value being updated. However no changes to the overall flush plan can be made, and manipulation of the Session will not have the desired effect. To manipulate the Session within an extension, use SessionExtension.

The return value is only significant within the MapperExtension chain; the parent mapper’s behavior isn’t modified by this method.

changed = Column(None, DateTime(), table=None, nullable=False)
created = Column(None, DateTime(), table=None, nullable=False)
class bag.sqlalchemy.tricks.EmailParts[source]

Bases: object

Mixin class that stores an email address in 2 columns.

One column contains the local part, another contains the domain. This makes it easy to find emails from the same domain.

Typical usage:

class Customer(SABase, EmailParts):
    __table_args__ = (UniqueConstraint('email_local', 'email_domain',
                      name='customer_email_key'), {})
email = <sqlalchemy.sql.elements.BinaryExpression object>
email_domain = Column('email_domain', Unicode(length=255), table=None, nullable=False)
email_local = Column('email_local', Unicode(length=160), table=None, nullable=False)
gravatar_image(default='mm', size=80, cacheable=True)[source]
set_email = <sqlalchemy.sql.elements.BinaryExpression object>
class bag.sqlalchemy.tricks.ID[source]

Bases: object

Mixin class that includes a primary key column “id”.

id = Column(None, Integer(), table=None, primary_key=True, nullable=False)
class bag.sqlalchemy.tricks.MinimalBase[source]

Bases: object

Declarative base class that auto-generates __tablename__.

clone(values=None, pk='id', sas=None)[source]

Return a clone of this model.

Optionally update some of its values. Optionally add the clone to the sas session. The name of the primary key column should be given as pk.

classmethod create_or_update(session, values={}, **filters)[source]

Load and update entity if it exists, else create one.

First obtains either an existing object or a new one, based on filters. Then applies values and returns a tuple (object, is_new).

classmethod get_or_create(session, **filters)[source]

Retrieve or add object; return a tuple (object, is_new).

is_new is True if the object already exists in the database.

update(adict, transient=False)[source]

Merge dictionary into this entity.

Optionally check that the keys exist.

update_association(sas, cls, field, ids, filters={}, synchronize_session=None)[source]

When you have a many-to-many relationship, there is an association table between 2 main tables. The problem of setting the data in this case is a recurring one and it is solved here. Some associations might be deleted and some might be created.

Example usage:

user = session.query(User).get(1)
# Suppose there's a many-to-many relationship to Address,
# named UserAddress.
new_associations = user.update_association(
    sas,                 # the SQLAlchemy session
    cls=UserAddress,      # the association class
    field='address_id'     # name of the remote foreign key
    ids=[5, 42, 89],        # the IDs of the user's addresses
    filters={"user": user},  # to load existing associations
    )
for item in new_associations:
    print(item)

This method returns a list of any new association instances because you might want to finish the job by doing something more with them (e. g. setting other attributes).

A new query is needed to retrieve the totality of the associations.

update_from_schema(schema, adict)[source]

Validate adict against schema; return updated entity.

Validates the information in the dictionary adict against a Colander schema. If validation fails, colander.Invalid is raised. If happy, returns the updated model instance.

class bag.sqlalchemy.tricks.SubtransactionTrick(engine, sessionmaker)[source]

Bases: object

Encloses your code in a subtransaction. Good for writing tests.

Usage:

trick = SubtransactionTrick(my_engine, sessionmaker)
# Be sure to use the session provided as the ``sas`` variable:
my_session = trick.sas
# Finally, call ``close()`` to roll back the changes:
trick.close()
close()[source]

Roll back everything that happened with the session.

…including calls to commit().

bag.sqlalchemy.tricks.col(attrib)[source]

Return the column that stores an attrib of a model.

Given a sqlalchemy.orm.attributes.InstrumentedAttribute (type of the attributes of model classes), return the corresponding column. E.g.: col(User.email)

bag.sqlalchemy.tricks.commit_session_or_transaction(sas)[source]

Not sure if using the transaction package or not? No problem.

Return type:None
bag.sqlalchemy.tricks.fk(attrib, nullable=False, index=True, primary_key=False, doc=None, ondelete='CASCADE')[source]

Return a ForeignKey column while automatically setting the type.

bag.sqlalchemy.tricks.fk_rel(cls, attrib='id', nullable=False, index=True, primary_key=False, doc=None, ondelete='CASCADE', backref=None, order_by=None, lazy='select')[source]

Return a ForeignKey column and a relationship.

Automatically sets the type of the foreign key.

Usage:

# A relationship in an Address model pointing to a parent Person:
person_id, person = fk_rel(Person, nullable=False,
    index=True, backref='addresses', ondelete='CASCADE')

A backref is created only if you provide its name in the argument. nullable and index are usually ommited, because these are the default values and they are good.

ondelete is “CASCADE” by default, but you can set it to “SET NULL”, or None which translates to “NO ACTION” (less interesting). If provided, order_by is used on the backref.

To load the backref greedily, use lazy='joined' as per http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html

You may also pass an attrib which is the column name for the foreign key.

bag.sqlalchemy.tricks.foreign_key_from_col(col)[source]
bag.sqlalchemy.tricks.foreign_keys_in(cls)[source]
bag.sqlalchemy.tricks.get_col(model, attribute_name)[source]

Introspect the SQLAlchemy model; return the column object.

…for attribute_name. E.g.: get_col(User, 'email')

bag.sqlalchemy.tricks.get_length(model, field)[source]

Return the length of column field of a SQLAlchemy model.

bag.sqlalchemy.tricks.is_model_class(val)[source]

Return whether the parameter is a SQLAlchemy model class.

Return type:bool
bag.sqlalchemy.tricks.length(attrib)[source]

Return the length of the attrib.

bag.sqlalchemy.tricks.many_to_many(Model1, Model2, pk1='id', pk2='id', metadata=None, backref=None)[source]

Easily set up a many-to-many relationship between 2 existing models.

Return an association table and the relationship itself.

Usage:

customer_user, Customer.users = many_to_many(Customer, User,
pk2=’__id__’)
bag.sqlalchemy.tricks.model_property_names(cls, whitelist=None, blacklist=None, include_relationships=True)[source]

Return the property names in the passed class, maybe filtered.

bag.sqlalchemy.tricks.models_and_tables_in(arg)[source]

Return 2 lists containing the model classes and tables in arg.

arg may be a resource spec, a module or a dictionary:

models, tables = models_and_tables_in(globals())
Return type:Tuple[List[~T], List[~T]]
bag.sqlalchemy.tricks.models_from_ids(sas, cls, ids)[source]

Generator that, given a sequence of IDs, yields model instances.

Performance is poor. TODO SOMEONE IMPROVE THIS PLEASE

bag.sqlalchemy.tricks.now_column(nullable=False, **k)[source]

Return a DateTime column that defaults to utcnow.

Return type:Column
bag.sqlalchemy.tricks.persistent_attribute_names_of(cls)[source]

Return a list of the names of the persistent attributes of cls.

…except collections.

bag.sqlalchemy.tricks.pk(tablename)[source]

Return a primary key column.

Return type:Column