SQLAlchemy, MySQL, and python - how should I handle boolean?

1
votes

I have a Pyramid application that I am using with SQLAlchemy and MySQL. For database fields that I wanted to treat as boolean, I've been using a "BIT" data type on the SQLAlchemy side, and BIT(1) on the MySQL side.

This had all been working fine, but I was checking some newly updated code on my webhost and their version of phpMyAdmin is newer than the one I'm using locally; I was browsing a table that has a BIT field and on the newer phpMyAdmin none of the data appears - it's just blank. On my local instance BIT fields display as 0 or 1. If I tried to inline edit the hosted phpMyAdmin it wouldn't take any values I tried. I did try my application code and it appears to be able to toggle the true/false values just fine.

The got me wondering - with this setup should I be approaching it differently? SQLAlchemy does support Boolean, which seems like it would be more intuitive and appropriate, should I use that and set the MySQL fields to TINYINT instead?

What is the conventionally accepted way to handle booleans between SQLAlchemy and MySQL?

1
mysqlphpmyadminsqlalchemypyramid
MySQL actually has a BOOLEAN datatype, but it's an alias for TINYINT(1). You save nothing with BIT(1), since it still requires a full byte for storage. I'd definitely recommend going with SQLAlchemy Boolean and MySQL TINYINT. If you want to enforce a constraint that the TINYINT only be 0 or 1, you can do so by making it a foreign key into a Booleans table. - eggyal
Ah, I didn't realize that, or maybe I aw the tinyint reference but didn't see the full-on BOOLEAN native type. Thanks! - Peter Tirrell

1 Answers

3
votes

MySQL has a BOOL type (which is what SQLAlchemy uses) so I'm not sure why you don't just use that? Apparently it is an alias for TINYINT.

from sqlalchemy import Boolean and you should be good to go.