MySQL Python tutorial

About this tutorial

This is a Python programming tutorial for the MySQL database. It covers the basics of MySQL programming with Python. It uses the MySQLdb module. The examples were created and tested on Ubuntu Linux. There is a similar MySQL C API tutorial, MySQL Visual Basic tutorial, or MySQL PHP tutorial on ZetCode. If you need to refresh your knowledge of the Python language, there is a full Python tutorial. You may also consider to look at the MySQL tutorial, too.

About MySQL database

MySQL is a leading open source database management system. It is a multi user, multithreaded database management system. MySQL is especially popular on the web. It is one of the parts of the very popular LAMP platform. Linux, Apache, MySQL, PHP. Currently MySQL is owned by Oracle. MySQL database is available on most important OS platforms. It runs under BSD Unix, Linux, Windows or Mac. Wikipedia and YouTube use MySQL. These sites manage millions of queries each day. MySQL comes in two versions. MySQL server system and MySQL embedded system.

Before we start
We need to install several packages to execute the examples in this tutorial. If you don't already have MySQL installed, we must install it.

$ sudo apt-get install mysql-server

This command installs the MySQL server and various other packages. While installing the package, we are prompted to enter a password for the MySQL root account.

$ apt-cache search MySQLdb python-mysqldb - A Python interface to MySQL python-mysqldb-dbg - A Python interface to MySQL (debug extension)

bibus - bibliographic database eikazo - graphical frontend for SANE designed for mass-scanning

We don't know the package name for the MySQLdb module. We use the apt-cache command to figure it out.

$ sudo apt-get install python-mysqldb

Here we install the Python interface to the MySQL database. Both _mysql and MySQL modules. Next, we are going to create a new database user and a new database. We use the mysql client.

$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Your MySQL connection id is 30 Server version: 5.0.67-0ubuntu6 (Ubuntu) Commands end with ; or \g.

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW DATABASES; +--------------------+ | Database |

+--------------------+ | information_schema |

| mysql

+--------------------+ 2 rows in set (0.00 sec)

We connect to the database using the root account. We show all available databases with the SHOW DATABASES statement.

mysql> CREATE DATABASE testdb; Query OK, 1 row affected (0.02 sec)

We create a new testdb database. We will use this database throughout the tutorial.

mysql> CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'test623'; Query OK, 0 rows affected (0.00 sec)

mysql> USE testdb; Database changed

mysql> GRANT ALL ON testdb.* TO 'testuser'@'localhost'; Query OK, 0 rows affected (0.00 sec)

mysql> quit; Bye

We create a new database user. We grant all privileges to this user for all tables of the testdb database.

_mysql module
The _mysql module implements the MySQL C API directly. It is not compatible with the Python DB API interface. Generally, the programmers prefer the object oriented MySQLdb module. We will concern ourself with the latter module. Here we present only one small example with the _mysql module.

#!/usr/bin/python # -*- coding: utf-8 -*-

import _mysql import sys

try: conn = _mysql.connect('localhost', 'testuser', 'test623', 'testdb');

conn.query("SELECT VERSION()") result = conn.use_result()

print "MySQL version: %s" % \ result.fetch_row()[0]

conn.close()

except _mysql.Error, e:

print "Error %d: %s" % (e.args[0],e.args[1]) sys.exit(1)

The example will get and print the version of the MySQL database. For this, we use the SELECT VERSION() SQL statement.

MySQLdb module
MySQLdb is a thin Python wrapper around _mysql. It is compatible with the Python DB API, which makes the code more portable. Using this model is the preferred way of working with the MySQL.

First example
In the first example, we will get the version of the MySQL database.

#!/usr/bin/python # -*- coding: utf-8 -*-

import MySQLdb as mdb import sys

try: conn = mdb.connect('localhost', 'testuser', 'test623', 'testdb');

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

data = cursor.fetchone()

cursor.close() conn.close()

except mdb.Error, e: print "Error %d: %s" % (e.args[0],e.args[1]) sys.exit(1)

