How to connect your Python application to MySQL Database

Setup your database

If you do not have MySQL or MariaDB on your machine, you should start by downloading and installing one of them. Once they are installed on your computer, make sure the MySQL server is running. Using the terminal of your choice, log in as root to create a database and a user for your application:

mysql -u root

After running the above command you will be asked to enter your root password if you had chosen one when installing the server, otherwise just press Enter. If you have logged in successfully, you will be taken to the MySQL console prompt.

Create the database for your application:

create database my_database;

Create a user for your application:

CREATE USER 'my_user'@'localhost' IDENTIFIED BY 'my_password';

Give user full rights to your application database:

GRANT ALL ON my_database.* TO 'my_user'@'localhost';

After each command, you should see a console message, which will contain the text "Query OK" if everything went well. Now that you have created the user and the database for your application, you can quit the MySQL prompt.

quit

Write your python program

For this demo, I am using python3 and I am using a virtual environment. If you want to read more about Virtual environments, check the docs. I will use pyenv as the name of my virtual environment as it is one of the default names accepted by my favorite editor, VS Code as venv folder.

In your terminal, create your virtual environment:

$ python3 -m venv pyenv

Activate the virtual environment:

pyenv \Scripts\activate.bat // for Windows
source pyenv/bin/activate // Unix or MacOS

Now you are using a virtual environment for python3. To confirm, test in your terminal:

$ python --version
Python 3.8.2

Install the MySQL database driver for Python:

$ pip install mysql-connector-python

Open your python file and let's connect to the database we created earlier(finally!)

// import MySQL database driver
import mysql.connector

// create a dictionary variable that contains the information needed to connect to your database.
config = {'host': '127.0.0.1', 'user': 'my_user',
          'password': 'my_password', 'database': 'my_database'}

// connect to the MySQL server and save the connection reference in a variable named `conn`
conn = mysql.connector.connect(**config)

// create a cursor to communicate with the database
cursor = conn.cursor()

// send the CREATE command to the server. In this case, will create a simple table called demo
cursor.execute("""CREATE table demo (
id int auto_increment primary key,
ts timestamp default current_timestamp,
greeting varchar(128) not null)""")

// send SHOW command to server to display all tables from the database
cursor.execute("""SHOW tables""")

// ask for the result of the above command and print them
print(cursor.fetchall())

// send DROP table command to server to delete `demo` table
cursor.execute("""DROP table demo""")

// clean after you
cursor.close()
conn.close()

By running the application from above, your terminal should display a tuple:

[('demo',)]

Conclusion

In this short tutorial, you have created a database and a user from MySQL console prompt, you have used a virtual environment for your application and you have learned how to connect your Python application to MySQL (or MariaDB) database.