Python Tutorial / Connect to MariaDB and Manage Transactions

Python Tutorial

Use a database driver to connect Python to MariaDB and bind SQL values separately from statement text. This example uses PyMySQL and requires a running server, a dedicated database and an application account.

Prepare the server and a dedicated account

Run the following SQL through an administrator connection to a local MariaDB server. If any named database or account already exists, inspect its use and choose new practice names instead. Replace the sample password with a local secret; do not use an administrator account in the application.

CREATE DATABASE python_lab CHARACTER SET utf8mb4;
CREATE USER 'python_app'@'127.0.0.1' IDENTIFIED BY 'replace-with-a-local-secret';
CREATE TABLE python_lab.expenses (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  external_id VARCHAR(64) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL,
  amount BIGINT NOT NULL CHECK(amount >= 0)
) ENGINE=InnoDB;
GRANT SELECT, INSERT, UPDATE ON python_lab.expenses TO 'python_app'@'127.0.0.1';

The account is limited to the table operations the example needs. InnoDB supplies transactional behavior. This is a local practice database, separate from any existing application data.

For the relevant APIs and platform requirements, see Connect to MariaDB and Manage Transactions documentation.

Install the driver and provide secrets

python -m pip install PyMySQL

Set MARIADB_USER and MARIADB_PASSWORD in the current process environment using your operating system’s environment facilities or project secret manager. Do not place their actual values in source control or logs. Typing secrets into shell commands can also leave them in history.

The example connects to 127.0.0.1. For a remote server, configure TLS with the appropriate CA and hostname verification according to the server and driver documentation. Changing only the address does not preserve the local connection’s assumptions.

Run the complete connection and storage code

Save mariadb_demo.py:

import os
import pymysql

con = pymysql.connect(
    host="127.0.0.1", port=int(os.environ.get("MARIADB_PORT", "3306")),
    user=os.environ["MARIADB_USER"], password=os.environ["MARIADB_PASSWORD"],
    database="python_lab", charset="utf8mb4", autocommit=False,
    connect_timeout=5, read_timeout=5, write_timeout=5,
)
try:
    try:
        with con.cursor() as cur:
            cur.execute("INSERT INTO expenses(external_id,name,amount) VALUES(%s,%s,%s) ON DUPLICATE KEY UPDATE name=VALUES(name), amount=VALUES(amount)", ("demo-1", "lunch", 8000))
        con.commit()
    except Exception:
        con.rollback()
        raise
    with con.cursor() as cur:
        cur.execute("SELECT name,amount FROM expenses WHERE external_id=%s", ("demo-1",))
        print(cur.fetchone())
finally:
    con.close()

Run from the environment containing PyMySQL:

python mariadb_demo.py

A correctly prepared server returns ('lunch', 8000). The stable external_id causes reruns to update that transaction rather than create another one. Amounts are whole KRW units.

Verify parameters, rollback and cleanup

PyMySQL uses %s value placeholders for numbers as well as strings. Supply values in the execute arguments; do not use Python % interpolation or an f-string. Table and column names are SQL structure and require controlled selection if dynamic.

Use an incorrect password to inspect connection failure, then restore the setting.
Try -1 and verify the database constraint failure and preservation of the prior value.
Use a restricted account to distinguish permitted operations from denied ones.

If connect fails, no connection object has been created. After connection succeeds, manage cursor lifetime, transaction success and close separately. A database rollback cannot undo earlier network requests or file changes. The SQLite transaction guide illustrates the same separation for a local embedded database.

Python Tutorial