MySQL: eight examples

Each example includes Mojo code, an explanation, and the expected result. Follow the numbered examples in order, placing each snippet inside the application function shown at the beginning of the guide.

The following examples build the same Notes application using MySQL. MySQL uses ? instead of PostgreSQL's $1 and $2 placeholders.

Start your MySQL application

Use this program structure and place each example between the lease acquisition and release:

from mojara.dbclient import PooledDatabase

def main() raises:
    var db = PooledDatabase(
        "127.0.0.1", 3306, "notes", "your_password", "notes",
        capacity=3,
    )
    var lease = db.acquire(timeout_ms=3000)

    # Put each MySQL example here, in order.

    lease.release()
    db.close()

The constructor takes host, port, username, password, database name, and pool capacity.

MySQL example 1: Execute a SELECT query

    var greeting = lease.query("SELECT 'Hello from MySQL' AS message")
    print(greeting.cell(0, 0))

query() runs the SQL. cell(0, 0) returns the first field.

Output: Hello from MySQL

MySQL example 2: Create the Notes table

    var schema = lease.query(
        "CREATE TABLE IF NOT EXISTS notes (id INT PRIMARY KEY, title VARCHAR(255) NOT NULL, completed TINYINT(1) NOT NULL DEFAULT 0) ENGINE=InnoDB"
    )
    print("Notes table is ready")

VARCHAR(255) stores the title. TINYINT(1) holds a completion flag, and InnoDB supports transactions.

Output: Notes table is ready

MySQL example 3: Insert a note using parameters

    var inserted = lease.query3(
        "INSERT INTO notes (id, title) VALUES (?, ?) ON DUPLICATE KEY UPDATE title = ?",
        "101", "Learn Mojara", "Learn Mojara",
    )
    print("Note saved")

query3() binds each value to a ? placeholder. When ID 101 already exists, the statement updates the title instead of failing.

Result: note 101 contains the title Learn Mojara.

MySQL example 4: Read all notes

    var notes = lease.query("SELECT id, title FROM notes ORDER BY id")
    for i in range(notes.rows()):
        print(notes.cell(i, 0), notes.cell(i, 1))

Loop through rows() and read each column using cell(row, column).

Output includes: 101 Learn Mojara

MySQL example 5: Find a note by ID

    var selected = lease.query1(
        "SELECT title FROM notes WHERE id = ?",
        "101",
    )
    if selected.rows() > 0:
        print(selected.cell(0, 0))

query1() safely binds the note ID.

Output: Learn Mojara

MySQL example 6: Update a note

    var updated = lease.query2(
        "UPDATE notes SET title = ? WHERE id = ?",
        "Master Mojara routing", "101",
    )
    print("Rows updated:", updated.affected())

The first parameter is the new title, and the second is the note ID.

Output: Rows updated: 1

MySQL example 7: Roll back and commit transactions

First roll back an insertion:

    lease.begin()
    var temporary = lease.query2(
        "INSERT INTO notes (id, title) VALUES (?, ?)",
        "102", "Temporary note",
    )
    lease.rollback()

    var after_rollback = lease.query(
        "SELECT COUNT(*) FROM notes WHERE id = 102"
    )
    print("After rollback:", after_rollback.cell(0, 0))

rollback() reverses the insertion.

Output: After rollback: 0

Now commit the same ID:

    lease.begin()
    var committed = lease.query2(
        "INSERT INTO notes (id, title) VALUES (?, ?)",
        "102", "Saved with a transaction",
    )
    lease.commit()

    var after_commit = lease.query(
        "SELECT COUNT(*) FROM notes WHERE id = 102"
    )
    print("After commit:", after_commit.cell(0, 0))

commit() saves the insertion.

Output: After commit: 1

MySQL example 8: Delete a note

    var deleted = lease.query1(
        "DELETE FROM notes WHERE id = ?",
        "102",
    )
    print("Rows deleted:", deleted.affected())

affected() reports how many records were deleted.

Output: Rows deleted: 1

Complete the program by returning the connection and closing the pool:

    lease.release()
    db.close()