SQL usage examples

SQL databases are accessed through SQLAlchemy sessions and any feature SQLAlchemy supports is available to Zato services.

Regardless of how a database is accessed and what sort of queries are issued, it’s always required to close a session object when a service is done using it. It’s easiest to use the Python’s built-in contextlib.closing manager for doing it.

The full API is documented here.

Issuing raw SQL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
# stdlib
from contextlib import closing

# Zato
from zato.server.service import Service

class MyService(Service):
    def handle(self):

        # Always use templates and bind variables
        template = 'SELECT name FROM http_soap WHERE data_format=:data_format'
        parameters = {'data_format':'json'}

        with closing(self.outgoing.sql.get('my-conn').session()) as session:
            result = session.execute(template, parameters)
            for item in result:
                self.logger.info(item)
INFO - (u'zato.security.wss.create.json',)
INFO - (u'zato.http-soap.ping.json',)
INFO - (u'zato.service.configure-request-response.json',)
INFO - (u'zato.kvdb.data-dict.dictionary.get-key-list.json',)

SQLAlchemy models

SQLAlchemy models can be used instead of issuing plain SQL queries. This is what Zato's own services to stay database-neutral and support more than one database engine.

Refer to the API docs for a usage example involving SQLAlchemy models.