Mysql Python Cursor Descriptive Essay

MySQL Python tutorial

This is MySQL Python programming tutorial. It covers the basics of MySQL programming with Python. It uses the module. The examples were created and tested on Ubuntu Linux.

There is a similar PostgreSQL Python tutorial, MySQL Visual Basic tutorial, or MySQL PHP tutorial on ZetCode. SQLAlchemy tutorial covers SQLAlchemy SQL Toolkit and Object Relational Mapper. 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 part of the very popular LAMP platform which consists of Linux, Apache, MySQL, and PHP. Currently MySQL is owned by Oracle. MySQL database is available on most important OS platforms. It runs on BSD Unix, Linux, Windows, or Mac OS. 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 do not 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 do not know the package name for the MySQLdb module. We use the command to figure it out.

$ sudo apt-get install python-mysqldb

Here we install the Python interface to the MySQL database. Both and modules.

Next, we are going to create a new database user and a new database. We use the client.

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

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

We create a new 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 database.

_mysql module

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

#!/usr/bin/python # -*- coding: utf-8 -*- import _mysql import sys try: con = _mysql.connect('localhost', 'testuser', 'test623', 'testdb') con.query("SELECT VERSION()") result = con.use_result() print "MySQL version: %s" % \ result.fetch_row()[0] except _mysql.Error, e: print "Error %d: %s" % (e.args[0], e.args[1]) sys.exit(1) finally: if con: con.close()

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

MySQLdb module

is a thin Python wrapper around . 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: con = mdb.connect('localhost', 'testuser', 'test623', 'testdb'); cur = con.cursor() cur.execute("SELECT VERSION()") ver = cur.fetchone() print "Database version : %s " % ver except mdb.Error, e: print "Error %d: %s" % (e.args[0],e.args[1]) sys.exit(1) finally: if con: con.close()

In this script, we connect to the testdb database and execute the statement. This will return the current version of the MySQL database. We print it to the console.

import MySQLdb as mdb

We import the module.

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

We connect to the database. The 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.

cur = con.cursor() cur.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 method of the cursor and execute the SQL statement.

ver = cur.fetchone()

We fetch the data. Since we retrieve only one record, we call the method.

print "Database version : %s " % ver

We print the data that we have retrieved to the console.

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.

finally: if con: con.close()

In the final step, we release the resources.

$ ./version.py Database version : 5.5.9

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 con = mdb.connect('localhost', 'testuser', 'test623', 'testdb'); with con: cur = con.cursor() cur.execute("DROP TABLE IF EXISTS Writers") cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, \ Name VARCHAR(25))") cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')") cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')") cur.execute("INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')") cur.execute("INSERT INTO Writers(Name) VALUES('Emile Zola')") cur.execute("INSERT INTO Writers(Name) VALUES('Truman Capote')")

We create a table and add five authors to it.

with con:

With the with keyword, the Python interpreter automatically releases the resources. It also provides error handling.

cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, \ Name VARCHAR(25))")

This SQL statement creates a new database table called . It has two columns: and .

cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')") cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')") ...

We use the 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 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 con = mdb.connect('localhost', 'testuser', 'test623', 'testdb'); with con: cur = con.cursor() cur.execute("SELECT * FROM Writers") rows = cur.fetchall() for row in rows: print row

In this example, we retrieve all data from the table.

cur.execute("SELECT * FROM Writers")

This SQL statement selects all data from the Writers table.

rows = cur.fetchall()

The 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 con = mdb.connect('localhost', 'testuser', 'test623', 'testdb'); with con: cur = con.cursor() cur.execute("SELECT * FROM Writers") for i in range(cur.rowcount): row = cur.fetchone() print row[0], row[1]

We again print the data from the table to the console. This time, we fetch the rows one by one.

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

We fetch the rows one by one using the method. The rowcount property gives the number of rows returned by the SQL statement.

$ ./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 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 con = mdb.connect('localhost', 'testuser', 'test623', 'testdb') with con: cur = con.cursor(mdb.cursors.DictCursor) cur.execute("SELECT * FROM Writers LIMIT 4") rows = cur.fetchall() for row in rows: print row["Id"], row["Name"]

In this example, we get the first four rows of the table using the dictionary cursor.

cur = con.cursor(mdb.cursors.DictCursor)

We use the dictionary cursor.

cur.execute("SELECT * FROM Writers LIMIT 4")

We fetch four rows from the table.

for row in rows: print row["Id"], row["Name"]

We refer to the data by column names of the table.

$ ./dictcur.py 1 Jack London 2 Honore de Balzac 3 Lion Feuchtwanger 4 Emile Zola

Example output.

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 con = mdb.connect('localhost', 'testuser', 'test623', 'testdb') with con: cur = con.cursor() cur.execute("SELECT * FROM Writers LIMIT 5") rows = cur.fetchall() desc = cur.description print "%s %3s" % (desc[0][0], desc[1][0]) for row in rows: print "%2s %3s" % row

Again, we print the contents of the 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 = cur.description

A description attribute of the cursor 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. The module supports one of them, the ANSI format codes.

#!/usr/bin/python # -*- coding: utf-8 -*- import MySQLdb as mdb con = mdb.connect('localhost', 'testuser', 'test623', 'testdb') with con: cur = con.cursor() cur.execute("UPDATE Writers SET Name = %s WHERE Id = %s", ("Guy de Maupasant", "4")) print "Number of rows updated:", cur.rowcount

We change the name of an author on the fourth row.

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

We use the two placeholders identified by the markers. Before the SQL statement is executed, the values are bound to their placeholders.

