How to connect to MySQL database using Python

Don’t you hate closing a file and forgetting to save it? It’s an unpleasant situation, but it happens no matter how many years of computer experience you have. And that’s what happens when you don’t connect your project to a database.

In this article I will talk about how to connect Python to MySQL database

Okay, so you’ve got your project up and running. Why do you need to connect to a database?

Unless you want your project to not save any information, you don’t need to. A database is used to store information that we can use later, and we don’t have to start from scratch.

We will explain step by step how to connect and create a MySQL database using Python, in the shortest and most efficient way possible.

What is a Database, and why is it important?

A database stores data in an organized way so that you can search and retrieve that data later.

A database must contain one or more tables. A table is very similar to a spreadsheet, it is basically a set of rows and columns.

All rows have the same columns, and each column contains the data itself. If it helps, think of your tables the same way you would think of a table in Excel.

Data can be inserted, retrieved, updated, and deleted from a table. The word, created, is often used instead of inserted; these four functions are affectionately abbreviated as CRUD (Create, Read, Update, and Delete).

A relational database organizes data into tables and links them together based on defined relationships. These relationships allow you to retrieve and combine data from one or more tables with a single query.

A database will be extremely useful if you followed the tutorial where I showed you how to create a login page from scratch .

But that was just a bunch of words. To truly understand a relational database, you need to build your own. So, let’s get started.

Installing MySQL

The first thing you need to do if you want to connect Python to a MySQL database is to download and install the latest version of MySQL. Simply go to the MySQL website and download the correct version for your computer’s operating system, if you haven’t already done so. The version I’m using here is MySQL Community Server 8.0.19.

I also strongly recommend you install a tool to graphically manage your databases like MySQL Workbench or phpMyAdmin .

Once this is done, just check if your database is working with any of the tools I suggested, or any other you prefer, using the username and password you created during installation.

Python connector to MySQL database

Second step, we need to connect Python to the MySQL database, through a connector.

Python does not come natively with a connector. You need to download it. A Connector is nothing more than a module that helps communication between Python and the MySQL database. We have at least 5 modules available, but in this tutorial, we will use the one provided by MySQL itself .

To install the Connector, the simplest (and recommended) way is to use pip , which is the package installer for Python. I mentioned pip in this other article . If you already have pip installed, just run the command below to install the Python/MySQL Connector.

pip install mysql-connector-python

Connecting Python to MySQL Database

Connecting Python to the MySQL database using the connector we just installed is quite simple.

The first thing we need to do is import the connector. Then use the function connect()with the credentials you generated during the MySQL installation. The host name can be either the host name localhostor the host name, 127.0.0.1which are synonyms.
In this test we will only open a connection and close it immediately afterwards, with the function close().

import mysql.connector

cnx = mysql.connector.connect(user= 'root' , password= '*******' , host= '127.0.0.1' )

cnx.close()

Copy and paste this code, save the file as connection.pyor however you prefer. Open a window in your terminal in the folder where the file is located and run the command python connection.py.

If this code runs without problems, it means it worked and you can now use MySQL.

Creating a MySQL Database Using Python

It’s time to create our first database. Let’s modify the code above a bit and create the code that will connect Python to the MySQL database.

Let’s create an instance of an object of the class MySQLCursorinstead of just closing the connection. The object interacts with the MySQL server using an object MySQLConnection.

To create the cursor, simply use the method cursor()on an existing connection. Now, write our SQL Query and execute it using the method execute()of the connection object mycursorwe just created.

The SQL command to create a database is:

CREATE DATABASE mydatabase

Putting it all together, we have this code below.

import mysql.connector

cnx = mysql.connector.connect(user= 'root' , password= '*******' , host= '127.0.0.1' )

mycursor = cnx.cursor()
mycursor.execute( "CREATE DATABASE mydatabase" )

Just run this file with the same command I showed above, python connection.py. If this code also runs without problems, congratulations, you have just created your first database using Python.

Conclusion

To check your newly created database you can use one of the tools I mentioned earlier. Using MySQLWorkbench I got this result.

To recap everything we’ve seen so far.
First step, we need to download MySQL, then install the Python/MySQL connector to be able to connect Python to the MySQL database and finally, create your first database using the MySQLCursor class.

Connecting your project to a database is an important and essential step. As your project grows, you can expect to accumulate more data. That’s why I’m explaining how to connect Python to a MySQL database.

MySQL is an extremely reliable and widely used database, your project will be very well served with it.

Well, I hope it’s clear why you should have a database in your project and how simple it is to create a connection.

See you next time.