Python SQL

Before they can be used, an SQL connection definition needs to be declared in Dashboard. This transparently creates a background connection pool that your Python code makes use of.

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 is always required to close a session object when a service is done using it. It is easiest to use the Python's built-in contextlib.closing manager for doing it.

Issuing raw SQL queries

# -*- coding: utf-8 -*-

# 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 - ('zato.security.wss.create.json',)
INFO - ('zato.http-soap.ping.json',)
INFO - ('zato.service.configure-request-response.json',)
INFO - ('zato.kvdb.data-dict.dictionary.get-key-list.json',)

SQLAlchemy models

Given that the underlying SQL library is SQLAlchemy, your services can use any feature that the library offers, e.g. it is possible to use its ORM or models instead of issuing queries directly.

# -*- coding: utf-8 -*-

# stdlib
from contextlib import closing

# Zato
from zato.server.service import Service

# Your app
from sql_models import Client

class MyService(Service):

    def handle(self):

        with closing(self.outgoing.sql.get('my-conn').session()) as session:
            client = session.query(Client).\
                filter(Client.name = 'John Doe').\
                filter(Client.segment = 'ABC').\
                one()

            # Process the client selected from the database now ..
            pass

The session object is an SQLAlchemy session object taken from a connection pool defined in Dashboard - refer to the SQLAlchemy's documentation for details of how to use it with any kind of SQL queries.

Microsoft SQL Server (MS SQL)

Note that MS SQL connections allow only for invocation of stored procedures. It is not possible to issue other types of SQL queries nor to use SQLAlchemy models with MS SQL.

Schedule a meaningful demo

Book a demo with an expert who will help you build meaningful systems that match your ambitions

"For me, Zato Source is the only technology partner to help with operational improvements."

— John Adams
Program Manager of Channel Enablement at Keysight