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.


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