Few days ago, the folks at SQLAlchemy pushed some proposed modification to the MySQL Connector/Python dialect. Before this patch, previous report yielded 72 errors and 11 failures. Now we got down to 9 errors, but the failures are still lingering. Is this an improvement? Yes and no, failures should go down, but there are some SQLAlchemy tests I just can't figure out, yet.. clues are welcome!
Here are some detailed results which also included MySQLdb and oursql. I used SQLAlchemy revision 6788 (i.e. from svn trunk) which has now 2143 unittests:
- mysql.connector rev216: SKIP=1, errors=9, failures=11 (355.707s)
- MySQLdb 1.2.3c1: SKIP=2, errors=8, failures=1 (315.884s)
- oursql 0.9.1: SKIP=1, errors=8, failures=2 (322.318s)
Software used: MacOSX v10.6.2, MySQL v5.1.42 and Python v2.6.1.
Using SQLAlchemy might not be the best way to messure how mature MySQL Connector/Python is, but it sure helps lots.
To far fetched (pun inteded), some might think.. Below you'll find a procedure to get a list of MySQL supported character sets and their collations. The output is Python and can be used to build a (big) tuple.
The problem is that character set IDs in MySQL have 'gaps'. For example hebrew has ID 16, and the next character set in the COLLATIONS-table, tis620, has ID 18. Not a big problem, just a bit annoying. This list is hardcoded in MySQL Connector/Python and I needed something to easily maintain it.
Solutions were using text editing skills, parsing it in Python to produce a list, etc.. But why not a Stored Routine? The following stored procedure is looping over a cursor, calculating the number of rows the gap has, and inserts blanks as needed.
DROP PROCEDURE IF EXISTS python_getcharsets;
DELIMITER //
CREATE PROCEDURE python_getcharsets()
BEGIN
DECLARE i,diff,cid,done INT DEFAULT 0;
DECLARE chname,coname VARCHAR(32);
DECLARE cur CURSOR FOR SELECT ID,CHARACTER_SET_NAME,
COLLATION_NAME FROM INFORMATION_SCHEMA.COLLATIONS
ORDER BY ID;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
loop_cur: LOOP
FETCH cur INTO cid,chname,coname;
IF done THEN LEAVE loop_cur; END IF;
SET i = i + 1; SET diff = cid - i;
WHILE diff DO
SELECT "None,";
SET diff = diff - 1; SET i = i + 1;
END WHILE;
SELECT CONCAT('("',chname,'","',coname,'"), # ',cid);
END LOOP loop_cur;
CLOSE cur;
END//
DELIMITER ;
CALL python_getcharsets();
DROP PROCEDURE IF EXISTS python_getcharsets;
You would save the above to a file called getcharsets.sql for example, and execute it like this:
shell> mysql -N test < getcharsets.sql
..
("cp1251","cp1251_bulgarian_ci"), # 14
("latin1","latin1_danish_ci"), # 15
("hebrew","hebrew_general_ci"), # 16
None,
("tis620","tis620_thai_ci"), # 18
("euckr","euckr_korean_ci"), # 19
..
I'll spare you the complete output, but as you can see from above sample: the gap has been stuffed with a None-Python value. Taking this output, you'll inserted it your code:
desc = (
None,
("big5","big5_chinese_ci"), # 1
("latin2","latin2_czech_cs"), # 2
("dec8","dec8_swedish_ci"), # 3
("cp850","cp850_general_ci"), # 4
..
Silly? Definitely basic stuff, but I got my code a bit faster, and cleaner!
Is there a performance penalty using a pure Python database interface? Yes there is. But how much? .. also depends on who wrote it.
I started implementing some benchmarking for MySQL Connector/Python. Main reason is to identified bottlenecks or just plain bad coded. Another reasons: it's cool and the question was raised during my talk at FOSDEM and also online.
Oh, yes, MySQL Connector/Python ain't fast right now. Just compare a script spawning 10 threads opening 1000 connections. It indeed just connects.
mysql.connector 0.1.3-devel average: 0.0091820 MySQLdb 1.2.3c1 average: 0.0026477 oursql 0.9.1 average: 0.0007394 (MacOSX 10.6.2, MySQL 5.1.42, Python 2.6.1)
I'm not surprised with how bad Connector/Python did. I'm more intrigued by how oursql is performing compared to MySQLdb. Nice!
Well, it's a start. Now off for a walk!
Yesterday, my new website went online and it caused a bit of problems with aggregators. My old domain was set to redirect to my new website, but left my old website with blog entries. That's now fixed.
New website is http://geert.vanderkelen.org. No ads, completely on Blogger. This way I can eventually get rid of my 'root' server and reduce costs.
Sorry for the noise.
A preliminary schedule is now available for the MySQL Conference & Expo 2010 (in Santa Clara, California, USA). I have two talks and a tutorial, currently scheduled as follows:
- MySQL Cluster Tutorial, Mon 08:30 on 12 Apr 2010, together with Andrew Hutchings and Andrew Morgan
- MySQL Cluster: An Introduction, Tue 11:55 on 13 Apr 2010
- Connecting MySQL and Python, Tue 15:05 on 13 Apr 2010
Schedule might change a bit, so I suggest keeping an eye on the conference website.
And please remember: we might have changed colors a few times past 2 years, but we are still the nice and friendly people we were back in MySQL AB!
Yes, MySQL has transactions if you use InnoDB or NDB Cluster for example. Using these transactional storage engines, you'll have to commit (or roll back) your inserts, deletes or updates.
I've seen it a few times now with people being surprised that no data is going into the tables. It's not so a silly problem in the end. If you are used to the defaults in MySQL you don't have to commit anything since it is automatically done for you.
Take the Python Database Interfaces for MySQL. PEP-249 says that, by default, auto-commit should be turned off. You could turn it back on, but it's good practice to be explicit and commit in your code. Remember the Zen of Python!
Here is just a small example to show it. Uses MySQL Connector/Python, but it should also work with the other MySQL database interfaces:
import mysql.connector
cnx = mysql.connector.connect(db='test')
cur = cnx.cursor()
cur.execute("""CREATE TABLE innodb_t1 (
id INT UNSIGNED NOT NULL,
c1 VARCHAR(128),
PRIMARY KEY (id)
) ENGINE=InnoDB""")
ins = "INSERT INTO innodb_t1 (id,c1) VALUES (%s,%s)"
cur.execute(ins,
(1,'MySQL Support Team _is_ already the best',))
cnx.commit()
cur.close()
cnx.close()
This post explains how to compile oursql and install it on MacOS 10.6. oursql is a Python database interface for MySQL, an alternative to MySQL for Python (i.e. MySQLdb) and MySQL Connector/Python.
First, find out which MySQL you installed. This can be either the 32-bit or the 64-bit version. To make sure, find the mysqld (e.g. in /usr/local/mysql/bin) and do the following in a Terminal window:
shell> file /usr/local/mysql/bin/mysqld .../mysqld: Mach-O 64-bit executable x86_64
If you see x86_64, you got 64-bit, otherwise 32-bit. If you see both, then you have a universal build. This is important for specifying the ARGSFLAG when building.
Download oursql from Launchpad and unpack it into some directory. Using the information from above, you'll have to do following for 64-bit platform (or universal build) in a Terminal window:
shell> ARCHFLAGS="-arch x86_64" python setup.py build shell> sudo python setup.py install
For 32-bit, you'll have to do:
shell> ARCHFLAGS="-arch i386" python setup.py build shell> sudo python setup.py install
Following error will be reported when you don't specify the correct ARCHFLAGS:
ld: warning: in .../lib/libmysqlclient.dylib, file is not of required architecture
Tips:
- When building failed, it is good to remove oursql, unpack it and try again.
- If you don't want to compile anything, or run into more troubles, give MySQL Connector/Python a try (alpha releases). It's a pure Python implementation of the MySQL Client/Server protocol and doesn't need compiling or a MySQL installation.
- You can download MySQL from either www.mysql.com or dev.mysql.com.
Apparently, my talk at FOSDEM 2010 about Connecting MySQL and Python was the only one about Python? There should be more, or?
I have a hand-out ready in PDF. The slides are not usable without my chatter. It contains a few examples and links. Any comments, corrections, criticism.. are welcome!
The longer version of this talk will be given at the O'Reilly MySQL Conference&Expo 2010 in Santa Clara, California (USA).
Today we made a change in the schedule of talks held in the MySQL Developer Room at FOSDEM 2010, swapping two talks. Change is:
- 12:15 CET - Giuseppe's 'Sharding for the Masses
- 14:25 CET - Geert's Connecting MySQL and Python (title also changed)
The printed booklets found at the conference will not reflect the change, but the printable schedule has already been updated.