pyramid_sqlassist
=================
SQLAssist offers a streamlined integration for handling multiple
[SQLAlchemy](https://github.com/sqlalchemy/sqlalchemy) database connections
under [Pyramid](https://github.com/pylons/pyramid). It ships with first-party
support for "reader", "writer" and "logger" concepts - but can be extended to
support any design.
SQLAssist also offers some utility mixin/base classes for SQLAlchemy
applications that are useful for debugging applications. A debugtoolbar panel
is included as well, which can be used to further aid in development.
This package has been working in production environments for many years.
PRs are always appreciated.
Recent changes:
With `v0.16.0`, SQLAlchemy 2.0 and Python 3.7 are required.
With `v0.13.0`, SQLAlchemy 1.3.0 and zope.sqlalchemy 1.2.0 are required.
With `v0.12.0`, there have been some API changes and the introduction of a
`pyramid_debugtoolbar` panel.
# WARNING
This package uses scoped Sessions by default.
`v0.9.1` introduced a capability to use non-scoped Sessions.
This appears to work, but has not been tested as thoroughly as I'd like.
Non-scoped Sessions are not integrated with the `transaction` package, as they
were incompatible with Zope's transaction extension when support was last
attempted.
There is probably a way to get this to work, or things may have changed.
Pull Requests are welcome.
# Overview
The package facilitates managing multiple SQLAlchemy connections under Pyramid
through a single API. It has been used in Celery too.
There are 4 steps to using this package:
1. It is the job of your Pyramid application's `model` to create
SQLAlchemy engines.
2. Each created engine should be passed into
`pyramid_sqlassist.initialize_engine`
3. After initializing all the engines, invoke
`pyramid_sqlassist.register_request_method` with the name of the request
attribute you wish to use
4. SQLAlchemy classes in your model must inherit from
`pyramid_sqlassist.DeclaredTable` -- which is just an instance of
SQLAlchemy's `declarative_base`.
Note: If your Pyramid application connects to the database BEFORE a process
fork, you must call `pyramid_sqlassist.reinit_engine(/engine/)`. This can be
streamlined with the
[`pyramid_forksafe`](https://github.com/jvanasco/pyramid_forksafe) plugin.
## What does all this accomplish?
`pyramid_sqlassist` maintains a private Python dict in it's
namespace: `_ENGINE_REGISTRY`.
Calling `initialize_engine` will wrap each SQLAlchemy engine into a SQLAssist
`EngineWrapper` and then register it into the `_ENGINE_REGISTRY`. The wrapper
contains a SQLAlchemy `sessionmaker` created for each engine, along with some
convenience functions.
Calling `register_request_method` will invoke Pyramid's `add_request_method` to
add a `DbSessionsContainer` onto the Pyramid Request as a specified attribute
name.
The `DbSessionsContainer` automatically register a cleanup function via
Pyramid's `add_finished_callback` if the database is used.
As a convenience, the active Pyramid request is stashed in each SQLAlchmey
session's "info" dict.
# Example
This is an example `model.py` for a Pyramid app, which creates a READER and
WRITER connection.
# model.py
import sqlalchemy
import pyramid_sqlassist
from . import model_objects
def initialize_database(config, settings):
# setup the reader
engine_reader = sqlalchemy.engine_from_config(settings,
prefix="sqlalchemy_reader.",
)
pyramid_sqlassist.initialize_engine('reader',
engine_reader,
is_default=False,
model_package=model_objects,
use_zope=False,
is_scoped=is_scoped,
)
# setup the writer
engine_writer = sqlalchemy.engine_from_config(settings,
prefix="sqlalchemy_writer.",
echo=sqlalchemy_echo,
)
pyramid_sqlassist.initialize_engine('writer',
engine_writer,
is_default=False,
model_package=model_objects,
use_zope=False,
is_scoped=is_scoped,
)
# setup the shared interface
pyramid_sqlassist.register_request_method(config, 'dbSession')
# Miscellaneous info
Because Pyramid will lazily create the request database interaction object, it
is very lightweight. On initialization, the container will register a
cleanup routine via `add_finished_callback`.
The `DbSessionsContainer` exposes some methods:
* `reader` - property. memoized access to "reader" connection
* `writer` - property. memoized access to "writer" connection
* `logger` - property. memoized access to "logger" connection
* `any` - property. invokes `get_any()`
* `get_reader` - method. lazy access to "reader" connection
* `get_writer` - method. lazy access to "writer" connection
* `get_logger` - method. lazy access to "logger" connection
* `get_any` - method. tries to find memoized connections.
otherwise will invoke another method.
On first access of every "Session", the container will re-initialize that
Session by invoking it as a callable, issuing a `.rollback()`, and stashing the
current Pyramid request in the Session's `info` dict.
Within your code, the request can be retrieved via `object_session`
from sqlalchemy.orm.session import object_session
_session = object_session(ExampleObject)
request = _session.info['request']
The cleanup function will call `session.remove()` for all Sessions that were
used within the request.
A postfork hook is available if needed via `reinit_engine`.
For all managed engines, `engine.dispose()` will be called.
# Why it works:
`DeclaredTable` is simply an instance of
`sqlalchemy.ext.declarative.declarative_base`, bound to our own metadata
# via Pyramid
# Recommended naming convention used by Alembic, as various different database
# providers will autogenerate vastly different names making migrations more
# difficult. See: http://alembic.zzzcomputing.com/en/latest/naming.html
NAMING_CONVENTION = {
"ix": 'ix_%(column_0_label)s',
"uq": "uq_%(table_name)s_%(column_0_name)s",
"ck": "ck_%(table_name)s_%(constraint_name)s",
"fk": "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
"pk": "pk_%(table_name)s"
}
# store the metadata in the package (GLOBAL)
_metadata = sqlalchemy.MetaData(naming_convention=NAMING_CONVENTION)
# this is used for inheritance only
DeclaredTable = declarative_base(metadata=_metadata)
Subclassing tables from `DeclaredTable` takes care of all the core ORM setup.
When `initialize_engine` is called, by default
`sqlalchemy.orm.configure_mappers` is triggered (this can be deferred to first
usage of the ORM, but most people will want to take the performance hit on
startup and try to push the mapped tables into shared memory before a fork).
# Misc Objects
## `objects.UtilityObject`
* core object with utility methods for quick prototyping of applications
## `.tools`
* this namepace is currently unused;
it houses some in-progress code for supporting table reflection
# debugtoolbar support
Simply add `pyramid_sqlassist.debugtoolbar` to `debugtoolbar.includes`
for your application.
For example, this is a line from a `.ini` file
debugtoolbar.includes = pyramid_sqlassist.debugtoolbar
The SQLAssist debugtoolbar panel includes information such as:
* the request attribute the `DbSessionsContainer` has been memoized into
* the connections which were active for the request
* the engine status for the active request (initialized, started, ended)
* the engines configured for the application and available to the request
The panel is intended to help debug issues with connections - though there
should be none.
# TODO:
* debugtoolbar: show id information for the underlying connections and
connection pool to help troubleshoot potential forking issues
# Notes
* `PYTHONOPTIMIZE`. All logging functions are nested under `if __debug__:`
statements; they can be compiled away during production
# Thanks
Sections of this code were originally taken from or inspired by:
* SQLAlchemy docs
* [Using Thread-Local Scope with Web Applications](http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#using-thread-local-scope-with-web-applications)
* [Session Frequently Asked Questions](http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#session-frequently-asked-questions)
* Mike Bayer's blog post 'Django-style Database Routers in SQLAlchemy'
* Pyramid's `@reify` decorator and `set_request_property` attribute
* Mike Orr's package 'sqlahelper'
* this was originally based on FindMeOn™'s Pylons based library "opensocialnetwork"
# Example Usage
In your `env.ini`, specify multiple sqlalchemy urls (which might be to
different dbs or the same db but with different permissions):
sqlalchemy_reader.url = postgres://myapp_reader:myapp@localhost/myapp
sqlalchemy_writer.url = postgres://myapp_writer:myapp@localhost/myapp
/__init__.py:main
from . import models
try:
import uwsgi
def post_fork_hook():
models.database_postfork()
uwsgi.post_fork_hook = post_fork_hook
except ImportError:
pass
def main(global_config, **settings):
...
models.initialize_database(settings)
...
/models/__init__.py
import sqlassist
ENGINES_ENABLED = ['reader', 'writer', ]
def initialize_database(settings):
engine_reader = sqlalchemy.engine_from_config(settings, prefix="sqlalchemy_reader.")
sqlassist.initialize_engine('reader',engine_reader,default=True, reflect=myapp.models, use_zope=False)
engine_writer = sqlalchemy.engine_from_config(settings, prefix="sqlalchemy_writer.")
sqlassist.initialize_engine('writer',engine_writer,default=False, reflect=myapp.models, use_zope=True)
# custom property: `request.dbSession`
config.add_request_method(
request_setup_dbSession,
'dbSession',
reify=True,
)
def database_postfork():
for i in ENGINES_ENABLED:
sqlassist.reinit_engine(i)
def request_setup_dbSession(request):
return sqlassist.DbSessionsContainer(request)
/models/actual_models.py
import sqlalchemy as sa
from sqlassist import DeclaredTable
class TestTable(DeclaredTable):
__tablename__ = 'groups'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.Unicode(255), nullable=False)
description = sa.Column(sa.Text, nullable=False)
in your handlers, you have the following. Note, `sqlalchemy` is only imported
to grab an exception:
import sqlalchemy
class BaseHandler(object):
def __init__(self,request):
self.request = request
class ViewHandler(BaseHandler):
def index(self):
print self.request.dbSession.reader.query(models.actual_models.TestTable).all()
try:
#this should fail , assuming reader can't write
dbTestTable = models.actual_models.TestTable()
dbTestTable.name= 'Test Case 1'
self.request.dbSession.reader.add(dbTestTable)
self.request.dbSession.reader.commit()
except sqlalchemy.exc.ProgrammingError:
self.request.dbSession.reader.rollback()
raise ValueError("Commit Failed!")
#but this should work , assuming writer can write
dbTestTable = models.actual_models.TestTable()
dbTestTable.name = 'Test Case 2'
self.request.dbSession.writer.add(dbTestTable)
self.request.dbSession.writer.commit()
# UtilityObject
If you inherit from this class, your SQLAlchemy objects have some convenience
methods:
* `get__by__id`( self, dbSession, id_ , id_column='id' ):
* `get__by__ids`( self, dbSession, ids , id_column='id' ):
* `get__by__column__lower`( self, dbSession, column_name , search , allow_many=False ):
* `get__by__column__similar`( self, dbSession , column_name , seed , prefix_only=True):
* `get__by__column__exact_then_ilike`( self, dbSession, column_name, seed ):
* `get__range`( self, dbSession, start=0, limit=None, sort_direction='asc', order_col=None, order_case_sensitive=True, filters=[], debug_query=False):
* `columns_as_dict`(self):
# Another important note...
## DbSessionsContainer
This convenience class ONLY deals with 3 connections right now :
* reader
* writer
* logger
If you have more/different names - subclass (or create a patch to deal with
dynamic names!) I didn't have time for that.
The reader and writer classes will start with an automatic rollback;
The logger will not.
# `transaction` support
By default, the package will try to load the following libraries:
import transaction
from zope.sqlalchemy import register as zope_register
This can be disabled with an environment variable
export SQLASSIST_DISABLE_TRANSACTION=1
# Caveats
## $$COMMIT$$
if you're using "Zope" & "transaction" modules :
* you need to call `transaction.commit`
* IMPORTANT remember that `mark_changed` exists!
if you're not using "Zope" & "transaction" modules :
* you need to call "dbSession_writer.commit()"
## Rollbacks
you want to call `rollback` on the specific database Sessions to control what
is in each one
## catching exceptions if you're trying to support both `transaction.commit()` and `dbsession.commit()`
let's say you do this:
try:
dbSession_writer_1.add(object1)
dbSession_writer_1.commit()
except AssertionError , e:
print "Should fail because Zope wants this"
# add to writer
dbSession_writer_2.add(object2)
# commit
transaction.commit()
in this event, both object1 and object2 will be committed by `transaction.commit()`
You must explicitly invoke a `rollback` after the `AssertionError`
# Reflected Tables
this package once supported trying to handle table reflection.
It is being removed unless someone wants to do a better job.
Raw data
{
"_id": null,
"home_page": "https://github.com/jvanasco/pyramid_sqlassist",
"name": "pyramid-sqlassist",
"maintainer": null,
"docs_url": null,
"requires_python": null,
"maintainer_email": null,
"keywords": "web Pyramid SQLAlchemy",
"author": "Jonathan Vanasco",
"author_email": "jonathan@findmeon.com",
"download_url": "https://files.pythonhosted.org/packages/99/70/8b0a53406b809503dabcbfa1ef0b04d8247a61b07bb426985cccc35feaa2/pyramid_sqlassist-0.16.0.tar.gz",
"platform": null,
"description": "\n\npyramid_sqlassist\n=================\n\nSQLAssist offers a streamlined integration for handling multiple\n[SQLAlchemy](https://github.com/sqlalchemy/sqlalchemy)\u00a0database connections\nunder [Pyramid](https://github.com/pylons/pyramid). It ships with first-party\nsupport for \"reader\", \"writer\" and \"logger\" concepts - but can be extended to\nsupport any design.\n\nSQLAssist also offers some utility mixin/base classes for SQLAlchemy\napplications that\u00a0are useful for debugging applications. A debugtoolbar panel\nis included as well, which can be used to further aid in development.\n\nThis package has been working in production environments for many years.\n\nPRs are always appreciated.\n\nRecent changes:\n\nWith `v0.16.0`, SQLAlchemy 2.0 and Python 3.7 are required.\n\nWith `v0.13.0`, SQLAlchemy 1.3.0 and zope.sqlalchemy 1.2.0 are required.\n\nWith `v0.12.0`, there have been some API changes and the introduction of a\n`pyramid_debugtoolbar` panel.\n\n\n# WARNING\n\nThis package uses scoped Sessions by default.\n\n`v0.9.1` introduced a capability to use non-scoped Sessions. \nThis appears to work, but has not been tested as thoroughly as I'd like.\n\nNon-scoped Sessions are not integrated with the `transaction` package, as they\nwere incompatible with Zope's transaction extension when support was last\nattempted.\n\nThere is probably a way to get this to work, or things may have changed.\nPull Requests are welcome.\n\n\n# Overview\n\nThe package facilitates managing multiple SQLAlchemy connections under Pyramid\nthrough a single API. It has been used in Celery too.\n\nThere are 4 steps to using this package:\n\n1. It is the job of your Pyramid application's `model` to create\n SQLAlchemy engines.\n2. Each created engine should be passed into\n `pyramid_sqlassist.initialize_engine`\n3. After initializing all the engines, invoke\n `pyramid_sqlassist.register_request_method` with the name of the request\n attribute you wish to use\n4. SQLAlchemy classes in your model must inherit from\n `pyramid_sqlassist.DeclaredTable` -- which is just an instance of\n SQLAlchemy's `declarative_base`.\n\nNote: If your Pyramid application connects to the database BEFORE a process\nfork, you must call `pyramid_sqlassist.reinit_engine(/engine/)`. This can be\nstreamlined with the\n[`pyramid_forksafe`](https://github.com/jvanasco/pyramid_forksafe) plugin. \n\n\n## What does all this accomplish?\n\n`pyramid_sqlassist` maintains a private Python dict in it's\nnamespace: `_ENGINE_REGISTRY`. \n\nCalling `initialize_engine` will wrap each SQLAlchemy engine into a SQLAssist\n`EngineWrapper` and then register it into the `_ENGINE_REGISTRY`. The wrapper\ncontains a SQLAlchemy `sessionmaker` created for each engine, along with some\nconvenience functions.\n\nCalling `register_request_method` will invoke Pyramid's `add_request_method` to\nadd a `DbSessionsContainer` onto the Pyramid Request as a specified attribute\nname.\n\nThe `DbSessionsContainer` automatically register a cleanup function via\nPyramid's `add_finished_callback` if the database is used.\n\nAs a convenience, the active Pyramid request is stashed in each SQLAlchmey\nsession's \"info\" dict.\n\n\n# Example\n\nThis is an example `model.py` for a Pyramid app, which creates a READER and\nWRITER connection.\n\n\n # model.py\n\timport sqlalchemy\n\timport pyramid_sqlassist\n\n\tfrom . import model_objects\n\n\n def initialize_database(config, settings):\n\n\t\t# setup the reader\n\t\tengine_reader = sqlalchemy.engine_from_config(settings,\n\t\t\t\t\t\t\t\t\t\t\t\t\t prefix=\"sqlalchemy_reader.\",\n\t\t\t\t\t\t\t\t\t\t\t\t\t )\n\t\tpyramid_sqlassist.initialize_engine('reader',\n\t\t\t\t\t\t\t\t\t\t\tengine_reader,\n\t\t\t\t\t\t\t\t\t\t\tis_default=False,\n\t\t\t\t\t\t\t\t\t\t\tmodel_package=model_objects,\n\t\t\t\t\t\t\t\t\t\t\tuse_zope=False,\n\t\t\t\t\t\t\t\t\t\t\tis_scoped=is_scoped,\n\t\t\t\t\t\t\t\t\t\t\t)\n\n\t\t# setup the writer\n\t\tengine_writer = sqlalchemy.engine_from_config(settings,\n\t\t\t\t\t\t\t\t\t\t\t\t\t prefix=\"sqlalchemy_writer.\",\n\t\t\t\t\t\t\t\t\t\t\t\t\t echo=sqlalchemy_echo,\n\t\t\t\t\t\t\t\t\t\t\t\t\t )\n\t\tpyramid_sqlassist.initialize_engine('writer',\n\t\t\t\t\t\t\t\t\t\t\tengine_writer,\n\t\t\t\t\t\t\t\t\t\t\tis_default=False,\n\t\t\t\t\t\t\t\t\t\t\tmodel_package=model_objects,\n\t\t\t\t\t\t\t\t\t\t\tuse_zope=False,\n\t\t\t\t\t\t\t\t\t\t\tis_scoped=is_scoped,\n\t\t\t\t\t\t\t\t\t\t\t)\n\n\t\t# setup the shared interface\n\t\tpyramid_sqlassist.register_request_method(config, 'dbSession')\n\n\n\n# Miscellaneous info\n\nBecause Pyramid will lazily create the request database interaction object, it\nis very lightweight. On initialization, the container will register a\ncleanup routine via `add_finished_callback`.\n\t\nThe `DbSessionsContainer` exposes some methods:\n\n* `reader` - property. memoized access to \"reader\" connection\n* `writer` - property. memoized access to \"writer\" connection\n* `logger` - property. memoized access to \"logger\" connection\n* `any` - property. invokes `get_any()`\n\n* `get_reader` - method. lazy access to \"reader\" connection\n* `get_writer` - method. lazy access to \"writer\" connection\n* `get_logger` - method. lazy access to \"logger\" connection\n* `get_any` - method. tries to find memoized connections.\n otherwise will invoke another method.\n\nOn first access of every \"Session\", the container will re-initialize that\nSession by invoking it as a callable, issuing a `.rollback()`, and stashing the\ncurrent Pyramid request in the Session's `info` dict. \n\nWithin your code, the request can be retrieved via `object_session`\n\n\tfrom sqlalchemy.orm.session import object_session\n\t_session = object_session(ExampleObject)\n\trequest = _session.info['request']\n\nThe cleanup function will call `session.remove()` for all Sessions that were\nused within the request.\n\nA postfork hook is available if needed via `reinit_engine`. \nFor all managed engines, `engine.dispose()` will be called.\n\n# Why it works:\n\n`DeclaredTable` is simply an instance of\n`sqlalchemy.ext.declarative.declarative_base`, bound to our own metadata\n\n\t# via Pyramid\n\t# Recommended naming convention used by Alembic, as various different database\n\t# providers will autogenerate vastly different names making migrations more\n\t# difficult. See: http://alembic.zzzcomputing.com/en/latest/naming.html\n\tNAMING_CONVENTION = {\n\t\t\"ix\": 'ix_%(column_0_label)s',\n\t\t\"uq\": \"uq_%(table_name)s_%(column_0_name)s\",\n\t\t\"ck\": \"ck_%(table_name)s_%(constraint_name)s\",\n\t\t\"fk\": \"fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s\",\n\t\t\"pk\": \"pk_%(table_name)s\"\n\t}\n\n\t# store the metadata in the package (GLOBAL)\n\t_metadata = sqlalchemy.MetaData(naming_convention=NAMING_CONVENTION)\n\n\t# this is used for inheritance only\n DeclaredTable = declarative_base(metadata=_metadata)\n\nSubclassing tables from `DeclaredTable` takes care of all the core ORM setup.\n\nWhen `initialize_engine` is called, by default\n`sqlalchemy.orm.configure_mappers` is triggered (this can be deferred to first\nusage of the ORM, but most people will want to take the performance hit on\nstartup and try to push the mapped tables into shared memory before a fork).\n\n\n# Misc Objects\n\n## `objects.UtilityObject`\n\n* core object with utility methods for quick prototyping of applications\n\n## `.tools`\n\n* this namepace is currently unused;\n it houses some in-progress code for supporting table reflection\n\n\n# debugtoolbar support\n\nSimply add `pyramid_sqlassist.debugtoolbar` to `debugtoolbar.includes`\nfor your application.\n\nFor example, this is a line from a `.ini` file\n\n\tdebugtoolbar.includes = pyramid_sqlassist.debugtoolbar\n\nThe SQLAssist debugtoolbar panel includes information such as:\n\n* the request attribute the `DbSessionsContainer` has been memoized into\n* the connections which were active for the request\n* the engine status for the active request (initialized, started, ended)\n* the engines configured for the application and available to the request \n\nThe panel is intended to help debug issues with connections - though there\nshould be none. \n\n\n# TODO:\n\n* debugtoolbar: show id information for the underlying connections and\n connection pool to help troubleshoot potential forking issues\n\n\n# Notes\n\n* `PYTHONOPTIMIZE`. All logging functions are nested under `if __debug__:`\n statements; they can be compiled away during production\n\n\n# Thanks\n\nSections of this code were originally taken from or inspired by:\n\n* SQLAlchemy docs\n * [Using Thread-Local Scope with Web Applications](http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#using-thread-local-scope-with-web-applications)\n * [Session Frequently Asked Questions](http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#session-frequently-asked-questions)\n* Mike Bayer's blog post 'Django-style Database Routers in SQLAlchemy'\n* Pyramid's `@reify` decorator and `set_request_property` attribute\n* Mike Orr's package 'sqlahelper'\n* this was originally based on FindMeOn\u2122's Pylons based library \"opensocialnetwork\"\n\n\n# Example Usage\n\nIn your `env.ini`, specify multiple sqlalchemy urls (which might be to\ndifferent dbs or the same db but with different permissions):\n\n\tsqlalchemy_reader.url = postgres://myapp_reader:myapp@localhost/myapp\n\tsqlalchemy_writer.url = postgres://myapp_writer:myapp@localhost/myapp\n\n/__init__.py:main\n\n\tfrom . import models\n\n try:\n import uwsgi\n\n def post_fork_hook():\n models.database_postfork()\n\n uwsgi.post_fork_hook = post_fork_hook\n\n except ImportError:\n pass\n\n\tdef main(global_config, **settings):\n\t\t...\n\t\tmodels.initialize_database(settings)\n\t\t...\n\n/models/__init__.py\n\n\timport sqlassist\n\t\n\tENGINES_ENABLED = ['reader', 'writer', ]\n\n\tdef initialize_database(settings):\n\n\t\tengine_reader = sqlalchemy.engine_from_config(settings, prefix=\"sqlalchemy_reader.\")\n\t\tsqlassist.initialize_engine('reader',engine_reader,default=True, reflect=myapp.models, use_zope=False)\n\n\t\tengine_writer = sqlalchemy.engine_from_config(settings, prefix=\"sqlalchemy_writer.\")\n\t\tsqlassist.initialize_engine('writer',engine_writer,default=False, reflect=myapp.models, use_zope=True)\n\n\t\t# custom property: `request.dbSession`\n\t\tconfig.add_request_method(\n\t\t\trequest_setup_dbSession,\n\t\t\t'dbSession',\n\t\t\treify=True,\n\t\t)\n\n\tdef database_postfork():\n\t\tfor i in ENGINES_ENABLED:\n\t\t\tsqlassist.reinit_engine(i)\n\n\tdef request_setup_dbSession(request):\n\t\treturn sqlassist.DbSessionsContainer(request)\n\n/models/actual_models.py\n\n\timport sqlalchemy as sa\n\tfrom sqlassist import DeclaredTable\n\n\tclass TestTable(DeclaredTable):\n\t\t__tablename__ = 'groups'\n\n\t\tid = sa.Column(sa.Integer, primary_key=True)\n\t\tname = sa.Column(sa.Unicode(255), nullable=False)\n\t\tdescription = sa.Column(sa.Text, nullable=False)\n\nin your handlers, you have the following. Note, `sqlalchemy` is only imported\nto grab an exception:\n\n\timport sqlalchemy\n\n\tclass BaseHandler(object):\n\t\tdef __init__(self,request):\n\t\t\tself.request = request\n\n\tclass ViewHandler(BaseHandler):\n\n\t\tdef index(self):\n\n\t\t\tprint self.request.dbSession.reader.query(models.actual_models.TestTable).all()\n\n\t\t\ttry:\n\t\t\t\t#this should fail , assuming reader can't write\n\t\t\t\tdbTestTable = models.actual_models.TestTable()\n\t\t\t\tdbTestTable.name= 'Test Case 1'\n\t\t\t\tself.request.dbSession.reader.add(dbTestTable)\n\t\t\t\tself.request.dbSession.reader.commit()\n\t\n\t\t\texcept sqlalchemy.exc.ProgrammingError:\n\t\t\t\tself.request.dbSession.reader.rollback()\n\t\t\t\traise ValueError(\"Commit Failed!\")\n\n\t\t\t#but this should work , assuming writer can write\n\t\t\tdbTestTable = models.actual_models.TestTable()\n\t\t\tdbTestTable.name = 'Test Case 2'\n\t\t\tself.request.dbSession.writer.add(dbTestTable)\n\t\t\tself.request.dbSession.writer.commit()\n\n\n# UtilityObject\n\nIf you inherit from this class, your SQLAlchemy objects have some convenience\nmethods:\n\n* `get__by__id`( self, dbSession, id_ , id_column='id' ):\n* `get__by__ids`( self, dbSession, ids , id_column='id' ):\n* `get__by__column__lower`( self, dbSession, column_name , search , allow_many=False ):\n* `get__by__column__similar`( self, dbSession , column_name , seed , prefix_only=True):\n* `get__by__column__exact_then_ilike`( self, dbSession, column_name, seed ):\n* `get__range`( self, dbSession, start=0, limit=None, sort_direction='asc', order_col=None, order_case_sensitive=True, filters=[], debug_query=False):\n* `columns_as_dict`(self):\n\n\n\n# Another important note...\n\n## DbSessionsContainer\n\nThis convenience class ONLY deals with 3 connections right now :\n\n* reader\n* writer\n* logger\n\nIf you have more/different names - subclass (or create a patch to deal with\ndynamic names!) I didn't have time for that.\n\nThe reader and writer classes will start with an automatic rollback;\nThe logger will not.\n\n\n# `transaction` support\n\nBy default, the package will try to load the following libraries:\n\n import transaction\n from zope.sqlalchemy import register as zope_register\n\nThis can be disabled with an environment variable\n\n\texport SQLASSIST_DISABLE_TRANSACTION=1\n\n\n\n# Caveats\n\n## $$COMMIT$$\n\nif you're using \"Zope\" & \"transaction\" modules :\n\n* you need to call `transaction.commit`\n* IMPORTANT remember that `mark_changed` exists!\n\nif you're not using \"Zope\" & \"transaction\" modules :\n\n* you need to call \"dbSession_writer.commit()\"\n\n## Rollbacks\n\nyou want to call `rollback` on the specific database Sessions to control what\nis in each one\n\n\n## catching exceptions if you're trying to support both `transaction.commit()` and `dbsession.commit()`\n\nlet's say you do this:\n\n\ttry:\n\t\tdbSession_writer_1.add(object1)\n\t\tdbSession_writer_1.commit()\n\texcept AssertionError , e:\n\t\tprint \"Should fail because Zope wants this\"\n\n\t# add to writer\n\tdbSession_writer_2.add(object2)\n\n\t# commit\n\ttransaction.commit()\n\nin this event, both object1 and object2 will be committed by `transaction.commit()`\n\nYou must explicitly invoke a `rollback` after the `AssertionError`\n\n\n# Reflected Tables\n\nthis package once supported trying to handle table reflection. \nIt is being removed unless someone wants to do a better job.\n\n",
"bugtrack_url": null,
"license": "MIT",
"summary": "Efficiently manage multiple SqlAlchemy connections for Pyramid",
"version": "0.16.0",
"project_urls": {
"Homepage": "https://github.com/jvanasco/pyramid_sqlassist"
},
"split_keywords": [
"web",
"pyramid",
"sqlalchemy"
],
"urls": [
{
"comment_text": null,
"digests": {
"blake2b_256": "99708b0a53406b809503dabcbfa1ef0b04d8247a61b07bb426985cccc35feaa2",
"md5": "55261dd0b897ff154afd994e892db9eb",
"sha256": "7bae3f8018cf423f49d9a64b688f37daaffdb7677ae06e3bb649ede5ad8ef093"
},
"downloads": -1,
"filename": "pyramid_sqlassist-0.16.0.tar.gz",
"has_sig": false,
"md5_digest": "55261dd0b897ff154afd994e892db9eb",
"packagetype": "sdist",
"python_version": "source",
"requires_python": null,
"size": 31562,
"upload_time": "2025-09-01T20:15:09",
"upload_time_iso_8601": "2025-09-01T20:15:09.991112Z",
"url": "https://files.pythonhosted.org/packages/99/70/8b0a53406b809503dabcbfa1ef0b04d8247a61b07bb426985cccc35feaa2/pyramid_sqlassist-0.16.0.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2025-09-01 20:15:09",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "jvanasco",
"github_project": "pyramid_sqlassist",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"tox": true,
"lcname": "pyramid-sqlassist"
}
JSON
