def test_get_or_make_pk(dbpath): con, cur = light_orm.get_con_cur(dbpath, DB_SQL) assert light_orm.get_or_make_pk(cur, 'est', {'date': 2010}) == (1, True) # should not insert a second copy assert light_orm.get_or_make_pk(cur, 'est', {'date': 2010}) == (1, False) cur.execute("select count(*) from est") assert cur.fetchone()[0] == 1
def test_get_rec(dbpath): con, cur = light_orm.get_con_cur(dbpath, DB_SQL) light_orm.get_or_make_pk(cur, 'est', {'date': 2010, 'site': 1}) light_orm.get_or_make_pk(cur, 'est', {'date': 2010, 'site': 2}) res = light_orm.get_rec(cur, 'est', {'date': 2010, 'site': 1}) assert res == {'est': 1, 'date': 2010, 'flow': None, 'site': 1} cur.execute("select count(*) from est") assert cur.fetchone()[0] == 2
def main(): pizzas = { "margarita": ["cheese", "tomato", "basil"], "hawaiian": ["cheese", "pineapple", "ham"], "vegetarian": ["cheese", "tomato", "pepper", "mushroom"], } con, cur = lo.get_con_cur("pizza.db", DB_SQL) for pizza_name, toppings in pizzas.items(): pizza, new = lo.get_or_make_pk(cur, 'pizza', {'name': pizza_name}) for topping_name in toppings: topping, new = lo.get_or_make_pk(cur, 'topping', {'name': topping_name}) lo.get_or_make_rec(cur, 'ingredient', dict(pizza=pizza, topping=topping)) con.commit() print("%d pizzas" % lo.do_one(cur, 'select count(*) as n from pizza').n) for pizza in lo.do_query( cur, "select name, count(*) as n from pizza " "join ingredient using (pizza) group by name", ): print("%s %d ingredients" % (pizza.name, pizza.n)) # we get this far without needing the ingredient pk in the ingredient table # but if we want to edit the ingredient records, the pk is needed: # set topping grams to length of pizza name, naturally for pizza in lo.get_recs(cur, 'pizza'): for ingredient in lo.get_recs(cur, 'ingredient', {'pizza': pizza.pizza}): ingredient.grams = len(pizza.name) lo.save_rec(cur, ingredient) con.commit() # re-open DB just because con, cur = lo.get_con_cur("pizza.db", DB_SQL) for pizza in lo.get_recs(cur, 'pizza'): print("\n%s\n%s" % (pizza.name, '=' * len(pizza.name))) for ingredient in lo.get_recs(cur, 'ingredient', {'pizza': pizza.pizza}): topping = lo.get_rec(cur, 'topping', {'topping': ingredient.topping}) print(" %s, %dg" % (topping.name, ingredient.grams)) # OR for ingredient in lo.do_query( cur, "select topping.name, grams from pizza " "join ingredient using (pizza) join topping using (topping) " "where pizza=?", [pizza.pizza], ): # print(" %s, %dg" % (ingredient.name, ingredient.grams)) pass
def test_multi_fail(dbpath): con, cur = light_orm.get_con_cur(dbpath, DB_SQL) light_orm.get_or_make_pk(cur, 'est', {'date': 2010, 'site': 1}) light_orm.get_or_make_pk(cur, 'est', {'date': 2010, 'site': 2}) with pytest.raises(Exception): res = light_orm.get_pk(cur, 'est', {'date': 2010}) return res # for pylint with pytest.raises(Exception): res = light_orm.do_one(cur, "select * from est") return res # for pylint
def test_save_rec(dbpath): con, cur = light_orm.get_con_cur(dbpath, DB_SQL) light_orm.get_or_make_pk(cur, 'est', {'date': 2010}) res = light_orm.get_rec(cur, 'est', {'date': 2010}) assert res['site'] is None res['site'] = 123 light_orm.save_rec(cur, res) con.commit() con, cur = light_orm.get_con_cur(dbpath, read_only=True) res = light_orm.get_rec(cur, 'est', {'date': 2010}) assert res['site'] == 123
def test_get_all_recs(dbpath): con, cur = light_orm.get_con_cur(dbpath, DB_SQL) light_orm.get_or_make_pk(cur, 'est', {'date': 2010, 'site': 1}) light_orm.get_or_make_pk(cur, 'est', {'date': 2010, 'site': 2}) light_orm.get_or_make_pk(cur, 'est', {'date': 2020, 'site': 2}) res = light_orm.get_recs(cur, 'est') res.sort(key=lambda x: x['est']) assert res == [ { 'est': 1, 'date': 2010, 'flow': None, 'site': 1 }, { 'est': 2, 'date': 2010, 'flow': None, 'site': 2 }, { 'est': 3, 'date': 2020, 'flow': None, 'site': 2 }, ] cur.execute("select count(*) from est") assert cur.fetchone()[0] == 3
def test_get_pks(dbpath): con, cur = light_orm.get_con_cur(dbpath, DB_SQL) light_orm.get_or_make_pk(cur, 'est', {'date': 2010, 'site': 1}) light_orm.get_or_make_pk(cur, 'est', {'date': 2010, 'site': 2}) light_orm.get_or_make_pk(cur, 'est', {'date': 2020, 'site': 2}) assert sorted(light_orm.get_pks(cur, 'est', {'date': 2010})) == [1, 2] cur.execute("select count(*) from est") == 3 assert cur.fetchone()[0] == 3
def check_db(cur, insert=True): if insert: light_orm.get_or_make_pk(cur, 'est', {'date': 2010, 'flow': 11.2}) res = light_orm.get_rec(cur, 'est', {'date': 2010}) assert res == {'est': 1, 'date': 2010, 'flow': 11.2, 'site': None}
def test_do_one(dbpath): con, cur = light_orm.get_con_cur(dbpath, DB_SQL) light_orm.get_or_make_pk(cur, 'est', {'date': 2010, 'site': 1}) light_orm.get_or_make_pk(cur, 'est', {'date': 2010, 'site': 2}) res = light_orm.do_one(cur, "select count(*) as count from est") assert res == {'count': 2}