$ ./prepared.py Number of rows updated: 1

We have updated one row.

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.

Inserting images

People often look for ways to insert images into databases. We will show how it can be done in SQLite and Python. Note that some people do not recommend to put images into databases. Images are binary data. MySQL database has a special data type to store binary data called (Binary Large Object). , , and are variants of the binary object type.

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

For this example, we create a new table called .

#!/usr/bin/python # -*- coding: utf-8 -*- import MySQLdb as mdb def read_image(): fin = open("woman.jpg") img = fin.read() return img con = mdb.connect('localhost', 'testuser', 'test623', 'testdb') with con: cur = con.cursor() data = read_image() cur.execute("INSERT INTO Images VALUES(1, %s)", (data, ))

In the above script, we read a JPG image from the disk and insert it into the table.

def read_image(): fin = open("woman.jpg") img = fin.read() return img

The method reads binary data from the JPG file, located in the current working directory.

cur.execute("INSERT INTO Images VALUES(1, %s)", (data, ))

We insert the image data into the table.

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 def writeImage(data): fout = open('woman2.jpg', 'wb') with fout: fout.write(data) con = mdb.connect('localhost', 'testuser', 'test623', 'testdb') with con: cur = con.cursor() cur.execute("SELECT Data FROM Images WHERE Id=1") data = cur.fetchone()[0] writeImage(data)

We read one image from the table.

cur.execute("SELECT Data FROM Images WHERE Id=1")

We select one record from the table.

fout = open('woman2.jpg', 'wb')

We open a writable binary file.

fout.write(data)

We write the data to the disk.

Now we should have an image called 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 method commits the updates made using that cursor, and the 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. Since MySQL 5.5, InnoDB becomes the default storage engine. 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 and 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: con = mdb.connect('localhost', 'testuser', 'test623', 'testdb') cur = con.cursor() cur.execute("DROP TABLE IF EXISTS Writers") cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, \ Name VARCHAR(25)) ENGINE=INNODB") cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')") cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')") cur.execute("INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')") cur.execute("INSERT INTO Writers(Name) VALUES('Emile Zola')") cur.execute("INSERT INTO Writers(Name) VALUES('Truman Capote')") cur.execute("INSERT INTO Writers(Name) VALUES('Terry Pratchett')") con.commit() except mdb.Error, e: if con: con.rollback() print "Error %d: %s" % (e.args[0],e.args[1]) sys.exit(1) finally: if con: con.close()

We recreate the table. We explicitly work with transactions.

cur = con.cursor()

In Python DB API, we do not call the BEGIN statement to start a transaction. A transaction is started when the cursor is created.

cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, \ Name VARCHAR(25)) ENGINE=INNODB")

We are dealing with a InnoDB table type. For older MySQL versions (<5.5), we need to specify the engine type with the option.

con.commit()

We must end a transaction with either a or a method. If we comment this line, the table is created but the data is not written to the table.

In this tutorial, we have been working with transactions without explicitly stating it. We used context managers. The context manager handles the entry and the exit from the desired runtime context for the execution of the block of code. Context managers are normally invoked using the statement.

Connection objects in module can be used as context managers. They automatically commit or rollback transactions. Connection context managers clean up code by factoring out , , and statements.

#!/usr/bin/python # -*- coding: utf-8 -*- import MySQLdb as mdb con = mdb.connect('localhost', 'testuser', 'test623', 'testdb') with con: cur = con.cursor() cur.execute("DROP TABLE IF EXISTS Writers") cur.execute("CREATE TABLE Writers(Id INT PRIMARY KEY AUTO_INCREMENT, \ Name VARCHAR(25))") cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')") cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')") cur.execute("INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')") cur.execute("INSERT INTO Writers(Name) VALUES('Emile Zola')") cur.execute("INSERT INTO Writers(Name) VALUES('Truman Capote')") cur.execute("INSERT INTO Writers(Name) VALUES('Terry Pratchett')")

In our code example, the context manager deals with all the work necessary for error handling. It automatically commits or rolls back a transaction.

This was MySQL Python tutorial. ZetCode has a complete e-book for SQLite Python:
SQLite Python e-book.

ZetCode last modified October 7, 2014 © 2007 - 2018 Jan BodnarFollow on Facebook

Recently I had the same issue with field value being a byte string instead of unicode. Here's a little analysis.

Overview

In general all one needs to do to have unicode values from a cursor, is to pass argument to connection constructor and have non-binary table fields (e.g. ). Passing is useless because it is set to true whenever has a value.

MySQLdb respects cursor description field types, so if you have a column in cursor the values will be converted to Python instances, to and so on, but binary values will be represented as is, by byte strings. Most of decoders are defined in , and one can override them on instance basis by providing argument to connection constructor.

But unicode decoders are an exception here, which is likely a design shortcoming. They are appended directly to connection instance converters in its constructor. So it's only possible to override them on instance-basic.

Workaround

Let's see the issue code.

It shows that field is returned as a byte string instead of unicode. However it is not binary, (MySQLdb field flags). It seems like bug in the library (opened #90). But the reason for it I see as (, MySQLdb field types) just hasn't a converter at all.

Thus by manipulating connection instance dict, it is possible to achieve desired unicode decoding behaviour.

If you want to override the behaviour here's how a dict entry for possible text field looks like after constructor.

. This means that if a field has binary flag it will be , otherwise unicode decoder will be applied. So you want to try to convert binary values as well, you can pop the first tuple.

answered Nov 28 '14 at 10:51

One thought on “Mysql Python Cursor Descriptive Essay

Leave a Reply

Your email address will not be published. Required fields are marked *