MySQL and mSQL

Dynamic Connectivity

The API we have discussed so far in the chapter is really all you need for the simple, but most common database access of every day select, insert, update, and delete calls. Some more complex applications, however, may require that you not know everything—or perhaps anything—about the database to which you are connecting and the statements you are sending to it. While both APIs support database-level meta-data—runtime information about the database to which you are connected—only the MySQL API provides full support for dynamically generated SQL calls, including result set meta-data.

MySQL Statement Handlers

As we noted earlier, MySQL has two query processing tools. The simple form returns a result set in the form of a list of lists. The more complex form returns a statement handler.

A statement handler represents the results of a MySQL query handled via the query() method (as opposed to using the do() method). Example 11.2 shows how you can use the statement handler to generate runtime information about a query or update.

Example 11-2. Dynamic Database Access Using a MySQL Statement Handler

[7:20pm] athens> python Python 1.5.1 (#1, Jun 13 1998, 22:38:15) [GCC 2.7.2] on sunos5 Copyright 1991-1995 Stichting Mathematisch Centrum, Amsterdam >>> import MySQL; >>> db = MySQL.connect(); >>> db.selectdb('db_test'); >>> result = db.query("INSERT INTO test(test_id,test_val) VALUES(4, 'Bing!')"); >>> print result.affectedrows(); 1 >>> result = db.query("SELECT * FROM test"); ...