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.

Transactions and connection failures build on the rules demonstrated in Handle Exceptions with try, except, else, finally and raise.

For selector or server-connection prerequisites, consult MariaDB Tutorial / Connect Python to MariaDB and Use Parameterized Queries for the corresponding exercises.

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.

This example connects over TCP to 127.0.0.1, so match the account’s host to 127.0.0.1 as in the setup SQL. Use an administrator only for schema preparation and python_app for the program. Set the following variables in the current terminal session; do not put the real password into a command literal or print it. Diagnose server/port reachability, then account host/password, then SELECT/INSERT/UPDATE privileges on python_lab.expenses. Do not solve a table privilege failure by granting server-wide access.

In Windows PowerShell, enter the password without echoing it, then remove the environment and temporary variables when finished.

$env:MARIADB_USER = "python_app"
$secret = Read-Host "MariaDB password" -AsSecureString
$credential = New-Object System.Net.NetworkCredential("", $secret)
$env:MARIADB_PASSWORD = $credential.Password
python mariadb_demo.py
Remove-Item Env:MARIADB_PASSWORD
Remove-Item Env:MARIADB_USER
Remove-Variable secret, credential

In Bash on macOS or Ubuntu, set variables for the current shell as follows.

export MARIADB_USER=python_app
read -r -s -p "MariaDB password: " MARIADB_PASSWORD
printf "\n"
export MARIADB_PASSWORD
python mariadb_demo.py
unset MARIADB_PASSWORD MARIADB_USER

The expected result is ('lunch', 8000); verify the connection on your own practice server. Environment variables pass to child processes and do not provide the protections of a dedicated secret store.

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.

Official behavior and conditions: PyMySQL: connection options

Python Tutorial