Getting Started with MySQL and Python

Introduction

For any fully functional deployable application, the persistence of data is indispensable. A trivial way of storing data would be to write it to a file in the hard disk, but one would prefer writing the application specific data to a database for obvious reasons. Python provides language support for writing data to a wide range of databases.

Python DB API

At the heart of Python support for database programming is the Python DB API (PEP – 249) which does not depend on any specific database engine. Depending on the database we use at the persistence layer, an appropriate implementation of Python DB API should be imported and used in our program. In this tutorial, we will be demonstrating how to use Python to connect to MySQL database and do transactions with it. For this, we will be using the MySQLdb Python package.

Before we proceed with connecting to the database using Python, we need to install MySQL connector for Python. This can be done in two ways:

• One way is to download the appropriate installer for the OS and bit version directly from the MySQL official site.
• Another way is to use pip to install it.

$ pip install mysql-connector-python

If there is a specific MySQL version installed in the local machine, then you may need a specific MySQL connector version so that no compatibility issues arise, which we can get using the following command:

$ pip install mysql-connector-python==<insert_version_number_here>

Finally, we need to install MySQL client module that will enable us to connect to MySQL databases from our Python application, which acts as the client:

$ pip install mysqlclient

Connecting to the Database

Once we have the connector installed in place, the import MySQLdb statement should not throw any error on executing the Python file.

Prerequisites

Note: It is assumed that the readers have basic understanding of databases in general and the MySQL database in specific, along with knowledge of structured query language (SQL). However, the basic process to create a database and a user has been explained in this section. Follow these steps:

• Ensure that your MySQL server is running. This can be checked via MySQL WorkBench -> Server Status.
• Open MySQL WorkBench or MySQL CLI. Create a new database. Let us call it pythondb.

CREATE DATABASE pythondb;
USE pythondb;

• Create a new user pythonuser with password pythonpwd123 and grant access to pythondb

CREATE USER 'pythonuser'@'localhost' IDENTIFIED BY 'pythonpwd123'
GRANT ALL PRIVILEGES ON pythondb.* To 'pythonuser'@'localhost'
FLUSH PRIVILEGES

Checking your Connection to pythondb

Here is a simple script that can be used to programmatically test connection to the newly created database:

#!/usr/bin/python

import MySQLdb

dbconnect = MySQLdb.connect("localhost", "pythonuser", "pythonpwd123", "pythondb")

cursor = dbconnect.cursor()
cursor.execute("SELECT VERSION()")

data = cursor.fetchone()
if data:
  print('Version retrieved: ', data)
else:
  print('Version not retrieved.')

dbconnect.close()

Output

Version retrieved: 5.7.19

The version number shown above is just a dummy number. It should match the installed MySQL server's version.

Let us take a closer look at the sample program above to learn how it works. First off, import MySQLdb is used to import the required python module.

MySQLdb.connect() method takes hostname, username, password, and database schema name to create a database connection. On successfully connecting with the database, it will return a connection object (which is referred to as dbconnect here).

Using the connection object, we can execute queries, commit transactions and rollback transactions before closing the connection.

Once we get the connection object, we need to get a MySQLCursor object in order to execute queries using the execute method. The result set of the transaction can be retrieved using the fetchall, fetchone, or fetchmany methods, which will be discussed later in this tutorial.

There are three important methods related to database transactions apart from the execute method. We will learn briefly about these methods now.

The dbconnect.commit() method informs the database that the changes executed before calling this function shall be finalized and there is no scope for rolling back to the previous state if the transaction is successful.

Sometimes, if a transaction failure occurs, we will need to change the database to the previous state before the failure happens so that the data is not lost or corrupted. In such a case, we will need to rollback the database to the previous state using dbconnect.rollback().

Finally, the dbconnect.close() method is used to close the connection to the database. To perform further transactions, we need to create a new connection.

Create a New Table

Once the connection with pythondb is established successfully, we are ready to go to the next step. Let us create a new table in it:

import MySQLdb

dbconnect = MySQLdb.connect("localhost","pythonuser","pythonpwd123","pythondb" )

cursor = dbconnect.cursor()
cursor.execute("DROP TABLE IF EXISTS MOVIE")

query = "CREATE TABLE MOVIE(  \
          id int(11) NOT NULL,\
          name varchar(20),\
          year int(11),\
          director varchar(20),\
          genre varchar(20),\
          PRIMARY KEY (id))"

cursor.execute(query)

dbconnect.close()

After executing the above script, you should be able to see a new table movie created for the schema pythondb. This can be viewed using MySQL WorkBench.

Performing CRUD Operations

Now we'll perform some insert, read, modify, and delete operations in the newly created database table via the Python script.

Creating a New Record

The following script demonstrates how to insert a new record into MySQL database using a Python script:

#!/usr/bin/python

import MySQLdb

dbconnect = MySQLdb.connect("localhost", "pythonuser", "pythonpwd123", "pythondb")

cursor = dbconnect.cursor()

query = 'insert into movie(id, name, year, director, genre)  \
       values (1, "Bruce Almighty", 2003, "Tom Shaydac", "Comedy")'
try:
   cursor.execute(query)
   dbconnect.commit()
except:
   dbconnect.rollback()
finally:
   dbconnect.close()

Reading Rows from a Table

Once a new row is inserted in the database, you can fetch the data in three ways using the cursor object:

• cursor.fetchall() – can be used to get all rows
• cursor.fetchmany() – can be used to get a selected number of rows
• cursor.fetchone() – can be used to get only the first row from the result set

For simplicity, we will use the "select all" SQL query and use a for loop over the result set of the fetchall method to print individual records.

#!/usr/bin/python

import MySQLdb

dbconnect = MySQLdb.connect("localhost", "pythonuser", "pythonpwd123", "pythondb")

cursor = dbconnect.cursor()

query = "SELECT * FROM movie"
try:
   cursor.execute(query)
   resultList = cursor.fetchall()
   for row in resultList:
      print ("Movie ID =", row[0])
      print ("Name =", row[1])
      print ("Year =", row[2])
      print ("Director = ", row[3])
      print ('Genre = ', row[4])
except:
   print ("Encountered error while retrieving data from database")
finally:
   dbconnect.close()

Output:

Movie ID = 1
Name = Bruce Almighty
Year = 2003
Director = Tom Shaydac
Genre = Comedy

Updating a Row

Let us now update the Genre of "Bruce Almighty" from Comedy to Satire:

import MySQLdb

dbconnect = MySQLdb.connect("localhost", "pythonuser", "pythonpwd123", "pythondb")

# The cursor object obtained below allows SQL queries to be executed in the database session.
cursor = dbconnect.cursor()

updatequery = "update movie set genre = 'Satire' where id = 1"

cursor.execute(updatequery)

dbconnect.commit()

print(cursor.rowcount, "record(s) affected")

Output:

1 record(s) affected

Deleting a Record

Here is a Python script that demonstrates how to delete a database row:

import MySQLdb

dbconnect = MySQLdb.connect("localhost", "pythonuser", "pythonpwd123", "pythondb")

# The cursor object obtained below allows SQL queries to be executed in the database session.
cursor = dbconnect.cursor()

updatequery = "DELETE FROM movie WHERE id = 1"

cursor.execute(updatequery)

dbconnect.commit()

print(cursor.rowcount, "record(s) deleted")

After executing the above script, you should be able to see the following output if everything goes well.

Output

1 record(s) deleted

Conclusion

In this article, we learned how to use the Python DB API to connect to a database. Specifically, we saw how a connection can be established to a MySQL database using the MySQLdb implementation of Python DB API. We also learned how to perform transactions with the database.