Usage¶
Configuration¶
Before being able to integrate SqlAlchemy with Watson, there are a few things that must be implemented first within your applications config.
- Add the init event to your applications configuration.
'events': {
events.INIT: [
('watson.db.listeners.Init', 1, True)
],
}
- Create a default configuration for a database session.
db = {
'connections': {
'default': {
'connection_string': 'sqlite:///:memory:',
'engine_options': {},
'session_options': {}
}
}
}
engine_options
and session_options
are optional values and can
contain any kwarg values that create_session
and sessionmaker
from SqlAlchemy take.
A full example configuration might look like this:
db = {
'connections': {
'default': {
'connection_string': 'sqlite:///../data/db/default.db',
'metadata': 'app.models.Model',
'engine_options': {
'encoding': 'utf-8',
'echo': False,
'pool_recycle': 3600
}
},
},
'migrations': {
'path': '../data/db/migrations',
'use_twophase': False
},
'fixtures': {
'path': '../data/db/fixtures',
'data': (
# Fixtures will be located in ../data/db/fixtures/model.json
# and will be inserted into the 'default' database.
('model', None),
)
}
}
Fixtures¶
Fixtures are a way of inserting some initial data into a database to populate it. They are stored in basic JSON format, and can be defined as follows.
[
{
"class": "app.models.Model",
"fields": {
"id": 1,
"column": "Value"
}
}
// .. more records
]
Each fixture that is to be loaded via the populate command should be included in the data
value of the fixtures
in the format (FIXTURE_NAME, DATABASE_CONNECTION_NAME)
. If DATABASE_CONNECTION_NAME is set to None, then the default connection will be used.
Migrations¶
Watson DB utilizes Alembic to handle migrations, which can be run via the command line. See the commands section of this document for more information on the individual commands.
Commands¶
The commands available to you are split into two namespaces, db, and db:migrate. These can be accessed via ./console.py db
and ./console.py db:migrate
respectively.
db¶
create
Creates the databases against the associated model metadata and connections.
dump
Prints out the SQL statements used to create the database.
populate
Inserts the data from the fixtures into the databases.
db:migrate¶
These commands are essentially wrappers to the Alembic command line. Additional arguments that can be specified can be found by appending –help to the command.
branches
current
downgrade
history
init
revision
stamp
upgrade
Services¶
Services provide a straightforward way to interact with the models in your application without having to directly call against the SqlAlchemy session itself. Each service should be defined within the configuration to use the relevant SqlAlchemy session in it’s constructor.
dependencies = {
'definitions': {
'myservice': {
'item': 'myapp.services.MyService',
'init': ['sqlalchemy_session_default']
},
'mycontroller': {
'item': 'myapp.controllers.MyController',
'property': {
'service': 'myservice'
}
}
}
}
Example¶
Once configured, the session can be retrieved from the container via
container.get('sqlalchemy_session_[SESSION_NAME]')
.
watson.db also provides a paginator class for paginating a set of results back from SQLAlchemy. Basic usage includes:
# within myapp.models
from watson.db import models
class MyModel(models.Model):
# .. columns
# within myapp.services
from watson.db import services
from myapp import models
class MyService(services.Base):
__model__ = models.MyModel
# within myapp.controllers, assuming the MyService object has
# been injected into the controller as the `service` attribute.
from watson.db import utils
from watson.framework import controllers
class MyController(controllers.Rest):
def GET(self):
return {
'paginator': utils.Pagination(self.service.query, limit=50)
}
# within view
{% for item in paginator %}
{% endfor %}
<div class="pagination">
{% for page in paginator.iter_pages() %}
{% if page == paginator.page %}
<a href="#" class="current">{{ page }}</a>
{% else %}
<a href="#">{{ page }}</a>
{% endif %}
{% endfor %}
</div>