Outgoing SQL connections

Overview

Fetches an SQLAlchemy connection session from a pool of connections to the SQL database specified.

The session needs to be closed by the programmer - otherwise the pool will run out of connections - the easiest way to do it is to use the with statement along with the closing decorator, as in an example below.

The database may be one of:

  • PostgreSQL
  • Oracle

The underlying SQL toolkit is SQLAlchemy.

When using Simple IO (SIO) in conjunction with SQLAlchemy models, services are able to construct responses - JSON or XML ones - directly out of a model’s instance, such as in a usage example.

Note

Support for Oracle connections needs to be manually enabled by administrators.

API

self.outgoing.sql.get(out_name).session()

get(out_name[, enforce_is_active=True]):

Returns an object whose .session() method returns an SQLAlchemy session object which can be used to issue SQL queries against a database configured for the given connection name.

Parameters:
  • out_name (string) – Outgoing connection to use
  • enforce_is_active (boolean) – Whether the connection’s is_active flag should be taken into account when checking out the object. If False, it’s possible to fetch it even though it’s not active.
Return type:

An object whose .session() method should be invoked to obtain a handle to an SQL session

Usage examples

It’s possible to issue SQL directly:

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

# Zato
from zato.server.service import Service

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

        out_name = 'CRM Connection'
        query = 'SELECT current_time'

        with closing(self.outgoing.sql.get(out_name).session()) as session:
            result = session.execute(query).fetchall()
            self.logger.info(result)
1
2
INFO - [(datetime.time(20, 11, 25, 688245,
  tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=120, name=None)),)]

You can use SQLAlchemy models:

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

# Let's imagine your application defines an SQLAlchemy Customer model somewhere
# from myapp.model import Customer

# Zato
from zato.server.service import Service

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

        out_name = 'CRM Connection'
        cust_id = 1

        with closing(self.outgoing.sql.get(out_name).session()) as session:
            customer = session.query(Customer).\
                filter(Customer.id==cust_id).\
                one()

            self.logger.info(customer)
INFO - <myapp.model.Customer object at 0x5b4c810>>

SQLAlchemy and SIO can be used to create responses directly out of the former's models:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
# stdlib
from contextlib import closing

# Let's imagine your application defines an SQLAlchemy Customer model somewhere
# from myapp.model import Customer

# Zato
from zato.server.service import Service

class MyService(Service):

    class SimpleIO(object):
        output_required = ('name', 'last_name', 'is_active')

    def handle(self):

        out_name = 'CRM Connection'
        cust_id = 1

        with closing(self.outgoing.sql.get(out_name).session()) as session:
            self.response.payload = session.query(Customer).\
                filter(Customer.id==cust_id).\
                one()
$ zato service invoke /opt/zato/dev/server1/ sql1.my-service
{u'name': 'Max', u'last_name': u'Dumas', u'is_active': true}

Always remember to close the session object:

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

# Zato
from zato.server.service import Service

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

        out_name = 'CRM Connection'

        # Use a decorator to automatically close the session ..
        with closing(self.outgoing.sql.get(out_name).session()) as session:
            pass

        # .. or remember to close it manually.
        session = self.outgoing.sql.get(out_name).session()
        session.close()