How to connect Python programs to MariaDB

Posted on 6月 25, 2020 by

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.

OR

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

Using the same execute() method with an INSERT statement, you can add rows to the table.

cursor.execute(
    "INSERT INTO employees (first_name,last_name) VALUES (?, ?)", 
    (first_name, last_name))

By default, MariaDB Connector/Python enables auto-commit. If you would like to manually manage your transactions, only committing when you are ready, you can disable it by setting the autocommit attribute on the connection to False.

# Disable Auto-Commit
conn.autocommit = False

Once this is done, you can commit and rollback transactions using the commit() and rollback() methods. MariaDB Server allows you to run multiple concurrent transactions on the same table without locking it when you use the InnoDB storage engine.

While inserting rows, you may want to find the Primary Key of the last inserted row when it is generated, as with auto-incremented values. You can retrieve this using the lastrowid() method on the cursor.

Updating and deleting rows is done similarly to inserting them. The only difference is in the query used.

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

This is how easy and straightforward it is to connect your Python code to a MariaDB database. Here is how a complete script should look like:

#!/usr/bin/python 
import mariadb 

conn = mariadb.connect(
    user="db_user",
    password="db_user_passwd",
    host="localhost",
    database="employees")
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}")
    
#insert information 
try: 
    cur.execute("INSERT INTO employees (first_name,last_name) VALUES (?, ?)", ("Maria","DB")) 
except mariadb.Error as e: 
    print(f"Error: {e}")

conn.commit() 
print(f"Last Inserted ID: {cur.lastrowid}")
    
conn.close()

For More Information