Skip to main content
Markdown

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

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:

Terminal
exaloop --with mysql run -release client.codon
Output
[(2, 4.0)]
Use an individual SDK without the CLI
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 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:

MethodResult
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 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:

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:

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​