SQLAlchemy patterns

Coaster provides a number of mixin classes for SQLAlchemy models. To use in your Flask app:

from flask import Flask
from flask.ext.sqlalchemy import SQLAlchemy
from coaster.sqlalchemy import BaseMixin

app = Flask(__name__)
db = SQLAlchemy(app)

class MyModel(BaseMixin, db.Model):
    __tablename__ = 'my_model'

Mixin classes must always appear before db.Model in your model’s base classes.

class coaster.sqlalchemy.IdMixin

Provides the id primary key column

id = Column(None, Integer(), table=None, primary_key=True, nullable=False)

Database identity for this model, used for foreign key references from other models

query_class

alias of Query

class coaster.sqlalchemy.TimestampMixin

Provides the created_at and updated_at audit timestamps

created_at = Column(None, DateTime(), table=None, nullable=False, default=ColumnDefault(<function <lambda> at 0x10966d0c8>))

Timestamp for when this instance was created, in UTC

query_class

alias of Query

updated_at = Column(None, DateTime(), table=None, nullable=False, onupdate=ColumnDefault(<function <lambda> at 0x10966d1b8>), default=ColumnDefault(<function <lambda> at 0x10966d140>))

Timestamp for when this instance was last updated (via the app), in UTC

class coaster.sqlalchemy.PermissionMixin

Provides the permissions() method used by BaseMixin and derived classes

permissions(user, inherited=None)

Return permissions available to the given user on this object

class coaster.sqlalchemy.UrlForMixin

Provides a placeholder url_for() method used by BaseMixin-derived classes

url_for(action='view', **kwargs)

Return public URL to this instance for a given action (default ‘view’)

class coaster.sqlalchemy.BaseMixin

Base mixin class for all tables that adds id and timestamp columns and includes stub permissions() and url_for() methods

class coaster.sqlalchemy.BaseNameMixin(*args, **kw)

Base mixin class for named objects

Changed in version 0.5.0: If you used BaseNameMixin in your app before Coaster 0.5.0: name can no longer be a blank string in addition to being non-null. This is configurable and enforced with a SQL CHECK constraint, which needs a database migration:

for tablename in ['named_table1', 'named_table2', ...]:
    # Drop CHECK constraint first in case it was already present
    op.drop_constraint(tablename + '_name_check', tablename)
    # Create CHECK constraint
    op.create_check_constraint(tablename + '_name_check', tablename, "name!=''")

classmethod get(name)

Get an instance matching the name

make_name(reserved=[])

Autogenerates a name from the title. If the auto-generated name is already in use in this model, make_name() tries again by suffixing numbers starting with 2 until an available name is found.

Parameters:reserved – List or set of reserved names unavailable for use

reserved_names = []

Prevent use of these reserved names

classmethod upsert(name, **fields)

Insert or update an instance

class coaster.sqlalchemy.BaseScopedNameMixin(*args, **kw)

Base mixin class for named objects within containers. When using this, you must provide an model-level attribute “parent” that is a synonym for the parent object. You must also create a unique constraint on ‘name’ in combination with the parent foreign key. Sample use case in Flask:

class Event(BaseScopedNameMixin, db.Model):
    __tablename__ = 'event'
    organizer_id = db.Column(None, db.ForeignKey('organizer.id'))
    organizer = db.relationship(Organizer)
    parent = db.synonym('organizer')
    __table_args__ = (db.UniqueConstraint('organizer_id', 'name'),)

Changed in version 0.5.0: If you used BaseScopedNameMixin in your app before Coaster 0.5.0: name can no longer be a blank string in addition to being non-null. This is configurable and enforced with a SQL CHECK constraint, which needs a database migration:

for tablename in ['named_table1', 'named_table2', ...]:
    # Drop CHECK constraint first in case it was already present
    op.drop_constraint(tablename + '_name_check', tablename)
    # Create CHECK constraint
    op.create_check_constraint(tablename + '_name_check', tablename, "name!=''")

classmethod get(parent, name)

Get an instance matching the parent and name

make_name(reserved=[])

