Python SQL

Creating an SQL connection pool in your Dashboard will let you issue SQL queries as in the examples below.

You can connect to the following database types:

• MySQL
• PostgreSQL
• Microsoft SQL Server
• Oracle DB (paid add-on)

Issuing SQL queries

For MySQL, PostgreSQL and Oracle DB, issue SQL queries as below.

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

# stdlib
from contextlib import closing

# Zato
from zato.server.service import Service

class MyService(Service):

    def handle(self):

        # Query to issue
        query = 'select * from users where user_id = :user_id'

        # Parameters for the query
        params = {'user_id': 1}

        # Get a connection from its pool
        conn = self.out.sql['My Oracle DB']

        # Run the query
        result = conn.execute(query, params)

        # Return the response to our caller
        self.response.payload = result

Now, the caller will get on output something like this:

[{"user_id":1, "username":"john.doe"}]

Returning a single object

When using conn.execute, you'll get a list of objects, even if your query returns a single row.

But, sometimes, you'll know that you're going to get a single row only, in which case you can use the calls below

# {"user_id":1, "username":"john.doe"} or Exception
result = conn.one(query, params)

# {"user_id":1, "username":"john.doe"} or None, or Exception
result = conn.one_or_none(query, params)

The difference between the two methods is this:

• conn.one will always return a single row as a dict, or it will raise an exception if the query returned zero rows, or if the query returned more than one row. In short, you get either one row or an exception.

• conn.one_or_none - like above, but it will not raise an exception if the query returned no row. In short, you get a single result, or None if the query didn't return anything, but you still get an exception if the query returned multiple rows.

Oracle DB

• Check here for a full article about using Oracle DB connections in Zato.
• Note that to use Oracle DB connections, a paid add-in is required. Contact Zato Source for the details of the offer.

Microsoft SQL Server (MS SQL)

• MS SQL connections allow only for invocation of stored procedures. It is not possible to issue other SQL queries with this database.
• Check here for a full article about using MS SQL connections in Zato.