SQLAlchemy, MySQL and sql_mode=traditional
As everyone should know, by default MySQL is an embarassing stupid toy:
mysql> create table foo (val integer not null); Query OK, 0 rows affected (0.03 sec) mysql> insert into foo values (1/0); ERROR 1048 (23000): Column 'val' cannot be null mysql> insert into foo values (1); Query OK, 1 row affected (0.00 sec) mysql> update foo set val=1/0 where val=1; Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql> select * from foo; +-----+ | val | +-----+ | 0 | +-----+ 1 row in set (0.00 sec)
Luckily, you can tell it to stop being embarassingly stupid:
mysql> set sql_mode="traditional"; Query OK, 0 rows affected (0.00 sec) mysql> update foo set val=1/0 where val=0; ERROR 1365 (22012): Division by 0
(There is an even better sql mode you can choose, though: it is called "Install PostgreSQL")
Unfortunately, I've been hired to work on a project that relies on the embarassing stupid behaviour of MySQL, so I cannot set sql_mode=traditional globally or the existing house of cards will collapse.
Here is how you set it session-wide with SQLAlchemy 0.6.x: it took me quite a while to find out:
import sqlalchemy.interfaces # Without this, MySQL will silently insert invalid values in the # database, causing very long debugging sessions in the long run class DontBeSilly(sqlalchemy.interfaces.PoolListener): def connect(self, dbapi_con, connection_record): cur = dbapi_con.cursor() cur.execute("SET SESSION sql_mode='TRADITIONAL'") cur = None engine = create_engine(..., listeners=[DontBeSilly()])
Why does it take all that effort is beyond me. I'd have expected this to be turned on by default, possibly with a switch that insane people could use to turn it off.