Autogenerates a name from the title. If the auto-generated name is already in use in this model, make_name() tries again by suffixing numbers starting with 2 until an available name is found.

permissions(user, inherited=None)

Permissions for this model, plus permissions inherited from the parent.

reserved_names = []

Prevent use of these reserved names

short_title()

Generates an abbreviated title by subtracting the parent’s title from this instance’s title.

classmethod upsert(parent, name, **fields)

Insert or update an instance

class coaster.sqlalchemy.BaseIdNameMixin(*args, **kw)

Base mixin class for named objects with an id tag.

Changed in version 0.5.0: If you used BaseIdNameMixin in your app before Coaster 0.5.0: name can no longer be a blank string in addition to being non-null. This is configurable and enforced with a SQL CHECK constraint, which needs a database migration:

for tablename in ['named_table1', 'named_table2', ...]:
    # Drop CHECK constraint first in case it was already present
    op.drop_constraint(tablename + '_name_check', tablename)
    # Create CHECK constraint
    op.create_check_constraint(tablename + '_name_check', tablename, "name!=''")

make_name()

Autogenerates a name from the title

url_id

Return the URL id

url_id_attr = 'id'

The attribute containing id numbers used in the URL in id-name syntax, for external reference

url_name

Returns a URL name combining url_id and name in id-name syntax

class coaster.sqlalchemy.BaseScopedIdMixin(*args, **kw)

Base mixin class for objects with an id that is unique within a parent. Implementations must provide a ‘parent’ attribute that is either a relationship or a synonym to a relationship referring to the parent object, and must declare a unique constraint between url_id and the parent. Sample use case in Flask:

class Issue(BaseScopedIdMixin, db.Model):
    __tablename__ = 'issue'
    event_id = db.Column(None, db.ForeignKey('event.id'))
    event = db.relationship(Event)
    parent = db.synonym('event')
    __table_args__ = (db.UniqueConstraint('event_id', 'url_id'),)

classmethod get(parent, url_id)

Get an instance matching the parent and url_id

make_id()

Create a new URL id that is unique to the parent container

permissions(user, inherited=None)

Permissions for this model, plus permissions inherited from the parent.

url_id_attr = 'url_id'

The attribute containing the url id value, for external reference

class coaster.sqlalchemy.BaseScopedIdNameMixin(*args, **kw)

Base mixin class for named objects with an id tag that is unique within a parent. Implementations must provide a ‘parent’ attribute that is a synonym to the parent relationship, and must declare a unique constraint between url_id and the parent. Sample use case in Flask:

class Event(BaseScopedIdNameMixin, db.Model):
    __tablename__ = 'event'
    organizer_id = db.Column(None, db.ForeignKey('organizer.id'))
    organizer = db.relationship(Organizer)
    parent = db.synonym('organizer')
    __table_args__ = (db.UniqueConstraint('organizer_id', 'url_id'),)

Changed in version 0.5.0: If you used BaseScopedIdNameMixin in your app before Coaster 0.5.0: name can no longer be a blank string in addition to being non-null. This is configurable and enforced with a SQL CHECK constraint, which needs a database migration:

for tablename in ['named_table1', 'named_table2', ...]:
    # Drop CHECK constraint first in case it was already present
    op.drop_constraint(tablename + '_name_check', tablename)
    # Create CHECK constraint
    op.create_check_constraint(tablename + '_name_check', tablename, "name!=''")

classmethod get(parent, url_id)

Get an instance matching the parent and name

make_name()

Autogenerates a title from the name

url_name

Returns a URL name combining url_id and name in id-name syntax

class coaster.sqlalchemy.CoordinatesMixin

Adds latitude and longitude columns with a shorthand coordinates property that returns both.

class coaster.sqlalchemy.JsonDict(*args, **kwargs)

Represents a JSON data structure. Usage:

column = Column(JsonDict)

impl

alias of TEXT

class coaster.sqlalchemy.MarkdownComposite(text, html=None)

Represents GitHub-flavoured Markdown text and rendered HTML as a composite column.