8 Apr 2000 FarcePest   » (Journeyer)

Released MySQLdb-0.2.0. A lot of changes in this version, and most of them are non-obvious. Which is a good thing, right?

There's now a mutex in the standard Cursor class, which allows two threads to share a connection. Personally, I think sharing connections is a bad idea. For one, each connection is a seperate thread in mysqld, so by sharing a connection, you don't get to take advantage of multiple CPUs, or doing something while another operation blocks. Second, transactions are coming to MySQL, or so I hear, sometime in 3.23. It seems likely that transactions will begin and end on a per-connection basis, i.e. in most database designs, the commit/rollback is done on the connection, not the cursor.

There are now multiple cursor classes, built using several MixIn classes. I won't list them here; they are in the documentation. But it's something like this:

  • return rows as tuples or columns
  • use client-side (mysql_store_result) or server-side (mysql_use_result) cursors (the latter you especially don't want to share connections with)
  • raise Warning or not

Or you can mix up your own class.

0.1.3 introduced some methods that were intended to create a little backwards compatibility with the older MySQLmodule: cursor.fetchXXXDict(). It didn't quite work out that way. MySQLmodule would set the keys to be "table.column", and my implementation used "column". I figure, if your columns aren't unique, you should just alias them with AS in your SQL. The people who wanted the old way of doing things seemed to use SELECT * a lot, which is usually a bad idea.

So then I got the bright idea: Set the key to "column" unless it already exists; otherwise set it to "table.column". This gets rid of most of the ambiguity problems.

The other thing I did which relates to backwards-compatibility is I got rid of nearly all of the keyword options on the db.cursor() method, namely use and dict. There is a new one, cursorclass, which you can set to your own cursorclass. If you are a freak for fetching dictionaries, you would now do db.cursor(cursorclass=DictCursor), and this gives you a cursor that returns dictionaries. cursor.fetchXXXDict() is deprecated; just use cursor.fetchXXX() on the DictCursor instance.

Breaking the Cursor class into a bunch of MixIns let me do another important optimization. By default, the old cursor class used client-side cursors (mysql_store_result). This means your entire result set is sucked into memory on the client side. Then the various fetch calls would return rows. When you deleted the cursor, only then would the MYSQL_RES be freed.

What it does now is: After it does the query, it fetches all the rows, and hides them in the cursor. Then it frees the MYSQL_RES. Theoretically, this cuts the memory utilization down. Queries are probably slower (for large result sets), but fetches now are literally just slices.

This also allowed the introduction of some non-standard methods: cursor.seek(offset[, whence=0]) and cursor.tell().

Another optimization: A new string literal function in _mysql that not only does mysql_escape_string but adds the necessary quotes in place. This should speed up string INSERTS, since not as many strings are being built up and torn down.

Because of the work to make connections sharable, you now have to call cursor.insert_id() instead of db.insert_id() to get the last inserted auto increment field value.

I wonder sometimes whether the MixIn classes for Cursors are really worth it. They might cost performance, since you need the BaseCursor class and three MixIns to implement the standard cursor. On the other hand, some program logic is removed, which is usually a good thing.

On the Zope front: There's a ZMySQLDA-1.1.4 out. The patch I have to make ZMySQLDA use MySQLdb/_mysql doesn't work on it. However, I do have a new version (culled together from ZOracleDA) that I haven't released yet. If you want to test it, let me know. It seems to work perfectly fine for SELECT; someone has told me it doesn't work for other things. I'm using it in an on-going project; I just haven't had to write to the database yet.

MySQLdb is the number one download at The Vaults of Parnassus, beating out stuff like Numerical Python and Zope. Wow. I guess a lot of people like MySQL. So due to demand, I've started to build Red Hat RPMs as well. The package is named MySQL-python so that it's a sub-package of MySQL.

Latest blog entries     Older blog entries

New Advogato Features

New HTML Parser: The long-awaited libxml2 based HTML parser code is live. It needs further work but already handles most markup better than the original parser.

Keep up with the latest Advogato features by reading the Advogato status blog.

If you're a C programmer with some spare time, take a look at the mod_virgule project page and help us with one of the tasks on the ToDo list!