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)