3. Using MySQLdb

MySQLdb is a Python Database API Specification 2.0 database module, so you should be familiar with the spec. Deviations from the spec are documented in the MySQLdb documentation.

3.1 What do I do if I am completely clueless?

Get a clue. Clues have been provided in the examples directory of the MySQLdb distribution.

3.2 No, I mean really clueless!

Okay, it goes something like this:

1. Import MySQLdb.
2. Create a Connection object.
3. Create a Cursor object.
4. Execute your query on the Cursor object.
5. If your query returns rows, you can use the Cursor object to fetch them.
6. Rinse, lather, repeat.

import MySQLdb
db = MySQLdb.connect(db='mydb',user='myuser',passwd='mypasswd')
c = db.cursor()
c.execute(myquery)
results = c.fetchall()

3.3 But MySQL doesn't have cursors!

True enough. MySQLdb fakes it, though, because the spec requires it.

3.4 cursor.rollback() is missing!

MySQL doesn't do transactions. cursor.rollback() is supposed to roll back (cancel) the current transaction. If you really need to do this, then you definitely want cursor.rollback() to fail, because it can't do what you want it to do.

OTOH, cursor.commit(), which attempts to commit the transaction to the database, does exist and always succeeds, because MySQL essentially is always in auto-commit mode.

MySQL-3.23 will, sometime in the near future, support transactions. When this happens, cursor.commit() will actually do something (and may fail if MySQL returns an error or warning condition), and cursor.rollback() will actually exist and undo the current transaction.

3.5 How do I use some of the special MySQL features?

Short answer: Don't, if you can avoid it. Your program will not be portable to other databases.

Long answer: MySQLdb exports all symbols from _mysql. There are only a couple MySQL functions available this way, though. The Connection object does wrap nearly all of the various MySQL calls that use a MYSQL argument (the connection handle in the C API). So let's say you want to use mysql_select_db(newdb). In MySQLdb, that's db.select_db(newdb) where db is your Connection object.

3.6 I still wanna use _mysql directly.

Well, it may be appropriate in some cirumstances. The patched ZMySQLDA does this, because MySQLdb does a lot of type conversion that isn't necessary for Zope's purposes.

1. Read the MySQL docs, particularly the C API, for an overview.
2. Read the MySQLdb docs. This shows how the C API is transliterated into Python.

---