Accessing MySQL, Step by Step continued - Python

3. Execution of the SQL statement

The steps enumerated until now have done the job of connecting the application with the database and providing an object that simulates the functionality of cursors. The stage has been set for execution of SQL statements. Any SQL statement supported by MySQL can be executed using the execute() method of the Cursor class. The SQL statement is passed as a string to it. Once the statement is executed successfully, the Cursor object will contain the result set of the retrieved values. For example, to retrieve all the rows of a table named USER_MASTER the statement would be:

cursor.execute(“select * from USER_MASTER”)

Once the above statement is executed, the cursor object would contain all the retrieved. This brings us to the fourth step, fetching of the resultset. Before moving on to the next step, there is one point you must understand. The execute() function accepts and executes any valid SQL statement, including DDL statements such as delete table, alter table, and so on. In the case of DDL statements, there is no fifth step (i.e. iteration over the results fetched).

4. Fetching the resultset

The flexibility of Python comes to the fore in this step also. In the real world, fetching all the rows at once may not be feasible. MySQLdb answers this situation by providing different versions of the fetch() function of Cursor class. The two most commonly used versions are:

• fetchone(): This fetches one row in the form of a Python tuple. All the data types are mapped to the Python data types except one -- unsigned integer. To avoid any overflow problem, it is mapped to the long. 
  
• fetchall(): This fetches all the rows as tuple of tuples. While fetchone() increments the cursor position by one, fetchall() does nothing of that kind. Everything else is similar.

The subtleties will become clear from the following example. To fetch one row at a time and display the result, the block would be:

numrows = int(cursor.rowcount) #get the count of total
rows in the #resultset

# get and display one row at a time
for x in range(0,numrows):
        row = cursor.fetchone()
        print row[0], "-->", row[1]

The above result can be achieved by using fetchall() as shown below:

result = cursor.fetchall()

# iterate through resultset
for record in result:
        print record[0] , "-->", record[1]

The iteration is through the core Python APIs only. As the returned data structure is tuple, no extra API is required.

That covers all the steps required to access MySQL. What I have discussed up to now is "the approach of tackling a problem of the type database connectivity." However, in real life, reusability plays a more important role than it has in what you have seen so far. Hence in the next section I will be using the steps discussed until now to create a generic class for accessing MySQL.