Control decimal division precision in MySQLdb

Question

MySQLdb does some weirdness where it seems to always return a Decimal object with 2 more significant figures than the numerator of a division operation.

If the denominator is relatively large, this means that sometimes the result gets truncated to zero:

>>> import MySQLdb
>>> db = MySQLdb.connect(.....)
>>> c = db.cursor();
>>> c.execute("select 1000/ 20990933630")
1L
>>> c.fetchall()
((Decimal("0.0000"),),)
>>> c.execute("select 1000.0000000000000000000000/ 20990933630")
1L
>>> c.fetchall()
((Decimal("4.763961516084313397E-8"),),)
>>> c.execute("select (1000 + 0.000000000000000000000) / 20990933630")
1L
>>> c.fetchall()
((Decimal("4.76396151608431340E-8"),),)

Can I force float division? Is there a more elegant way of doing this than adding 0.0000000000 to everything?

Answer 1

You can change the div_precision_increment variable. It defaults to 4.

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar%5Fdiv%5Fprecision%5Fincrement

Here's an example using your division:

mysql> select 1000/ 20990933630;
+-------------------+
| 1000/ 20990933630 |
+-------------------+
|            0.0000 | 
+-------------------+
1 row in set (0.00 sec)

mysql> set local div_precision_increment = 30;
Query OK, 0 rows affected (0.00 sec)

mysql> select 1000/ 20990933630;
+----------------------------------+
| 1000/ 20990933630                |
+----------------------------------+
| 0.000000047639615160843133969739 | 
+----------------------------------+
1 row in set (0.00 sec)

mysql>