Connecting Python to MySQLs is as simple as installing a library. Connect to your database and send SQL commands.

Hey everybody!, today we are going to talk about how to connect Python 3 with your MySQL database. This is going to be a somehow long post, but don’t be scared, 75% of it is setting up a database for the examples bellow.

If you already have a database, you will see a little message that is going tell you what to skip. If you are new or don’t have a database set up, I recommend you to read the entire post.

If you are having troubles leave me a comment. I will do my best to help you.

Hard time finding information

Everything started a few days ago. I was making a simple pong game when a cool idea crossed my mind:

Why no make it multiplayer? Sure, why not?

Like any of you, when I decide to start a new random project, the first thing I do is to make some fast research, aka Google it. Unfortunately, none of the result where working. I tried many different libraries, but none of them where for Python 3. Everything was outdated and many were even wrong.

So here I am, making this simple blog post, to save you many hours walking around the web without any direction of where to go. Hope it helps you, enjoy it!

To install python 3 take a look at this post: how to install python in 5 minutes

Setting up a MySQL database

Let’s start by creating a simple MySQL database. It can be hosted anywhere you like, localhost or external (this is the option we will use here).

If you already have a database created, you can skip this section.

The fastest way to create our MySQL database to for testing purpose is to make it in www.freemysqlhosting.net. Just click, Start my free account, register and done.

Once you are done, go to www.freemysqlhosting.net/account/. There, select the location of your database (just pick any, not really important in this tutorial). Next, create your database.

Once your database is created, you should receive an email with the authentication you are going to need (make sure you DO NOT delete it). Inside the email there is a link to phpmyadmin , click it.

PhpMyAdmin

To login into your phpmyadmin account, just fill all the fields with the data received in the email. Now it’s time to create our table.

Now, you must find the SQL button at the top of the page. Click there and paste the next code. This will create the table we are going to use

CREATE TABLE ` sql9213614 ` . ` test ` ( ` id ` INT NOT NULL AUTO_INCREMENT , ` name ` TEXT NOT NULL , ` email ` TEXT NOT NULL , PRIMARY KEY ( ` id ` )) ENGINE = InnoDB ;

We are done for now, you can move to the Python section.

Python MySQL code

We just set up everything we are going to need for this tutorial. Now it’s time to get your hands dirty.

Installation

The first thing we are going to need (and probably the only requirement) is to install the library that is going to help us connect with our database. To do that, we are going to use PyMySQL . The library repository can be found here: PyMySQL in Github.

$ pip install pymysql

Most basic example

Let’s take a look at the minimum piece of code. All this does is to retrieve the database version, enough to check if everything is working as expected. At the bottom I will make a detailed explanation of how it works.

Keep in mind you must change the configuration section with your database credentials.

import pymysql ############### CONFIGURE THIS ################### # Open database connection db = pymysql . connect ( "database_host" , "username" , "password" , "database_name" ) ################################################## # prepare a cursor object using cursor() method cursor = db . cursor () # execute SQL query using execute() method. cursor . execute ( "SELECT VERSION()" ) # Fetch a single row using fetchone() method. data = cursor . fetchone () print ( "Database version : {0}" . format ( data )) # disconnect from server db . close ()

If you run this, and your database credentials are correct, you should see the console print your database version. If it worked, congratulations, you are already done with the hard part.

If you can’t make it work don’t hesitate to leave a comment, I will do my best to help you.

Insert rows into the database

Well, now that we know everything is working, it’s time to start saving data to it. As always, I will paste the code and explain it at the bottom.

import pymysql ############### CONFIGURE THIS ################### # Open database connection db = pymysql . connect ( "database_host" , "username" , "password" , "database_name" ) ################################################## # prepare a cursor object using cursor() method cursor = db . cursor () # Prepare SQL query to INSERT a record into the database. sql = "INSERT INTO test(id, name, email) \ VALUES (NULL,'{0}','{1}')" . format ( "cosme" , "testmail@sever.com" ) try : # Execute the SQL command cursor . execute ( sql ) # Commit your changes in the database db . commit () except : # Rollback in case there is any error db . rollback () # disconnect from server db . close ()

There isn’t much to explain, I think the code is pretty clear by itself. There are just a few things you must keep in mind:

The database name is case sensitive, so it’s test not TEST



not TEST To avoid writing the index column, you must pass NULL





If you run this code, you will see that new row has been added to your database.

Read database

This case is pretty similar to INSERT . We are going to do a simple example with SELECT , but you can use any SQL command.

import pymysql ############### CONFIGURE THIS ################### # Open database connection db = pymysql . connect ( "database_host" , "username" , "password" , "database_name" ) ################################################## # prepare a cursor object using cursor() method cursor = db . cursor () # Prepare SQL query to READ a record into the database. sql = "SELECT * FROM test \ WHERE id > {0}" . format ( 0 ) # Execute the SQL command cursor . execute ( sql ) # Fetch all the rows in a list of lists. results = cursor . fetchall () for row in results : id = row [ 0 ] name = row [ 1 ] email = row [ 2 ] # Now print fetched result print ( "id = {0}, name = {1}, email = {1}" . format ( id , name , email )) # disconnect from server db . close ()

That’s all. Make sure you have something already written in your table or nothing is going to return.

Conclusion

I think you are getting how this works, you can use many other database commands, like DELETE . All follow the same logic, but for more details don’t forget to check PyMySQL documentation.

Despite the fact MySQL is a really old database technology, it’s still being used everywhere and with the help of Python 3 you can create really simple and fast apps.

Unfortunately, I had other troubles to finish my pong game (database connection takes around 1 second to establish making the game lag), but that’s another story.