MariaDB Tutorial / Connect Python to MariaDB and Use Parameterized Queries

When connecting Python to MariaDB, pass SQL text and user-supplied values separately through the driver API. Parameterized queries keep values out of SQL string construction, while a least-privilege account and explicit commit or rollback control the database changes.

Prepare the environment and application account

This example uses Python 3, PyMySQL 1.1.2, and a local TCP connection to MariaDB. Its core behavior was checked with Python 3.12 and MariaDB 12.3.3. PyMySQL is a Python database driver; its value placeholders in this example are %s. Do not substitute another driver’s question-mark syntax. Use the least-privilege scope from Create Users and Grant Only Required Privileges and the transaction boundaries in Start InnoDB Transactions with COMMIT and ROLLBACK when committing a successful insert/read workflow.

Run the following setup through an administrator connection. Replace the public placeholder Replace_With_Your_Own_Lab_Password with your own lab password. The CREATE USER statement is for initial setup; inspect an existing account and its grants rather than attempting to recreate it blindly. The account and privilege tutorial explains these separate responsibilities.

CREATE DATABASE IF NOT EXISTS appops_lab
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE appops_lab;
CREATE TABLE app_notes (
  note_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  note_name VARCHAR(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE USER 'appops_app'@'127.0.0.1'
  IDENTIFIED BY 'Replace_With_Your_Own_Lab_Password';
GRANT SELECT, INSERT ON appops_lab.app_notes TO 'appops_app'@'127.0.0.1';

The account host 127.0.0.1 assumes TCP access to a native local server. Containers and remote databases may see a different client origin. Check account host matching and connection paths instead of solving connection problems by allowing administrator access from every host.

Create an isolated project and save the complete program

Create a new project folder and run these Windows PowerShell commands. Calling the environment’s Python directly avoids changing PowerShell’s activation-script policy.

python -m venv .venv
.venv\Scripts\python.exe -m pip install PyMySQL==1.1.2

On Linux or macOS, use .venv/bin/python for the same operations. Save the complete program below as UTF-8 notes_app.py. It asks for the password at runtime rather than embedding it in source code or SQL text.

import argparse
import getpass
import os
import pymysql

def save_and_find(conn, name, sort_key="id"):
    if not name or len(name) > 80:
        raise ValueError("Name must contain 1 to 80 characters")
    columns = {"id": "note_id", "name": "note_name, note_id"}
    if sort_key not in columns:
        raise ValueError("Unknown sort key")
    with conn.cursor() as cur:
        cur.execute("INSERT INTO app_notes (note_name) VALUES (%s)", (name,))
        cur.execute(
            "SELECT note_id, note_name FROM app_notes "
            "WHERE note_name = %s ORDER BY " + columns[sort_key],
            (name,),
        )
        return cur.fetchall()

def main():
    parser = argparse.ArgumentParser()
    parser.add_argument("--name", default="O'Reilly")
    parser.add_argument("--sort", choices=("id", "name"), default="id")
    args = parser.parse_args()
    password = getpass.getpass("Database password: ")
    conn = pymysql.connect(
        host=os.getenv("DB_HOST", "127.0.0.1"),
        port=int(os.getenv("DB_PORT", "3306")),
        user=os.getenv("DB_USER", "appops_app"),
        password=password,
        database="appops_lab",
        charset="utf8mb4", autocommit=False,
        connect_timeout=5, read_timeout=10, write_timeout=10,
    )
    try:
        rows = save_and_find(conn, args.name, args.sort)
        conn.commit()
        for note_id, note_name in rows:
            print(note_id, note_name)
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()

if __name__ == "__main__":
    main()

The connection specifies connect, read, and write timeouts and uses utf8mb4. This article covers a local PC lab. A connection across another network also needs a TLS configuration that verifies the server certificate; supplying a password alone does not protect the transport.

Run ordinary and apostrophe-containing names

.venv\Scripts\python.exe notes_app.py --name "Notebook"
.venv\Scripts\python.exe notes_app.py --name "O'Reilly" --sort name

These are separate program runs. Enter the database password for each. The first stores Notebook and the second stores O'Reilly, including its apostrophe, then queries matching names. On a fresh table, running each once returns its one corresponding row. Do not require a particular auto-increment ID: prior data and rolled-back inserts can affect numbering.

Running the same name again inserts another row and returns all rows matching that name. The example does not declare names unique. If a business process requires deduplication, define its identity rule and an appropriate database constraint rather than assuming the program has already enforced it.

Separate values from SQL syntax

The important call is cur.execute(sql, (name,)): SQL and a tuple of values are separate arguments. The comma creates a one-element tuple. Do not manually quote the %s placeholder or interpolate a user value with Python’s % operator, an f-string, or string concatenation. The official PyMySQL examples follow this separation.

A name such as x' OR 1=1 -- must remain one stored value and match that same string, rather than turning into a condition selecting every row. “Parameterized query” here describes the driver API. It does not claim that every driver necessarily uses the server-side prepared-statement protocol.

Identifiers and SQL keywords, including column names and ASC or DESC, cannot be substituted as ordinary value parameters. The ORDER BY fragment comes only from the developer-defined columns mapping. Both argparse and the function itself validate the choice, so another caller cannot bypass the allowlist. See the Cursor.execute argument reference.

Verify transactions and privileges together

With autocommit=False, a successful helper call does not automatically persist its insert. The main function commits after insertion and selection succeed, rolls back on an exception, and closes the connection in finally. Distinguish this commit boundary from what another connection can currently see; review transaction boundaries when testing.

The application account has only SELECT and INSERT. It does not create or drop tables, so it does not need CREATE or DROP. Least privilege does not replace correct parameter binding, but it reduces the operations available if the program has another defect. The connection option reference covers autocommit and timeout settings.

Diagnose errors and clean up the exercise

For non-secret connection settings, PowerShell can set an override such as $env:DB_PORT = "3307". Keep the interactive password prompt. Remove a no-longer-needed port override with Remove-Item Env:DB_PORT.

When finished, stop the program and have the administrator confirm these are the disposable lab account and table before removing them. Do not substitute the names of business accounts or tables.

DROP USER 'appops_app'@'127.0.0.1';
DROP TABLE appops_lab.app_notes;