print "Database version : %s " % data

In this script, we connect to the testdb database and execute the SELECT VERSION() statement. This will return the current version of the MySQL database. We print it to the console. import MySQLdb as mdb We import the MySQLdb module. conn = mdb.connect('localhost', 'testuser', 'test623', 'testdb'); We connect to the database. The connect() method has four parameters. The first parameter is the host, where the MySQL database is located. In our case it is a localhost,

e.g. our computer. The second parameter is the database user name. It is followed by the user's account password. The final parameter is the database name. cursor = conn.cursor() cursor.execute("SELECT VERSION()") From the connection, we get the cursor object. The cursor is used to traverse the records from the result set. We call the execute() method of the cursor and execute the SQL statement. data = cursor.fetchone() We fetch the data. Since we retrieve only one record, we call the fetchone() method. cursor.close() conn.close() Releasing of resources. except mdb.Error, e: print "Error %d: %s" % (e.args[0],e.args[1]) sys.exit(1) We check for errors. This is important, since working with databases is error prone. print "Database version : %s " % data We print the data that we have retrieved to the console.

$ ./version.py Database version : 5.1.41-3ubuntu12.6

The output might look like the above.

Creating and populating a table

We create a table and populate it with some data.

#!/usr/bin/python # -*- coding: utf-8 -*-

import MySQLdb as mdb import sys

try: conn = mdb.connect('localhost', 'testuser', 'test623', 'testdb');

cursor = conn.cursor() cursor.execute("CREATE TABLE IF NOT EXISTS \ Writers(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(25))") cursor.execute("INSERT INTO Writers(Name) VALUES('Jack London')") cursor.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')") cursor.execute("INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')") cursor.execute("INSERT INTO Writers(Name) VALUES('Emile Zola')") cursor.execute("INSERT INTO Writers(Name) VALUES('Truman Capote')")

conn.commit()

cursor.close()

conn.close()

except mdb.Error, e:

print "Error %d: %s" % (e.args[0],e.args[1]) sys.exit(1)

We create a Writers table and add five authors to it.

cursor.execute("CREATE TABLE IF NOT EXISTS \ Writers(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(25))")

This SQL statement creates a new database table called Writers. It has two columns. Id and Name. cursor.execute("INSERT INTO Writers(Name) VALUES('Jack London')") cursor.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')") ... We use the INSERT statement to insert authors to the table. Here we add two rows.

mysql> SELECT * FROM Writers; +----+-------------------+ | Id | Name |

+----+-------------------+ | | 1 | Jack London 2 | Honore de Balzac | |

| | |

3 | Lion Feuchtwanger | 4 | Emile Zola 5 | Truman Capote | |

+----+-------------------+ 5 rows in set (0.00 sec)

After executing the script, we use the mysql client tool to select all data from the Writers table.

Retrieving data
Now, that we have inserted some data into the database, we want to get it back.

#!/usr/bin/python # -*- coding: utf-8 -*-

import MySQLdb as mdb import sys

try: conn = mdb.connect('localhost', 'testuser', 'test623', 'testdb');

cursor = conn.cursor() cursor.execute("SELECT * FROM Writers")

rows = cursor.fetchall()

for row in rows: print row

cursor.close() conn.close()

except mdb.Error, e:

print "Error %d: %s" % (e.args[0],e.args[1]) sys.exit(1)

In this example, we retrieve all data from the Writers table. cursor.execute("SELECT * FROM Writers") This SQL statement selects all data from the Writers table. rows = cursor.fetchall() The fetchall() method gets all records. It returns a result set. Technically, it is a tuple of tuples. Each of the inner tuples represent a row in the table. for row in rows: print row We print the data to the console, row by row.

$ ./retrieve.py (1L, 'Jack London') (2L, 'Honore de Balzac') (3L, 'Lion Feuchtwanger') (4L, 'Emile Zola') (5L, 'Truman Capote')

This is the output of the example. Returning all data at a time may not be feasible. We can fetch rows one by one.

