SQL Server BIT is native boolean

[sqlalchemy-bot]

Migrated issue, originally created by Michael Bayer (@zzzeek)

we should not be creating a constraint for Boolean on SQL Server and we should support native boolean rules. The BIT type only stores 1 or 0 and converts any other integer into a 1:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    data = Column(Boolean(create_constraint=False))

#e = create_engine("mssql+pymssql://scott:tiger^5HHH@mssql2017:1433/test", echo=True)
e = create_engine("mssql+pyodbc://scott:tiger^5HHH@mssql2017:1433/test?driver=FreeTDS", echo=True)

Base.metadata.drop_all(e)
Base.metadata.create_all(e)

conn = e.connect()
conn.execute("INSERT INTO a (data) VALUES (1)")
conn.execute("INSERT INTO a (data) VALUES (15)")

if e.dialect.driver == 'pymssql':
    conn.execute("INSERT INTO a (data) VALUES (%(data)s)", {"data": True})
    conn.execute("INSERT INTO a (data) VALUES (%(data)s)", {"data": 25})
    conn.execute("INSERT INTO a (data) VALUES (%(data)s)", {"data": 1})
else:
    conn.execute("INSERT INTO a (data) VALUES (?)", [True])
    conn.execute("INSERT INTO a (data) VALUES (?)", [25])
    conn.execute("INSERT INTO a (data) VALUES (?)", [1])


print e.execute(select([A.data])).fetchall()

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Enable native boolean for SQL Server

SQL Server supports what SQLAlchemy calls "native boolean"
with its BIT type, as this type only accepts 0 or 1 and the
DBAPIs return its value as True/False. So the SQL Server
dialects now enable "native boolean" support, in that a
CHECK constraint is not generated for a :class:.Boolean
datatype. The only difference vs. other native boolean
is that there are no "true" / "false" constants so "1" and
"0" are still rendered here.

Tests are implicit in the existing suites.

Change-Id: I75bbcd549884099fb1a177e68667bf880c40fa7c
Fixes: #4061

→ 888f112

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

regression. SQL Server does not allow "WHERE 1", see #4250. we need a third state of boolean that is in between "native" and "numeric"

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

OK also, the drivers do seem to be returning True/False for this type natively, so we technically don't need the result processor either.

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

but....int_to_bool is not a big deal. not worth complicating it more.