- changed status to resolved
SQL Server BIT is native boolean
Issue #4061
resolved
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()
Comments (1)
-
reporter - Log in to comment
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→ <<cset 888f112b78e1>>