Introduction to MySQL with Python

When working on my capstone project a couple of months back, I had to train machine learning models on data stored in a MySQL database.

I tried many methods to establish a database connection in Python, and discovered that the easiest way to do this was with the Python MySQL library.

In this article, I will take you through the following topics:

• Install and import the MySQL-connector library
• Establish a MySQL connection with Python
• Access database tables with Python
• Convert tables into Pandas dataframes
• Insert values into tables with Python

Installations and Imports

To establish a MySQL database connection with Python, you first need to install the mysql-connector library. You can do this easily with the following command:

pip install mysql-connector-python

Now, import the library to make sure it works:

import mysql.connector

Establish the connection

Great! Now, you can establish a connection with your MySQL database. Run the following lines of code:

mydb = mysql.connector.connect (

        host="your_hostname",
        user="your_username",
        password="your_password",
        database = "your_db_name"
        
        )
        
mycursor = mydb.cursor()

If these lines of code run with no errors, congratulations! You have successfully established a MySQL connection with Python.

Access database tables

To access a table in the database with Python, run this line of code:

mycursor.execute('SELECT * FROM table_name')

This will select all the rows and columns of the table you want to load. To convert this table into a Pandas dataframe, run these two lines of code:

table_rows = mycursor.fetchall()
df = pd.DataFrame(table_rows)

You can load multiple tables this way, turn them into dataframes, pre-process them, and build machine learning models.

Inserting values into SQL tables

When working on my capstone project, I wanted to capture live incoming traffic and add it to the database.

You can easily add rows to specific database columns using Python.

For example:

Above is an example of a MySQL table. If I want to add a new row to the table above with Python, I can do something like this:

sql = "INSERT INTO table_name (Result, Length, Country, Domain, Hour, OS, Mobile) VALUES (%s, %s, %s, %s, %s, %s)"
        
val = ('benign','1096','USA', '127.0.0.1:5000', '22', 'Windows', '0')

mycursor.execute(sql, val)
mydb.commit()

The above lines will add a new row to the table with the above values.

That's all for this article, I hope you've learnt something useful from this tutorial. Thanks for reading!