def date_fixture(self, metadata): t = Table( "test_dates", metadata, Column("adate", Date), Column("atime1", Time), Column("atime2", Time), Column("adatetime", DateTime), Column("adatetimeoffset", DATETIMEOFFSET), Column("adatetimewithtimezone", DateTime(timezone=True)), ) d1 = datetime.date(2007, 10, 30) t1 = datetime.time(11, 2, 32) d2 = datetime.datetime(2007, 10, 30, 11, 2, 32) d3 = datetime.datetime( 2007, 10, 30, 11, 2, 32, 123456, util.timezone(datetime.timedelta(hours=-5)), ) return t, (d1, t1, d2, d3)
def test_datetime_offset( self, datetimeoffset_fixture, dto_param_value, expected_offset_hours, should_fail, ): t = datetimeoffset_fixture dto_param_value = dto_param_value() with testing.db.begin() as conn: if should_fail: assert_raises( sa.exc.DBAPIError, conn.execute, t.insert(), adatetimeoffset=dto_param_value, ) return conn.execute( t.insert(), adatetimeoffset=dto_param_value, ) row = conn.execute(t.select()).first() if dto_param_value is None: is_(row.adatetimeoffset, None) else: eq_( row.adatetimeoffset, datetime.datetime( 2007, 10, 30, 11, 2, 32, 123456, util.timezone( datetime.timedelta(hours=expected_offset_hours) ), ), )
class TypeRoundTripTest(fixtures.TestBase, AssertsExecutionResults, ComparesTables): __only_on__ = "mssql" __backend__ = True @classmethod def setup_class(cls): global metadata metadata = MetaData(testing.db) def teardown(self): metadata.drop_all() def test_decimal_notation(self): numeric_table = Table( "numeric_table", metadata, Column( "id", Integer, Sequence("numeric_id_seq", optional=True), primary_key=True, ), Column("numericcol", Numeric(precision=38, scale=20, asdecimal=True)), ) metadata.create_all() test_items = [ decimal.Decimal(d) for d in ( "1500000.00000000000000000000", "-1500000.00000000000000000000", "1500000", "0.0000000000000000002", "0.2", "-0.0000000000000000002", "-2E-2", "156666.458923543", "-156666.458923543", "1", "-1", "-1234", "1234", "2E-12", "4E8", "3E-6", "3E-7", "4.1", "1E-1", "1E-2", "1E-3", "1E-4", "1E-5", "1E-6", "1E-7", "1E-1", "1E-8", "0.2732E2", "-0.2432E2", "4.35656E2", "-02452E-2", "45125E-2", "1234.58965E-2", "1.521E+15", # previously, these were at -1E-25, which were inserted # cleanly however we only got back 20 digits of accuracy. # pyodbc as of 4.0.22 now disallows the silent truncation. "-1E-20", "1E-20", "1254E-20", "-1203E-20", "0", "-0.00", "-0", "4585E12", "000000000000000000012", "000000000000.32E12", "00000000000000.1E+12", # these are no longer accepted by pyodbc 4.0.22 but it seems # they were not actually round-tripping correctly before that # in any case # '-1E-25', # '1E-25', # '1254E-25', # '-1203E-25', # '000000000000.2E-32', ) ] with testing.db.connect() as conn: for value in test_items: result = conn.execute(numeric_table.insert(), dict(numericcol=value)) primary_key = result.inserted_primary_key returned = conn.scalar( select([numeric_table.c.numericcol ]).where(numeric_table.c.id == primary_key[0])) eq_(value, returned) def test_float(self): float_table = Table( "float_table", metadata, Column( "id", Integer, Sequence("numeric_id_seq", optional=True), primary_key=True, ), Column("floatcol", Float()), ) metadata.create_all() try: test_items = [ float(d) for d in ( "1500000.00000000000000000000", "-1500000.00000000000000000000", "1500000", "0.0000000000000000002", "0.2", "-0.0000000000000000002", "156666.458923543", "-156666.458923543", "1", "-1", "1234", "2E-12", "4E8", "3E-6", "3E-7", "4.1", "1E-1", "1E-2", "1E-3", "1E-4", "1E-5", "1E-6", "1E-7", "1E-8", ) ] for value in test_items: float_table.insert().execute(floatcol=value) except Exception as e: raise e # todo this should suppress warnings, but it does not @emits_warning_on("mssql+mxodbc", r".*does not have any indexes.*") def test_dates(self): "Exercise type specification for date types." columns = [ # column type, args, kwargs, expected ddl (mssql.MSDateTime, [], {}, "DATETIME", []), (types.DATE, [], {}, "DATE", [">=", (10, )]), (types.Date, [], {}, "DATE", [">=", (10, )]), (types.Date, [], {}, "DATETIME", ["<", (10, )], mssql.MSDateTime), (mssql.MSDate, [], {}, "DATE", [">=", (10, )]), (mssql.MSDate, [], {}, "DATETIME", ["<", (10, )], mssql.MSDateTime), (types.TIME, [], {}, "TIME", [">=", (10, )]), (types.Time, [], {}, "TIME", [">=", (10, )]), (mssql.MSTime, [], {}, "TIME", [">=", (10, )]), (mssql.MSTime, [1], {}, "TIME(1)", [">=", (10, )]), (types.Time, [], {}, "DATETIME", ["<", (10, )], mssql.MSDateTime), (mssql.MSTime, [], {}, "TIME", [">=", (10, )]), (mssql.MSSmallDateTime, [], {}, "SMALLDATETIME", []), (mssql.MSDateTimeOffset, [], {}, "DATETIMEOFFSET", [">=", (10, )]), ( mssql.MSDateTimeOffset, [1], {}, "DATETIMEOFFSET(1)", [">=", (10, )], ), (mssql.MSDateTime2, [], {}, "DATETIME2", [">=", (10, )]), (mssql.MSDateTime2, [0], {}, "DATETIME2(0)", [">=", (10, )]), (mssql.MSDateTime2, [1], {}, "DATETIME2(1)", [">=", (10, )]), ] table_args = ["test_mssql_dates", metadata] for index, spec in enumerate(columns): type_, args, kw, res, requires = spec[0:5] if (requires and testing._is_excluded("mssql", *requires) or not requires): c = Column("c%s" % index, type_(*args, **kw), nullable=None) testing.db.dialect.type_descriptor(c.type) table_args.append(c) dates_table = Table(*table_args) gen = testing.db.dialect.ddl_compiler(testing.db.dialect, schema.CreateTable(dates_table)) for col in dates_table.c: index = int(col.name[1:]) testing.eq_( gen.get_column_specification(col), "%s %s" % (col.name, columns[index][3]), ) self.assert_(repr(col)) dates_table.create(checkfirst=True) reflected_dates = Table("test_mssql_dates", MetaData(testing.db), autoload=True) for col in reflected_dates.c: self.assert_types_base(col, dates_table.c[col.key]) @testing.metadata_fixture() def date_fixture(self, metadata): t = Table( "test_dates", metadata, Column("adate", Date), Column("atime1", Time), Column("atime2", Time), Column("adatetime", DateTime), Column("adatetimeoffset", DATETIMEOFFSET), ) d1 = datetime.date(2007, 10, 30) t1 = datetime.time(11, 2, 32) d2 = datetime.datetime(2007, 10, 30, 11, 2, 32) return t, (d1, t1, d2) def test_date_roundtrips(self, date_fixture): t, (d1, t1, d2) = date_fixture with testing.db.begin() as conn: conn.execute(t.insert(), adate=d1, adatetime=d2, atime1=t1, atime2=d2) row = conn.execute(t.select()).first() eq_( (row.adate, row.adatetime, row.atime1, row.atime2), (d1, d2, t1, d2.time()), ) @testing.metadata_fixture() def datetimeoffset_fixture(self, metadata): t = Table( "test_dates", metadata, Column("adatetimeoffset", DATETIMEOFFSET), ) return t @testing.combinations( ("dto_param_none", lambda: None, None, False), ( "dto_param_datetime_aware_positive", lambda: datetime.datetime( 2007, 10, 30, 11, 2, 32, 123456, util.timezone(datetime.timedelta(hours=1)), ), 1, False, ), ( "dto_param_datetime_aware_negative", lambda: datetime.datetime( 2007, 10, 30, 11, 2, 32, 123456, util.timezone(datetime.timedelta(hours=-5)), ), -5, False, ), ( "dto_param_datetime_aware_seconds_frac_fail", lambda: datetime.datetime( 2007, 10, 30, 11, 2, 32, 123456, util.timezone(datetime.timedelta(seconds=4000)), ), None, True, testing.requires.python37, ), ( "dto_param_datetime_naive", lambda: datetime.datetime(2007, 10, 30, 11, 2, 32, 123456), 0, False, ), ( "dto_param_string_one", lambda: "2007-10-30 11:02:32.123456 +01:00", 1, False, ), # wow ( "dto_param_string_two", lambda: "October 30, 2007 11:02:32.123456", 0, False, ), ("dto_param_string_invalid", lambda: "this is not a date", 0, True), id_="iaaa", argnames="dto_param_value, expected_offset_hours, should_fail", ) def test_datetime_offset( self, datetimeoffset_fixture, dto_param_value, expected_offset_hours, should_fail, ): t = datetimeoffset_fixture dto_param_value = dto_param_value() with testing.db.begin() as conn: if should_fail: assert_raises( sa.exc.DBAPIError, conn.execute, t.insert(), adatetimeoffset=dto_param_value, ) return conn.execute( t.insert(), adatetimeoffset=dto_param_value, ) row = conn.execute(t.select()).first() if dto_param_value is None: is_(row.adatetimeoffset, None) else: eq_( row.adatetimeoffset, datetime.datetime( 2007, 10, 30, 11, 2, 32, 123456, util.timezone( datetime.timedelta(hours=expected_offset_hours)), ), ) @emits_warning_on("mssql+mxodbc", r".*does not have any indexes.*") @testing.provide_metadata def _test_binary_reflection(self, deprecate_large_types): "Exercise type specification for binary types." columns = [ # column type, args, kwargs, expected ddl from reflected (mssql.MSBinary, [], {}, "BINARY(1)"), (mssql.MSBinary, [10], {}, "BINARY(10)"), (types.BINARY, [], {}, "BINARY(1)"), (types.BINARY, [10], {}, "BINARY(10)"), (mssql.MSVarBinary, [], {}, "VARBINARY(max)"), (mssql.MSVarBinary, [10], {}, "VARBINARY(10)"), (types.VARBINARY, [10], {}, "VARBINARY(10)"), (types.VARBINARY, [], {}, "VARBINARY(max)"), (mssql.MSImage, [], {}, "IMAGE"), (mssql.IMAGE, [], {}, "IMAGE"), ( types.LargeBinary, [], {}, "IMAGE" if not deprecate_large_types else "VARBINARY(max)", ), ] metadata = self.metadata metadata.bind = engines.testing_engine( options={"deprecate_large_types": deprecate_large_types}) table_args = ["test_mssql_binary", metadata] for index, spec in enumerate(columns): type_, args, kw, res = spec table_args.append( Column("c%s" % index, type_(*args, **kw), nullable=None)) binary_table = Table(*table_args) metadata.create_all() reflected_binary = Table("test_mssql_binary", MetaData(testing.db), autoload=True) for col, spec in zip(reflected_binary.c, columns): eq_( str(col.type), spec[3], "column %s %s != %s" % (col.key, str(col.type), spec[3]), ) c1 = testing.db.dialect.type_descriptor(col.type).__class__ c2 = testing.db.dialect.type_descriptor( binary_table.c[col.name].type).__class__ assert issubclass( c1, c2), "column %s: %r is not a subclass of %r" % (col.key, c1, c2) if binary_table.c[col.name].type.length: testing.eq_(col.type.length, binary_table.c[col.name].type.length) def test_binary_reflection_legacy_large_types(self): self._test_binary_reflection(False) @testing.only_on("mssql >= 11") def test_binary_reflection_sql2012_large_types(self): self._test_binary_reflection(True) def test_autoincrement(self): Table( "ai_1", metadata, Column("int_y", Integer, primary_key=True, autoincrement=True), Column("int_n", Integer, DefaultClause("0"), primary_key=True), ) Table( "ai_2", metadata, Column("int_y", Integer, primary_key=True, autoincrement=True), Column("int_n", Integer, DefaultClause("0"), primary_key=True), ) Table( "ai_3", metadata, Column("int_n", Integer, DefaultClause("0"), primary_key=True), Column("int_y", Integer, primary_key=True, autoincrement=True), ) Table( "ai_4", metadata, Column("int_n", Integer, DefaultClause("0"), primary_key=True), Column("int_n2", Integer, DefaultClause("0"), primary_key=True), ) Table( "ai_5", metadata, Column("int_y", Integer, primary_key=True, autoincrement=True), Column("int_n", Integer, DefaultClause("0"), primary_key=True), ) Table( "ai_6", metadata, Column("o1", String(1), DefaultClause("x"), primary_key=True), Column("int_y", Integer, primary_key=True, autoincrement=True), ) Table( "ai_7", metadata, Column("o1", String(1), DefaultClause("x"), primary_key=True), Column("o2", String(1), DefaultClause("x"), primary_key=True), Column("int_y", Integer, autoincrement=True, primary_key=True), ) Table( "ai_8", metadata, Column("o1", String(1), DefaultClause("x"), primary_key=True), Column("o2", String(1), DefaultClause("x"), primary_key=True), ) metadata.create_all() table_names = [ "ai_1", "ai_2", "ai_3", "ai_4", "ai_5", "ai_6", "ai_7", "ai_8", ] mr = MetaData(testing.db) for name in table_names: tbl = Table(name, mr, autoload=True) tbl = metadata.tables[name] # test that the flag itself reflects appropriately for col in tbl.c: if "int_y" in col.name: is_(col.autoincrement, True) is_(tbl._autoincrement_column, col) else: eq_(col.autoincrement, "auto") is_not_(tbl._autoincrement_column, col) # mxodbc can't handle scope_identity() with DEFAULT VALUES if testing.db.driver == "mxodbc": eng = [ engines.testing_engine( options={"implicit_returning": True}) ] else: eng = [ engines.testing_engine( options={"implicit_returning": False}), engines.testing_engine( options={"implicit_returning": True}), ] for counter, engine in enumerate(eng): with engine.begin() as conn: conn.execute(tbl.insert()) if "int_y" in tbl.c: eq_( conn.execute(select([tbl.c.int_y])).scalar(), counter + 1, ) assert (list(conn.execute( tbl.select()).first()).count(counter + 1) == 1) else: assert 1 not in list( conn.execute(tbl.select()).first()) conn.execute(tbl.delete())
def test_date_roundtrip(self): t = Table( "test_dates", metadata, Column( "id", Integer, Sequence("datetest_id_seq", optional=True), primary_key=True, ), Column("adate", Date), Column("atime", Time), Column("adatetime", DateTime), Column("adatetimeoffset", DATETIMEOFFSET), ) metadata.create_all() d1 = datetime.date(2007, 10, 30) t1 = datetime.time(11, 2, 32) d2 = datetime.datetime(2007, 10, 30, 11, 2, 32) dto = datetime.datetime( 2007, 10, 30, 11, 2, 32, 0, util.timezone(datetime.timedelta(hours=1)), ) t.insert().execute(adate=d1, adatetime=d2, atime=t1, adatetimeoffset=dto) # NOTE: this previously passed 'd2' for "adate" even though # "adate" is a date column; we asserted that it truncated w/o issue. # As of pyodbc 4.0.22, this is no longer accepted, was accepted # in 4.0.21. See also the new pyodbc assertions regarding numeric # precision. t.insert().execute(adate=d1, adatetime=d2, atime=d2, adatetimeoffset=dto) x = t.select().execute().fetchall()[0] self.assert_(x.adate.__class__ == datetime.date) self.assert_(x.atime.__class__ == datetime.time) self.assert_(x.adatetime.__class__ == datetime.datetime) self.assert_(x.adatetimeoffset.__class__ == datetime.datetime) t.delete().execute() t.insert().execute(adate=d1, adatetime=d2, atime=t1, adatetimeoffset=dto) eq_( select( [t.c.adate, t.c.atime, t.c.adatetime, t.c.adatetimeoffset], t.c.adate == d1, ).execute().fetchall(), [(d1, t1, d2, dto)], )