#!/usr/bin/python # -*- coding: utf-8 -*-

import MySQLdb as mdb import sys

try: conn = mdb.connect('localhost', 'testuser', 'test623', 'testdb');

cursor = conn.cursor() cursor.execute("SELECT * FROM Writers")

numrows = int(cursor.rowcount)

for i in range(numrows): row = cursor.fetchone() print row[0], row[1]

cursor.close() conn.close()

except mdb.Error, e:

print "Error %d: %s" % (e.args[0],e.args[1]) sys.exit(1)

We again print the data from the Writers table to the console. This time, we fetch the rows one by one. numrows = int(cursor.rowcount) Here we determine the number of rows returned by the SQL statement. for i in range(numrows): row = cursor.fetchone() print row[0], row[1] We fetch the rows one by one using the fetchone() method.

$ ./retrieve2.py 1 Jack London 2 Honore de Balzac 3 Lion Feuchtwanger 4 Emile Zola 5 Truman Capote

Output of the example.

The dictionary cursor

There are multiple cursor types in the MySQLdb module. The default cursor returns the data in a tuple of tuples. When we use a dictionary cursor, the data is sent in a form of Python dictionaries. This way we can refer to the data by their column names.

#!/usr/bin/python # -*- coding: utf-8 -*-

import MySQLdb as mdb import sys

try: conn = mdb.connect('localhost', 'testuser', 'test623', 'testdb');

cursor = conn.cursor(mdb.cursors.DictCursor) cursor.execute("SELECT * FROM Writers")

rows = cursor.fetchall()

for row in rows: print "%s %s" % (row["Id"], row["Name"])

cursor.close() conn.close()

except mdb.Error, e:

print "Error %d: %s" % (e.args[0],e.args[1]) sys.exit(1)

In this example, we print the contents of the Writers table using the dictionary cursor. cursor = conn.cursor(mdb.cursors.DictCursor) We use the DictCursor dictionary cursor. rows = cursor.fetchall() We fetch all data. for row in rows:

print "%s %s" % (row["Id"], row["Name"]) We refer to the data by column names of the Writers table.

Column headers
Next we will show, how to print column headers with the data from the database table.

#!/usr/bin/python # -*- coding: utf-8 -*-

import MySQLdb as mdb import sys

try: conn = mdb.connect('localhost', 'testuser', 'test623', 'testdb');

cursor = conn.cursor() cursor.execute("SELECT * FROM Writers")

rows = cursor.fetchall()

desc = cursor.description

print "%s %3s" % (desc[0][0], desc[1][0])

for row in rows: print "%2s %3s" % row

cursor.close() conn.close()

except mdb.Error, e:

print "Error %d: %s" % (e.args[0],e.args[1]) sys.exit(1)

Again, we print the contents of the Writers table to the console. Now, we include the names of the columns too. The column names are considered to be the 'meta data'. It is obtained from the cursor object. desc = cursor.description A Cursor Object's description attribute returns information about each of the result columns of a query. print "%s %3s" % (desc[0][0], desc[1][0]) Here we print and format the table column names. for row in rows: print "%2s %3s" % row And here, we traverse and print the data.

$ ./columnheaders.py Id Name 1 Jack London 2 Honore de Balzac 3 Lion Feuchtwanger 4 Emile Zola 5 Truman Capote

Ouput of the script.

Prepared statements
Now we will concern ourselves with prepared statements. When we write prepared statements, we use placeholders instead of directly writing the values into the statements. Prepared statements increase security and performance. The Python DB API specification suggests 5 different ways how to build prepared statements. MySQLdb module supports one of them, the ANSI printf format codes.

#!/usr/bin/python # -*- coding: utf-8 -*-

import MySQLdb as mdb import sys

try: conn = mdb.connect('localhost', 'testuser', 'test623', 'testdb');

cursor = conn.cursor()

cursor.execute("UPDATE Writers SET Name = %s WHERE Id = %s", ("Guy de Maupasant", "4"))

