Blog
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:
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:
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 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.
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."