Connect to MariaDB database in Python

Before you start connect to MariaDB in Python, you need to import the MySQL connector that also works for MariaDB. In our examples, we will use Visual Studio Code, which is one of the most powerful and widely used editors for developing in Python.

» More Python Examples

For this example also we will use the DBeaber Community database administration tool, an excellent SQL client software application.

We create a new database connection in MariaDB:

We create a database called Tests:

We also create a table called Users:

CREATE TABLE Users (
  ID int(11) NOT NULL AUTO_INCREMENT,
  USERNAME varchar(100) NOT NULL,
  PASSWORD varchar(100) NOT NULL,
  NAME varchar(100) DEFAULT NULL,
  PRIMARY KEY (ID)
)

We added some test logs:

And finally the code:

import mysql.connector as mariadb

# geekole.com

mariadb_connection = mariadb.connect(host='localhost', port='3306',
                                   user='root', password='password', database='Tests')
cursor = mariadb_connection.cursor()

try:
    cursor.execute("SELECT ID,USERNAME,PASSWORD,NAME FROM Users")
    for id_user, username, password, name in cursor:
        print("id: " + str(id_user))
        print("username: " + username)
        print("password: " + password)
        print("name: " + name)

except mariadb.Error as error:
    print("Error: {}".format(error))

mariadb_connection.close()

The things you should understand from this block of code are the following:

Of course you must import the database connector:

import mysql.connector as mariadb

Shell

# In a virtual environment or using Python 3 (could also be pip3.10 depending on your version)
pip3 install mysql-connector-python

# If you get permissions error
sudo pip3 install mysql-connector-python
pip install mysql-connector-python --user

# If you don't have pip in your PATH environment variable
python -m pip install mysql-connector-python

# In Python 3 (could also be pip3.10 depending on your version)
python3 -m pip install mysql-connector-python

You create a connection with the host, port, user, password and name of the database:

mariadb_connection = mariadb.connect(host=’localhost’, port=’3306′, user=’root’, password=’password’, database=’Tests’)

You perform the query with the execute function, which can take values for the WHERE, although in this case we don’t:

cursor.execute(“SELECT ID,USERNAME,PASSWORD,NAME FROM Users”)

We print the data of each record in a for loop from the data contained in cursor and after the call to the execute function:

print(“id: ” + str(id_user))

print(“username: ” + username)

print(“password: ” + password)

print(“name: ” + name)

And last but not least, we close the connection

mariadb_connection.close()

In the end, when you run your code, you’ll get something like this:

Of course, you can insert or update records and we will add examples on how to do it soon. We hope you find this example of how to connect to MariaDB in Python useful.