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.
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. You can acquire the session from
request via get_session and perform arbitrary operations on it.
from cone.sql import get_session
session = get_session(request)
result = session.query(MyRecord).all()
Test coverage
Summary of the test coverage report:
lines cov% module
50 100% cone.sql.__init__
186 99% cone.sql.model
57 100% cone.sql.testing
18 100% cone.sql.tests
License
Copyright (c) 2017, 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.
