-   [](https://platform.exaloop.io/)
-   Databases
-   [MySQL](https://platform.exaloop.io/db/mysql/)
-   Client applications

# MySQL Client Applications

The native MySQL client executes parameterized queries and returns results as typed Codon values. Use it for application-side calculations, transactional updates, and incremental fetching from large query results.

This guide starts with a temporary table and a filtered query, then covers connection security, result conversion, and transaction behavior.

**Before you begin:** install the [all-SDK distribution](https://platform.exaloop.io/start/distribution/) and have access to an Oracle MySQL 8.0+ development database. Configure the connection variables shown below, including a trusted CA for TCP connections.

## Read and Write

This example creates a temporary table, inserts parameterized rows, and materializes a filtered query:

```python title="client.codon"
import exaloop.databases.mysql as mysql
import os

with mysql.connect(host=os.environ.get("MYSQL_HOST", "127.0.0.1"),
                   user=os.environ.get("MYSQL_USER", ""),
                   password=os.environ.get("MYSQL_PASSWORD", ""),
                   database=os.environ.get("MYSQL_DATABASE", ""),
                   unix_socket=os.environ.get("MYSQL_UNIX_SOCKET", ""),
                   sslca=os.environ.get("MYSQL_SSL_CA", "")) as connection:
    connection.execute("CREATE TEMPORARY TABLE codon_readings (id BIGINT PRIMARY KEY, value DOUBLE)")
    connection.executemany("INSERT INTO codon_readings VALUES (?, ?)", [(1, 2.5), (2, 4.0)])
    with connection.query("SELECT id, value FROM codon_readings WHERE value > ? ORDER BY id", (3.0,)) as result:
        readings = result.fetchall(Tuple[int, float])
    assert readings == [(2, 4.0)]
    print(readings)
```

Save the example as `client.codon` and set the connection variables for your server. Keep credentials in private configuration, not in source control. Run it with your installed SDK:

```sh title="Terminal"
exaloop --with mysql run -release client.codon
```

```text title="Output"
[(2, 4.0)]
```

Use an individual SDK without the CLI

```sh title="Terminal"
codon run -release -plugin /path/to/sdk/mysql client.codon
```

The tuple type declares an integer ID and a floating-point measurement in each row. Bind values separately through `?` placeholders to keep them out of the SQL text; placeholders represent values, not SQL identifiers.

## Connections and Security

For a remote server, supply its hostname and trusted CA certificate through `host` and `sslca`. TCP connections verify the certificate and hostname by default. A local Unix socket instead uses filesystem permissions for access control; select it with `unix_socket`.

Use a `with` block to close the connection when the work is done, and give each concurrent worker its own connection. The client does not automatically reconnect or retry writes: after a lost connection, the application may not know whether a write reached the server.

The [connection reference](https://platform.exaloop.io/db/reference/#mysql) lists timeouts, authentication, and TLS options.

## Parameters and Results

Use `query()` when you want rows back and `execute()` when you only need the affected-row count. Both accept SQL and a separate tuple or list of parameters. For several writes with the same SQL, `executemany()` accepts a sequence of parameter sets.

Choose a tuple type that matches your result columns. For a nullable measurement, use `Tuple[int, Optional[float]]` so a missing value can be represented by `None`. The same row type works with all the fetching methods:

| Method | Result |
| --- | --- |
| `fetchone(Row)` | Next tuple, or `None` at end |
| `fetchmany(Row, size=2048)` | At most `size` tuples |
| `rows(Row)` | Row iterator |
| `batches(Row, size=2048)` | Batch iterator |
| `fetchall(Row)` | All remaining tuples |

Rows you fetch remain usable after the result closes. For exact decimal values, read text and use an explicit SQL cast when writing them back; converting through `float` can lose precision. See [SQL value conversion](https://platform.exaloop.io/db/reference/#sql-values) for dates, JSON, binary data, and other types.

## Transactions

Statements autocommit outside an explicit transaction. As with the PostgreSQL client, **a transaction scope rolls back unless you explicitly commit**. With an open connection and an `accounts` table, a transfer can be written as follows. Here, `amount` is the transfer amount, and `source` and `destination` are account IDs:

```python title="Source"
with connection.transaction() as transaction:
    connection.execute("UPDATE accounts SET balance=balance-? WHERE id=?", (amount, source))
    connection.execute("UPDATE accounts SET balance=balance+? WHERE id=?", (amount, destination))
    transaction.commit()
```

The final `commit()` completes the successful transfer. Leaving the block before that point rolls it back. If you prefer to express the operation as a function, `connection.run_transaction(operation, *args)` commits when that function returns and rolls back if it raises.

MySQL can implicitly commit schema changes such as `CREATE TABLE`, and a transaction wrapper cannot undo that behavior. Those changes belong outside these scopes. Rollback also requires a transactional storage engine such as InnoDB.

## Large Queries

For a large query, use a cursor to fetch the output incrementally instead of loading it all at once:

```python title="Source"
with connection.cursor("SELECT id, value FROM readings ORDER BY id") as cursor:
    for batch in cursor.batches(Tuple[int, float], size=1024):
        process(batch)
```

Finish or close the cursor before sending another command on that connection. Batching keeps client memory bounded, although MySQL may still need server-side storage to execute the query. Calling `fetchall()` on a cursor loads all remaining rows, so keep batch iteration for large outputs.

## Next Steps

-   [Package and deploy the client](https://platform.exaloop.io/db/mysql/deployment/#client-applications) with its libraries.
-   [Create a SQL function](https://platform.exaloop.io/db/mysql/functions/) to run a calculation inside MySQL.
-   Review [connection options](https://platform.exaloop.io/db/reference/#mysql) for authentication and timeouts.

Source: https://platform.exaloop.io/db/mysql/client/