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.
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: ...
Get Managing & Using MySQL, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.