The following is the documentation for MySQLmodule-1.4.

This module has been developed under Linux (RH50),
MySQL 3.21.30, and Python 1.5.1.

For copyright notices see MySQLmodule.c.

MySQLmodule-1.x is based on mySQLmodule-0.1.4 by

   Copyright (C) 1997  Joseph Skinner <joe@earthlink.co.nz>
   Copyright (C) 1997  James Henstridge <james@daa.com.au>

   mySQLmodule-0.1.4 is based on mSQLmodule, which is

   Portions copyright (C) 1995  Thawte Consulting, cc
   Portions copyright (C) 1994  Anthony Baxter.

See 'Credits' for details.

Joerg Senekowitsch (senekow@ibm.net), October 1998

-----------------------------------------------------------------------------
**CONTENTS**

 0. Why another Python/MySQL interface
 1. Compiling and installing the MySQL module
 2. Exported types, functions and classes
 3. Using the MySQL module
 4. Notes on server side storage
 5. Acknowledgements


**WHY ANOTHER PYTHON/MYSQL INTERFACE**

I recently came across a problem storing strings containing ASCII zero (\0) 
in the MySQL database. After first blaming Python (which threw the original 
error), I checked the mySQLmodule code and ran across a bunch of problems.
Some routines would not free allocated storage, some MySQL API functions 
were not available, and I disliked the fact that mySQLmodule would return 
different data structures depending on the method (and tuples at that!). 
So I ripped the code apart, left the framework, but made lots of changes. 
Because the changes include a change in (Python) methods and return 
values, I upped the major version number and changed the name of the 
module to MySQL, so that it would not clash with the original mySQL.

Rational for the change to return "list of lists" instead of
"tuple" or "list of tuple". I don't want my own DB access routines 
to worry about the return type and I want to be able to modify the
data in the returned table. 

Data = DBH['select * from MyTable']
if Data:
    rows = len(Data)
    cols = len(Data[0])
    for i in range(rows):
        for j in range(cols):
            if not Data[i][j]: Data[i][j] = DefaultElement()
    Do_Something(Data)

No need to complicate that by having tuples inside the outer list.
For those who prefer dictionaries, the STH method fetchdict()
will return a list of dictionaries. The dictionary keys are
qualified with the corresponding table name(s).


**COMPILING AND INSTALLING**

  i. See README.NT if you are installing on a WIN32 system.

 ii. Copy MySQLmodule.c into your Modules subdirectory of the Python
     source distribution.

iii. Add the following line to your Setup file in that directory:

     MySQL MySQLmodule.c -L/usr/local/lib/mysql/ -lmysqlclient \
	-I/usr/local/include/mysql

     Note that the location of the MySQL library and the include
     directory may be different on your particular system. You may
     build the module shared (insert below the *shared* indicator 
     in the Setup file).

 iv. If you have built Python before, simply run make in your 
     main Python directory. If not, follow the instructions on
     how to compile/install Python.

To build a dynamically loadable module without access to the python
source tree, use (Trond Eivind Glomsrd):

     gcc -shared -I/usr/include/python1.5 -I/usr/local/include/mysql \
     MySQLmodule.c -lmysqlclient -L/usr/lib/python1.5/config -lpython1.5 \
     -o MySQLmodule.so

and move the resulting MySQLmodule.so file into the PYTHONPATH. Again,
substitute the proper locations of your include and library files.

Note that the module is case sensitive, and that the name has been changed
to MySQL *deliberately* so as not to break any existing code that uses
the old mySQL module.


**EXPORTED TYPES AND OBJECTS**

The module (MySQL) exports the following:

  DBH_Type:    the type of the database object
  STH_Type:    the type of the cursor object
  error:       an exception raised in certain circumstances (rather than TypeError)
  __doc__:     the version accessible from Python
  connect([host[,user[,pass]]]) 
      a function returning a database object.  The optional arguments
      are the name of the host to connect to, the username for authenticating
      to MySQL, and the associated password. If no host is given, the function
      will assume 'localhost' (and use a fast Unix socket for the connection)
  escape(string)
      will (hopefully) return a string properly escaped to allow insertion
      into the DB. This routine calls mysql_escape_string(), which is broken
      in 3.21.29-gamma. Versions >=3.21.30 seem to work correctly.

MySQL.connect() returns a database handle (DBH) with the following methods:

