sqlservice 0.11.0

The missing SQLAlchemy ORM interface

The missing SQLAlchemy ORM interface.

Links

• Project: https://github.com/dgilland/sqlservice

• Documentation: http://sqlservice.readthedocs.io

• PyPI: https://pypi.python.org/pypi/sqlservice/

• TravisCI: https://travis-ci.org/dgilland/sqlservice

Introduction

So what exactly is sqlservice and what does “the missing SQLAlchemy ORM interface” even mean? SQLAlchemy is a fantastic library and features a superb ORM layer. However, one thing SQLAlchemy lacks is a unified interface for easily interacting with your database through your ORM models. This is where sqlservice comes in. It’s interface layer on top of SQLAlchemy’s session manager and ORM layer that provides a single point to manage your database connection/session, create/reflect/drop your database objects, and easily persist/destroy model objects.

Features

This library is meant to enhance your usage of SQLAlchemy. SQLAlchemy is great and this library tries to build upon that by providing useful abstractions on top of it.

• Database client that helps manage an ORM scoped session.

• Base class for a declarative ORM Model that makes updating model columns and relationships easier and converting to a dictionary a breeze.

• Decorator-based event register for SQLAlchemy ORM events that can be used at the model class level. No need to register the event handler outside of the class definition.

• An application-side nestable transaction context-manager that helps implement pseudo-subtransactions for those that want implicit transaction demarcation, i.e. session autocommit, without using session subtransactions.

• And more!

Requirements

• Python 2.7 or Python >= 3.4

• SQLAlchemy >= 1.0.0

• pydash >= 3.4.3

Quickstart

First, install using pip:

pip install sqlservice

Then, define some ORM models:

import re

from sqlalchemy import Column, ForeignKey, orm, types

from sqlservice import declarative_base, event


Model = declarative_base()

class User(Model):
    __tablename__ = 'user'

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

    roles = orm.relation('UserRole')

    @event.on_set('phone', retval=True)
    def on_set_phone(self, value, oldvalue, initator):
        # Strip non-numeric characters from phone number.
        return re.sub('[^0-9]', '', value)

class UserRole(Model):
    __tablename__ = 'user_role'

    id = Column(types.Integer(), primary_key=True)
    user_id = Column(types.Integer(), ForeignKey('user.id'), nullable=False)
    role = Column(types.String(25), nullable=False)

Next, configure the database client:

from sqlservice import SQLClient

config = {
    'SQL_DATABASE_URI': 'sqlite:///db.sql',
    'SQL_ISOLATION_LEVEL': 'SERIALIZABLE',
    'SQL_ECHO': True,
    'SQL_ECHO_POOL': False,
    'SQL_CONVERT_UNICODE': True,
    'SQL_POOL_SIZE': 5,
    'SQL_POOL_TIMEOUT': 30,
    'SQL_POOL_RECYCLE': 3600,
    'SQL_MAX_OVERFLOW': 10,
    'SQL_AUTOCOMMIT': False,
    'SQL_AUTOFLUSH': True,
    'SQL_EXPIRE_ON_COMMIT': True
}

db = SQLClient(config, model_class=Model)

Prepare the database by creating all tables:

db.create_all()

Finally (whew!), start interacting with the database.

Insert a new record in the database:

data = {'name': 'Jenny', 'email': '[e-mail]', 'phone': '555-867-5309'}
user = db.User.save(data)

Fetch records:

assert user is db.User.get(data.id)
assert user is db.User.find_one(id=user.id)
assert user is db.User.find(User.id == user.id)[0]

Serialize to a dict:

assert user.to_dict() == {'id': 1,
                          'name': 'Jenny',
                          'email': '[e-mail]',
                          'phone': '5558675309'}

assert dict(user) == user.to_dict()

Update the record and save:

user.phone = '222-867-5309'
db.User.save(user)

Upsert on primary key automatically:

assert user is db.User({'id': 1,
                        'name': 'Jenny',
                        'email': '[e-mail]',
                        'phone': '5558675309'})

Destroy the model record:

db.User.destroy(user)
# OR db.User.destroy([user])
# OR db.User.destroy(user.id)
# OR db.User.destroy([user.id])
# OR db.User.destroy(dict(user))
# OR db.User.destroy([dict(user)])

