Book HomeManaging and Using MySQLSearch this book
PreviousNext

Chapter 20. The Python DB-API

Contents:

Module: MySQLdb

DB-API supports database-independent database access. The MySQL implementation of this API, MySQLdb, can be downloaded from http://dustman.net/andy/python/MySQLdb. It comes with a RedHat RPM Linux installer, a Win32 installer, and a Python script for other platforms. For those other platforms:

  1. Uncompress the .tar.gz file that contains MySQLdb using the commands gunzip FILENAME.tar.gz and tar xf FILENAME.tar.

  2. Change directories into the newly generated MySQLdb directory.

  3. Issue the command: python setup.py install.

The MySQLdb module contains the standard DB-API methods and attributes as well as several proprietary methods and attributes. Proprietary APIs are marked with asterisks.

20.1. Module: MySQLdb

The entry point into the MySQL module is via the MySQLdb.connect( ) method. The return value from this method represents a connection to a MySQL database that you can use for all of your MySQL operations.

20.1.1. Module Attributes

apilevel

Synopsis

A string constant storing the version of the DB-API that MySQLdb supports.

paramstyle

Synopsis

Defines the type of parameter placeholder in parameterized queries. DB-API supports many valid values for this attribute, but MySQLdb actually supports only format and pyformat. This attribute is largely meaningless to MySQL developers.

quote_conv

Synopsis

Maps Python types to MySQL literals via a dictionary mapping.

threadsafety

Synopsis

Specifies the level of thread safety supported by MySQLdb. Possible values are:

0
Threads may not share the module.

1
Threads may share the module but not the connections.

2
Threads may share the module and connections.

3
Threads may share the module, connections, and cursors.

type_conv

Synopsis

Maps MySQL types from strings to the desired mapping type. This value is initialized with: 

{ FIELD_TYPE.TINY : int,
FIELD_TYPE.SHORT: int,
FIELD_TYPE.LONG: long,
FIELD_TYPE.FLOAT: float,
FIELD_TYPE.DOUBLE: float,
FIELD_TYPE.LONGLONG: long,
FIELD_TYPE.INT24: int,
FIELD_TYPE.YEAR: int }

20.1.2. Module Methods

MySQL.connect()

Signature

 
connection = MySQL.connect(params)

Synopsis

Connects to the MySQL database engine represented by the various connection keyword/value parameters. These parameters include:

host
The name of the server on which the MySQL database is running

user
The user ID for connecting to MySQL. MySQL should allow this user to make the connection.

passwd
The password to authenticate the user ID for the connection.

db
The MySQL database to which the application attempts to connect.

port
Directs MySQLdb to connect to a MySQL installation on a custom part. When left unspecified, the method will use the default MySQL port of 3306.

unix_socket
Identifies the location of a socket or named pipe to use if the host allows it.

client_flags
An integer specifying the client connection flags to use. These client connection flags are the same ones enumerated in Chapter 19 for the mysql_real_connect( ) method.

This method returns a Python object representing a connection to a MySQL database.

Example

connection = MySQLdb.connect(host='carthage', user='test', 
                             passwd='test', db='test');

20.1.3. Connection Attributes

db

Synopsis

A window into the MySQL C API. MySQLdb uses this attribute to make calls to the underlying C API.

20.1.4. Connection Methods

close()

Signature

 
close( )

Synopsis

Closes the current connection to the database and releases any associated resources.

Example

connection = MySQLdb.connect(host='carthage', user='test', 
                             passwd='test', db='test');
connection.close( );
commit()

Signature

commit( )

Synopsis

Commits the current transaction by sending a COMMIT to MySQL.

Example

connection = MySQLdb.connect(host='carthage', user='test', 
                             passwd='test', db='test');
connection._transactional = 1;
cursor = connection.cursor( );
cursor.execute("UPDATE TNAME SET COL = 1 WHERE PK = 2045");
cursor.execute("UPDATE TNAME SET COL = 1 WHERE PK = 3200");
connection.commit( );
connection.close( );
cursor()

Signature

cursor = cursor( )

Synopsis

Creates a cursor associated with this connection. Transactions involving any statements executed by the newly created cursor are governed by this connection.

Example

connection = MySQLdb.connect(host='carthage', user='test', 
                             passwd='test', db='test');
cursor = connection.cursor( );
cursor.execute("UPDATE TNAME SET COL = 1 WHERE PK = 2045");
connection.close( );
rollback( )

Signature

rollback( )

Synopsis

Rolls back any uncommitted statements. This works only if MySQL is set up for transactional processing in this context.

Example

connection = MySQLdb.connect(host='carthage', user='test', 
                              passwd='test', db='test');
connection._transactional = 1;
cursor = connection.cursor( );
cursor.execute("UPDATE TNAME SET COL = 1 WHERE PK = 2045");
try:
    cursor.execute("UPDATE TNAME SET COL = 1 WHERE PK = 3200");
    connection.commit( );
