Flask SqlAlchemy MySql Boolean Type Always Returns True

Question

I have a Flask application connected to a MySql DB using SqlAlchemy. The table has 3 x boolean (bit) fields as shown below:

+------------------------+---------------+------+-----+-------------------+----------------+
| Field                  | Type          | Null | Key | Default           | 
Extra          |
+------------------------+---------------+------+-----+-------------------+----------------+
| ID                     | int(11)       | NO   | PRI | NULL              | 
auto_increment |
| clientID               | int(11)       | YES  |     | NULL              |                
|
| accountType            | varchar(2)    | YES  |     | NULL              |                
|
| systemType             | varchar(1)    | YES  |     | NULL              |                
|
| clientName             | varchar(400)  | YES  |     | NULL              |                
|
| clientURL              | varchar(5000) | YES  |     | NULL              |                
|
| clientTelephone        | varchar(300)  | YES  |     | NULL              |                
|
| clientAddressLine1     | varchar(500)  | YES  |     | NULL              |                
|
| clientAddressLine2     | varchar(500)  | YES  |     | NULL              |                
|
| clientAddressLine3     | varchar(500)  | YES  |     | NULL              |                
|
| clientPostcode         | varchar(50)   | YES  |     | NULL              |                
|
| clientCountry          | varchar(100)  | YES  |     | NULL              |                
|
| accessBenchmarking     | bit(1)        | YES  |     | NULL              |                
|
| accessTechnicalSupport | bit(1)        | YES  |     | NULL              |                
|
| accountLive            | bit(1)        | YES  |     | NULL              |                
|
| clientTown             | varchar(100)  | YES  |     | NULL              |                
|
| clientCounty           | varchar(100)  | YES  |     | NULL              |                
|
| dateTimeStamp          | timestamp     | YES  |     | CURRENT_TIMESTAMP |                
|
+------------------------+---------------+------+-----+-------------------+----------------+

Each of the bit fields has a value set to 0.

The SqlAlchemy Model for this is:

class ClientAccounts(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    clientID = db.Column(db.Integer)
    accountType = db.Column(db.Text(2))
    systemType = db.Column(db.Text(1))
    clientName = db.Column(db.Text(400))
    clientURL = db.Column(db.Text(5000))
    clientTelephone = db.Column(db.Text(300))
    clientAddressLine1 = db.Column(db.Text(500))
    clientAddressLine2 = db.Column(db.Text(500))
    clientAddressLine3 = db.Column(db.Text(500))
    clientPostcode = db.Column(db.Text(50))
    clientCountry = db.Column(db.Text(100))
    accessBenchmarking = db.Column(db.Boolean)
    accessTechnicalSupport = db.Column(db.Boolean)
    accountLive = db.Column(db.Boolean)
    clientTown = db.Column(db.Text(100))
    clientCounty = db.Column(db.Text(100))

The code to retrieve the values is here:

#check for valid and live user account
CheckAccount = ClientAccounts.query.filter_by(
clientID=accountNo,
).first()
if not CheckAccount is None:

    accessBenchmarking = CheckAccount.accessBenchmarking
    accessTechnicalSupport = CheckAccount.accessTechnicalSupport
    accountLive = CheckAccount.accountLive

print 'db return ...'

print accessBenchmarking
print accessTechnicalSupport
print accountLive

The values are always returned as True even though they are set to False in the DB. The returned vales can be seen here:

INFO:sqlalchemy.engine.base.Engine:('11111111', 1)
db return ...
True
True
True

Does anybody have any idea what's causing this?

Answer 1

For those who come across this thread without finding the solid solution for this:

I fixed this issue by changing the MYSQL connector to mysql-connector from pymysql.

pip3 install mysql-connector

'mysql+mysqlconnector://username:password!!@127.0.0.1:3306/'

I was lost for a long time, making this work. Didn't know the connector would be the issue.

Answer 2

I figured out a fix for this. Changing the field data type from bit to tinyint for each boolean field did the trick. I'm still none the wiser as to why bit doesn't work with SqlAlchemy. Maybe it's the version of MySql Python I'm using?