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

Test Environment

Fedora 31 updated


Here in this article we will try to integrate our simple python application with Mariadb database server which is a popular open source releational 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.

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.

Mariadb client and server package installation 
[root@fed31 ~]# dnf install mariadb mariadb-server
...
Installed:
  mariadb-3:10.3.22-1.fc31.x86_64                                mariadb-backup-3:10.3.22-1.fc31.x86_64              mariadb-common-3:10.3.22-1.fc31.x86_64              
  mariadb-cracklib-password-check-3:10.3.22-1.fc31.x86_64        mariadb-errmsg-3:10.3.22-1.fc31.x86_64              mariadb-gssapi-server-3:10.3.22-1.fc31.x86_64       
  mariadb-server-3:10.3.22-1.fc31.x86_64                         mariadb-server-utils-3:10.3.22-1.fc31.x86_64        mysql-selinux-1.0.0-8.fc30.noarch                   
  perl-DBD-MySQL-4.050-4.fc31.x86_64                             perl-DBI-1.642-5.fc31.x86_64                        perl-Math-BigInt-1:1.9998.16-439.fc31.noarch        
  perl-Math-Complex-1.59-452.fc31.noarch                        

Complete!

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 privilages table to make the following changes take effect

Secure Mariadb database 
[root@fed31 ~]# mysql_secure_installation

Step3: Start the Mariadb server

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

Start Mariadb service 
[root@fed31 tmp]# systemctl start mariadb.service 
[root@fed31 tmp]# 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.

Connect to mysql 
[root@fed31 ~]# 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.

Create new database 
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.

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

MariaDB [hostlist]> 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.

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

Step8 : Fetch data from new table

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

Fetch data from table 
MariaDB [hostlist]> 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.

Install connector and dependencies 
[admin@fed31 ~]$ sudo dnf install mariadb-connector-c mariadb-devel
...
Installed:
  mariadb-connector-c-devel-3.1.7-1.fc31.x86_64    mariadb-devel-3:10.3.22-1.fc31.x86_64    openssl-devel-1:1.1.1g-1.fc31.x86_64    zlib-devel-1.2.11-20.fc31.x86_64   

[admin@fed31 ~]$ sudo dnf install python-devel
...
Installed:
  python3-devel-3.7.7-3.fc31.x86_64

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.

Install mariadb pip package 
[admin@fed31 ~]$ pip install mariadb --user
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 requrired connectors and dependent packages we can test the Python to MariaDB integration below simple python program

Python program to connect Mariadb and output 
#!/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.