6. MySQLdb -- DB API interface

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.

6.1 Functions and attributes

Only a few top-level functions and attributes are defined within MySQLdb.

connect(parameters...)

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:

host

name of host to connect to. Default: use local host

user

user to authenticate as. Default: current effective user.

passwd

password to authenticate with. Default: no password.

db

database to use. Default: no default database.

conv

literal-to-Python type conversion dictionary. Default: MySQLdb.type_conv

quote_conv

Python type-to-literal conversion dictionary. Default: MySQLdb.quote_conv

cursorclass

cursor class that cursor() uses, unless overridden. Default: MySQLdb.Cursor.

unix_pipe

location of UNIX socket. Default: use TCP.

port

TCP port of MySQL server. Default: standard port (3306).

apilevel

String constant stating the supported DB API level. '2.0'

threadsafety

Integer constant stating the level of thread safety the interface supports. As of MySQLdb version 0.2.0, this is set to 2, which means: Threads may share the module and connections. Cursors employ a mutex in the connection object to ensure that cursors do not use the connection at the same time. Generally, sharing a connection probably reduces performance; the MySQL server maintains a seperate thread for each connection. See the MySQL documentation for more details.

paramstyle

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.

type_conv

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.

quote_conv

A dictionary mapping Python types (from the standard types module or built-in function type() to MySQL literals. By default, the value is treated as a string. When creating a Connection object, you can pass your own quote converter dictionary as a keyword parameter.

6.2 Connection Objects

Connection objects are returned by the connect() function.

commit()

If the database supports transactions, this commits the current transaction; otherwise this method successfully does nothing.

MySQL supports transactions as of version 3.23.15-alpha.

rollback()

If the database supports transactions, this rolls back (cancels) the current transaction; otherwise a NotSupportedError is raised.

Compatibility note: The older MySQLmodule defines this method, which sucessfully does nothing. This is dangerous behavior, as a successful 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.

cursor([cursorclass])

MySQL does not support cursors; however, cursors are easily emulated. You can supply an alternative cursor class as an optional parameter. If this is not present, it defaults to the value given when creating the connection object, or the standard Cursor class. Also see the additional supplied cursor classes in the usage section.

6.3 Cursor Objects

callproc()

Not implemented.

close()

Closes the cursor. Future operations raise ProgrammingError. If you are using server-side cursors, it is very important to close the cursor when you are done with it and before creating a new one. Otherwise, deadlock may occur.

insert_id()

Returns the last AUTO_INCREMENT field value inserted into the database. (Non-standard)

nextset()

Not implemented.

setinputsizes()

Does nothing, successfully.

setoutputsizes()

Does nothing, successfully.

---