PC Tips & Tricks

In this post I will show you how to connect to a MySQL database, retrieve data from it and insert data in it using Python.

What you need:
1. Python v. 2.3.4 or newer
2. MySQL v. 3.23 or newer
3. MySQLdb – the Python DB API-2.0 interface

First verify that your version of Python is 2.3.4 or later, and that the MySQLdb module is installed. You can check both of these requirements by running Python in interactive mode from the command line prompt (something like % for Unix or C:\> for Windows):
% python
Python 2.5.2 (r252:60911, Jun 6 2008, 23:32:27)
[GCC 4.3.1 20080507 (prerelease) [gcc-4_3-branch revision 135036]] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import MySQLdb
Traceback (most recent call last):
File "", line 1, in ?
ImportError: No module named MySQLdb

Scripts that access MySQL through DB-API using MySQLdb generally perform the following steps:
* Import the MySQLdb module
* Open a connection to the MySQL server
* Issue statements and retrieve their results
* Close the server connection

Writing the scripts
For the script examples I created a database test and a table fruits;

mysql> select * from fruits;
+------+--------+
| id | name
+------+--------+
| 1 | apple
| 2 | pear
| 3 | orange
+------+--------+
3 rows in set (0.00 sec)


Use a text editor to create a file named fruits.py that contains the following script. This script uses MySQLdb to interact with the MySQL server, selects all the records in the table and prints them.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

import MySQLdb
conn = MySQLdb.connect (host = "localhost",
                        user = "root",
                        passwd = "",
                        db = "test")
cursor = conn.cursor ()
cursor.execute ("SELECT id,name from fruits")
while (1):
  row = cursor.fetchone ()
  if row == None:
    break
  print "%s, %s" % (row[0], row[1])
print "Number of rows returned: %d" % cursor.rowcount
cursor.close ()
conn.close ()

The result is:

pctips@linux:~> python fruits.py
1, apple
2, pear
3, orange
Number of rows returned: 3

In this example I used fetchone() for retrieving one row at a time. fetchone() returns the next row of the result set as a tuple, or the value None if no more rows are available. The loop checks for this and exits when the result set has been exhausted. For each row returned, the tuple contains two values (that’s how many columns the SELECT statement asked for), which fruits.py prints. The print statement shown above accesses the individual tuple elements. However, because they are used in order of occurrence within the tuple, the print statement could just as well have been written like this:

print “%s, %s” % row

After displaying the statement result, the script also prints the number of rows returned (available as the value of the rowcount attribute).

Another way to retrieve the result is to use fetchall(). It returns the entire result set all at once as a tuple of tuples, or as an empty tuple if the result set is empty. To access the individual row tuples, iterate through the row set that fetchall() returns:

7
8
9
10
11

cursor.execute ("SELECT id, name FROM fruits")
   rows = cursor.fetchall ()
   for row in rows:
     print "%s, %s" % (row[0], row[1])
print "Number of rows returned: %d" % cursor.rowcount

This code prints the row count by accessing rowcount, just as for the fetchone() loop. Another way to determine the row count when you use fetchall() is by taking the length of the value that it returns:

print “%d rows were returned” % len (rows)

If you want to insert data into the table use:

1
2
3
4
5
6
7

cursor.execute ("""
       INSERT INTO fruits (id, name)
       VALUES
         ('4', 'banana'),
         ('5', 'cherry'),
      """)
   print "Number of rows inserted: %d" % cursor.rowcount

MySQLdb supports a placeholder capability that enables you to bind data values to special markers within the statement string. This provides an alternative to embedding the values directly into the statement. The placeholder mechanism handles adding quotes around data values, and it escapes any special characters that occur within values. The following examples demonstrate an UPDATE statement that changes cherry to strawberry, first using literal values and then using placeholders. The literal-value statement looks like this:

1
2
3
4
5

   cursor.execute ("""
         UPDATE fruits SET name = 'strawberry'
         WHERE name = 'cherry'
       """)
   print "Number of rows updated: %d" % cursor.rowcount

Alternatively, you can issue the same statement by using %s placeholder markers and binding the appropriate values to them:

1
2
3
4
5

   cursor.execute ("""
         UPDATE fruits SET name = %s
         WHERE name = %s
       """, ("strawberry", "cherry"))
   print "Number of rows updated: %d" % cursor.rowcount

After issuing the statements, fruits.py closes the cursor, commits the changes, and disconnects from the server:

cursor.close ()
conn.commit ()
conn.close ()

The commit() method commits any outstanding changes in the current transaction to make them permanent in the database. In DB-API, connections begin with autocommit mode disabled, so you must call commit() before disconnecting or changes may be lost. This is the case if you use a transactional storage engine such as InnoDB. If you use a MyISAM table, commit has no effect because changes to MyISAM tables take effect immediately regardless of the autocommit mode.
Credits to http://www.kitebird.com/

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.