For more details, please see the full documentation at http://sqlservice.readthedocs.io.

Changelog

v0.11.0 (2017-03-10)

• Make SQLClient.save() update the declarative model registry whenever an model class isn’t in it. This allows saving to work when a SQLClient instance was created before models have been imported yet.

• Make SQLClient.expunge() support multiple instances.

• Make SQLClient.save() and SQLQuery.save() handle saving empty dictionaries.

v0.10.0 (2017-02-13)

• Add engine_options argument to SQLClient() to provide additional engine options beyond what is supported by the config argument.

• Add SQLClient.bulk_insert for performing an INSERT with a multi-row VALUES clause.

• Add SQLClient.bulk_insert_many for performing an executemany() DBAPI call.

• Add additional SQLClient.session proxy properties on SQLClient.<proxy>:

  • bulk_insert_mappings

  • bulk_save_objects

  • bulk_update_mappings

  • is_active

  • is_modified

  • no_autoflush

  • preapre

• Store SQLClient.models as a static dict instead of computed property but recompute if an attribute error is detected for SQLClient.<Model> to handle the case of a late model class import.

• Fix handling of duplicate base class names during SQLClient.models creation for model classes that are defined in different submodules. Previously, duplicate model class names prevented those models from being saved via SQLClient.save().

v0.9.1 (2017-01-12)

• Fix handling of scopefunc option in SQLClient.create_session.

v0.9.0 (2017-01-10)

• Add session_class argument to SQLClient() to override the default session class used by the session maker.

• Add session_options argument to SQLClient() to provide additional session options beyond what is supported by the config argument.

v0.8.0 (2016-12-09)

• Rename sqlservice.Query to SQLQuery. (breaking change)

• Remove sqlservice.SQLService class in favor of utilizing SQLQuery for the save and destroy methods for a model class. (breaking change)

• Add SQLQuery.save().

• Add SQLQuery.destroy().

• Add SQLQuery.model_class property.

• Replace service_class argument with query_class in SQLClient.__init__(). (breaking change)

• Remove SQLClient.services. (breaking change)

• When a model class name is used for attribute access on a SQLClient instance, return an instance of SQLQuery(ModelClass) instead of SQLService(ModelClass). (breaking change)

v0.7.2 (2016-11-29)

• Fix passing of synchronize_session argument in SQLService.destroy and SQLClient.destroy. Argument was mistakenly not being used when calling underlying delete method.

v0.7.1 (2016-11-04)

• Add additional database session proxy attributes to SQLClient:

  • SQLClient.scalar -> SQLClient.session.scalar

  • SQLClient.invalidate -> SQLClient.session.invalidate

  • SQLClient.expire -> SQLClient.session.expire

  • SQLClient.expire_all -> SQLClient.session.expire_all

  • SQLClient.expunge -> SQLClient.session.expunge

  • SQLClient.expunge_all -> SQLClient.session.expunge_all

  • SQLClient.prune -> SQLClient.session.prune

• Fix compatibility issue with pydash v3.4.7.

v0.7.0 (2016-10-28)

• Add core.make_identity factory function for easily creating basic identity functions from a list of model column objects that can be used with save().

• Import core.save, core.destroy, core.transaction, and core.make_identity into make package namespace.

v0.6.3 (2016-10-17)

• Fix model instance merging in core.save when providing a custom identity function.

v0.6.2 (2016-10-17)

• Expose identity argument in SQLClient.save and SQLService.save.

v0.6.1 (2016-10-17)

• Fix bug where the models variable was mistakenly redefined during loop iteration in core.save.

v0.6.0 (2016-10-17)

• Add identity argument to save method to allow a custom identity function to support upserting on something other than just the primary key values.

• Make Query entity methods entities, join_entities, and all_entities return entity objects instead of model classes. (breaking change)

• Add Query methods model_classes, join_model_classes, and all_model_classes return the model classes belonging to a query.

v0.5.1 (2016-09-28)

• Fix issue where calling <Model>.update(data) did not correctly update a relationship field when both <Model>.<relationship-column> and data[<relationship-column>] were both instances of a model class.

v0.5.0 (2016-09-20)

• Allow Service.find_one, Service.find, and Query.search to accept a list of lists as the criterion argument.

