20.1 Module: MySQLdb

only for RuBoard - do not distribute or recompile

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:

: 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

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.

only for RuBoard - do not distribute or recompile