print "Number of rows updated: %d" % cursor.rowcount

cursor.close() conn.close()

except mdb.Error, e:

print "Error %d: %s" % (e.args[0],e.args[1]) sys.exit(1)

We change the name of an author on the fourth row. cursor.execute("UPDATE Writers SET Name = %s WHERE Id = %s", ("Guy de Maupasant", "4")) This is the way to do it. We use the two %s placeholders. Before the SQL statement is executed, the values are bound to their placeholders.

mysql> SELECT Name FROM Writers WHERE Id=4;

+------------------+ | Name |

+------------------+ | Guy de Maupasant | +------------------+ 1 row in set (0.00 sec)

The author on the fourth row was successfully changed.

Writing images
Some people prefer to put their images into the database, some prefer to keep them on the file system for their applications. Technical difficulties arise when we work with millions of images. Images are binary data. MySQL database has a special data type to store binary data called BLOB (Binary Large Object).

mysql> CREATE TABLE Images(Id INT PRIMARY KEY AUTO_INCREMENT, Data MEDIUMBLOB); Query OK, 0 rows affected (0.06 sec)

For this example, we create a new table called Images.

#!/usr/bin/python # -*- coding: utf-8 -*-

import MySQLdb as mdb import sys

try: fin = open("chrome.png") img = fin.read() fin.close()

except IOError, e:

print "Error %d: %s" % (e.args[0],e.args[1]) sys.exit(1)

try: conn = mdb.connect(host='localhost',user='testuser', passwd='test623', db='testdb') cursor = conn.cursor() cursor.execute("INSERT INTO Images SET Data='%s'" % \ mdb.escape_string(img))

conn.commit()

cursor.close()

conn.close()

except mdb.Error, e:

print "Error %d: %s" % (e.args[0],e.args[1]) sys.exit(1)

In the above script, we read a png image and insert it into the Images table. fin = open("chrome.png") img = fin.read() We open and read an image. The read() function returns the data as string. cursor.execute("INSERT INTO Images SET Data='%s'" % \ mdb.escape_string(img)) This string data is inserted into the table. Before doing so, it is processed by the escape_string() method. It escapes a string for use as a query parameter. This is common practice to avoid malicious sql injection attacks.

Reading images
In the previous example, we have inserted an image into the database table. Now we are going to read the image back from the table.

#!/usr/bin/python # -*- coding: utf-8 -*-

import MySQLdb as mdb

import sys

try: conn = mdb.connect(host='localhost',user='testuser', passwd='test623', db='testdb')

cursor = conn.cursor()

cursor.execute("SELECT Data FROM Images LIMIT 1")

fout = open('image.png','wb') fout.write(cursor.fetchone()[0]) fout.close()

cursor.close() conn.close()

except IOError, e:

print "Error %d: %s" % (e.args[0],e.args[1]) sys.exit(1)

We read one image from the Images table. cursor.execute("SELECT Data FROM Images LIMIT 1") We select one record from the table. fout = open('image.png','wb') We open a writable binary file. fout.write(cursor.fetchone()[0]) We fetch the data from the previous SQL statement and write it to the file. Now we should have an image called image.png in our current directory. We can check if it is the same image, that we have inserted into the table.

Transaction support
A transaction is an atomic unit of database operations against the data in one or more databases. The effects of all the SQL statements in a transaction can be either all committed to the database or all rolled back. For databases that support transactions, the Python interface silently starts a transaction when the cursor is created. The commit() method commits the updates made using that cursor, and the rollback() method discards them. Each method starts a new transaction. The MySQL database has different types of storage engines. The most common are the MyISAM and the InnoDB engines. The MyISAM is the default one. There is a trade-off between data security and database speed. The MyISAM tables are faster to process and they do not support transactions. The commit() and rollback() methods are not implemented. They do nothing. On the other hand, the InnoDB tables are more safe against the data loss. They support transactions. They are slower to process.

#!/usr/bin/python # -*- coding: utf-8 -*-

