My First working Python3 MariaDB Lookup Script

Python3 MariaDB Lookup Script

This week I decided to put the Python3 knowledge that I had gained studying for the last couple of weeks to work and write a MariaDB lookup script to get details from a MySQL database and show the details on screen.

I got the idea because at work are development teams have team names that have nothing to do with their roles so it gets a bit difficult sometimes to know which team does what and how is in which team. The details are on a web page on GitLab but I figured why not try and make a Python script to show the details without having to open a website.

The first thing that I had to do was create a database and add the data needed which I did with a bit of googling so I had my database and it had all of the details needed. I then set about googling how to lookup data from MySQL in Python and found details on the MariaDB Connector/C which was exactly what I needed to pull the data from my MariaDB database.

To install the MariaDB connector I first had to install some dependency’s by running:

sudo apt-get install libmariadb3 libmariadb-dev 

The Mariadb Connector/C was then installed using PIP by running:

pip3 install mariadb

I then played around with the usage until I got the script working in its basic form so that the user has to input the group that they want to find details for at the command prompt (list of groups given):

#!/usr/bin/python
# taken from https://mariadb.com/resources/blog/how-to-connect-python-programs-to-mariadb/

import mariadb

conn = mariadb.connect(
    user="root",
    password="ENTER_PASSWORD",
    host="localhost",
    database="ENTER_DB_NAME")
cur = conn.cursor()

#retrieving information fro
m command input:
some_name = input ("""Options are:
        GROUP1_NAME, GROUP2_NAME, GROUP3_NAME, GROUP4_NAME, GROUP5_NAME and GROUP5_NAME
        Type group name here: """)
cur.execute("SELECT Characteristic2, Characteristic3, Characteristic4 FROM Teams WHERE Charecteristic1=?", (some_name,))


for Characteristic2, Charecteristics3, Members in cur:
    print(f"""Charecteristics2: {Characteristics2},
    
    Charecteristic3: {Charecteristics3},
    
    Charecteristic4: {Charecteristics4}""")

So the program basically loads Mariadb Connector/C with:

import mariadb

Mariadb Connector/C then connects to the database using:

conn = mariadb.connect(
    user="root",
    password="ENTER_PASSWORD",
    host="localhost",
    database="ENTER_DB_NAME")
cur = conn.cursor()

Python3 then prints the following on the screen and asks the user to enter a group name which it adds as the some_name variable

some_name = input ("""Options are:
        GROUP1_NAME, GROUP2_NAME, GROUP3_NAME, GROUP4_NAME, GROUP5_NAME and GROUP5_NAME
        Type group name here: """)

Python then uses the some_name variable and then does a database lookup with:

cur.execute("SELECT Characteristic2, Characteristic3, Characteristic4 FROM Teams WHERE Charecteristic1=?", (some_name,))

The information from the lookup are then printed on screen:

for Characteristic2, Charecteristics3, Members in cur:
    print(f"""Charecteristics2: {Characteristics2},
    
    Charecteristic3: {Charecteristics3},
    
    Charecteristic4: {Charecteristics4}""")

So for my script the output looks like:

For more Python scripts go to my Python category.

As I stated before this is my first script and it is pretty basic. Over the coming months I will add to the script to make it more flexible and also I want to allow the use of arguments when running the script to make its use faster from the command line. 

I have added the script to a new repository on my GitHub site so that any further updates will be automatically updated in GitHub. Along with the script I have added a README.md which outlines the use of the script and also a planning document that show the improvements that I want to add to the script to make it more user friendly and add make it more flexible so one day it will be a complete lookup program not just a script.

Although the current scrip is basic I am pretty pleased with it as it is my first ever project and it did exactly what I wanted it to do and it was surprisingly simple and took a lot less time than I imagined to complete which was good.