def test_select_field_on_alias():
result = compute_up(t.amount, select(s).limit(10).alias('foo'))
assert normalize(str(select(result))) == normalize("""
SELECT foo.amount
FROM (SELECT accounts.name AS name, accounts.amount AS amount, accounts.id AS id
FROM accounts
LIMIT :param_1) as foo""")
def test_multi_column_join():
metadata = sa.MetaData()
lhs = sa.Table('aaa', metadata,
sa.Column('x', sa.Integer),
sa.Column('y', sa.Integer),
sa.Column('z', sa.Integer))
rhs = sa.Table('bbb', metadata,
sa.Column('w', sa.Integer),
sa.Column('x', sa.Integer),
sa.Column('y', sa.Integer))
L = symbol('L', 'var * {x: int, y: int, z: int}')
R = symbol('R', 'var * {w: int, x: int, y: int}')
joined = join(L, R, ['x', 'y'])
expected = lhs.join(rhs, (lhs.c.x == rhs.c.x)
& (lhs.c.y == rhs.c.y))
expected = select(list(unique(expected.columns, key=lambda c:
c.name))).select_from(expected)
result = compute(joined, {L: lhs, R: rhs})
assert str(result) == str(expected)
assert str(select(result)) == str(select(expected))
# Schemas match
print(result.c.keys())
print(joined.fields)
assert list(result.c.keys()) == list(joined.fields)
def test_join():
metadata = sa.MetaData()
lhs = sa.Table('amounts', metadata,
sa.Column('name', sa.String),
sa.Column('amount', sa.Integer))
rhs = sa.Table('ids', metadata,
sa.Column('name', sa.String),
sa.Column('id', sa.Integer))
expected = lhs.join(rhs, lhs.c.name == rhs.c.name)
expected = select(list(unique(expected.columns, key=lambda c:
c.name))).select_from(expected)
L = TableSymbol('L', '{name: string, amount: int}')
R = TableSymbol('R', '{name: string, id: int}')
joined = join(L, R, 'name')
result = compute(joined, {L: lhs, R: rhs})
assert str(result) == str(expected)
assert str(select(result)) == str(select(expected))
# Schemas match
assert list(result.c.keys()) == list(joined.columns)
def test_join():
metadata = sa.MetaData()
lhs = sa.Table('amounts', metadata, sa.Column('name', sa.String),
sa.Column('amount', sa.Integer))
rhs = sa.Table('ids', metadata, sa.Column('name', sa.String),
sa.Column('id', sa.Integer))
expected = lhs.join(rhs, lhs.c.name == rhs.c.name)
expected = select(list(unique(expected.columns,
key=lambda c: c.name))).select_from(expected)
L = symbol('L', 'var * {name: string, amount: int}')
R = symbol('R', 'var * {name: string, id: int}')
joined = join(L, R, 'name')
result = compute(joined, {L: lhs, R: rhs})
assert normalize(str(result)) == normalize("""
SELECT amounts.name, amounts.amount, ids.id
FROM amounts JOIN ids ON amounts.name = ids.name""")
assert str(select(result)) == str(select(expected))
# Schemas match
assert list(result.c.keys()) == list(joined.fields)
# test sort on join
result = compute(joined.sort('amount'), {L: lhs, R: rhs})
assert normalize(str(result)) == normalize("""
SELECT amounts.name, amounts.amount, ids.id
FROM amounts JOIN ids ON amounts.name = ids.name
ORDER BY amounts.amount""")
def test_multi_column_join():
metadata = sa.MetaData()
lhs = sa.Table('aaa', metadata,
sa.Column('x', sa.Integer),
sa.Column('y', sa.Integer),
sa.Column('z', sa.Integer))
rhs = sa.Table('bbb', metadata,
sa.Column('w', sa.Integer),
sa.Column('x', sa.Integer),
sa.Column('y', sa.Integer))
L = symbol('L', 'var * {x: int, y: int, z: int}')
R = symbol('R', 'var * {w: int, x: int, y: int}')
joined = join(L, R, ['x', 'y'])
expected = lhs.join(rhs, (lhs.c.x == rhs.c.x)
& (lhs.c.y == rhs.c.y))
expected = select(list(unique(expected.columns, key=lambda c:
c.name))).select_from(expected)
result = compute(joined, {L: lhs, R: rhs})
assert str(result) == str(expected)
assert str(select(result)) == str(select(expected))
# Schemas match
print(result.c.keys())
print(joined.fields)
assert list(result.c.keys()) == list(joined.fields)
def test_join():
metadata = sa.MetaData()
lhs = sa.Table('amounts', metadata,
sa.Column('name', sa.String),
sa.Column('amount', sa.Integer))
rhs = sa.Table('ids', metadata,
sa.Column('name', sa.String),
sa.Column('id', sa.Integer))
expected = lhs.join(rhs, lhs.c.name == rhs.c.name)
expected = select(list(unique(expected.columns, key=lambda c:
c.name))).select_from(expected)
L = Symbol('L', 'var * {name: string, amount: int}')
R = Symbol('R', 'var * {name: string, id: int}')
joined = join(L, R, 'name')
result = compute(joined, {L: lhs, R: rhs})
assert normalize(str(result)) == normalize("""
SELECT amounts.name, amounts.amount, ids.id
FROM amounts JOIN ids ON amounts.name = ids.name""")
assert str(select(result)) == str(select(expected))
# Schemas match
assert list(result.c.keys()) == list(joined.fields)
def test_select_field_on_alias():
result = compute_up(t.amount, select(s).limit(10).alias('foo'))
assert normalize(str(select(result))) == normalize("""
SELECT foo.amount
FROM (SELECT accounts.name AS name, accounts.amount AS amount, accounts.id AS id
FROM accounts
LIMIT :param_1) as foo""")
def test_count_on_table():
assert normalize(str(select(compute(t.count(), s)))) == normalize("""
SELECT count(accounts.id) as count_1
FROM accounts""")
assert normalize(str(select(compute(t[t.amount > 0].count(), s)))) == \
normalize("""
SELECT count(accounts.id) as count_1
FROM accounts
WHERE accounts.amount > :amount_1""")
def test_count_on_table():
assert normalize(str(select(compute(t.count(), s)))) == normalize("""
SELECT count(accounts.id) as count_1
FROM accounts""")
assert normalize(str(select(compute(t[t.amount > 0].count(), s)))) == \
normalize("""
SELECT count(accounts.id) as count_1
FROM accounts
WHERE accounts.amount > :amount_1""")
def test_lower_column():
metadata = sa.MetaData()
name = sa.Table(
'name',
metadata,
sa.Column('id', sa.Integer),
sa.Column('name', sa.String),
)
city = sa.Table(
'place',
metadata,
sa.Column('id', sa.Integer),
sa.Column('city', sa.String),
sa.Column('country', sa.String),
)
tname = symbol('name', discover(name))
tcity = symbol('city', discover(city))
assert lower_column(name.c.id) is name.c.id
assert lower_column(select(name).c.id) is name.c.id
j = name.join(city, name.c.id == city.c.id)
col = [c for c in j.columns if c.name == 'country'][0]
assert lower_column(col) is city.c.country
def test_join_complex_clean():
metadata = sa.MetaData()
name = sa.Table('name', metadata,
sa.Column('id', sa.Integer),
sa.Column('name', sa.String),
)
city = sa.Table('place', metadata,
sa.Column('id', sa.Integer),
sa.Column('city', sa.String),
sa.Column('country', sa.String),
)
sel = select(name).where(name.c.id > 10)
tname = symbol('name', discover(name))
tcity = symbol('city', discover(city))
ns = {tname: name, tcity: city}
expr = join(tname[tname.id > 0], tcity, 'id')
result = compute(expr, ns)
assert normalize(str(result)) == normalize("""
SELECT name.id, name.name, place.city, place.country
FROM name JOIN place ON name.id = place.id
WHERE name.id > :id_1""")
def test_reductions_on_complex_selections():
assert normalize(str(select(compute(t[t.amount > 0].id.sum(), s)))) == \
normalize("""
SELECT sum(accounts.id) as id_sum
FROM accounts
WHERE accounts.amount > :amount_1 """)
def test_columnwise_on_complex_selection():
assert normalize(str(select(compute(t[t.amount > 0].amount + 1, s)))) == \
normalize("""
SELECT accounts.amount + :amount_1 AS anon_1
FROM accounts
WHERE accounts.amount > :amount_2
""")
def test_reductions_on_complex_selections():
assert normalize(str(select(compute(t[t.amount > 0].id.sum(), s)))) == \
normalize("""
SELECT sum(accounts.id) as id_sum
FROM accounts
WHERE accounts.amount > :amount_1 """)
def test_count_on_table():
result = select(compute(t.count(), s))
assert normalize(str(result)) == normalize("""
SELECT count(accounts.id) as count_1
FROM accounts""")
result = select(compute(t[t.amount > 0].count(), s))
assert (normalize(str(result)) == normalize("""
SELECT count(accounts.id) as count_1
FROM accounts
WHERE accounts.amount > :amount_1""")
or normalize(str(result)) == normalize("""
SELECT count(alias.id) as count
FROM (SELECT accounts.name AS name, accounts.amount AS amount, accounts.id AS id
FROM accounts
WHERE accounts.amount > :amount_1) as alias"""))
def test_columnwise_on_complex_selection():
assert normalize(str(select(compute(t[t.amount > 0].amount + 1, s)))) == \
normalize("""
SELECT accounts.amount + :amount_1 AS anon_1
FROM accounts
WHERE accounts.amount > :amount_2
""")
def test_join_complex_clean():
metadata = sa.MetaData()
name = sa.Table('name', metadata,
sa.Column('id', sa.Integer),
sa.Column('name', sa.String),
)
city = sa.Table('place', metadata,
sa.Column('id', sa.Integer),
sa.Column('city', sa.String),
sa.Column('country', sa.String),
)
sel = select(name).where(name.c.id > 10)
tname = symbol('name', discover(name))
tcity = symbol('city', discover(city))
ns = {tname: name, tcity: city}
expr = join(tname[tname.id > 0], tcity, 'id')
result = compute(expr, ns)
expected1 = """
SELECT name.id, name.name, place.city, place.country
FROM name JOIN place ON name.id = place.id
WHERE name.id > :id_1"""
expected2 = """
SELECT alias.id, alias.name, place.city, place.country
FROM (SELECT name.id as id, name.name AS name
FROM name
WHERE name.id > :id_1) AS alias
JOIN place ON alias.id = place.id"""
assert (normalize(str(result)) == normalize(expected1) or
normalize(str(result)) == normalize(expected2))
def test_reductions_on_complex_selections():
assert normalize(str(select(compute(t[t.amount > 0].id.sum(), s)))) == \
normalize("""
with alias as
(select accounts.id as id
from
accounts
where
accounts.amount > :amount_1)
select sum(alias.id) as id_sum from alias""")
def test_by_head():
t2 = t.head(100)
expr = by(t2, t2['name'], t2['amount'].sum())
result = compute(expr, s)
s2 = select(s).limit(100)
expected = sa.select([s2.c.name,
sa.sql.functions.sum(s2.c.amount).label('amount')]
).group_by(s2.c.name)
assert str(result) == str(expected)
def test_reductions_on_complex_selections():
assert normalize(str(select(compute(t[t.amount > 0].id.sum(), s)))) == \
normalize("""
with alias as
(select accounts.id as id
from
accounts
where
accounts.amount > :amount_1)
select sum(alias.id) as id_sum from alias""")
def test_count_on_table():
result = select(compute(t.count(), s))
assert normalize(str(result)) == normalize("""
SELECT count(accounts.id) as count_1
FROM accounts""")
result = select(compute(t[t.amount > 0].count(), s))
assert (
normalize(str(result)) == normalize("""
SELECT count(accounts.id) as count_1
FROM accounts
WHERE accounts.amount > :amount_1""")
or
normalize(str(result)) == normalize("""
SELECT count(alias.id) as count
FROM (SELECT accounts.name AS name, accounts.amount AS amount, accounts.id AS id
FROM accounts
WHERE accounts.amount > :amount_1) as alias"""))
def test_by_head():
t2 = t.head(100)
expr = by(t2['name'], t2['amount'].sum())
result = compute(expr, s)
s2 = select(s).limit(100)
# expected = sa.select([s2.c.name,
# sa.sql.functions.sum(s2.c.amount).label('amount_sum')]
# ).group_by(s2.c.name)
expected = """
SELECT accounts.name, sum(accounts.amount) as amount_sum
FROM accounts
GROUP by accounts.name
LIMIT :param_1"""
assert normalize(str(result)) == normalize(str(expected))
def test_union():
ts = [TableSymbol('t_%d' % i, '{name: string, amount: int, id: int}')
for i in [1, 2, 3]]
ss = [sa.Table('accounts_%d' % i, metadata,
sa.Column('name', sa.String),
sa.Column('amount', sa.Integer),
sa.Column('id', sa.Integer, primary_key=True)) for i in [1, 2, 3]]
expr = union(*ts)
result = str(select(compute(expr, dict(zip(ts, ss)))))
assert "SELECT name, amount, id" in str(result)
assert "accounts_1 UNION accounts_2 UNION accounts_3" in str(result)
def test_slice():
start, stop, step = 50, 100, 1
result = str(compute(t[start:stop], s))
# Verifies that compute is translating the query correctly
assert result == str(select(s).offset(start).limit(stop))
# Verifies the query against expected SQL query
expected = """
SELECT accounts.name, accounts.amount, accounts.id FROM accounts
LIMIT :param_1 OFFSET :param_2
"""
assert normalize(str(result)) == normalize(str(expected))
# Step size of 1 should be alright
compute(t[start:stop:step], s)
def test_slice():
start, stop, step = 50, 100, 1
result = str(compute(t[start:stop], s))
# Verifies that compute is translating the query correctly
assert result == str(select(s).offset(start).limit(stop))
# Verifies the query against expected SQL query
expected = """
SELECT accounts.name, accounts.amount, accounts.id FROM accounts
LIMIT :param_1 OFFSET :param_2
"""
assert normalize(str(result)) == normalize(str(expected))
# Step size of 1 should be alright
compute(t[start:stop:step], s)
def test_union():
metadata = sa.MetaData()
ts = [
Symbol('t_%d' % i, 'var * {name: string, amount: int, id: int}')
for i in [1, 2, 3]
]
ss = [
sa.Table('accounts_%d' % i, metadata, sa.Column('name', sa.String),
sa.Column('amount', sa.Integer),
sa.Column('id', sa.Integer, primary_key=True))
for i in [1, 2, 3]
]
expr = union(*ts)
result = str(select(compute(expr, dict(zip(ts, ss)))))
assert "SELECT name, amount, id" in str(result)
assert "accounts_1 UNION accounts_2 UNION accounts_3" in str(result)
def test_lower_column():
metadata = sa.MetaData()
name = sa.Table('name', metadata,
sa.Column('id', sa.Integer),
sa.Column('name', sa.String),
)
city = sa.Table('place', metadata,
sa.Column('id', sa.Integer),
sa.Column('city', sa.String),
sa.Column('country', sa.String),
)
tname = symbol('name', discover(name))
tcity = symbol('city', discover(city))
assert lower_column(name.c.id) is name.c.id
assert lower_column(select(name).c.id) is name.c.id
j = name.join(city, name.c.id == city.c.id)
col = [c for c in j.columns if c.name == 'country'][0]
assert lower_column(col) is city.c.country
def compute(t, ddesc):
query = select(compute(t, ddesc.table)) # Get the query out
with ddesc.engine.connect() as conn:
result = conn.execute(query).fetchall() # Use SQLAlchemy to actually perform the query
return result
def test_relabel():
result = compute(t.relabel({'name': 'NAME', 'id': 'ID'}), s)
expected = select([s.c.name.label('NAME'), s.c.amount, s.c.id.label('ID')])
assert str(result) == str(expected)
def test_head():
assert str(compute(t.head(2), s)) == str(select(s).limit(2))
def test_sort():
assert str(compute(t.sort('amount'), s)) == \
str(select(s).order_by(s.c.amount))
assert str(compute(t.sort('amount', ascending=False), s)) == \
str(select(s).order_by(sqlalchemy.desc(s.c.amount)))
def test_multicolumn_sort():
assert str(compute(t.sort(['amount', 'id']), s)) == \
str(select(s).order_by(sa.asc(s.c.amount), sa.asc(s.c.id)))
assert str(compute(t.sort(['amount', 'id'], ascending=False), s)) == \
str(select(s).order_by(sa.desc(s.c.amount), sa.desc(s.c.id)))
def test_sort():
assert str(compute(t.sort('amount'), s)) == \
str(select(s).order_by(s.c.amount))
assert str(compute(t.sort('amount', ascending=False), s)) == \
str(select(s).order_by(sqlalchemy.desc(s.c.amount)))
def test_head():
assert str(compute(t.head(2), s)) == str(select(s).limit(2))
def test_count_on_table():
assert normalize(str(select(compute(t.count(), s)))) == normalize("""
SELECT count(accounts.id) as tbl_row_count
FROM accounts""")
def test_relabel():
result = compute(t.relabel({'name': 'NAME', 'id': 'ID'}), s)
expected = select([s.c.name.label('NAME'), s.c.amount, s.c.id.label('ID')])
assert str(result) == str(expected)
def test_multicolumn_sort():
assert str(compute(t.sort(['amount', 'id']), s)) == \
str(select(s).order_by(sa.asc(s.c.amount), sa.asc(s.c.id)))
assert str(compute(t.sort(['amount', 'id'], ascending=False), s)) == \
str(select(s).order_by(sa.desc(s.c.amount), sa.desc(s.c.id)))
