# Python SQL databases

Connection pools, queries and single-row lookups across PostgreSQL, MySQL, MS SQL, Oracle, Snowflake and Redshift.

Zato provides built-in support for connecting to SQL databases from your Python services. Connection pools are configured in the Dashboard, and your code accesses them through a simple, consistent API regardless of which database you use.

This approach means your services focus on business logic rather than connection management, credential handling, or low-level database drivers. Zato handles all of that for you.

## Supported databases {#supported-databases}

Zato includes built-in support for the following database types:

- **[PostgreSQL](https://zato.io/docs/dev/examples/sql/postgresql.html)**
- **[MySQL](https://zato.io/docs/dev/examples/sql/mysql.html)**
- **[Microsoft SQL Server](https://zato.io/docs/dev/examples/sql/mssql.html)**
- **[Oracle Database](https://zato.io/docs/dev/examples/sql/oracle.html)**
- **[Snowflake](https://zato.io/docs/dev/examples/sql/snowflake.html)**
- **[Amazon Redshift](https://zato.io/docs/dev/examples/sql/redshift.html)**

Each database page includes specific connection examples and features unique to that database type.

## Verifying a connection {#verifying-a-connection}

Each connection in the Dashboard has a **Ping** link. It runs a test statement specific to the database type and shows the response time, which confirms that the host, credentials and network path are correct before any service uses the connection.

## Basic usage {#basic-usage}

Once you have configured a connection pool in the Dashboard, accessing it from your service is straightforward. The connection pool is available through `self.out.sql`, and you reference it by the name you gave it in the Dashboard.

```python
# -*- coding: utf-8 -*-

# Zato
from zato.server.service import Service

class GetActiveFlights(Service):

    def handle(self):

        # Get a connection from the pool by name
        conn = self.out.sql['My Database']

        # Define your query with named parameters
        query = 'SELECT * FROM flights WHERE status = :status'

        # Parameters as a dictionary
        params = {'status': 'active'}

        # Execute and get results as a list of dictionaries
        result = conn.execute(query, params)

        # Return to caller
        self.response.payload = result
```

The response will be a list of dictionaries, one per row:

```python
[
    {"flight_id": 101, "status": "active", "destination": "JFK"},
    {"flight_id": 205, "status": "active", "destination": "LHR"}
]
```

## Returning a single row {#returning-a-single-row}

When you know your query returns exactly one row, use `conn.one` or `conn.one_or_none` instead of `conn.execute`. These methods return a dictionary directly rather than a list, making your code cleaner when dealing with single-row lookups.

```python
# -*- coding: utf-8 -*-

# Zato
from zato.server.service import Service

class GetFlightById(Service):

    input = 'flight_id'

    def handle(self):

        conn = self.out.sql['My Database']

        query = 'SELECT * FROM flights WHERE flight_id = :flight_id'
        params = {'flight_id': self.request.input.flight_id}

        # Returns a dict directly, raises exception if zero or multiple rows
        flight = conn.one(query, params)

        self.response.payload = flight
```

The difference between the two methods:

- **`conn.one`** - Returns a single row as a dictionary. Raises an exception if the query returns zero rows or more than one row. Use this when you expect exactly one result.

- **`conn.one_or_none`** - Returns a single row as a dictionary, or `None` if no rows are found. Still raises an exception if more than one row is returned. Use this when zero results is a valid outcome.

```python
# Returns dict or raises exception
result = conn.one(query, params)

# Returns dict, None, or raises exception (if multiple rows)
result = conn.one_or_none(query, params)
```

## Audit log {#audit-log}

The connection's `Audit log` dropdown sets what the [audit log](https://zato.io/docs/admin/audit-log/index.html#outgoing-sql-connections) stores per statement - outcome and duration only, the SQL text, the text with parameters, or everything with rows. Queries and stored procedure calls alike.

## Database-specific features {#database-specific-features}

## Learn more {#learn-more}

- [Development documentation](https://zato.io/docs/dev/) - The complete reference for writing services
- [Requests and responses](https://zato.io/docs/dev/request-response/) - What a service receives, what it returns and how both are structured
- [Integration examples](https://zato.io/docs/dev/examples/) - Ready-made code for the systems you are likely to connect to
- [IDE and debugging](https://zato.io/docs/dev/ide/) - Write services in the Dashboard or in your own editor
- [Data models](https://zato.io/docs/dev/model/) - Declare inputs and outputs and have them validated for you
- [In-depth API tutorial](https://zato.io/tutorials/main/01.html) - The full platform tutorial, from installation to production patterns