except:
    connection.rollback( );
connection.close( );

20.1.5. Cursor Attributes

arraysize

Synopsis

Specifies the number of rows to fetch at a time with the fetchmany( ) method call. By default, this value is set to 1. In other words, fetchmany( ) fetches one row at a time by default.

description

Synopsis

Describes a result column as a read-only sequence of seven-item sequences. Each sequence contains the following values: name, type_code, display_size, internal_size, precision, scale, and null_ok.

rowcount

Synopsis

Provides the number of rows returned through the last executeXXX( ) call. This attribute is read-only and has a value of -1 when no executeXXX( ) call has been made, or the last operation does not provide a row count.

20.1.6. Cursor Methods

callproc( )

Signature

 
callproc(procname [,parameters])

Synopsis

This method is not supported by MySQL.

Method: close( )

Signature

close( )

Synopsis

Closes the cursor explicitly. Once closed, a cursor will throw a ProgrammingError if any operation is attempted on the cursor.

Example

cursor = connection.cursor( );
cursor.close( );
execute( )

Signature

cursor = execute(sql [,parameters])

Synopsis

Sends arbitrary SQL to MySQL for execution. If the SQL specified is parameterized, the optional second argument is a sequence or mapping containing parameter values for the SQL. Any results or other information generated by the SQL can then be accessed through the cursor.

The parameters of this method may also be lists of tuples to enable you to perform multiple operations at once. This usage is considered deprecated as of the DB-API 2.0 specification. You should use the executemany( ) method instead.

Example

connection = MySQLdb.connect(host='carthage', user='test', 
                             passwd='test', db='test');
cursor = connection.cursor( );
cursor.execute('SELECT * FROM TNAME');
executemany( )

Signature

cursor.executemany(sql,parameters)

Synopsis

Prepares an SQL statement and sends it to MySQL for execution against all parameter sequences or mappings in the parameters sequence.

Example

connection = MySQLdb.connect(host='carthage', user='test', 
                             passwd='test', db='test');
cursor = connection.cursor( );
cursor.executemany("INSERT INTO COLOR ( COLOR, ABBREV ) VALUES (%s, %s )",
                   (("BLUE", "BL"), ("PURPLE", "PPL"), ("ORANGE", "ORN")));
Method: fetchall( )

Signature

rows = cursor.fetchall( )

Synopsis

Fetches all remaining rows of a query result as a sequence of sequences.

Example

connection = MySQLdb.connect(host='carthage', user='test', 
                             passwd='test', db='test');
cursor = connection.cursor( );
cursor.execute("SELECT * FROM TNAME");
for row in cursor.fetchall( ):
    # process row
fetchmany( )

Signature

rows = cursor.fetchmany([size])

Synopsis

Fetches the next set of rows of a result set as a sequence of sequences. If no more rows are available, this method returns an empty sequence.

If specified, the size parameter dictates how many rows should be fetched. The default value for this parameter is the cursor's arraysize value. If the size parameter is larger than the number of rows left, the resulting sequence will contain all remaining rows.

Example

connection = MySQLdb.connect(host='carthage', user='test', 
                             passwd='test', db='test');
cursor = connection.cursor( );
cursor.execute("SELECT * FROM TNAME");
rows = cursor.fetchmany(5);
fetchone( )

Signature

row = cursor.fetchone( )

Synopsis

Fetches the next row of a result set returned by a query as a single sequence. This method will return None when no more results exist. It will throw an error if the SQL executed is not a query.

Example

connection = MySQLdb.connect(host='carthage', user='test', 
                             passwd='test', db='test');
cursor = connection.cursor( );
cursor.execute("SELECT * FROM TNAME");
row = cursor.fetchone( );
print "Key: ", row[0];
print "Value: ", row[1];
insert_id( )*

Signature

id = cursor.insert_id( )

Synopsis

Returns the last inserted ID from the most recent INSERT on an AUTO_INCREMENT field.

Example

connection = MySQLdb.connect(host='carthage', user='test', 
                             passwd='test', db='test');
cursor = connection.cursor( );
cursor.execute("INSERT INTO TNAME (COL) VALUES (1)");
id = cursor.insert_id( );
nextset( )

Signature

cursor.nextset( )

Synopsis

This method always returns None for MySQL.

setinputsizes( )

Signature

cursor.setinputsizes(sizes)

Synopsis

This method does nothing in MySQL.

setoutputsize( )

Signature

cursor.setoutputsize(size [,column])

Synopsis

This method does nothing in MySQL.

PreviousHomeNext
19.2. FunctionsBook IndexIndex
Library Navigation Links

Copyright © 2003 O'Reilly & Associates. All rights reserved.