SQLAlchemy boolean value is None

Question

I have this table in my Pyramid app

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    .....
    is_active = Column(Boolean, unique=False)
    def __init__(self, name, raw_password):
        is_active = True

When I did my test, it said is_active is None.

def test_register_user(self):
    user = User('user1', '1234')
    self.sess.add(user)
    self.sess.flush()

    #print user
    #self.assertTrue(user.is_active, True)
    user_db_record = self.sess.query(User).filter_by(name=user.name).first()
    self.assertEqual(user_db_record.is_active, True)

From my integration log I see when we are creating the row, is_active is set to None. Why?

Solution 1

You have to set a default value otherwise None/NULL is used:

is_active = Column(Boolean, unique=False, default=True)

You wanted to do this in __init__ but you used is_active = True (a local variable) instead of self.is_active = True.

Solution 2

If you're using Flask-SQLAlchemy, you can use this command to create a server side default.

from sqlalchemy.sql import expression
active = db.Column(db.Boolean, server_default=expression.true(), nullable=False)

This will create a default value on the database so anyone can write to it and the DB will have the default value.

Solution 3

is_active = Column(Boolean, server_default='t', default=True)

Comments

[ruslaniv]

If anybody has come here looking for an answer using SqlAlchemy and JetBrains' DataGrip, then do not use their visual editor to insert values into the table, it will ignore the default keyword and set the column's value to <null>. Instead use a raw SQL statement from the console, then the value will be set correctly.

[user1012451]

Oh... self. was missing. Embarrassing. Thanks!

[Hussain]

What should be the value of server_default for Boolean?

[ρss]

Please add some explanation to your answer!

[havelock]

@Hussain Postgres docs state a list of valid literals for True/False values and 't' / 'f' are among them. I went with server_default='t' and it worked for me.

[havelock]

Seriously, with South/Django migration background, sometimes I think writing raw SQL migrations is a better approach compared to Alembic - at least it works in a predictable way...

[havelock]

@ρss Alembic seems not to be generating SQL-level default value clauses in migration code from default alone, you have to supply server_default explicitly. As for the 't' value, please see my comment above.

[cowbert]

@havelock one of the main selling points of SQLAlchemy ORM is to abstract the actual server/driver implementation (otherwise why bother doing anything beyond iterating over the Selectable and type(NamedTuple)?). So setting default in the column def and assigning the default value in __init__ will create both the default constraint on the server and assign it upfront during ORM object instantiation.

[henrycjc]

server_default="true" also works (note it is not the Python keyword True but a string "true")

[jave.web]

how is that Flask related? because this seems to be regular SQLAlchemy thing, I think you may have mixed up the db.TYPE stuff with server_default arg of SQLAlchemy Column?