Python Mysqldb returning '\x01' for bit values

Question

Im using Mysqldb with Python(2.7) to return a bunch of values form a Mysql database with a charset latin1.

The mysql database has values of type bit(1) when these are returned they look like this '\x01'

Decimal values return like this Decimal('0E-8')

They rest of the values and types are fine.

Sample Database resultset:

{'test': '\x01', 'currency': u'bla', 'balance': Decimal('0E-8'), 'first': u'John'}

This is how im connecting to the database: self.con = MySQLdb.connect(host = conn['host'], user = conn['user'], passwd=conn['passwd'], db=conn['db'], charset=conn['charset'],port = int(conn['port']) if 'port' in conn else 3306)

I would like the bit(1) values to be return as either True or False. I have looked around and I think it might be to do MySQLdb.converters.conversions but im not sure how to implement this. Any ideas would be great. Thanks

And I don't have access to change the types in the DB.

Answer 1

try:

orig_conv = MySQLdb.converters.conversions
#Adding support for bit data type
orig_conv[FIELD_TYPE.BIT] = lambda data: data == '\x01'

passing this into my connection.
MySQLdb.connect(conv=orig_conv)

using "orig_conv[FIELD_TYPE.BIT] = bool" just returned everything as true for me

Answer 2

Talking to developers of MySQL internals, they all say the BIT data type is broken and riddled with bugs.

Example: Why you should not use BIT columns in MySQL by Baron Schwartz, co-author of High Performance MySQL.

So I'd suggest using TINYINT and store either 0 or 1 to represent a boolean. You won't waste any space, because the size of a BIT column rounds up to the nearest whole byte anyway.

MySQL even has a data type alias BOOL. When you use it, it is mapped to TINYINT(1) (though the 1 doesn't affect anything).

---

If you can't change the data type, then you can map it to an integer in your SQL query:

MySQL uses 1 or 0 for boolean values, so you can just do this:

SELECT (test=B'1') AS test, currency, balance, first FROM ...

Or you can be more verbose:

SELECT IF(test=B'1', 1, 0) AS test, currency, balance, first FROM ...

Or you can be completely SQL compliant:

SELECT CASE test=B'1' WHEN true THEN 1 ELSE 0 END AS test, currency, balance, first FROM ...

Answer 3

Figured this out: So had to dig a little in the Mysqldb package.

orig_conv = MySQLdb.converters.conversions
#Adding support for bit data type
orig_conv[FIELD_TYPE.BIT] = bool

passing this into my connection.
MySQLdb.connect(conv=orig_conv)

The FIELD_TYPE can be found in MySQLdb.constants and can be imported this way

from MySQLdb.constants import FIELD_TYPE

Answer 4

from pymysql import converters
converions = converters.conversions
converions[pymysql.FIELD_TYPE.BIT] = lambda x: '0' if x == '\x00' else '1'
db = pymysql.connect(mysql_host, mysql_user, mysql_password, mysql_db, 3306,charset='utf8',conv=converions)