Methods

Methods can be available to the entire module to a connection or a cursor. This section describes the various methods according to whether they are module, connection, or cursor methods.

Module Methods

Module methods are available to the entire module. The most important is the connect() method:

 dbh = MySQLdb.connect(parameters)

Connects to the specified hostname and database with the specified username and password, returning a connection object (or database handle). The parameters include the following:

host  Defaults to the localhost.

user  Defaults to the current user.

passwd  No default (empty password).

db  No default.

conv  Dictionary to map literals to the Python type. It defaults to MySQLdb.converters.conversions.

cursorclass  The class that cursor() uses. It defaults to MySQLdb.cursors.Cursor.

compress  Enables protocol compression.

named_pipe  On Windows, connects with a named pipe.

init_command  Specifies a statement for the database server to run as soon as the connection is created.

read_default_file  The MySQL configuration file to use.

read_default_group  The default group to read.

unix_socket  On Unix, connects using the specified socket. It uses TCP by default.

port  Defaults to 3306.

For example:

 dbh = MySQLdb.connect(user='guru2b', passwd='g00r002b',–  host='test.host.co.za', db='firstdb')

Connection Methods

These methods are for use on a connection object, returned from the MySQLdb.connect() method. You'll almost always use the cursor() and close() methods. The commit() and rollback() methods are used only for transactions.

begin

dbh.begin()

Begins a transaction, turning off AUTOCOMMIT if it's on, until the transaction ends with a call to commit() or rollback().

close

dbh.close()

Closes the connection and frees the associated resources.

commit

dbh.commit()

Commits any open transactions.

cursor

dbh.cursor([cursorClass])

Returns a new cursor object (which provides methods to access and manipulate data). You can specify a different class, if you want (by default it's the cursorclass specified in the connection, which defaults to the Cursor class).

rollback

dbh.rollback()

Rolls back any open transactions. Closing the connection without explicitly calling this method will implicitly call rollback for any open transactions.

Cursor Methods

These methods are for accessing and manipulating data; they work on a cursor object, returned from the cursor() method.

close

cursor.close()

Immediately frees resources associated with the cursor.

execute

cursor.execute(query[,parameters])

Prepares and executes a database query. The method also allows you to use placeholders to optimize repeat queries of a similar type by specifying various parameters. Placeholders are usually marked with a question mark (?), but MySQLdb does not currently support this. You need to use %s to indicate a placeholder (if the paramstyle attribute is set to format) because MySQLdb treats all values as strings no matter what type the fields actually are.

For example:

cursor.execute('INSERT INTO customer(first_name,surname) VALUES–  (%s, %s)',('Mike', 'Harksen'))

You can also use a Python mapping as the second argument if you set MySQLdb.paramstyle = 'pyformat'.

You can use lists of tuples as the second parameter, but this usage is deprecated. Use executemany instead.

executemany

cursor.executemany(query,seq_of_parameters)

Prepares a database query and then runs multiple instances with placeholders to optimize repeats of similar queries.

For example:

cursor.executemany('INSERT INTO customer(first_name,surname) VALUES–  (%s, %s)',(('Mike', 'Harksen'),( 'Mndeni', 'Vidal'),( 'John', 'Vilakazi')))

You can also use a Python mapping as the second set of arguments if you set MySQLdb.paramstyle = 'pyformat'.

fetchall

cursor.fetchall()  

Fetches all rows of a query result (from the current row pointer), returning them as a sequence of sequences (list of tuples).

The arraysize attribute of the cursor can affect the method's performance.

This throws an exception if there's an error.

For example:

cursor.execute("SELECT first_name, surname FROM customer") for row in cursor.fetchall():     print "Firstname: ", row[0]     print "Surname: ", row[1]            

fetchmany

cursor.fetchmany([size=cursor.arraysize]);

Fetches a number of rows from a query result, returning a sequence of sequences (list of tuples). You specify the number of rows with the optional size parameter or the cursor's arraysize if not specified. The method will not return more rows than are available.

It's best to use the arraysize attribute for performance reasons or to keep the size parameter the same between fetchmany calls.

This throws an exception if there's an error.

fetchone

cursor.fetchone() 

Returns the next row from a query result set.

This throws an exception if there's an error.

For example:

cursor.execute("SELECT first_name, surname FROM customer") row = cursor.fetchone():     print "Firstname: ", row[0]     print "Surname: ", row[1]

insert_id

cursor.insert_id()

A non-DB-API standard method that returns the previous AUTO_INCREMENT field value.

nextset, setinputsizes, and setouputsizes

cursor.nextset() 

These standard methods are not currently used for MySQL.