Geert JM Vanderkelen

MySQL Connector/Python v1.0.5 beta is now available for download from the MySQL website. This version is feature complete and we welcome and appreciate feedback and bug reports.

We’re also interested in hearing your feedback for future enhancements. Let us know how you’re using the connector too, especially if you are using it with Django, SQLAlchemy and similar Python technologies.

A few things have changed since the last development releases and we hope the manual shipping with the Connector/Python distribution (and also available online soon) will help you get up to speed.

Here are a few important changes that might be incompatible with current scripts using the now obsolete development releases v0.3.2 and earlier:

• MySQLCursor.execute() returns None. When multiple statements are send, it will return a generator object.
• MySQLConnection.ping() raises and exception. The is_connected() method will return True or False.
• The following methods of MySQLConnection changed: set_charset() replaced by set_charset_collation(); unset_client_flag() and set_client_flag() are removed, use set_client_flags() instead.

There have been quite a few changes and bug fixes. I recommend checking the ChangeLog file in the distribution of MySQL Connector/Python, and, once available, also the change log in the MySQL manual.

You can download Connector/Python 1.0.5-beta at:

• Download: http://dev.mysql.com/downloads/connector/python/

If you’d like to ask questions or seek advice in using the connector, check out the following MySQL forum:

• Forum: http://forums.mysql.com/list.php?50

I’m currently figuring out how to name the MySQL Connector/Python distributions so it works well with PyPi. Source archives would be named like mysql-connector-python-X.Y.Z.tar.gz.

The ‘name’ metadata would be ‘MySQL Connector Python’, thus without any underscores or dashes. This works OK, but if people have objections, please leave a comment.

This post explains how to compile Python v3.2 including support for the GNU Readline Library on MacOS X 10.7 (Lion), not using tools like MacPorts. One of the problems we try to solve is making history work in the interactive Python interpreter.

Pre-requirement is to have Apple’s XCode installed (check the Appstore).

First, you need a fresh copy of the GNU Readline Library and compile it. At the time of writing, readline v6.2 was the latest. Fire up a Terminal and do the following:

shell> tar xzf readline-6.2.tar.gz
shell> cd readline-6.2
shell> MACOSX_DEPLOYMENT_TARGET=10.7 ARCHFLAGS="-arch x86_64" \
       ./configure --prefix=/opt/local
shell> make && sudo make install

Note that you can change the location /opt/local to anything you like, but avoid /usr/lib or other system location.

When readline compiled and installed successfully, compile Python v3.2 as follows:

shell> tar xzf Python-3.2.3.tgz
shell> cd Python-3.2.3
shell> LDFLAGS="-L/opt/local/lib" CFLAGS="-I/opt/local/include" \
       ./configure --prefix=/opt/python/3.2.3
shell> make && sudo make install

Note that you might have more arguments to pass to the ./configure if you have special needs. Note also that /opt/python/3.2.3 is where Python will be installed to avoid clashing with the system provided Python installations.

You should now have history support in the Python interpreter:

shell> /opt/python/3.2.3/bin/python3
>>> import this
>>>

Two extra tips when you try to (manually) bootstrap MySQL on Windows from the command line:

1. When bootstrapping, use --no-defaults or --defaults-file.
2. Use --standalone when starting the MySQL server.

The first point really bugged me yesterday. I’m used to installing MySQL manually and having no option files available where MySQL would read them by default.

On Windows, however, I used the MySQL installer. So, when I launch unit testing for Connector/Python it bugged me saying log files of InnoDB didn’t match the configuration. This is indeed a newbie error..

I was asked whether it was possible to use more descriptive URLs for my blog posts instead of using the post ID only. WordPress permalinks only allow to have one structure active, but that doesn’t mean you can’t change the webserver (lighttpd in my case) to do some redirections.

Until an hour ago, my blog accepted only the following kind of url:

http://example.com/post/871

This was, in my opinion, easier to use since shorter. However, it is not very descriptive. So, we would like to have the following URLs:

    (1) http://example.com/more-descriptive-urls
    (2) http://example.com/post/871

