Here are the examples of the python api sqlalchemy.over taken from open source projects. By voting up you can indicate which examples are most useful and appropriate.
2 Examples
0
Source : test_compiler.py
with Apache License 2.0
from gethue
with Apache License 2.0
from gethue
def test_over(self):
self.assert_compile(func.row_number().over(), "row_number() OVER ()")
self.assert_compile(
func.row_number().over(
order_by=[table1.c.name, table1.c.description]
),
"row_number() OVER (ORDER BY mytable.name, mytable.description)",
)
self.assert_compile(
func.row_number().over(
partition_by=[table1.c.name, table1.c.description]
),
"row_number() OVER (PARTITION BY mytable.name, "
"mytable.description)",
)
self.assert_compile(
func.row_number().over(
partition_by=[table1.c.name], order_by=[table1.c.description]
),
"row_number() OVER (PARTITION BY mytable.name "
"ORDER BY mytable.description)",
)
self.assert_compile(
func.row_number().over(
partition_by=table1.c.name, order_by=table1.c.description
),
"row_number() OVER (PARTITION BY mytable.name "
"ORDER BY mytable.description)",
)
self.assert_compile(
func.row_number().over(
partition_by=table1.c.name,
order_by=[table1.c.name, table1.c.description],
),
"row_number() OVER (PARTITION BY mytable.name "
"ORDER BY mytable.name, mytable.description)",
)
self.assert_compile(
func.row_number().over(
partition_by=[], order_by=[table1.c.name, table1.c.description]
),
"row_number() OVER (ORDER BY mytable.name, mytable.description)",
)
self.assert_compile(
func.row_number().over(
partition_by=[table1.c.name, table1.c.description], order_by=[]
),
"row_number() OVER (PARTITION BY mytable.name, "
"mytable.description)",
)
self.assert_compile(
func.row_number().over(partition_by=[], order_by=[]),
"row_number() OVER ()",
)
self.assert_compile(
select(
[
func.row_number()
.over(order_by=table1.c.description)
.label("foo")
]
),
"SELECT row_number() OVER (ORDER BY mytable.description) "
"AS foo FROM mytable",
)
# test from_obj generation.
# from func:
self.assert_compile(
select(
[func.max(table1.c.name).over(partition_by=["description"])]
),
"SELECT max(mytable.name) OVER (PARTITION BY mytable.description) "
"AS anon_1 FROM mytable",
)
# from partition_by
self.assert_compile(
select([func.row_number().over(partition_by=[table1.c.name])]),
"SELECT row_number() OVER (PARTITION BY mytable.name) "
"AS anon_1 FROM mytable",
)
# from order_by
self.assert_compile(
select([func.row_number().over(order_by=table1.c.name)]),
"SELECT row_number() OVER (ORDER BY mytable.name) "
"AS anon_1 FROM mytable",
)
# this tests that _from_objects
# concantenates OK
self.assert_compile(
select([column("x") + over(func.foo())]),
"SELECT x + foo() OVER () AS anon_1",
)
# test a reference to a label that in the referecned selectable;
# this resolves
expr = (table1.c.myid + 5).label("sum")
stmt = select([expr]).alias()
self.assert_compile(
select([stmt.c.sum, func.row_number().over(order_by=stmt.c.sum)]),
"SELECT anon_1.sum, row_number() OVER (ORDER BY anon_1.sum) "
"AS anon_2 FROM (SELECT mytable.myid + :myid_1 AS sum "
"FROM mytable) AS anon_1",
)
# test a reference to a label that's at the same level as the OVER
# in the columns clause; doesn't resolve
expr = (table1.c.myid + 5).label("sum")
self.assert_compile(
select([expr, func.row_number().over(order_by=expr)]),
"SELECT mytable.myid + :myid_1 AS sum, "
"row_number() OVER "
"(ORDER BY mytable.myid + :myid_1) AS anon_1 FROM mytable",
)
def test_over_framespec(self):
0
Source : test_compiler.py
with MIT License
from sqlalchemy
with MIT License
from sqlalchemy
def test_over(self):
self.assert_compile(func.row_number().over(), "row_number() OVER ()")
self.assert_compile(
func.row_number().over(
order_by=[table1.c.name, table1.c.description]
),
"row_number() OVER (ORDER BY mytable.name, mytable.description)",
)
self.assert_compile(
func.row_number().over(
partition_by=[table1.c.name, table1.c.description]
),
"row_number() OVER (PARTITION BY mytable.name, "
"mytable.description)",
)
self.assert_compile(
func.row_number().over(
partition_by=[table1.c.name], order_by=[table1.c.description]
),
"row_number() OVER (PARTITION BY mytable.name "
"ORDER BY mytable.description)",
)
self.assert_compile(
func.row_number().over(
partition_by=table1.c.name, order_by=table1.c.description
),
"row_number() OVER (PARTITION BY mytable.name "
"ORDER BY mytable.description)",
)
self.assert_compile(
func.row_number().over(
partition_by=table1.c.name,
order_by=[table1.c.name, table1.c.description],
),
"row_number() OVER (PARTITION BY mytable.name "
"ORDER BY mytable.name, mytable.description)",
)
self.assert_compile(
func.row_number().over(
partition_by=[], order_by=[table1.c.name, table1.c.description]
),
"row_number() OVER (ORDER BY mytable.name, mytable.description)",
)
self.assert_compile(
func.row_number().over(
partition_by=[table1.c.name, table1.c.description], order_by=[]
),
"row_number() OVER (PARTITION BY mytable.name, "
"mytable.description)",
)
self.assert_compile(
func.row_number().over(partition_by=[], order_by=[]),
"row_number() OVER ()",
)
self.assert_compile(
select(
func.row_number()
.over(order_by=table1.c.description)
.label("foo")
),
"SELECT row_number() OVER (ORDER BY mytable.description) "
"AS foo FROM mytable",
)
# test from_obj generation.
# from func:
self.assert_compile(
select(func.max(table1.c.name).over(partition_by=["description"])),
"SELECT max(mytable.name) OVER (PARTITION BY mytable.description) "
"AS anon_1 FROM mytable",
)
# from partition_by
self.assert_compile(
select(func.row_number().over(partition_by=[table1.c.name])),
"SELECT row_number() OVER (PARTITION BY mytable.name) "
"AS anon_1 FROM mytable",
)
# from order_by
self.assert_compile(
select(func.row_number().over(order_by=table1.c.name)),
"SELECT row_number() OVER (ORDER BY mytable.name) "
"AS anon_1 FROM mytable",
)
# this tests that _from_objects
# concantenates OK
self.assert_compile(
select(column("x") + over(func.foo())),
"SELECT x + foo() OVER () AS anon_1",
)
# test a reference to a label that in the referecned selectable;
# this resolves
expr = (table1.c.myid + 5).label("sum")
stmt = select(expr).alias()
self.assert_compile(
select(stmt.c.sum, func.row_number().over(order_by=stmt.c.sum)),
"SELECT anon_1.sum, row_number() OVER (ORDER BY anon_1.sum) "
"AS anon_2 FROM (SELECT mytable.myid + :myid_1 AS sum "
"FROM mytable) AS anon_1",
)
# test a reference to a label that's at the same level as the OVER
# in the columns clause; doesn't resolve
expr = (table1.c.myid + 5).label("sum")
self.assert_compile(
select(expr, func.row_number().over(order_by=expr)),
"SELECT mytable.myid + :myid_1 AS sum, "
"row_number() OVER "
"(ORDER BY mytable.myid + :myid_1) AS anon_1 FROM mytable",
)
def test_over_framespec(self):