Skip to main content

SQLAlchemy integration for cone.app

Project description

Latest PyPI version Number of PyPI downloads https://travis-ci.org/bluedynamics/cone.sql.svg?branch=master https://coveralls.io/repos/github/bluedynamics/cone.sql/badge.svg?branch=master

cone.sql

This package provides SQLAlchemy integration in cone.app and basic application nodes for publishing SQLAlchemy models.

Installation

Include cone.sql to install dependencies in your application’s setup.py.

Configure Database and WSGI

Adopt your application config ini file to define database location and hook up the related elements to the WSGI pipeline.

[app:my_app]
use = egg:cone.app#main

pyramid.includes =
    pyramid_retry
    pyramid_tm

tm.commit_veto = pyramid_tm.default_commit_veto

cone.plugins =
    cone.sql

cone.sql.db.url = sqlite:///%(here)s/var/sqlite/my_db.db

[filter:remote_addr]
# for use behind nginx
use = egg:cone.app#remote_addr

[filter:session]
use = egg:cone.sql#session

[pipeline:main]
pipeline =
    remote_addr
    session
    my_app

Create Model and Nodes

Define the SQLAlchemy model.

from cone.sql import SQLBase
from cone.sql.model import GUID
from sqlalchemy import Column
from sqlalchemy import String

class MyRecord(SQLBase):
    __tablename__ = 'my_table'
    uid_key = Column(GUID, primary_key=True)
    field = Column(String)

Define an application node which represents the SQL row and uses the SQLAlchemy model. The class holds a reference to the related SQLAlchemy model.

from cone.sql.model import SQLRowNode

class MyNode(SQLRowNode):
    record_class = MyRecord

Define an application node which represents the table and acts as container for the SQL row nodes. The class holds a reference to the related SQLAlchemy model and the related SQLRowNode.

from cone.sql.model import SQLTableNode

class MyContainer(SQLTableNode):
    record_class = MyRecord
    child_factory = MyNode

Primary key handling

The node name maps to the primary key of the SQLAlchemy model (currenly no multiple primary keys are supported). Node names are converted to the primary key data type automatically. The conversion factories are defined at SQLTableNode.data_type_converters which can be extended by more data types if needed.

>>> SQLTableNode.data_type_converters
{<class 'sqlalchemy.sql.sqltypes.String'>: <type 'unicode'>,
<class 'cone.sql.model.GUID'>: <class 'uuid.UUID'>,
<class 'sqlalchemy.sql.sqltypes.Integer'>: <type 'int'>}

Integrate to the Application Model

In order to publish a SQL table node, the table node must be hooked up to the application model. To hook up the at root level, register it as entry.

import cone.app

cone.app.register_entry('container', MyContainer)

Session setup handlers

There exists a sql_session_setup decorator which can be used to perform session setup tasks like registering SQLAlchemy event listeners.

from cone.sql import sql_session_setup
from sqlalchemy import event

def after_flush(session, flush_context):
    """Do something after flush.
    """

@sql_session_setup
def bind_session_listener(session):
    """SQL session setup callback.
    """
    event.listen(session, 'after_flush', after_flush)

Query the database

Querying the database is done via SQLAlchemy. If you are in a request/response cycle, you should acquire the session from request via get_session and perform arbitrary operations on it. By reading the session from request we ensure the transaction manager to work properly if configured.

from cone.sql import get_session

session = get_session(request)
result = session.query(MyRecord).all()

If you need a session outside a request/response cycle you can create one by using the session_factory.

from cone.sql import session_factory

session = session_factory()
result = session.query(MyRecord).all()
session.close()

Principal ACL’s

SQL based Principal ACL’s are implemented in cone.sql.acl. The related table gets created as soon as you import from this module.

Using SQLPrincipalACL requires the model to implement node.interfaces.IUUID.

from cone.sql.acl import SQLPrincipalACL
from node.base import BaseNode
from node.interfaces import IUUID
from plumber import plumbing
from pyramid.security import Allow
from zope.interface import implementer
import uuid as uuid_module

@implementer(IUUID)
@plumbing(SQLPrincipalACL)
class SQLPrincipalACLNode(BaseNode):
    uuid = uuid_module.UUID('1a82fa87-08d6-4e48-8bc2-97ee5a52726d')

    @property
    def __acl__(self):
        return [
            (Allow, 'role:editor', ['edit']),
            (Allow, 'role:manager', ['manage']),
        ]

TODO

  • Support multiple primary keys.

Test coverage

Summary of the test coverage report:

Name                               Stmts   Miss  Cover
------------------------------------------------------
src/cone/sql/__init__.py              50      0   100%
src/cone/sql/acl.py                   62      0   100%
src/cone/sql/model.py                162      0   100%
src/cone/sql/testing.py               36      0   100%
src/cone/sql/tests/__init__.py        18      0   100%
src/cone/sql/tests/test_acl.py        86      0   100%
src/cone/sql/tests/test_model.py     225      0   100%
src/cone/sql/tests/test_sql.py        38      0   100%
------------------------------------------------------
TOTAL                                677      0   100%

Contributors

  • Robert Niederreiter (Author)

Changes

0.2 (2020-05-30)

  • Introduce cone.sql.SQLSessionFactory. Gets instanciated at application startup as singleton at cone.sql.session_factory. [rnix]

  • SQL database URL setting key in ini file changed from cone.sql.dbinit.url to cone.sql.db.url. [rnix]

  • SQL database URL definition is only required once in the app section of the ini file. sqlalchemy.url can be removed from session filter. [rnix]

  • Add SQL based principal ACL support. [rnix]

  • Python 3 compatibility. [rnix]

  • Fix hex formatting in cone.sql.model.GUID.process_bind_param. [rnix]

  • Register SQL session to transaction manager with zope.sqlalchemy.register. [rnix]

  • Use pyramid_tm instead of repoze.tm2. Disabled by default, must be enabled explicitely via pyramid.includes. [rnix]

  • Use pyramid_retry instead of repoze.retry. Disabled by default, must be enabled explicitely via pyramid.includes. [rnix]

  • Upgrade to cone.app 1.0b1. [rnix]

0.1 (2017-03-28)

  • Initial work. [rnix]

License

Copyright (c) 2017-2020, BlueDynamics Alliance, Austria All rights reserved.

Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:

  • Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.

  • Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.

  • Neither the name of the BlueDynamics Alliance nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.

THIS SOFTWARE IS PROVIDED BY BlueDynamics Alliance AS IS AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL BlueDynamics Alliance BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

cone.sql-0.2.tar.gz (15.7 kB view hashes)

Uploaded Source

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page