Brought to you by Andy Dustman and Comstar.Net, Inc..
Please read the licensing agreement with it's lack of warranty statement.
Download the friggin' thing.
MySQLdb is an interface to the popular MySQL database server for Python. The design goals are:
This module should be mostly compatible with an older interface written by Joe Skinner and others. However, the older version is a) not thread-friendly, b) written for MySQL 3.21, c) apparently not actively maintained. No code from that version is used in MySQLdb. MySQLdb is distributed free of charge under a license derived from the Python license.
Notes: MySQL 3.22.11 is known NOT to work. Only versions 3.22.19 and up are known to work. If you have an older version you should seriously consider upgrading for it's own sake. Some older versions may work due to some recent patches. It ought to work with 3.23 (currently under development) but has not been tested.
This module requires Python 1.5.2. Earlier versions will not work, because support for C long longs is required by MySQL. Thanks to Nikolas Kauer for pointing this out.
This version has been tested against MySQL-3.22.25, which seems to have a strange bug when handling TIME columns. For this reason, there is presently no type converter for TIME columns (the value is returned as a string).
The type converter dictionary is no longer stored within the _mysql module. See below for more details.
If you work out an installation routine for Windows, please contact the author.
This module works better if you have the DateTime module, but will function without it.
The web page documentation may be slightly ahead of the latest release and may reflect features of the next release.
If you want to write applications which are portable across databases, avoid using this module directly. _mysql provides an interface which mostly implements the MySQL C API. For more information, see the MySQL documentation, section 18. The documentation for this module is intentionally weak because you probably should use the higher-level MySQLdb module. If you really need it, use the standard MySQL docs and transliterate as necessary.
The C API has been wrapped in an object-oriented way. The only MySQL data structures which are implemented are the MYSQL (database connection handle) and MYSQL_RES (result handle) types. In general, any function which takes MYSQL *mysql as an argument is now a method of the connection object, and any function which takes MYSQL_RES *result as an argument is a method of the result object. Functions requiring none of the MySQL data structures are implemented as functions in the module. Functions requiring one of the other MySQL data structures are generally not implemented. Deprecated functions are not implemented. In all cases, the mysql_ prefix is dropped from the name.
| C API | _mysql |
|---|---|
|
mysql_affected_rows() |
conn.affected_rows() |
|
mysql_close() |
conn.close() |
|
mysql_connect() |
_mysql.connect() |
|
mysql_data_seek() |
result.data_seek() |
|
mysql_debug() |
_mysql.debug() |
|
mysql_dump_debug_info |
conn.dump_debug_info() |
|
mysql_escape_string() |
_mysql.escape_string() |
|
mysql_fetch_row() |
result.fetch_row()
result.fetch_rows() result.fetch_all_rows() |
|
mysql_get_client_info() |
_mysql.get_client_info() |
|
mysql_get_host_info() |
conn.get_host_info() |
|
mysql_get_proto_info() |
conn.get_proto_info() |
|
mysql_get_server_info() |
conn.get_server_info() |
|
mysql_info() |
conn.info() |
|
mysql_insert_id() |
conn.insert_id() |
|
mysql_list_dbs() |
conn.list_dbs() |
|
mysql_list_fields() |
conn.list_fields() |
|
mysql_list_processes() |
conn.list_processes() |
|
mysql_list_tables() |
conn.list_tables() |
|
mysql_num_fields() |
result.num_fields() |
|
mysql_num_rows() |
result.num_rows() |
|
mysql_ping() |
conn.ping() |
|
mysql_query() |
conn.query() |
|
mysql_real_connect() |
_mysql.connect() |
|
mysql_real_query() |
conn.query() |
|
mysql_row_seek() |
result.row_seek() |
|
mysql_row_tell() |
result.row_tell() |
|
mysql_select_db() |
conn.select_db() |
|
mysql_stat() |
conn.stat() |
|
mysql_store_result() |
conn.store_result() |
|
mysql_thread_id() |
conn.thread_id() |
|
mysql_use_result() |
conn.use_result() |
|
CLIENT_* |
_mysql.CLIENT.* |
|
CR_* |
_mysql.CR.* |
|
ER_* |
_mysql.ER.* |
|
FIELD_TYPE_* |
_mysql.FIELD_TYPE.* |
|
FLAG_* |
_mysql.FLAG.* |
MySQLdb is a thin Python wrapper around _mysql which makes it compatible with the Python DB API interface (version 2). In reality, a fair amount of the code which implements the API is in _mysql for the sake of efficiency.
The DB API specification should be your primary guide for using this module. Only deviations from the spec and other database-dependent things will be documented here. Note that all symbols from _mysql are imported into this module. Mostly these are the required exceptions the constant classes, and a very few functions.
Constructor for creating a connection to the database. Returns a Connection Object. Parameters are the same as for the MySQL C API. Note that all parameters must be specified as keyword arguments! The default value for each parameter is NULL or zero, as appropriate. Consult the MySQL documentation for more details. The important parameters are:
String constant stating the supported DB API level. '2.0'
Integer constant stating the level of thread safety the interface supports. Set to 1, which means: Threads may share the module, but not connections. This is the practice recommended by the MySQL documentation. However, it should be safe to share a connection between two threads provided only one thread at a time uses it (i.e. a mutex is employed). Note that this is only safe if the threads are using. mysql_store_result() as opposed to mysql_use_result(). The latter is not recommended for threaded applications. See the MySQL documentation for more details.
String constant stating the type of parameter marker formatting expected by the interface. Set to 'format' = ANSI C printf format codes, e.g. '...WHERE name=%s'. If a mapping object is used for conn.execute(), then the interface actually uses 'pyformat' = Python extended format codes, e.g. '...WHERE name=%(name)s'. However, the API does not presently allow the specification of more than one style in paramstyle.
Compatibility note: The older MySQLmodule uses a similar parameter scheme, but requires that quotes be placed around format strings which will contain strings, dates, and similar character data. This is not necessary for MySQLdb. It is recommended that %s (and not '%s') be used for all parameters, regardless of type. The interface performs all necessary quoting.
A dictionary mapping MySQL types (from FIELD_TYPE.*) to callable Python objects (usually functions) which convert from a string to the desired type. This is initialized with reasonable defaults for most types. When creating a Connection object, you can pass your own type converter dictionary as a keyword parameter. Otherwise, it uses a copy of type_conv which is safe to modify on a per-connection basis. The dictionary includes some of the factory functions from the DateTime module, if it is available. Several non-standard types (SET, ENUM) are returned as strings, which is how MySQL returns all columns. Note: TIME columns are returned as strings presently. This should be a temporary condition.
MySQL does not support transactions, so this method successfully does nothing.
MySQL does not support transactions, so this method is not defined. Note that the older MySQLmodule does define this method, which sucessfully does nothing. This is dangerous behavior, as a succesful rollback indicates that the current transaction was backed out, which is not true, and fails to notify the programmer that the database now needs to be cleaned up by other means.
MySQL does not support cursors; however, cursors are fairly easily emulated. Any positional or keyword arguments are passed to the cursor constructor.
The _mysql connection object. This may be used in case it is necessary to employ some MySQL-specific functions.
The class used to create a new cursor with conn.cursor(). If you subclass the Connection object, you will probably want to change this.
Cursor objects support some parameters when created, usually passed by conn.cursor(). They are also attributes of the cursor, but it is probably best to not mess with them.
While it is possible to create Cursor objects with the class constructor, this is not recommended, so they are hidden as _Cursor objects.
Not implemented.
Not implemented.
Does nothing, successfully.
Does nothing, successfully.
These methods work as described in the API. However, it should be noted that the interface relies on the __str__ method of each parameter. That is, each parameter is converted to a string when passed to MySQL. This presents a problem for the various date and time columns: __str__ for DateTime objects includes fractional seconds, which MySQL (up to 3.22.20a, at least), considers illegal input, and so zeros the field.
These functions all take a DateTime object as input and return an appropriately formatted string. They are intended for use with the executeXXX() methods.
In general, it is probably wise to not directly interact with the DB API except for small applicatons. Databases, even SQL databases, vary widely in capabilities and may have non-standard features. The DB API does a good job of providing a reasonably portable interface but some methods are non-portable. Specifically, the parameters accepted by connect() are completely implementation-dependent.
If you believe your application may need to run on several different databases, the author recommends the following approach, based on personal experience: Write a simplified API for your application which implements the specific queries and operations your application needs to perform. Implement this API as a base class which should be have few database dependencies, and then derive a subclass from this which implements the necessary dependencies. In this way, porting your application to a new database should be a relatively simple matter of creating a new subclass, assuming the new database is reasonably standard.
Copyright 1999 by Comstar.net, Inc., Atlanta, GA, US.
All Rights Reserved
Permission to use, copy, modify, and distribute this software and its
documentation for any purpose and without fee is hereby granted,
provided that the above copyright notice appear in all copies and that
both that copyright notice and this permission notice appear in
supporting documentation, and that the name of Comstar.net, Inc.
or COMSTAR not be used in advertising or publicity pertaining to
distribution of the software without specific, written prior permission.
COMSTAR DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE,
INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS, IN NO
EVENT SHALL COMSTAR BE LIABLE FOR ANY SPECIAL, INDIRECT OR
CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF
USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR
OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR
PERFORMANCE OF THIS SOFTWARE.
$Id: MySQL_doc.py,v 1.5 1999/07/20 04:04:20 adustman Exp $