(Note: in the following, 'Table' means 'list of lists' (except fetchdict))

  Table = DBH.listdbs([wild])
      returns a table giving the names of the databases on the
      MySQL host to which one has connected with MySQL.connect(). The optional
      argument is a MySQL wildcard string (same syntax as LIKE).

  DBH.selectdb(DB_Name[,storage])
      attaches this object to a particular database.  Queries executed
      will be directed to that database until another selectdb method
      call is made. The optional integer 'storage' can be used to keep
      query result sets on the server. Note that this negatively impacts
      the server performance, but allows clients with smaller memory 
      footprints, since records are only transferred upon request. 
      The default is 0, i.e. all records are transferred to the client.

  Table = DBH.listtables([wild])
      return a table with table names in the selected
      database.  Only valid after a selectdb call has been made.
      The optional argument can be used to restrict the returned set of
      tables (same syntax as LIKE).

  Table = DBH.listfields(table[,wild])
      return a table of the description(s) of the fields in the given table.
      The optional argument can be used to restrict the returned set
      of fields (same syntax as LIKE).

  Table = DBH.listprocesses()
      returns information about the running MySQL processes. Requires
      privileges (otherwise returns None).

  String = DBH.stat()
      returns status information from MySQL.

  DBH.create(DB_Definition)
      creates a new database.

  DBH.drop(DB_Name)
      could ruin your day.

  DBH.reload()
      reload MySQL privilege tables.

  DBH.shutdown()
      takes down the MySQL daemon.

  DBH.close()
      closes a DB connection.

  String = DBH.clientinfo()
      returns MySQLmodule version information.

  String = DBH.serverinfo()
      returns MySQL server information.

  String = DBH.hostinfo()
      returns information about the connecting host and connection type.

  Integer = DBH.protoinfo()
      returns the MySQL protocol version number (10).

  Table = DBH.do(query) or Table = DBH[query]
      return the result of the SQL query. Returns the result of the
      query or the number of affected rows (mySQL may lie about that).
      Both methods use the storage type established with DBH.selectdb().
      See code comments if you're running a WIN32 version.

  Integer = DBH.insert_id()
      access to the last generated auto_increment number. This number 
      can change if queries have been submitted between calls.
      See code comments if you're running a WIN32 version.

  STH = DBH.query(query[,storage])
      returns a statement handle for cursor methods (see below).
      The optional 'storage' parameter can be used to override
      the DBH default established with DBH.selectdb().

Methods for statement handles (STH):

  Table = STH.fetchrows([n])
      return the results of the DB query. If n < 0, all rows will be fetched.
      Otherwise, only the next n rows will be returned. The default is to
      return all rows.

  Table = STH.fetchdict([n])
      same as STH.fetchrows(), except that a list of dictionaries is returned
      with 'tablename.fieldname:data' pairs. 

  Table = STH.fields()
      return field descriptions of the result of the STH query. Currently
      MySQLmodule knows about "pri", "notnull", "auto_inc", "ukey", and "mkey".

  STH.seek(n)
      move cursor to row n (0 is the first row).
      Only available if client side result storage (=0, see DBH.selectdb)
      has been selected. Otherwise, will throw an exception.

  Integer = STH.numrows()
      returns how many rows are in the result of the STH query.
      Warning: in reality this number reflects how many records the
      *client* has received. For server side storage methods this
      number starts out at 0 and increases as the client fetches the
      rows. For client side storage, this number immediately gives
      the total number of rows for this query.

  Integer = STH.numfields()
      returns how many columns are in the result of the STH query.

  Integer = STH.affectedrows()
      returns how many rows have been affected by the last query.
      Note that MySQL lies about this number in certain cases.
      See code comments if you're running a WIN32 version.

  Integer = STH.insert_id()
      return the auto_increment value from an insert STH query.
      Note that this number is persistent as long as the STH exists.
      See code comments if you're running a WIN32 version.

  Integer = STH.eof()
      returns 1 if the last row has been read, otherwise 0.
      Always 1 if client side storage has been selected (default),
      and only marginally useful for server side storage, since
      the flag will change to true only _after_ an attempt has
      been made to read past the last record.


**USING THE MySQL MODULE**

import MySQL
DBH = MySQL.connect() # localhost
print DBH.listdbs()
DBH.selectdb('test')
print DBH.serverinfo()
print DBH.stat()
DBH["create table pytest (x int, y int, s char(20))"]
DBH["insert into pytest values (1,2,'abc')"]
DBH.do("insert into pytest values (3,4,'def')")
STH = DBH.query("insert into pytest values (5,6,'ghi')")
print STH.affectedrows()
print DBH['select * from pytest']
STH = DBH.query("select * from pytest")
print STH.numrows()
print STH.fields()
print STH.fetchrows(-1)
STH.seek(0)
print STH.fetchrows(1)
print STH.fetchrows(1)
STH.seek(0)
print STH.fetchrows(2)
print STH.fetchrows(2)
print STH.numfields()
STH.seek(0)
print STH.fetchdict(1)
print STH.fetchdict()
STH = DBH.query("select * from pytest",1)
print STH.fetchdict(1)
print STH.fetchdict() # compare to previous dicts
STH = DBH.query("select * from pytest",1)
print STH.fetchrows(1)
print STH.eof()
print STH.fetchrows()
print STH.eof()
DBH['drop table pytest']


**NOTES ON SERVER SIDE STORAGE**

MySQL offers two slightly different ways of accessing database data.

