Python Mysql Database

and how to connect it

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!

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;

enter image description here

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

Python 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
  • 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.

Fell free to ask any question or share how you manage to make your Python app connect to a MySQL database


Leave a comment