import MySQLdb as mdb import sys

try: conn = mdb.connect('localhost', 'testuser', 'test623', 'testdb');

cursor = conn.cursor()

cursor.execute("UPDATE Writers SET Name = %s WHERE Id = %s", ("Leo Tolstoy", "1")) cursor.execute("UPDATE Writers SET Name = %s WHERE Id = %s", ("Boris Pasternak", "2")) cursor.execute("UPDATE Writer SET Name = %s WHERE Id = %s", ("Leonid Leonov", "3"))

conn.commit()

cursor.close() conn.close()

except mdb.Error, e:

conn.rollback()

print "Error %d: %s" % (e.args[0],e.args[1]) sys.exit(1)

In this script, we try to update three rows. The storage engine of the table is MyISAM. cursor.execute("UPDATE Writers SET Name = %s WHERE Id = %s", ("Leo Tolstoy", "1")) cursor.execute("UPDATE Writers SET Name = %s WHERE Id = %s", ("Boris Pasternak", "2")) Here we want to change names of authors for rows 1 and 2. cursor.execute("UPDATE Writer SET Name = %s WHERE Id = %s", ("Leonid Leonov", "3")) There is an error in the SQL statement. There is no Writer table. conn.rollback() We can call the rollback() method, but it does nothing.

$ ./isam.py Error 1146: Table 'testdb.Writer' doesn't exist

mysql> SELECT * FROM Writers; +----+-------------------+ | Id | Name |

+----+-------------------+ | 1 | Leo Tolstoy |

| | | |

2 | Boris Pasternak

3 | Lion Feuchtwanger | 4 | Guy de Maupasant 5 | Truman Capote | |

+----+-------------------+ 5 rows in set (0.00 sec)

Running the script gives an error. But as we see, the first two rows already were changed. In the last example of this tutorial, we are going to recreate the Writers table. This time, the table will be of InnoDB type. InnoDB MySQL database tables support transactions.

DROP TABLE Writers;

CREATE TABLE IF NOT EXISTS Writers(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(25)) ENGINE=INNODB;

INSERT INTO Writers(Name) VALUES('Jack London'); INSERT INTO Writers(Name) VALUES('Honore de Balzac'); INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger'); INSERT INTO Writers(Name) VALUES('Emile Zola'); INSERT INTO Writers(Name) VALUES('Truman Capote');

This is writers.sql file. It is used to recreate the Writers table.

mysql> source writers.sql

Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.10 sec)

Query OK, 1 row affected (0.02 sec)

Query OK, 1 row affected (0.03 sec)

Query OK, 1 row affected (0.02 sec)

Query OK, 1 row affected (0.02 sec)

Query OK, 1 row affected (0.02 sec)

We can use the source commnad to load and execute the sql script.

#!/usr/bin/python # -*- coding: utf-8 -*-

import MySQLdb as mdb import sys

try:

conn = mdb.connect('localhost', 'testuser', 'test623', 'testdb');

cursor = conn.cursor()

cursor.execute("DELETE FROM Writers WHERE Id = 5") cursor.execute("DELETE FROM Writers WHERE Id = 4") cursor.execute("DELETE FROM Writer WHERE Id = 3")

conn.commmit()

except mdb.Error, e:

conn.rollback() print "Error %d: %s" % (e.args[0],e.args[1]) sys.exit(1)

cursor.close() conn.close()

Now, we are going to execute the above script. We want to delete three rows from the table. The third SQL statement has an error.

$ ./innodb.py Error 1146: Table 'testdb.Writer' doesn't exist

mysql> SELECT * FROM Writers; +----+-------------------+ | Id | Name |

+----+-------------------+ | | | | | 1 | Jack London 2 | Honore de Balzac | |

3 | Lion Feuchtwanger | 4 | Emile Zola 5 | Truman Capote | |

+----+-------------------+ 5 rows in set (0.00 sec)

The error occured before we have committed the changes to the database. The rollback() method was called and no deletions took place.