The default method in MySQLmodule is to use client side storage, i.e.
all queries, including the cursor (STH) methods fetch all data from the
server. Rows are accessed through STH.fetchrows(n) or STH.fetchdict(n)
individually (n=1), chunked (n>1), all at once (n<0), or, for the
wiseguys, none at all (n=0). STH.numrows() can tell *up front*, i.e. 
right after the query has been made, how many rows are in the result. 
STH.seek(k) can be used to access rows randomly. The drawback of client 
side storage is that it uses (client) memory to hold all the rows. 
Client side storage allows for constructs such as:

	STH = DBH.query("select * from Foo")
	N = STH.numrows()
	if N > 1000: raise Hell,"You must be joking!"
	for i in xrange(N):
		[Data] = STH.fetchdict(1)

Since the client has effectively transferred all rows, as far as the 
server is concerned all transactions on this channel have ceased
and the server is ready to accept new commands. It also means that
STH.eof() is always true (1) for client side storage.

Server side storage does not require that much client memory since
all records are transferred on a request basis. However, server side
storage has several drawbacks. Since now the possibility arises that
a client did not retrieve all rows, each new command must check whether
the server is ready to accept a new command. If not, the command must
clear the command channel by issuing enough reads to retrieve the
remaining rows. The (3.21) MySQL API does not offer some kind of "abort()" 
command. STH.numrows() no longer knows about how many rows were
selected by the query, so the above example code would fail.
STH.numrows() will, however, be updated as rows are read, e.g.:

	STH = DBH.query("select * from Foo")
	Data = STH.fetchrows(-1)
	print "Got",STH.numrows(),"rows." # len(Data) is the same

STH.eof() only makes sense with server side storage, but even here
it is not all that useful:

	STH = DBH.query("select 1")
	print STH.eof()          # will print 0
	Data = STH.fetchrows(1)  # retrieve the row
	print STH.eof()          # still 0 :-(
	Data = STH.fetchrows(1)  # must repeat. Data will be []
	print STH.eof()          # now we get 1, but we already 
                                 # knew that we've hit the end

One might consider this a bug. STH.seek(k) is no longer available and
will throw an error ("cannot seek on server"), i.e. rows must now be
read sequentially.

Server side storage also puts more strain on the server. In particular,
the server needs to remain in contact with the client until all rows
have been read. According to the MySQL manual clients are advised to
speedily retrieve the rows and not do any lengthy processing or, worse,
allow the user to stop the retrieval (e.g. by pressing Ctrl-S in an 
interactive interface).

For those who cannot decide which method is more suitable for their
application, MySQLmodule allows to mix both methods freely. The default
behavior can be set with DBH.selectdb() and can be changed for individual
cursor (STH) based queries. Note that incomplete server side queries 
will be cancelled by newly issued commands:

	STH = DBH.query("select * from Foo",1) # use server side storage
        Tables = DBH.listtables()              # stomp on previous results
        Data = STH.fetchrows()                 # nothing here anymore
   vs.
	STH = DBH.query("select * from Foo",0) # use client side storage
        Tables = DBH.listtables()              # won't interfere
        Data = STH.fetchrows()                 # no problem...

Server side storage also makes for brain warping code in MySQLmodule.
Normally (with client side storage) STH cursors are independent of
the database handle. Immediately after the query all data is transferred 
and the user is free to do whatever she likes with the DBH or STH handles.
With server side storage this becomes tricky. The memory chunk need for
the database connection is provided by the DBH handle. With server side
cursors, a pointer to this handle is stored in the STH handle. MySQLmodule
makes sure that this memory chunk is not deallocated before all outstanding
(server side) cursors are closed. This means that mysql_close() is not
necessarily called if the database handle DBH is destroyed:

	DBH = MySQL.connect()         # get a DB handle
	STH = DBH.query("select 1",1) # server side cursor
	del DBH                       # mysql_close() *not* called
	STH.fetchrows()               # will succeed!
        del STH                       # now mysql_close() will be called
	                              # in DBH_dealloc()

If you need to close the DB handle immediately, use DBH-close(). All further
attempts to communicate on this handle (even outstanding server side cursors)
will then receive a "... server has gone away" exception. Since mySQL 
cannot accept commands out of sequence, all DBH methods must check
for incomplete STH cursors. To access those the DBH handle contains a 
pointer to the STH cursor...sigh.


**ACKNOWLEDGEMENTS**

Thanks to the guys who developed Python (Guido et al.).
Python is a wonderful language.

Thanks to Monty Widenius and TcX Datakonsult AB for MySQL, which beat out 
mSQL, Oracle, Solid, and Postgres for my application.

Thanks to the individuals who contributed to the development
of the Python/MySQL interface:

   Joseph Skinner
   James Henstridge
   Thawte Consulting
   Anthony Baxter

Please see the file 'Credits' for the original mySQLmodule
copyright statements.