Dynamic Connectivity (MySQL & mSQL)

11.2. 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.

11.2.1. 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-1 shows how you can use the statement handler to generate runtime information about a query or update.

Example 11-1. 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");
>>> print result.numrows();
3
>>> print result.fields();
[['test_id', 'test', 'long', 11, 'notnull'], ['test_val', 'test', 'string', 100, '']]
>>> print result.fetchrows(-1);
[[1, 'This is a test.'], [2, 'This is a test.'], [4, 'Bing!']]
>>>

With the statement handler, you now have access to the number of rows affected by an update, insert, or delete in addition to a set of data about result sets from queries. In Example 11-1, we accessed the number of rows retrieved by a query and detailed information about the columns represented in the result set.

Of the new methods introduced in Example 11-1, only fetchrows() is not self-evident. This method fetches the next series of rows matching the number passed to it. In other words, if you call result.fetchrows(2), a list of the next two rows will be returned. This method will return a list of all rows -- as in the example above -- if you pass it a number less than 0. Combining this method with a call to seek() enables you to move around a result set. The seek() method accepts an integer parameter specifying which row you wish to work on where represents the first row.

11.2.2. Database Meta-data

Though only the MySQL API supports dynamic result set management (at least at the time of publishing of this book), both APIs support database meta-data through a nearly identical set of methods. Database meta-data is basically information about a database connection. Example 11-1 shows a Python session that interrogates MySQL and mSQL connections about themselves.

Example 11-1. Data

[7:56pm] 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 mSQL, MySQL;
>>> msql = mSQL.connect();
>>> mysql = MySQL.connect();
>>> print msql.listdbs();
['db_test', 'db_web']
>>> print mysql.listdbs();
[['db_test'], ['mysql'], ['test']]
>>> msql.selectdb('db_test');
>>> mysql.selectdb('db_test');
>>> print msql.listtables();
['test', 'hosts']
>>> print mysql.listtables();
[['test']]
>>> print msql.serverinfo;
2.0.1
>>> print mysql.serverinfo();
3.21.17a-beta-log
>>> print mysql.clientinfo();
MySQL-Python-1.1
>>> print msql.hostname;
None
>>> print mysql.hostinfo();
Localhost via UNIX socket
>>> print mysql.stat();
Uptime: 4868410  Running threads: 1  Questions: 174  Reloads: 4  Open tables: 4
>>> print mysql.listprocesses();
None
>>>

In this example, we have a litany of method calls that provide extended information about database connections. In a couple of instances, mSQL provides this information via immutable attributes rather than methods. The MySQL API, on the other hand, provides a lot more information than does the mSQL one. See the reference section, Part III, "Reference", for a full description of each of these methods and attributes.