Python to MariaDB Connector

We previously blogged about using Python to connect to MariaDB servers using the MySQL Python package. In June 2020, MariaDB made the first generally available release of Connector/Python. We’ve refreshed this blog to reflect the latest technology. If you need the old blog, you can find it here.

You can use the popular programming language Python to manage data stored in MariaDB Platform, including MariaDB Server, MariaDB MaxScale and MariaDB SkySQL. Here is everything you need to know about connecting to MariaDB Platform from Python for retrieving, updating and inserting information.

Preparing and Installing

You will need access to MariaDB Server. We recommend either of these two methods:
1. Download MariaDB Server on your own hardware. See our Deployment Guide for step-by-step instructions in our documentation.

2. Deploy MariaDB Platform, which includes MariaDB Enterprise Server, using MariaDB SkySQL. See our documentation to launch a database service in minutes.

If you want to try out Python integration with MariaDB but you don’t have a database to play with, you can use the popular employees example database.

MariaDB provides Python support through the MariaDB Connector/Python, which is available through the Python Package Index. To install, use PIP:

$ pip3 install mariadb

Connecting to MariaDB Server

1. To connect to MariaDB Server using MariaDB Connector/Python, you have to import it first, just as you would any other module: import mariadb

2. Next, establish a database connection with the connect() function. The function takes a series of named arguments specifying your client credentials, such as user name, host, password. If you are using a database instance on SkySQL, this information is provided in the Service Details page for your database instance.

The connection provides you with an interface for configuring your application’s connection to the MariaDB Server.

3. Lastly, call the cursor() method on the connection to retrieve the cursor.

The cursor provides you with an interface for interacting with the Server, such as running SQL queries and managing transactions.

# Module Imports
import mariadb
import sys

# Connect to MariaDB Platform
try:
    conn = mariadb.connect(
        user="db_user",
        password="db_user_passwd",
        host="192.0.2.1",
        port=3306,
        database="employees"

    )
except mariadb.Error as e:
    print(f"Error connecting to MariaDB Platform: {e}")
    sys.exit(1)

# Get Cursor
cur = conn.cursor()

Retrieving Data

Once you have the initial code in place you can start working with the data. The first thing you should do is try to retrieve information from the database. Here is code for a query against the employees database:

cur.execute(
    "SELECT first_name,last_name FROM employees WHERE first_name=?", 
    (some_name,))

MariaDB Connector/Python uses prepared statements, sanitizing and inserting the values from the tuple into the position of the question marks (?). This is safer than inserting through f-strings or format specifiers when working with user provided information.

The query results are stored in a list in the cursor object. To view the results, you can loop over the cursor.

# Print Result-set
for (first_name, last_name) in cur:
    print(f"First Name: {first_name}, Last Name: {last_name}")

Each row is passed from the cursor as a tuple containing the columns in the SELECT statement.

Adding Data

INSERT uses the same execute() call and the same ? placeholders as SELECT. The employees table in the sample database requires values for emp_no, birth_date, first_name, last_name, gender, and hire_date, so all six are supplied:

cur.execute(
    "INSERT INTO employees "
    "(emp_no, birth_date, first_name, last_name, gender, hire_date) "
    "VALUES (?, ?, ?, ?, ?, ?)",
    (new_emp_no, "1990-01-15", "Maria", "DB", "F", "2024-01-01"))

For a table where the primary key is defined with AUTO_INCREMENT, you can omit that column from the INSERT and the server generates the next value.

By default, MariaDB Connector/Python disables auto-commit. This follows the PEP-249 DBAPI 2.0 specification, which requires drivers to begin with auto-commit off. Writes belong to an implicit transaction that becomes permanent only after you call conn.commit(), or that you can discard with conn.rollback():

conn.commit()

If you would rather have each statement commit immediately, enable auto-commit on the connection:

# Enable Auto-Commit
conn.autocommit = True

With auto-commit disabled, MariaDB Server allows multiple concurrent transactions on the same table without locking when you use the InnoDB storage engine.

When you INSERT into a table whose primary key is AUTO_INCREMENT, the cursor’s lastrowid attribute returns the value the server generated. For a table without AUTO_INCREMENT (the employees table is one example), lastrowid returns None.

UPDATE and DELETE follow the same execute-then-commit pattern. Only the SQL changes.

Catching Exceptions

For any of your SQL actions (querying, updating, deleting, or inserting records) you should try to trap errors, so you can verify that your actions are being executed as expected and you know about any problems as they occur. To trap errors, use the Error class:

try:
    cursor.execute("some MariaDB query"))
except mariadb.Error as e:
    print(f"Error: {e}")

If the query in the try clause of the above code fails, MariaDB Server returns an SQL exception, which is caught in the except and printed to stdout. This programming best practice for catching exceptions is especially important when you’re working with a database, because you need to ensure the integrity of the information.

Once you finish working with the database make sure that you close this connection to avoid keeping unused connections open and thus wasting resources. You can close the connection with the close() method:

# Close Connection
conn.close()

If you used SkySQL to experiment, you may want to delete your database service to stop incurring charges.

The Complete Script

Putting all of the pieces together, here is a complete script that connects to the database, queries some employees by first name, inserts a new employee, and closes the connection:

#!/usr/bin/python
import mariadb
import sys

try:
    conn = mariadb.connect(
        user="db_user",
        password="db_user_passwd",
        host="localhost",
        database="employees")
except mariadb.Error as e:
    print(f"Error connecting to MariaDB Platform: {e}")
    sys.exit(1)

cur = conn.cursor()

# retrieving information
some_name = "Georgi"
cur.execute("SELECT first_name, last_name FROM employees WHERE first_name=?", (some_name,))

for first_name, last_name in cur:
    print(f"First name: {first_name}, Last name: {last_name}")

# inserting information
cur.execute("SELECT MAX(emp_no) FROM employees")
(max_emp_no,) = cur.fetchone()
new_emp_no = max_emp_no + 1

try:
    cur.execute(
        "INSERT INTO employees "
        "(emp_no, birth_date, first_name, last_name, gender, hire_date) "
        "VALUES (?, ?, ?, ?, ?, ?)",
        (new_emp_no, "1990-01-15", "Maria", "DB", "F", "2024-01-01"))
except mariadb.Error as e:
    print(f"Error: {e}")

conn.commit()
print(f"Inserted employee with emp_no: {new_emp_no}")

conn.close()

The script computes the next emp_no from the current maximum because employees.emp_no is not AUTO_INCREMENT. If you adapt this code to a table whose primary key auto-increments, omit that column from the INSERT and read cur.lastrowid to retrieve the generated value.

How to connect Python programs to MariaDB