'0' coerced to True sqlite boolean value since 1.1

Issue #4102 resolved
Sebastian Bank
created an issue

Use case: insert using rows (i.e. string tuples) from CSV

import sqlalchemy as sa
import sqlalchemy.ext.declarative

class Spam(sa.ext.declarative.declarative_base()):
    __tablename__ = 'spam'
    id = sa.Column(sa.Integer, primary_key=True)
    spam = sa.Column(sa.Boolean)

engine = sa.create_engine('sqlite://', echo=True)
Spam.metadata.create_all(engine)

sa.insert(Spam, bind=engine).execute(spam='0')
# worked until 1.0.19, fails with 1.1
assert not engine.scalar(sa.select([Spam.spam]))

Maybe this is intended though, see 7c74d702a963 (does this mean one cannot round-trip via CSV any more?):

return int(value) -> int(bool(value))

Comments (10)

  1. Michael Bayer repo owner

    so the string '0' coercion is noted in the migration notes:

    http://docs.sqlalchemy.org/en/latest/changelog/migration_11.html#non-native-boolean-integer-values-coerced-to-zero-one-none-in-all-cases

    the value '0' in Python is True:

    >>> bool('0')
    True
    

    does this mean one cannot round-trip via CSV any more?

    You can round trip CSVs you just need to ensure the correct string->data marshalling is present, not just booleans but things like dates, numbers too. One would think a lot of tools are out there to help with this, though googling it seems folks either roll their own (since it is simple) or they use the richer features of Pandas. In an earlier era, people would use form processing tools such as deform. csvschema seems pretty good too but looks a little stale (then again, csv hasn't changed much since 2014)...

  2. Sebastian Bank reporter

    Thanks. I understand that all strings except '' are falsy. However, the cited migration note does not mention strings, right (only integers)?

    IMHO it is surprising that directly inserting '0' into an Integer column does the expected coersion for the target data type but doing the same with a Boolean column stopped working analogically in 1.1.

    How about changing the conversion to return int(bool(int(value)))?

  3. Michael Bayer repo owner

    However, the cited migration note does not mention strings, right (only integers)?

    then I'll update the document.

    IMHO it is surprising that directly inserting '0' into an Integer column does the expected coersion for the target data type but doing the same with a Boolean column stopped working analogically in 1.1.

    The design (which actually has a critical issue I just found) is that the integer type does: int(value) and the boolean type does bool(value). It would be less consistent that the boolean coercion adds an additional int() to work around Python's calling style.

    How about changing the conversion to return int(bool(int(value)))?

    That would treat a string '0' as False which is not what Python does, and I dont see a reason this boolean type should not act as closely to Python's notion of booleans as possible.

    However, some backends are still doing this, because the coercion isn't taking place in all cases. #4104 is created in that this boolean coercion needs to be turned on for all backends to be consistent - most DBAPIs pass the string '0' straight through where it gets interpreted as false, so the change here did not take effect for every backend and that's kind of a critical issue.

  4. Michael Bayer repo owner

    Disallow all ambiguous boolean values for Boolean

    In release 1.1, the :class:.Boolean type was broken in that boolean coercion via bool() would occur for backends that did not feature "native boolean", but would not occur for native boolean backends, meaning the string "0" now behaved inconsistently. After a poll, a consensus was reached that non-boolean values should be raising an error, especially in the ambiguous case of string "0"; so the :class:.Boolean datatype will now raise ValueError if an incoming value is not within the range None, True, False, 1, 0.

    Change-Id: If70c4f79c266f0dd1a0306c0ffe7acb9c66c4cc3 Fixes: #4102

    → <<cset c63658973c95>>

  5. Log in to comment