O'Reilly logo

Managing & Using MySQL, 2nd Edition by Hugh E. Williams, Randy Yarger, George Reese, Tim King

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Cursors

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.

Example 10-1. 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: ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required