def test_iter_sql_join(pokedb):
query = """
SELECT
p.name,
t1.name AS type_1,
t2.name AS type_2
FROM
pokemons p,
pokemon_types pt1,
pokemon_types pt2,
types t1,
types t2
WHERE
pt1.no = 1 AND
pt1.pokemon_id = p.id AND
pt1.type_id = t1.id AND
pt2.no = 2 AND
pt2.pokemon_id = p.id AND
pt2.type_id = t2.id;
"""
with pokedb.connect() as conn:
dataset = stream.iter_sql(query=query, conn=conn)
x, y = next(dataset)
assert x["name"] == "Bulbasaur"
assert x["type_1"] == "Grass"
assert x["type_2"] == "Poison"
def test_iter_sql_join(pokedb):
query = '''
SELECT
p.name,
t1.name AS type_1,
t2.name AS type_2
FROM
pokemons p,
pokemon_types pt1,
pokemon_types pt2,
types t1,
types t2
WHERE
pt1.no = 1 AND
pt1.pokemon_id = p.id AND
pt1.type_id = t1.id AND
pt2.no = 2 AND
pt2.pokemon_id = p.id AND
pt2.type_id = t2.id;
'''
with pokedb.connect() as conn:
dataset = stream.iter_sql(query=query, conn=conn)
x, y = next(dataset)
assert x['name'] == 'Bulbasaur'
assert x['type_1'] == 'Grass'
assert x['type_2'] == 'Poison'
def test_iter_sql(pokedb):
with pokedb.connect() as conn:
dataset = stream.iter_sql(query="SELECT * FROM pokemons;", conn=conn)
x, y = next(dataset)
assert x["name"] == "Bulbasaur"
assert y is None
# This raises an exception because the resource is closed...
with pytest.raises(sql.exc.StatementError):
for x, y in stream.iter_sql(query="SELECT * FROM pokemons;",
conn=conn):
pass
# ... and yet we can still stream over the results because SQLAlchemy prefetches them
x, y = next(dataset)
assert x["name"] == "Ivysaur"
# The Pokedex from generation 1 contains 151 pokemons, and we've already seen 2 of them
assert sum(1 for _ in dataset) == 149
# Check that the stream is depleted
assert sum(1 for _ in dataset) == 0