Cursors represent SQL statements and their results. The connection object provides your application with a cursor via the cursor( ) method:

cursor = conn.cursor(  );

This cursor is the center of your Python database access. Through the execute( ) method, you send SQL to the database and process any results. The simplest form of database access is, of course, a simple insert:

conn = MySQLdb.connect(host='carthage', user='test', passwd='test', db='test'),
cursor = conn.cursor(  );
cursor.execute("INSERT INTO test (test_id, test_char) VALUES (1, 'test')");
print "Affected rows: ", cursor.rowcount;

In this example, the application inserts a new row into the database using the cursor generated by the MySQL connection. It then verifies the insert by printing out the number of rows affected. For inserts, this value should always be 1.

Query processing is a little more complex. Again, you use the execute( ) method to send SQL to the database. Instead of checking the affected rows, however, you grab the results from the cursor using one of many fetch methods. Example 10-1 shows a Python program processing a simple query.

import MySQLdb;

connection = None;
try:
    connection = MySQLdb.connect(host="carthage", user="user", 
                                 passwd="pass", db="test");
    cursor = connection.cursor(  );
    cursor.execute("SELECT test_id, test_val FROM test ORDER BY test_id");
    for row in cursor.fetchall(  ):
        print "Key: ", row[0];
        print "Value: ", row[1];
     connection.close(  );
 except:
    if connection:
        connection.close(  );

The cursor object actually provides several fetch methods: fetchone( ) , fetchmany( ) , and fetchall( ) . For each of these methods, a row is represented by a Python tuple. In Example 10-1, the fetchall( ) method fetches all the results from the query into a list of Python tuples. This method, like all the fetch methods, will throw an exception if the SQL was not a query.

Of course, fetching all the rows at once can be inefficient for large result sets. You can instead fetch each row one by one by using the fetchone( ) method. The fetchone( ) method returns a single row as a tuple where each element represents a column in the returned row. If you have already fetched all the rows of the result set, fetchone( ) will return None.

The final fetch method, fetchmany( ), is middle ground between fetchone( ) and fetchall( ). It enables an application to fetch a predefined number of rows at once. You can either pass in the number of rows you wish to see or rely on the value of cursor.arraysize to provide a default value.