The Model is the basic ORM class that represents your database schema. SQLAlchemy provides a very basic default ORM model class when one calls sqlalchemy.ext.declarative.declarative_base. SQLService does one better and provides the basic tools for handling your basic use cases.

The general approach to using sqlservice.ModelBase is to use it as the base class for your own custom Model class that extends/overrides ModelBase to fit your specific needs.

# in models/
from sqlalchemy import MetaData
from sqlservice import ModelBase, as_declarative, declarative_base

metadata = MetaData()

class Model(ModelBase):

# Or using the declarative_base function...
# Model = declarative_base(ModelBase, metadata=metadata)


All keyword arguments to sqlservice.as_declarative or sqlservice.declarative_base will be passed to sqlalchemy.ext.declarative.declarative_base.

From there you can use Model as the base class for your ORM model classes.

# in models/
from sqlalchemy import Column, types

from .base import Model

class User(Model):
    __tablename__ = 'user'

    id = Column(types.Integer(), primary_key=True)
    name = Column(types.String(100))
    email = Column(types.String(100))

Instantiation and Updating

What does ModelBase provide for you? Out of the box, you’ll be able to do things like:

Create a new instance from a dict or keyword arguments:

user = User({'name': 'Bob', 'email': '})
user = User(name='Bob', email='')


Under the hood ModelBase.__init__ calls update() so anything update() does, __init__ does too.

Update using attribute or item setters: = 'Bob Paulson'
user['name'] = 'Robert Paulson'

Update an instance using a dict or keyword arguments:

user.update(name='Bob Smith')
user.update({'email': ''})

The update() method is powerful enough to work with relationships and nested relationships. Consider the following:

# in models/
from sqlalchemy import Column, ForeignKey, types, orm

from .base import Model

class User(Model):
    __tablename__ = 'user'

    id = Column(types.Integer(), primary_key=True)
    name = Column(types.String(100))
    email = Column(types.String(100))

    about = orm.relation('UserAbout', uselist=False)
    devices = orm.relation('UserDevice')

class UserAbout(Model):
    __tablename__ = 'user_about'

    user_id = Column(types.Integer(), ForeignKey(''), primary_key=True)
    nickname = Column(types.String(100))
    hometown = Column(types.String(100))

class UserDevice(Model):
    __tablename__ = 'user_device'

    id = Column(types.Integer(), primary_key=True)
    user_id = Column(types.Integer(), ForeignKey(''), nullable=False)
    name = Column(types.String(100))

    keys = orm.relation('UserDeviceKey')

class UserDeviceKey(Model):
    __tablename__ = 'user_device_key'

    id = Column(types.Integer(), primary_key=True)
    device_id = Column(types.Integer(),
    key = Column(types.String(100))

You can now easily create a user, user devices, and device keys with a single data structure without having to use the relationship classes directly.

data = {
    'name': 'Bob Smith',
    'email': '',
    'about': {
        'nickname': 'Bobby',
        'hometown': 'Example City'
    'devices': [
        {'name': 'device1', 'keys': [{'key': 'key1a'}, {'key': 'key1b'}]},
        {'name': 'device2', 'keys': [{'key': 'key2a'}, {'key': 'key2b'}]}
user = User(data)

# <User(id=None, name='Bob Smith', email='')>

# <UserAbout(user_id=None, nickname='Bobby', hometown='Example City')>

# [<UserDevice(id=None, user_id=None, name='device1')>,
   <UserDevice(id=None, user_id=None, name='device2')>]

# [<UserDeviceKey(id=None, device_id=None, key='key1a')>,
   <UserDeviceKey(id=None, device_id=None, key='key1b')>]

# [<UserDeviceKey(id=None, device_id=None, key='key2a')>,
   <UserDeviceKey(id=None, device_id=None, key='key2b')>]

This is because ModelBase.update() works really hard to map dict keys to the correct relationship model class to automatically create new model instances from those dict objects. It works for relationships that are 1:1 or 1:M.

In addition, when you update the model with relationship data, it will nest calls to the relationship class’ update() methods.

user.update({'about': {'nickname': 'Bo'}})
# <UserAbout(user_id=None, nickname='Bo', hometown='Example City')>


Depending on whether you’ve set up relationship cascades, calling update() on relationships can result in integrity errors since SQLAlchemy will nullify orphaned relationship models when they are replaced.

user.update({'devices': [{'name': 'device3'}]})

# sqlalchemy.exc.IntegrityError: (raised as a result of Query-invoked autoflush;
# consider using a session.no_autoflush block if this flush is occurring
# prematurely) (sqlite3.IntegrityError) NOT NULL constraint failed:
# user_device.user_id [SQL: 'UPDATE user_device SET user_id=? WHERE
# = ?'] [parameters: ((None, 1), (None, 2))]

Dictionary Serialization

Want to serialize your models to dict objects?

# {'id': 1,
   'name': 'Bob Smith',
   'email': '',
   'about': {'nickname': 'Bo', 'hometown': 'Example City'},
   'devices': [{'id': 1, 'name': 'device1', 'user_id': 1}, {'id': 2, 'name': 'device2', 'user_id': 1}]}

As you can see, relationships are serialized too.

But how does this handle lazy loaded models? When serializing the only data that is serialized is what is already loaded. This is done to avoid triggerring a large number of individual queries on lazily loaded attributes. Essentially, Model.to_dict() only looks at what’s already present in user.__dict__ and never touches any attributes directly (which could lead to additional queries). So it’s up to you to ensure that your model is loaded with the data you want to be serialized before calling to_dict().

Need to serialize certain types differently? Add some adapters using __dict_args__ class attribute:

class User(Model):
    __dict_args__ = {
        'adapters': {
            UserAbout: lambda model, *_: {'nickname': model.nickname},
            # identical to above but using string name for Model...
            # 'UserAbout': lambda model, *_: {'nickname': model.nickname},
            'devices': lambda devices, *_: [(, device.keys) for device in devices],
            list: lambda items, col, *_: [ for item in items],
            (int, str): lambda value, col: col + ':' + str(value)

# {'id': 'id:1',
   'name': 'name:Bob Smith',
   'email': '',
   'about': {'nickname': 'Bo'},
   'devices': [('device1', ['key1a', 'key1b']),
               ('device2', ['key2a', 'key2b'])]}

The adapaters argument is expected to be a mapping to serializers where each key can be one of:

  • model class object (e.g. UserAbout)
  • string name of model class (e.g. 'UserAbout')
  • string name of model attribute (e.g. 'about' which corresponds to User.about)
  • other type (e.g. list, int, str, etc.)
  • tuple of types (e.g. (int, float))
  • None to exclude the key from serialization.

The serializer should be a callable that accepts three arguments: (value, column, model_instance) (the arguments passed in are based on the function definition and are automatically detected). The adapter serializer used when it’s key matches the matches the value’s type, descriptor name, or model class name. For relationships defined as a list or other list-like structure, the relationship class’ __dict_args__ will be used during nested serialization. If you need to reference classes that aren’t defined yet (e.g. other model classes), you can make __dict_args__ a @property or use the string class name if it’s another model class.

Object Identity

You can get the primary key identity of any model object:

# 1


If the model has multiple primary keys, a tuple is returned

Class Methods and Properties

The Model class includes other useful methods as well:

# <Mapper at 0x7fd9e7443b70; User>

# (Column('id', Integer(), table=<user>, primary_key=True, nullable=False),
   Column('name', String(length=100), table=<user>),
   Column('email', String(length=100), table=<user>))

# (Column('id', Integer(), table=<user>, primary_key=True, nullable=False),)

# (<RelationshipProperty at 0x7fd9ead007b8; about>,
   <RelationshipProperty at 0x7fd9e7421f28; devices>)

for descriptor in User.descriptors():
    (str(descriptor), repr(descriptor))
# User.about, <sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x7fd9e743f728>
# User.devices, <sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x7fd9e743f780>
#, <sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x7fd9e743f938>
#, <sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x7fd9e743f9e8>
#, <sqlalchemy.orm.attributes.InstrumentedAttribute object at 0x7fd9e743f888>