How to integrate Python with Mariadb Database server on Fedora Linux

Python

How to integrate Python with Mariadb Database server on Fedora Linux

By novicejava1 0
python_mariadb


Here in this article we will try to integrate our simple python application with Mariadb database server which is a popular open source relational database server. For this integration to work we need to install Mariadb connector and dependent package which we will see in the below step by step procedure.

Test Environment

Fedora 31 updated

Procedure

Step1: Install Mariadb Server

As a first step we need to install the below packages.

  • mariadb: This is the base package which contains the standard MariaDB/MySQL client programs and generic MySQL files.
  • mariadb-server: This is the package which contains the MariaDB server and some accompanying files and directories. MariaDB is a community developed branch of MySQL.
sudo dnf install mariadb mariadb-server

Step2: Secure the Mariadb installation

Once the installation of the above package is completed we need to make sure that we secure our installation by running the below utility which will do the necessary clean up and apply changes as shown below.

  • Change the root user password
  • Remove anonymous user access
  • Remove root login remotely
  • Remove test database
  • Reload the privileges table to make the following changes take effect
sudo mysql_secure_installation

Step3: Start the Mariadb server

Now, we are ready to start the Mariadb database server as a service.

sudo systemctl start mariadb.service
sduo systemctl status mariadb.service

Step4: Connect to Mariadb server

Once the server is started we can try to connect to the mysql utility using root user id and password and validate if you are able to see the default system databases list.

sudo mysql -u root -p

MariaDB [(none)]> show databases;

Step5: Create and connect to new database

Until this point we have our database ready and able to connect to it. Lets now create a new test database as shown below which we will use in our python program.

MariaDB [(none)]> create database hostlist;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> connect hostlist
Connection id:    10
Current database: hostlist

Step6: Create table in new database

Once the new database is created, let us create a new table. Here i am creating a simple table for storing the server inventory.

MariaDB [(none)]> show tables;
+--------------------+
| Tables_in_hostlist |
+--------------------+
| servers            |
+--------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> describe servers;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| servername  | varchar(100) | YES  |     | NULL    |       |
| type        | varchar(100) | YES  |     | NULL    |       |
| environment | varchar(100) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+
3 rows in set (0.001 sec)

Step7: Insert data into new table

Once the table is create, let us insert our server inventory into this table as shown below.

MariaDB [(none)]> insert into servers (servername, type, environment) values ("fed31.stack.com", "linux", "prod");
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]> insert into servers (servername, type, environment) values ("fed32.stack.com", "linux", "prod");
Query OK, 1 row affected (0.001 sec)
MariaDB [(none)]> insert into servers (servername, type, environment) values ("fed29.stack.com", "linux", "cob");
Query OK, 1 row affected (0.001 sec)

Step8 : Fetch data from new table

Let us validate our table data after the required data has been inserted into the table.

MariaDB [(none)]> select * from servers;
+-----------------+-------+-------------+
| servername      | type  | environment |
+-----------------+-------+-------------+
| fed31.stack.com | linux | prod        |
| fed32.stack.com | linux | prod        |
| fed29.stack.com | linux | cob         |
+-----------------+-------+-------------+
3 rows in set (0.000 sec)

Step9 : Install Mariadb connector and package dependencies

Until this point, we have our new database instance with new table ready with the required data. Now let us install the below dependent packages for getting the Python to Mariadb integration successful.

  • mariadb-connector-c: The MariaDB Native Client library (C driver) is used to connect applications developed in C/C++ to MariaDB and MySQL databases.
  • mariadb-devel: This package contains everything needed for developing MariaDB/MySQL server applications. For developing client applications, we need to use mariadb-connector-c
  • python-devel: This package contains the header files and configuration needed to compile Python extension modules (typically written in C or C++), to embed Python into other programs, and to make binary distributions for Python libraries.
sudo dnf install mariadb-connector-c mariadb-devel python-devel
  • mariadb pip package: MariaDB Connector/Python enables python programs to access MariaDB and MySQL databases, using an API which is compliant with the Python DB API 2.0 (PEP-249). It is written in C and uses MariaDB Connector/C client library for client server communication.
pip install mariadb --user

Output:

Collecting mariadb
  Using cached https://files.pythonhosted.org/packages/8f/c9/7050899dc1066409a17e1147d3afe1b078e582afdb755c6d3cb9c9a5c3ab/mariadb-1.0.0.tar.gz
Installing collected packages: mariadb
  Running setup.py install for mariadb ... done
Successfully installed mariadb-1.0.0

Step10: Simple python program to connect to Mariadb and fetch table data

Now, that we have installed all the required connectors and dependent packages we can test the Python to MariaDB integration below simple python program.

#!/usr/bin/env python
import mariadb

config = {'host': 'localhost', 'user' : 'root', 'password' : 'admin@1234'}
conn = mariadb.connect(**config, database='hostlist')

cur = conn.cursor()
cur.execute("select * from servers")

for host, type, env in cur:
  print(host + " " + type + " " + env)

Output:

fed31.stack.com linux prod
fed32.stack.com linux prod
fed29.stack.com linux cob

Hope you enjoyed reading this article. Thank you.