Flask SqlAlchemy MySql Boolean Type Always Returns True

Viewed 2016

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?

2 Answers

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.

Related