def test_dates( self, type_, args, kw, res, server_version, use_type_descriptor, driver ): "Exercise type specification for date types." if driver == "base": from sqlalchemy.dialects.mssql import base dialect = base.MSDialect() elif driver == "pyodbc": from sqlalchemy.dialects.mssql import pyodbc dialect = pyodbc.dialect() elif driver == "pymssql": from sqlalchemy.dialects.mssql import pymssql dialect = pymssql.dialect() else: assert False if server_version: dialect.server_version_info = server_version else: dialect.server_version_info = MS_2008_VERSION metadata = MetaData() typ = type_(*args, **kw) if use_type_descriptor: typ = dialect.type_descriptor(typ) col = Column("date_c", typ, nullable=None) date_table = Table("test_mssql_dates", metadata, col) gen = dialect.ddl_compiler(dialect, schema.CreateTable(date_table)) testing.eq_( gen.get_column_specification(col), "%s %s" % ( col.name, res, ), ) self.assert_(repr(col))
class IdentityInsertTest(fixtures.TestBase, AssertsCompiledSQL): __only_on__ = "mssql" __dialect__ = mssql.MSDialect() __backend__ = True @classmethod def setup_class(cls): global metadata, cattable metadata = MetaData(testing.db) cattable = Table( "cattable", metadata, Column("id", Integer), Column("description", String(50)), PrimaryKeyConstraint("id", name="PK_cattable"), ) def setup(self): metadata.create_all() def teardown(self): metadata.drop_all() def test_compiled(self): self.assert_compile( cattable.insert().values(id=9, description="Python"), "INSERT INTO cattable (id, description) " "VALUES (:id, :description)", ) def test_execute(self): with testing.db.connect() as conn: conn.execute(cattable.insert().values(id=9, description="Python")) cats = conn.execute(cattable.select().order_by(cattable.c.id)) eq_([(9, "Python")], list(cats)) result = conn.execute(cattable.insert().values(description="PHP")) eq_([10], result.inserted_primary_key) lastcat = conn.execute(cattable.select().order_by( desc(cattable.c.id))) eq_((10, "PHP"), lastcat.first()) def test_executemany(self): with testing.db.connect() as conn: conn.execute( cattable.insert(), [ { "id": 89, "description": "Python" }, { "id": 8, "description": "Ruby" }, { "id": 3, "description": "Perl" }, { "id": 1, "description": "Java" }, ], ) cats = conn.execute(cattable.select().order_by(cattable.c.id)) eq_( [(1, "Java"), (3, "Perl"), (8, "Ruby"), (89, "Python")], list(cats), ) conn.execute( cattable.insert(), [{ "description": "PHP" }, { "description": "Smalltalk" }], ) lastcats = conn.execute(cattable.select().order_by( desc(cattable.c.id)).limit(2)) eq_([(91, "Smalltalk"), (90, "PHP")], list(lastcats)) def test_insert_plain_param(self): with testing.db.connect() as conn: conn.execute(cattable.insert(), id=5) eq_(conn.scalar(select([cattable.c.id])), 5) def test_insert_values_key_plain(self): with testing.db.connect() as conn: conn.execute(cattable.insert().values(id=5)) eq_(conn.scalar(select([cattable.c.id])), 5) def test_insert_values_key_expression(self): with testing.db.connect() as conn: conn.execute(cattable.insert().values(id=literal(5))) eq_(conn.scalar(select([cattable.c.id])), 5) def test_insert_values_col_plain(self): with testing.db.connect() as conn: conn.execute(cattable.insert().values({cattable.c.id: 5})) eq_(conn.scalar(select([cattable.c.id])), 5) def test_insert_values_col_expression(self): with testing.db.connect() as conn: conn.execute(cattable.insert().values({cattable.c.id: literal(5)})) eq_(conn.scalar(select([cattable.c.id])), 5)
class IdentityInsertTest(fixtures.TablesTest, AssertsCompiledSQL): __only_on__ = "mssql" __dialect__ = mssql.MSDialect() __backend__ = True @classmethod def define_tables(cls, metadata): Table( "cattable", metadata, Column("id", Integer), Column("description", String(50)), PrimaryKeyConstraint("id", name="PK_cattable"), ) def test_compiled(self): cattable = self.tables.cattable self.assert_compile( cattable.insert().values(id=9, description="Python"), "INSERT INTO cattable (id, description) " "VALUES (:id, :description)", ) def test_execute(self, connection): conn = connection cattable = self.tables.cattable conn.execute(cattable.insert().values(id=9, description="Python")) cats = conn.execute(cattable.select().order_by(cattable.c.id)) eq_([(9, "Python")], list(cats)) result = conn.execute(cattable.insert().values(description="PHP")) eq_(result.inserted_primary_key, (10, )) lastcat = conn.execute(cattable.select().order_by(desc(cattable.c.id))) eq_((10, "PHP"), lastcat.first()) def test_executemany(self, connection): conn = connection cattable = self.tables.cattable conn.execute( cattable.insert(), [ { "id": 89, "description": "Python" }, { "id": 8, "description": "Ruby" }, { "id": 3, "description": "Perl" }, { "id": 1, "description": "Java" }, ], ) cats = conn.execute(cattable.select().order_by(cattable.c.id)) eq_( [(1, "Java"), (3, "Perl"), (8, "Ruby"), (89, "Python")], list(cats), ) conn.execute( cattable.insert(), [{ "description": "PHP" }, { "description": "Smalltalk" }], ) lastcats = conn.execute(cattable.select().order_by(desc( cattable.c.id)).limit(2)) eq_([(91, "Smalltalk"), (90, "PHP")], list(lastcats)) def test_insert_plain_param(self, connection): conn = connection cattable = self.tables.cattable conn.execute(cattable.insert(), dict(id=5)) eq_(conn.scalar(select(cattable.c.id)), 5) def test_insert_values_key_plain(self, connection): conn = connection cattable = self.tables.cattable conn.execute(cattable.insert().values(id=5)) eq_(conn.scalar(select(cattable.c.id)), 5) def test_insert_values_key_expression(self, connection): conn = connection cattable = self.tables.cattable conn.execute(cattable.insert().values(id=literal(5))) eq_(conn.scalar(select(cattable.c.id)), 5) def test_insert_values_col_plain(self, connection): conn = connection cattable = self.tables.cattable conn.execute(cattable.insert().values({cattable.c.id: 5})) eq_(conn.scalar(select(cattable.c.id)), 5) def test_insert_values_col_expression(self, connection): conn = connection cattable = self.tables.cattable conn.execute(cattable.insert().values({cattable.c.id: literal(5)})) eq_(conn.scalar(select(cattable.c.id)), 5) @testing.requires.schemas def test_insert_using_schema_translate(self, connection, metadata): t = Table( "t", metadata, Column("id", Integer), Column("description", String(50)), PrimaryKeyConstraint("id", name="PK_cattable"), schema=None, ) conn = connection.execution_options( schema_translate_map={None: config.test_schema}) metadata.create_all(conn) conn.execute(t.insert().values({"id": 1, "description": "descrip"})) eq_(conn.execute(select(t)).first(), (1, "descrip"))