• Rename ModelBase metaclass class attribute from ModelBase.Meta to ModelBase.metaclass. (breaking change)

• Add support for defining the metadata object on ModelBase.metadata and having it used when calling declarative_base.

• Add metadata and metaclass arguments to declarative_base that taken precedence over the corresponding class attributes set on the passed in declarative base type.

• Rename Model argument/attribute in SQLClient to __init__ to model_class. (breaking change)

• Remove Query.top method. (breaking change)

• Proxy SQLService.__getattr__ to getattr(SQLService.query(), attr) so that SQLService now acts as a proxy to a query instance that uses its model_class as the primary query entity.

• Move SQLService.find and SQLService.find_one to Query.

• Improve docs.

v0.4.3 (2016-07-11)

• Fix issue where updating nested relationship values can lead to conflicting state assertion error in SQLAlchemy’s identity map.

v0.4.2 (2016-07-11)

• Fix missing before and after callback argument passing from core.save to core._add.

v0.4.1 (2016-07-11)

• Fix missing before and after callback argument passing from SQLService.save to SQLClient.save.

v0.4.0 (2016-07-11)

• Add support for before and after callbacks in core.save, SQLClient.save, and SQLService.save which are invoked before/after session.add is called for each model instance.

v0.3.0 (2016-07-06)

• Support additional engine and session configuration values for SQLClient.

  • New engine config options:

    • SQL_ECHO_POOL

    • SQL_ENCODING

    • SQL_CONVERT_UNICODE

    • SQL_ISOLATION_LEVEL

  • New session config options:

    • SQL_EXPIRE_ON_COMMIT

• Add SQLClient.reflect method.

• Rename SQLClient.service_registry and SQLClient.model_registry to services and models. (breaking change)

• Support SQLClient.__getitem__ as proxy to SQLClient.__getattr__ where both db[User] and db['User'] both map to db.User.

• Add SQLService.count method.

• Add Query methods:

  • index_by: Converts Query.all() to a dict of models indexed by callback (pydash.index_by)

  • stack_by: Converts Query.all() to a dict of lists of models indexed by callback (pydash.group_by)

  • map: Maps Query.all() to a callback (pydash.map_)

  • reduce: Reduces Query.all() through callback (pydash.reduce_)

  • reduce_right: Reduces Query.all() through callback from right (pydash.reduce_right)

  • pluck: Retrieves value of of specified property from all elements of Query.all() (pydash.pluck)

  • chain: Initializes a chain object with Query.all() (pydash.chain)

• Rename Query properties: (breaking change)

  • model_classes to entities

  • joined_model_classes to join_entities

  • all_model_classes to all_entities

v0.2.0 (2016-06-15)

• Add Python 2.7 compatibility.

• Add concept of model_registry and service_registry to SQLClient class:

  • SQLClient.model_registry returns mapping of ORM model names to ORM model classes bound to SQLClient.Model.

  • SQLService instances are created with each model class bound to declarative base, SQLClient.Model and stored in SQLClient.service_registry.

  • Access to each model class SQLService instance is available via attribute access to SQLClient. The attribute name corresponds to the model class name (e.g. given a User ORM model, it would be accessible at sqlclient.User.

• Add new methods to SQLClient class:

  • save: Generic saving of model class instances similar to SQLService.save but works for any model class instance.

  • destroy: Generic deletion of model class instances or dict containing primary keys where model class is explicitly passed in. Similar to SQLService.destroy.

• Rename SQLService.delete to destroy. (breaking change)

• Change SQLService initialization signature to SQLService(db, model_class) and remove class attribute model_class in favor of instance attribute. (breaking change)

• Add properties to SQLClient class:

  • service_registry

  • model_registry

• Add properties to Query class:

  • model_classes: Returns list of model classes used to during Query creation.

  • joined_model_classes: Returns list of joined model classes of Query.

  • all_model_classes: Returns Query.model_classes + Query.joined_model_classes.

• Remove methods from SQLService class: (breaking change)

  • query_one

  • query_many

  • default_order_by (default order by determination moved to Query.search)

• Remove sqlservice.service.transaction decorator in favor of using transaction context manager within methods. (breaking change)

• Fix incorrect passing of SQL_DATABASE_URI value to SQLClient.create_engine in SQLClient.__init__.

v0.1.0 (2016-05-24)

• First release.