Hi, I'm Harlin and welcome to my blog. I write about Python, Alfresco and other cheesy comestibles.

Python - How to Work with MySQL

What we need:

  • Python 3.6.3
  • mysqlclient module
  • MySQL server
  • mysql-workbench

First, make sure you install MySQL server and ensure it's running. Instructions on how to install MySQL server:

Ubuntu

RHEL/CentOS/Fedora

Windows

To install the mysqlclient module, you'll first need to install MySQL server (and if you're on Linux, MySQL server development package).

For Ubuntu users, you can do:

$ sudo apt install libmysqld-dev

Or RHEL, CentOS or Fedora, you can do:

$ sudo yum install mysqld-devel

On Windows, you can either use pip or install mysqlclient manually. See https://pypi.python.org/pypi/mysqlclient.

For Linux, you can just use pip:

$ pip install mysqlclient

To install MySQL Workbench, you can use:

$ sudo apt install mysql-workbench

or (if you're on RHEL, CentOS or Fedora):

$ sudo yum install mysql-workbench

Or if you're on Windows, you can install it from https://dev.mysql.com/downloads/workbench/.

A lot of tutorials out there will show you how to create tables through Python but to be honest with you, I have never wrote an app that deals with databases where I have to write tables as part of my Python code. So, we'll use the MySQL Workbench to create our tables.

Let's open up MySQL Workbench and design a database with a table. If you're using Ubuntu, you can start it by issuing:

$ mysql-workbench

Mysql

Click on Database > Manage Connections

Mysql

Click New (bottom left hand corner) and then the Connection tab.

Mysql

Make sure that host, port and username are set properly. Click on Store in Keychain and enter the MySQL root user's password and click Ok. Next, click on Test Connection to make sure the connection was successful. You should see this:

Mysql

Name the connection whatever you like. I named mine Localhost. Then click Close.

Mysql

At this window, click on Database > Connect to Database and choose the one you just saved (mine was called Localhost). You should now see this window:

Mysql

And then, click on the Add Database Icon:

Mysql

Next, name the new schema as "mydb" and use "utf8-default collation" and then click apply:

Mysql

This will result in a dialog page as shown below. You can then click Apply:

Mysql

You will then see in the left hand corner our database called "mydb".

Mysql

Instead of using the 'root' user for our script, I recommend using a separate user to access your new database. You can set this up by running the mysql command line client like so:

$ mysql -u root -pYour_Password

This will show a database prompt like so:

mysql>

You can then set up privileges for a user (I use one called 'admin'):

mysql> grant all privileges on mydb.* to 'admin'@'localhost' identified by 'admin';
Query OK, 0 rows affected, 1 warning (0.02 sec)

Now, back to the MySQL Workbench, if you drill down into "mydb", you will see "Tables":

Mysql

Right-click on Tables and click Create Tables ...

This will bring up a table dialog. Go ahead and fill them out just like I have in the following screenshot:

Mysql

When you've done that, click on the Apply button. This will save the user table. To explain what we've just done with our user table:

Notice that we've created a primary key called id. This will allow us to make sure each record will have its own unique identifier. For id, we will check the NOT NULL, PRIMARY KEY, AUTOINCREMENT and UNIQUE boxes. Let's go over quickly what each of these mean:

  • NOT NULL - If a record is added, this means that this column value cannot be null or empty. There must be some kind of data for it. Note that I chose integer for its type. This means that the id will be a number.

  • PRIMARY KEY - A primary key id a column or a group of columns that uniquely identifies a row in the table.

  • AUTOINCREMENT - This means that we do not need to add the id value ourselves in our code and that as each record is added, this number will be autoincremented (usually by one).

  • UNIQUE - We won't get into what indexes are but know that the value for this column must be unique compared to the same column for other records or rows in the table.

The first_name and last_name only need to be set as NOT NULL. These do not have to be unique and we're not going to use them as a primary key.

The email column is set up as NOT NULL but we also set up the UNIQUE constraint for it so that each user we're creating must have its own unique email address.

Now, let's enter a few records. Leave the id column empty as MySQL should autocreate and autoincrement the id number when we create the fields.

We will create 3 users but first, let me show you how to get to the data part of the table so we can add them. Note below the user table in the interface. You can't see it (due to my shoddy screenshot software) but if you expand out to the "user" table and place the mouse curosor over it, a three icons will appear to the right. On the far right, you will see a grid icon. Click on that:

Mysql

And you will see this:

Mysql

Enter the users as I've shown below:

Mysql

Don't add an id for each of these as they will be automatically generated for us when we click Apply at the bottom right hand corner of this window.

After clicking Apply our users should now be saved with id's like they are shown here:

Mysql

Now, let's get to coding and create a script that will get the results of our records in our database:

#!/usr/bin/env python

# Importing the MySQLdb module
import MySQLdb

# Create a connection to MySQL database.
connection = MySQLdb.connect(
    host='127.0.0.1',
    user='admin',
    password='admin',
    db='mydb'
)

# Create a cursor object for querying.
cursor = connection.cursor()

# Execute an SQL select statement - should give us all
# records from the user table.
cursor.execute("SELECT * FROM `user`")

# Get the results of the query using fetchall()
results = cursor.fetchall()

# Print the results
print(results)

# Not necessary but good practice.
cursor.close()

# Do this. It's a good idea.
connection.close()

When you run this script you should get the following output:

$ ./demo1.py 
[(1, 'Tom', 'Brown', 'tom.brown@localhost'), (2, 'Bill', 'Blue', 'bill.blue@localhost'), (3, 'Sydney', 'Black', 'sydney.black@localhost')]

As you can see, it will return the list of rows we just created.

Now, let's go through each line of our script.

Of course, we'll need to import the MySQLdb module so that we can use it. Next, we'll create a connection handler (much like you would if you were going to open a file). Once we have the connection handler, we'll get a cursor object so we can run queries.

The cursor.fetchall() will allow us to get all the results of our query. Note that fetchall() will return a list of items (even if there's only one row, it will still come back as a list object).

You can see the list with the print(results) line. With MySQLdb, you normally would not need to use cursor.close() but I've found it's always a good idea to do this especially if you are doing batch updates or inserts. It's also a habit I picked up from Java. With Java, if you're not careful, leaving cursors open can cause some cursor overloading with RDBMS systems and some other weird behavior. So, in my opinion, I believe it's a good habit to get into.

Lastly, we close our connection.

Now, you'll notice with the print(results) we only get a list. What if we wanted to iterate through each record based on column? To do that, we can change our code to this:

...

# Get the results of the query using fetchall()
results = cursor.fetchall()

# Print the results
for row in results:
    user_id, first_name, last_name, email = row
    print(f'User ID: {user_id}')
    print(f'First Name: {first_name}')
    print(f'Last Name: {last_name}')
    print(f'Email Address: {email}')
    print('\n')

# Not necessary but good practice.
cursor.close()

# Do this. It's a good idea.
connection.close()

If you do that, we should see this more readable output:

$ ./demo1.py 
User ID: 1
First Name: Tom
Last Name: Brown
Email Address: tom.brown@localhost


User ID: 2
First Name: Bill
Last Name: Blue
Email Address: bill.blue@localhost


User ID: 3
First Name: Sydney
Last Name: Black
Email Address: sydney.black@localhost

With this knowledge, you should be able to run most any kind of query you want to even dynamic ones. What if we wanted to only get the results of one user called Sydney Black? We'll need to change our query and since we're expecting only one result, we can use fetchone() instead of fetchall(). Also, I'm going to show you how to handle variable substitutions for strings in the query that is done in a safe matter. Here's an example that will do that for us:

...

# Execute an SQL select statement - should give us all
# records from the user table.
cursor.execute("SELECT * FROM `user` WHERE LAST_NAME=%s", args=('Black',))

# Get the results of the query using fetchall()
record = cursor.fetchone()

# Print the results
user_id, first_name, last_name, email = record
print(f'User ID: {user_id}')
print(f'First Name: {first_name}')
print(f'Last Name: {last_name}')
print(f'Email Address: {email}')

# Not necessary but good practice.
cursor.close()

# Do this. It's a good idea.
connection.close()

And our output should be:

$ ./demo1.py 
User ID: 3
First Name: Sydney
Last Name: Black
Email Address: sydney.black@localhost

We changed results to be a record because with fetchone() we're only expecting one record to be returned. What happens if there is more than one record that gets returned with fetchone()? That will cause an error letting us know more than one record is being returned from our query.

As I mentioned earlier, you can use most any query with MySQL and Python. If you do not have a good grasp of SQL, I would suggest having a look here to learn more.

Any Comments, Always Welcome!