sqlservice
|version| |build| |coveralls| |license|
The missing SQLAlchemy ORM interface.
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.
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.
SQLAlchemy <http://www.sqlalchemy.org/>_ >= 2.0First, install using pip:
::
pip install sqlservice
Then, define some ORM models:
.. code-block:: python
import re
import typing as t
from sqlalchemy import ForeignKey, orm, types
from sqlalchemy.orm import Mapped, mapped_column
from sqlservice import declarative_base, event
Model = declarative_base()
class User(Model):
__tablename__ = "user"
id: Mapped[int] = mapped_column(types.Integer(), primary_key=True)
name: Mapped[t.Optional[str]] = mapped_column(types.String(100))
email: Mapped[t.Optional[str]] = mapped_column(types.String(100))
phone: Mapped[t.Optional[str]] = mapped_column(types.String(10))
roles: Mapped[t.List["UserRole"]] = orm.relationshipship("UserRole")
@event.on_set("phone", retval=True)
def on_set_phone(self, value):
# Strip non-numeric characters from phone number.
return re.sub("[^0-9]", "", value)
class UserRole(Model):
__tablename__ = "user_role"
id: Mapped[int] = mapped_column(types.Integer(), primary_key=True)
user_id: Mapped[int] = mapped_column(types.Integer(), ForeignKey("user.id"), nullable=False)
role: Mapped[str] = mapped_column(types.String(25), nullable=False)
Next, configure the database client:
.. code-block:: python
from sqlservice import AsyncDatabase, Database
db = Database(
"sqlite:///db.sql",
model_class=Model,
isolation_level="SERIALIZABLE",
echo=True,
echo_pool=False,
pool_size=5,
pool_timeout=30,
pool_recycle=3600,
max_overflow=10,
autoflush=True,
)
# Same options as above are supported but will default to compatibility with SQLAlchemy asyncio mode.
async_db = AsyncDatabase("sqlite:///db.sql", model_class=Model)
Prepare the database by creating all tables:
.. code-block:: python
db.create_all()
await async_db.create_all()
Finally (whew!), start interacting with the database.
Insert a new record in the database:
.. code-block:: python
user = User(name='Jenny', email=jenny@example.com, phone='555-867-5309')
with db.begin() as session:
session.save(user)
async with db.begin() as session:
await session.save(user)
Fetch records:
.. code-block:: python
session = db.session()
assert user is session.get(User, user.id)
assert user is session.first(User.select())
assert user is session.all(User.select().where(User.id == user.id)[0]
Serialize to a dict:
.. code-block:: python
assert user.to_dict() == {
"id": 1,
"name": "Jenny",
"email": "jenny@example.com",
"phone": "5558675309"
}
assert dict(user) == user.to_dict()
Update the record and save:
.. code-block:: python
user.phone = '222-867-5309'
with db.begin() as session:
session.save(user)
async with async_db.begin() as session:
await session.save(user)
Upsert on primary key automatically:
.. code-block:: python
other_user = User(id=1, name="Jenny", email="jenny123@example.com", phone="5558675309")
with db.begin() as session:
session.save(other_user)
assert user is other_user
For more details, please see the full documentation at http://sqlservice.readthedocs.io.
.. |version| image:: http://img.shields.io/pypi/v/sqlservice.svg?style=flat-square :target: https://pypi.python.org/pypi/sqlservice/
.. |build| image:: https://img.shields.io/github/actions/workflow/status/dgilland/sqlservice/main.yml?branch=master&style=flat-square :target: https://github.com/dgilland/sqlservice/actions
.. |coveralls| image:: http://img.shields.io/coveralls/dgilland/sqlservice/master.svg?style=flat-square :target: https://coveralls.io/r/dgilland/sqlservice
.. |license| image:: http://img.shields.io/pypi/l/sqlservice.svg?style=flat-square :target: https://pypi.python.org/pypi/sqlservice/
first(), one(), one_or_none(), save(), and save_all() that would result in sqlalchemy.exc.InvalidRequestError due to unique() not being applied to the results.Update README for v2.
The v2 release is a major rewrite of the library with many incompatibilities and breaking changes from v1. Please see the Migrating to v2.0 section in the docs for details.
Fix compatibility issues with SQLAlchemy 1.4.
The following features are incompatible with SQLAlchemy 1.4 and will raise an exception if used:
sqlservice.Query.entitiessqlservice.Query.join_entitiessqlservice.Query.all_entitiessqlservice.SQLClient.prune>=1.0,<1.4 due to incompatibilities with SQAlchemy 1.4.data argument to _data in ModelBase.__init__() and ModelBase.update() to avoid conflict when an ORM model has a column attribute named "data".SQLClient.disconnect().SQLClient.__init__().ModelBase.__dict_args__['adapters'] is None, then don't serialize that key when calling Model.to_dict().ModelBase.__dict_args__['adapters'] that resulted in an unhandled TypeError exception in some cases.SQLClient.bulk_diff_update() aren't different than previous mappings.SQLClient.bulk_common_update() and core.bulk_common_update().SQLClient.bulk_diff_update() and core.bulk_diff_update().SQLClient.bulk_insert() and bulk_insert_many() to core.bulk_insert() and core.bulk_insert_many() respectively.SQLQuery.save() to not create an intermediate list when saving multiple items.SQLQuery.save().Drop support for Python 2.7. (breaking change)
Don't mutate models argument when passed in as a list to SQLClient.save|core.save.
Allow generators to be passed into SQLClient.save|core.save and SQLClient.destroy|core.destroy.
Remove deprecated methods: (breaking change)
SQLClient.shutdown() (use SQLClient.disconnect())SQLQuery.chain()SQLQuery.pluck()SQLQuery.key_by()SQLQuery.map()SQLQuery.reduce()SQLQuery.reduce_right()SQLQuery.stack_by()Add SQLClient.DEFAULT_CONFIG class attribute as way to override config defaults at the class level via subclassing.
Rename SQLClient.shutdown() to disconnect() but keep shutdown() as a deprecated alias.
Deprecate SQLClient.shutdown(). Use SQLClient.disconnect() instead. Will be removed in v1.
Deprecate SQLQuery methods below. Use pydash library directly or re-implement in subclass of SQLQuery and pass to SQLClient() via query_class argument. Methods will be removed in v1:
SQLQuery.chain()SQLQuery.pluck()SQLQuery.key_by()SQLQuery.map()SQLQuery.reduce()SQLQuery.reduce_right()SQLQuery.stack_by()Change default behavior of SQLClient.transaction() to not override the current session's autoflush setting (use SQLClient.transaction(autoflush=True) instead. (breaking change)
Add boolean autoflush option to SQLClient.transaction() to set session's autoflush value for the duration of the transaction.
Add new sqlservice.event decorators:
on_init_scalaron_init_collectionon_modifiedon_bulk_replaceon_dispose_collectionSQLClient.ping() method that performs a basic connection check.ModelBase.class_registry() that returns the declarative class registry from declarative metadata. Roughly equivalent to ModelBase._decl_class_registry but with _sa_* keys removed.ModelBase.__dict_args__['adapters'] handlers.dict adapater as sqlservice.model.default_dict_adapter.ModelBase.__dict_args__['adapaters']. Works similar to string namesused in sqlalchemy.orm.relationship.ModelBase.__dict_args__['adapaters'].Remove readonly argument from SQLClient.transaction and replace with separate commit and rollback. (breaking change)
commit=True and rollback=False. This behavior mirrors the previous behavior.rollback=True, the commit argument is ignored and the top-level transaction is always rolled back. This is like readonly=True in version 0.17.0.commit=False and rollback=False, the "transaction" isn't finalized and is left open. This is like readonly=True in versions <=0.16.1.Rollback instead of commit in a readonly transaction issued by SQLClient.transaction. (potential breaking change)
repr(self.url) in SQLClient.__repr__() instead of str() to mask connection password if provided.SQLClient. For example, previously had to do SQLClient({'SQL_DATABASE_URI': '<db_uri>'}) but now can do SQLClient('<db_uri>').repr() support to SQLClient.SQL_POOL_PRE_PING config option to SQLClient that sets pool_pre_ping argument to engine. Requires SQLAlchemy >= 1.2. Thanks dsully_!Query.search() so that dict filter-by criteria will be applied to the base model class of the query if it's set (i.e. make db.query(ModelA).join(ModelB).search({'a_only_field': 'foo'}) work so that {'a_only_field': 'foo'} is filtered on ModelA.a_only_field instead of ModelB). This also applies to Query.find() and Query.find_one() which use search() internally.SQL_ENCODING config option mapping to SQLAlchemy parameter. Thanks dsully_!declarative_base pass extra keyword arguments to sqlalchemy.ext.declarative.declarative_base.ModelBase.metaclass and ModelBase.metadata hooks for hoisting those values to declarative_base(). Instead, pass optional metadata and metaclass arguments directly to declarative_base. (breaking change)declarative_base decorator usage with new decorator-only function, as_declarative. Previously, @declarative_base only worked as a decorator when not "called" (i.e. @declarative_base worked but @declarative_base(...) failed).ModelBase.__dict_args__ attribute for providing arguments to ModelBase.to_dict.adapters option to ModelBase.__dict_args__ for mapping model value types to custom serializatoin handlers during ModelBase.to_dict() call.v4.0.1.Query.pluck but now pluck works with a deep path and path list (e.g. ['a', 'b', 0, 'c'] to get 'value' in {'a': {'b': [{'c': 'value'}]}} which is something that Query.map doesn't support.Bump minimum requirement for pydash to v4.0.0. (breaking change)
Remove Query.pluck in favor or Query.map since map can do everything pluck could. (breaking change)
Rename Query.index_by to Query.key_by. (breaking change)
Rename callback argument to iteratee for Query methods:
key_bystack_bymapreducereduce_rightSQLClient.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.SQLClient.expunge() support multiple instances.SQLClient.save() and SQLQuery.save() handle saving empty dictionaries.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_mappingsbulk_save_objectsbulk_update_mappingsis_activeis_modifiedno_autoflushpreapreStore 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().
scopefunc option in SQLClient.create_session.session_class argument to SQLClient() to override the default session class used by the session maker.session_options argument to SQLClient() to provide additional session options beyond what is supported by the config argument.sqlservice.Query to SQLQuery. (breaking change)sqlservice.SQLService class in favor of utilizing SQLQuery for the save and destroy methods for a model class. (breaking change)SQLQuery.save().SQLQuery.destroy().SQLQuery.model_class property.service_class argument with query_class in SQLClient.__init__(). (breaking change)SQLClient.services. (breaking change)SQLClient instance, return an instance of SQLQuery(ModelClass) instead of SQLService(ModelClass). (breaking change)synchronize_session argument in SQLService.destroy and SQLClient.destroy. Argument was mistakenly not being used when calling underlying delete method.Add additional database session proxy attributes to SQLClient:
SQLClient.scalar -> SQLClient.session.scalarSQLClient.invalidate -> SQLClient.session.invalidateSQLClient.expire -> SQLClient.session.expireSQLClient.expire_all -> SQLClient.session.expire_allSQLClient.expunge -> SQLClient.session.expungeSQLClient.expunge_all -> SQLClient.session.expunge_allSQLClient.prune -> SQLClient.session.pruneFix compatibility issue with pydash v3.4.7.
core.make_identity factory function for easily creating basic identity functions from a list of model column objects that can be used with save().core.save, core.destroy, core.transaction, and core.make_identity into make package namespace.core.save when providing a custom identity function.identity argument in SQLClient.save and SQLService.save.models variable was mistakenly redefined during loop iteration in core.save.identity argument to save method to allow a custom identity function to support upserting on something other than just the primary key values.Query entity methods entities, join_entities, and all_entities return entity objects instead of model classes. (breaking change)Query methods model_classes, join_model_classes, and all_model_classes return the model classes belonging to a query.<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.Service.find_one, Service.find, and Query.search to accept a list of lists as the criterion argument.ModelBase.Meta to ModelBase.metaclass. (breaking change)metadata object on ModelBase.metadata and having it used when calling declarative_base.metadata and metaclass arguments to declarative_base that taken precedence over the corresponding class attributes set on the passed in declarative base type.SQLClient to __init__ to model_class. (breaking change)Query.top method. (breaking change)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.SQLService.find and SQLService.find_one to Query.before and after callback argument passing from core.save to core._add.before and after callback argument passing from SQLService.save to SQLClient.save.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.Support additional engine and session configuration values for SQLClient.
New engine config options:
SQL_ECHO_POOLSQL_ENCODINGSQL_CONVERT_UNICODESQL_ISOLATION_LEVELNew session config options:
SQL_EXPIRE_ON_COMMITAdd 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 <http://pydash.readthedocs.io/en/latest/api.html#pydash.collections.index_by>_)stack_by: Converts Query.all() to a dict of lists of models indexed by callback (pydash.group_by <http://pydash.readthedocs.io/en/latest/api.html#pydash.collections.group_by>_)map: Maps Query.all() to a callback (pydash.map_ <http://pydash.readthedocs.io/en/latest/api.html#pydash.collections.map_>_)reduce: Reduces Query.all() through callback (pydash.reduce_ <http://pydash.readthedocs.io/en/latest/api.html#pydash.collections.reduce_>_)reduce_right: Reduces Query.all() through callback from right (pydash.reduce_right <http://pydash.readthedocs.io/en/latest/api.html#pydash.collections.reduce_right>_)pluck: Retrieves value of of specified property from all elements of Query.all() (pydash.pluck <http://pydash.readthedocs.io/en/latest/api.html#pydash.collections.pluck>_)chain: Initializes a chain object with Query.all() (pydash.chain <http://pydash.readthedocs.io/en/latest/api.html#pydash.chaining.chain>_)Rename Query properties: (breaking change)
model_classes to entitiesjoined_model_classes to join_entitiesall_model_classes to all_entitiesAdd 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.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_registrymodel_registryAdd 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_onequery_manydefault_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__.
.. _dsully: https://github.com/dsully
MIT License
Copyright (c) 2020 Derrick Gilland
Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
FAQs
The missing SQLAlchemy ORM interface
We found that sqlservice demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
PyPI confirms no security flaws were exploited in the Ultralytics supply chain attack and highlights improvements for safer package publishing.
Security News
Research
The Socket Research Team breaks down a malicious wrapper package that uses obfuscation to harvest credentials and exfiltrate sensitive data.
Research
Security News
Attackers used a malicious npm package typosquatting a popular ESLint plugin to steal sensitive data, execute commands, and exploit developer systems.