Writing MySQL-Based Programs - MySQL - page 76

print ( "Number of rows updated: %d " % cursor . rowcount )
cursor . close ()
conn . commit ()
The Python DB API specification indicates that database connec‐
tions should begin with auto-commit mode disabled , so Connector/
Python disables auto-commit when it connects to the MySQL serv‐
er. If you use transactional tables, modifications to them are rolled
back when you close the connection unless you commit the changes
first, which is why the preceding example invokes the commit()
method. Changes to nontransactional tables such as MyISAM tables
are committed automatically, so this issue does not arise. For more
information on auto-commit mode, see Chapter 17 , particularly
Recipe 17.7 ).
If the statement returns a result set, fetch its rows, then close the cursor. The fetch
one() method returns the next row as a sequence, or None when there are no more rows:
cursor = conn . cursor ()
cursor . execute ( "SELECT id, name, cats FROM profile" )
while True :
row = cursor . fetchone ()
if row is None :
break
print ( "id: %s , name: %s , cats: %s " % ( row [ 0 ], row [ 1 ], row [ 2 ]))
print ( "Number of rows returned: %d " % cursor . rowcount )
cursor . close ()
As you can see from the preceding example, the rowcount attribute is useful for SE
LECT statements, too; it indicates the number of rows in the result set.
len(row) tells you the number of columns in the result set.
Alternatively, use the cursor itself as an iterator that returns each row in turn:
cursor = conn . cursor ()
cursor . execute ( "SELECT id, name, cats FROM profile" )
for ( id , name , cats ) in cursor :
print ( "id: %s , name: %s , cats: %s " % ( id , name , cats ))
print ( "Number of rows returned: %d " % cursor . rowcount )
cursor . close ()
The fetchall() method returns the entire result set as a sequence of row sequences.
Iterate through the sequence to access the rows:
cursor = conn . cursor ()
cursor . execute ( "SELECT id, name, cats FROM profile" )
rows = cursor . fetchall ()
for row in rows :
print ( "id: %s , name: %s , cats: %s " % ( row [ 0 ], row [ 1 ], row [ 2 ]))