Flask-SQLAlchemy SQLITE boolean field always returns False

Question

I'm using sqlite with SQLAlchemy. I have a class (trimmed) defined like so:

class User(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    can_view_records = db.Column(db.Boolean, default=False, nullable=False)

With my schema defined like so:

create TABLE users (
    id INTEGER not null,
    can_view_records BOOLEAN not null,
    PRIMARY KEY (id)
);

In my database, I have the following rows:

+----+------------------+
| id | can_view_records |
+----+------------------+
| 8  | 1                |
| 9  | 0                |
+----+------------------+

Querying them in Python always gives me False for the can_view_records field.

>>> User.query.get(8).can_view_records
False
>>> User.query.get(9).can_view_records
False

But performing a filter on that field succeeds:

>>> User.query.filter_by(can_view_records=True).all()
[<id 8>]

but even that result has returns False:

>>> User.query.filter_by(can_view_records=True).first().can_view_records
False

Am I missing something?

Answer 1

with same code can not reproduce on OSX with Python 2.7.10, SQLAlchemy==1.0.9 and Flask-SQLAlchemy==2.1, Flask==0.10.1:

#!/usr/bin/env python
# encoding: utf-8

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:////tmp/test.db'
db = SQLAlchemy(app)


class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), unique=True)
    email = db.Column(db.String(120), unique=True)
    can_view_records = db.Column(db.Boolean, default=False, nullable=False)

    def __init__(self, username, email, can_view_records):
        self.username = username
        self.email = email
        self.can_view_records = can_view_records

    def __repr__(self):
        return '<User %r>' % self.username

generate db:

>>>from app import db, User
>>>db.create_all()
>>>admin = User('admin', '[email protected]', True)
>>>guest = User('guest', '[email protected]', False)
>>>db.session.add(admin)
>>>db.session.add(guest)
>>>db.session.commit()

sqlite table looks like this:

sqlite> select * from user;
1|admin|[email protected]|1
2|guest|[email protected]|0

all query looks okay:

>>>User.query.get(1).can_view_records
>>>True
>>>User.query.get(2).can_view_records
>>>False
>>>User.query.filter_by(can_view_records=True).first().can_view_records
>>>True