def test_limit_offset_w_ambiguous_cols(self):
t = table('t', column('x', Integer), column('y', Integer))
cols = [t.c.x, t.c.x.label('q'), t.c.x.label('p'), t.c.y]
s = select(cols).where(t.c.x == 5).order_by(t.c.y).limit(10).offset(20)
self.assert_compile(
s, "SELECT anon_1.x, anon_1.q, anon_1.p, anon_1.y "
"FROM (SELECT t.x AS x, t.x AS q, t.x AS p, t.y AS y, "
"ROW_NUMBER() OVER (ORDER BY t.y) AS mssql_rn "
"FROM t "
"WHERE t.x = :x_1) AS anon_1 "
"WHERE mssql_rn > :param_1 AND mssql_rn <= :param_2 + :param_1",
checkparams={
'param_1': 20,
'param_2': 10,
'x_1': 5
})
c = s.compile(dialect=mssql.MSDialect())
eq_(len(c._result_columns), 4)
result_map = c._create_result_map()
for col in cols:
is_(result_map[col.key][1][0], col)
def test_limit_offset_with_correlated_order_by(self):
t1 = table('t1', column('x', Integer), column('y', Integer))
t2 = table('t2', column('x', Integer), column('y', Integer))
order_by = select([t2.c.y]).where(t1.c.x == t2.c.x).as_scalar()
s = select([t1]).where(t1.c.x == 5).order_by(order_by) \
.limit(10).offset(20)
self.assert_compile(
s,
"SELECT anon_1.x, anon_1.y "
"FROM (SELECT t1.x AS x, t1.y AS y, "
"ROW_NUMBER() OVER (ORDER BY "
"(SELECT t2.y FROM t2 WHERE t1.x = t2.x)"
") AS mssql_rn "
"FROM t1 "
"WHERE t1.x = :x_1) AS anon_1 "
"WHERE mssql_rn > :param_1 AND mssql_rn <= :param_2 + :param_1",
checkparams={'param_1': 20, 'param_2': 10, 'x_1': 5}
)
c = s.compile(dialect=mssql.MSDialect())
eq_(len(c._result_columns), 2)
assert t1.c.x in set(c._create_result_map()['x'][1])
assert t1.c.y in set(c._create_result_map()['y'][1])
def test_limit_zero_using_top(self):
t = table('t', column('x', Integer), column('y', Integer))
s = select([t]).where(t.c.x == 5).order_by(t.c.y).limit(0)
self.assert_compile(
s,
"SELECT TOP 0 t.x, t.y FROM t WHERE t.x = :x_1 ORDER BY t.y",
checkparams={'x_1': 5})
c = s.compile(dialect=mssql.MSDialect())
eq_(len(c._result_columns), 2)
assert t.c.x in set(c._create_result_map()['x'][1])
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):
cattable.insert().values(id=9, description='Python').execute()
cats = cattable.select().order_by(cattable.c.id).execute()
eq_([(9, 'Python')], list(cats))
result = cattable.insert().values(description='PHP').execute()
eq_([10], result.inserted_primary_key)
lastcat = cattable.select().order_by(desc(cattable.c.id)).execute()
eq_((10, 'PHP'), lastcat.first())
def test_executemany(self):
cattable.insert().execute([{'id': 89, 'description': 'Python'},
{'id': 8, 'description': 'Ruby'},
{'id': 3, 'description': 'Perl'},
{'id': 1, 'description': 'Java'}])
cats = cattable.select().order_by(cattable.c.id).execute()
eq_([(1, 'Java'), (3, 'Perl'), (8, 'Ruby'), (89, 'Python')],
list(cats))
cattable.insert().execute([{'description': 'PHP'},
{'description': 'Smalltalk'}])
lastcats = \
cattable.select().order_by(desc(cattable.c.id)).limit(2).execute()
eq_([(91, 'Smalltalk'), (90, 'PHP')], list(lastcats))
def test_limit_offset_using_window(self):
t = table('t', column('x', Integer), column('y', Integer))
s = select([t]).where(t.c.x == 5).order_by(t.c.y).limit(10).offset(20)
self.assert_compile(
s,
"SELECT anon_1.x, anon_1.y "
"FROM (SELECT t.x AS x, t.y AS y, "
"ROW_NUMBER() OVER (ORDER BY t.y) AS mssql_rn "
"FROM t "
"WHERE t.x = :x_1) AS anon_1 "
"WHERE mssql_rn > :param_1 AND mssql_rn <= :param_2 + :param_1",
checkparams={'param_1': 20, 'param_2': 10, 'x_1': 5}
)
c = s.compile(dialect=mssql.MSDialect())
eq_(len(c._result_columns), 2)
assert t.c.x in set(c._create_result_map()['x'][1])
assert t.c.y in set(c._create_result_map()['y'][1])
def test_offset_using_window(self):
t = table('t', column('x', Integer), column('y', Integer))
s = select([t]).where(t.c.x == 5).order_by(t.c.y).offset(20)
# test that the select is not altered with subsequent compile
# calls
for i in range(2):
self.assert_compile(
s,
"SELECT anon_1.x, anon_1.y FROM (SELECT t.x AS x, t.y "
"AS y, ROW_NUMBER() OVER (ORDER BY t.y) AS "
"mssql_rn FROM t WHERE t.x = :x_1) AS "
"anon_1 WHERE mssql_rn > :param_1",
checkparams={'param_1': 20, 'x_1': 5}
)
c = s.compile(dialect=mssql.MSDialect())
eq_(len(c._result_columns), 2)
assert t.c.x in set(c._create_result_map()['x'][1])
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)
