Vacation, and the mind is free. Then I scribble something, unleash it upon Earth where only a handful might read it:
Stop for a moment And watch around Hush for a second And hear the sound Think of your past step And watch the road ahead Pick up a fight And try to flight Break your habits Stop living by one's wits Take another curve And make some surf
Since a few decades, humanity got more and more cursed and/or blessed with a little device now fitting perfectly in a trousers' pocket or womon's purse. But how do you call it these days? Here are some possibilities which crossed my mind:
- Mobile
- GSM
- Handy (German)
- iPhone
- MP3 player
- Walkman
- PDA
- Alarm
- some kind of berry
- Book
- .. or.. Phone?
I just read my first book using Amazon Kindle on my iPhone. Although odd at first, it was surprisingly pleasant. Old books mind you, like ‘Adventures of Sherlock Holmes’. I still prefer the normal, papered books, but reading using one's phone is sometimes handy (pun intended). Suddenly, my phone turned into a .. a book.
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!