58

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?

user1012451
  • 3,343
  • 7
  • 29
  • 33
  • 1
    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 ``. Instead use a raw SQL statement from the console, then the value will be set correctly. – ruslaniv Jun 30 '21 at 05:47

5 Answers5

74

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.

Jochen Ritzel
  • 104,512
  • 31
  • 200
  • 194
  • 1
    Oh... `self.` was missing. Embarrassing. Thanks! – user1012451 Aug 21 '12 at 19:52
  • 13
    What should be the value of `server_default` for Boolean? – Hussain Jun 29 '15 at 04:10
  • 15
    @Hussain [Postgres docs](https://www.postgresql.org/docs/current/static/datatype-boolean.html) 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 Dec 22 '16 at 01:39
  • 1
    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 Dec 22 '16 at 01:39
  • @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. – cowbert Mar 17 '18 at 23:26
58

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.

Sowmitra Nalla
  • 583
  • 5
  • 5
  • 3
    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`? – jave.web Jun 23 '21 at 06:43
29
is_active = Column(Boolean, server_default='t', default=True)
far
  • 2,697
  • 1
  • 17
  • 9
  • 12
    Please add some explanation to your answer! – ρss Jan 04 '16 at 20:38
  • 6
    @ρ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](http://stackoverflow.com/questions/12045698/sqlalchemy-boolean-value-is-none#comment69750602_12045897). – havelock Dec 22 '16 at 01:42
  • 1
    `server_default="true"` also works (note it is not the Python keyword `True` but a string `"true"`) – henrycjc Nov 23 '20 at 00:27
  • In my case alembic generate Column(Boolean, server_default=text("false")) and this was not working, I modified to Column(Boolean, server_default='f', default=False) as @far suggested and it worked properly. – eduardosufan Oct 05 '22 at 15:17
  • One could use `expression.true()` rather than a literal `'t'` to assign `server_default` for portability. – George Jun 06 '23 at 02:19
1

If someone is looking for a solution and using Flask this is how you'd do it.

It is very important to note that server default should be used inside the migrations file, not on the model itself.

once you generate migrations using a

flask db migrate

you should see new migration in the folder ( migrations/versions/XXXXXX.py )

def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column('users', sa.Column('confirmed', sa.Boolean(), nullable=True, server_default='f'))
    op.add_column('users', sa.Column('confirmed_on', sa.DateTime(), nullable=True))
    op.add_column('users', sa.Column('is_admin', sa.Boolean(), nullable=True, server_default='f'))

This is the place where you'd want to add server_default.

Ojas Kale
  • 2,067
  • 2
  • 24
  • 39
0

Provided solutions here don't work for me (SQLAchemy 1.4.40)

def test_entity():
    class Entity(Base):
        __tablename__ = "test_entities"
        id = Column(BigInteger, primary_key=True)
        is_active = Column(Boolean, nullable=False, default=True)

    entity = Entity()
    assert entity.is_active is True

Expected :True
Actual   :None
Mike Reiche
  • 382
  • 3
  • 12