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"))