In WordPress v3.3 (and also earlier version), you go to “Settings > Permalinks” and change the “Common Settings” to a custom structure like this:

    Custom Structure: /%postname%/

This makes more descriptive URLs (1) work, but it breaks our existing shorter URLs (2).

Using lighttpd, we have to change the WordPress rules so we are redirecting URLs with a post ID, to the descriptive URLs.

url.rewrite-once += (
    # more rules..
    "^" + wpdir + "post/(\d+)/?$" => wpdir + "index.php/?p=$1",
    "^" + wpdir + "(.+)/?$" => wpdir + "index.php/$1"
)

The above redirects http://example.com/post/871 to /index.php/?p=871.

Restart lighttpd and we’re done. Now both the longer, more descriptive URLs will be default, and the old URLs using only the post ID, will redirect to the new location.

In this blog we discuss invalid dates in MySQL, how to retrieve them using Connector/Python and we raise the question: Should Connector/Python raise an error or just keep returning None on invalid dates?

If you run MySQL without proper SQL Modes, you will be able to update and
read invalid dates such as ’2012-06-00′. If you’ve payed attention the past decade, you’ll know that you can prevent this configuring your MySQL server setting SQL Mode to ‘TRADITIONAL’.

Now, the problem if this is allowed, how do we get invalid dates using MySQL Connector/Python?

Lets look at an example inserting an invalid date and trying to read it again using MySQL Connector/Python:

>>> cur = cnx.cursor()
>>> cur.execute("INSERT INTO t1 VALUES ('2012-06-00')")
>>> cnx.commit()

mysql> SELECT * FROM t1;
+------------+
| date       |
+------------+
| 2012-06-00 |
+------------+

>>> cur.execute("SELECT * FROM t1")
>>> cur.fetchall()
[(None,)]

The date ’2012-06-00′ is converted by Connector/Python to Python’s None. This is because datetime.date does not allow invalid dates.

How to get the invalid dates back in your application?

You can use the raw-option for cursors and Connector/Python will return the date as a string instead of trying to convert to datetime.date.

>>> cur = cnx.cursor(raw=True)
>>> cur.execute("SELECT * FROM t1")
>>> cur.fetchall()
[('2012-06-00',)]

You are then responsible of parsing the text and do something usefull with it.

Question: what should Connector/Python do when it can’t convert the invalid dates? Returning a None is actually not really correct because if you would allow NULL in the MySQL table, you would also get None.

Should an invalid DATE value raise an error instead of returning None? Personally, I consider it a bug and I think it indeed should raise an error.

We have moved bugs for MySQL Connector/Python from Launchpad to the MySQL Bugs website http://bugs.mysql.com. Reports which are (probably) fixed in newer code were not taken with. If there is a bug which you really want to get tracked: please report it again.

Please use the MySQL Bugs website to report problems using MySQL Connector/Python. To see a list of active reports, click here.

There have been some request to have some reconnect possibilities in Connector/Python. I’m wondering now whether there should be some automatic reconnect on certain errors within the database driver.

My personal feeling is to have no automatic reconnect within Connector/Python and the programmer has to come up with retrying transactions herself.

For example:

	cnx.disconnect() # For testing..
	tries = 2
	while tries > 0:
		tries -= 1
		try:
			cursor.execute("INSERT INTO t1 (c1) VALUES ('ham')")
			cnx.commit()
		except mysql.connector.InterfaceError:
			if tries == 0:
				print "Failed inserting data after retrying"
				break
			else:
				print "Reconnecting.."
				cnx.reconnect()
		else:
			break

The above mimics how you would handle transactions and trying them reconnecting. I have ideas how to get this into Connector/Python, but it would not really fit PEP-249.

Would the above use case of reconnecting be enough?

Today we registered MySQL Connector/Python with the Python Package Index (PyPI). It makes installing your favorite connector even easier (provided you first install setuptools or pip):

shell> easy_install mysql-connector
shell> pip install mysql-connector

Please report problems either using Launchpad or MySQL Bugs website.

In addition to reporting MySQL Connector/Python bugs on Launchpad, it is now also possible to enter them using http://bugs.mysql.com.