How to make a Grocery store Web app with Python, Flask and MySQL (Part-2 How to connect a database to python)

In the last part, I taught you how to use basic SQL to make a database. In this article, I will teach you how to connect a MySQL database to Python so you can read data from the database directly from Python. In this part of the series, we will only discuss how to connect the database to Python. The next part will cover how to use Flask to make a web app that gets its data from the database you created or downloaded.

Why should you learn this?

If you know how to make a database, you should at least also know how to connect it to Python using a module, so you can read data from the database directly in the Python code without having to switch to a client like MySQL Workbench every time. Connecting to Python also makes it easier to automate tasks and to work with a lot of data, due to modules like pandas being available in Python, among many other reasons to learn this skill.

Connecting to Python is actually really simple and easy thanks to modules like mysql.connector. This tutorial will focus on the mysql.connector module. It will be divided into 2 parts: a part on how to get mysql.connector and how to connect to the database, and a second part that will cover how to run queries on the database directly from Python. So let us begin by first learning what mysql.connector is.

What is mysql.connector module

This is an official module made by the same people that made MySQL. This connector only works for databases made on MySQL, though each type of SQL service has its own connecting module. With that in mind, let us begin the tutorial.

i) How to get mysql.connector and how to use it to connect to a database.

To get mysql.connector, follow the given steps.
• Open the Python IDE of your choice.
• Open an existing file or create a new one.
• Type pip install mysql-connector-python in the terminal or install the module by clicking a button like pip install if your IDE allows it.
• Wait for it to install.

After these steps, you will have the mysql.connector module installed. To check if it has been installed, as well as the version, simply run the code provided below:-

try:
    import mysql.connector
    print("The module is installed and working")
    print(f"Driver Version: {mysql.connector.__version__}")
except ImportError:
    print("The module isn't installed perhaps you did the steps wrong? Please try again")

This is an extremely simple try and except condition which tries to import a module and prints a message if it was successful, but prints a different message when it wasn't. You can use this pattern to check if another module is installed. Just replace mysql.connector with the name of the module you want to check, and do the same in the version print statement as well. Keep in mind that not every module has a __version__ attribute, and that a module's import name can differ from the name you use with pip.

Now that we have installed the module, here is the code to connect to a database.

import mysql.connector

connection = mysql.connector.connect(
    host="Your host",
    user="Your username",
    password="Your password",
    database="Your database name",
    port=3306,
)

This is the general code to connect using this module. Here is how it works line by line.

•import statement:- just imports the module into your Python file.

•connection = mysql.connector.connect :- Connection is just a variable name, while mysql.connector.connect is just the command to connect to a database, with the information about the database inside the parentheses.

•host="Your host" :- This line just wants to know where your database is. If it is on your computer, replace "Your host" with "localhost". If it is on a cloud server, replace it with the domain name of the service. If it is on another computer, replace it with the IP address of that computer instead.

•user="Your username" :- Just wants the username of your MySQL account. Replace "Your username" with, well, your username. If you didn't create a unique username, enter the default username, which is "root".

•password="Your password" :- Just wants your password. Replace "Your password" with the password you set when you installed MySQL. MySQL has no default password, so use the one you chose during installation.

•database="Your database name" :- Just wants the name of your database. Replace it with your database name. In my case, my database name was Grocery_store_db.

•port=3306 :- 3306 is just the default value. Change it if you have a different port. Make sure not to use inverted commas (") as it is a number and not a string, unlike the others.

ii) How to run queries directly from Python

To run a query in Python, we will first have to connect to the database as shown above.
To run a query, say to get the entire table, we would use the SELECT * FROM <table_name>; query. To do this in Python, we can use the following code directly after connecting to the database:-

import mysql.connector

try:
    connection = mysql.connector.connect(
        host="localhost",
        user="your_username",
        password="your_password",
        database="Grocery_store_db"
    )

    cursor = connection.cursor()
    cursor.execute("SELECT * FROM products")

    for row in cursor.fetchall():
        print(row)

except mysql.connector.Error as e:
    print(f"Error: {e}")

finally:
    if 'connection' in locals() and connection.is_connected():
        cursor.close()
        connection.close()

We first just connect to the database. Then we create a cursor using the cursor = connection.cursor() command, then we use cursor.execute with our SQL query in parentheses. Then we use a simple for loop to print all the rows. I have also used an except block to handle errors. Finally, we close both the cursor and the connection, but only if the connection was successfully created. Now I will assume you know how to use basic pandas, so I will provide a shortcut version where pandas does all the work.

import mysql.connector
import pandas as pd

conn = mysql.connector.connect(host="localhost", user="Your username", password="Your password", database="Grocery_store_db")


products = pd.read_sql("SELECT * FROM products", conn)
print(products)

conn.close()

As you can see, the cursor creation and everything else was handled by pandas. Recent versions of pandas may show a UserWarning recommending SQLAlchemy when you use a connection like this. The code still works.

Congratulations

That's it for this part. In the next part, I will teach you how to combine these skills to make a web app using Flask. Here is what you have achieved after finishing this tutorial.

•How to connect MySQL to Python.
•How to run queries on a server from Python.
•How to shorten some commands using pandas.