# Amazon Redshift in Python

Redshift cluster connections, queries and TLS options from Python services.

Zato lets you work with Amazon Redshift directly from your Python services. You set up connection pools in the Dashboard, and Zato manages the connections, credentials and pooling.

## Creating a connection {#creating-a-connection}

Open the Dashboard and go to `Connections → Outgoing → SQL`. Create a new connection, choose **Amazon Redshift**, and fill in your cluster details.

| Field | Description |
| --- | --- |
| Host | Your cluster endpoint, e.g. `examplecluster.abc123xyz789.us-west-2.redshift.amazonaws.com` |
| Port | Cluster port, `5439` by default |
| Database | Name of the database within the cluster |
| User | Redshift username |

Once saved, the connection becomes available to all your services by its configured name.

## Executing queries {#executing-queries}

Call `conn.execute` with your SQL and a dictionary of parameters. Each row comes back as a dictionary in a list.

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

# Zato
from zato.server.service import Service

class GetDailySales(Service):

    def handle(self):

        # Get the connection by the name configured in Dashboard
        conn = self.out.sql['My Redshift']

        # Query with named parameter
        query = 'SELECT * FROM sales WHERE region = :region'
        params = {'region': 'EMEA'}

        # Execute returns a list of dicts
        result = conn.execute(query, params)

        self.response.payload = result
```

Response:

```python
[
    {"sale_id": 1, "region": "EMEA", "amount": 1200.00},
    {"sale_id": 2, "region": "EMEA", "amount": 950.00}
]
```

## Querying a single row {#querying-a-single-row}

If your query should return exactly one row, `conn.one` gives you that row directly as a dictionary. It raises an exception when zero or multiple rows come back.

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

# Zato
from zato.server.service import Service

class GetSaleById(Service):
    """ Returns a single sale by its ID.
    """
    input = 'sale_id'

    def handle(self):

        # Get the connection from the pool
        conn = self.out.sql['My Redshift']

        # Build the query
        query = 'SELECT * FROM sales WHERE sale_id = :sale_id'

        # Assign input data to query parameters
        params = {'sale_id': self.request.input.sale_id}

        # Run the query - returns a dict directly, raises if not found
        sale = conn.one(query, params)

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

For cases where the row might not exist, `conn.one_or_none` returns `None` instead of raising an exception.

## Extra options {#extra-options}

When creating a connection in the Dashboard, you can provide extra options as a list of `key=value` pairs. Each option goes on its own line.

| Option | Description |
| --- | --- |
| `ssl` | Whether to use TLS, `True` by default |
| `sslmode` | TLS mode - `verify-ca` (default) or `verify-full` |
| `timeout` | Connection timeout in seconds |
| `tcp_keepalive` | Whether to use TCP keepalives, e.g. `True` |

Example in Dashboard:

```text
sslmode=verify-full
timeout=10
```

## How to avoid SQL injection attacks {#how-to-avoid-sql-injection-attacks}

Always use parameterized queries with named parameters (`:param_name`) rather than string formatting. This prevents SQL injection attacks and makes your queries more readable. With MS SQL, a `:param_name` marker with no matching parameter, or a parameter with no matching marker, is refused before the statement is sent to the database.

```python
# Correct - parameterized query
query = 'SELECT * FROM users WHERE user_id = :user_id AND status = :status'
params = {'user_id': 123, 'status': 'active'}
result = conn.execute(query, params)

# Wrong - string formatting (vulnerable to SQL injection)
# query = f'SELECT * FROM users WHERE user_id = {user_id}'
```

## More resources {#more-resources}

- [SQL databases overview](https://zato.io/docs/dev/examples/sql/index.html)
- [Snowflake](https://zato.io/docs/dev/examples/sql/snowflake.html)
- [PostgreSQL](https://zato.io/docs/dev/examples/sql/postgresql.html)
- [MySQL](https://zato.io/docs/dev/examples/sql/mysql.html)

## 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