def test_read_only_write(dbpath):
if USE_POSTGRES:
pytest.skip("Read only only supported for sqlite")
con, cur = light_orm.get_con_cur(dbpath, DB_SQL) # make the DB exist
with pytest.raises(Exception):
con, cur = light_orm.get_con_cur(dbpath, DB_SQL, read_only=True)
# now save a record in read-only mode
check_db(cur, insert=True)
def test_read_only_existing(dbpath):
if USE_POSTGRES:
pytest.skip("Read only only supported for sqlite")
con, cur = light_orm.get_con_cur(dbpath, DB_SQL)
check_db(cur)
con.commit()
con, cur = light_orm.get_con_cur(dbpath, DB_SQL, read_only=True)
check_db(cur, insert=False)
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_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_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_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 test_get_or_make_rec(dbpath):
con, cur = light_orm.get_con_cur(dbpath, DB_SQL)
res = light_orm.get_or_make_rec(cur, 'est', {'date': 2010, 'site': 1})
assert res == ({'est': 1, 'date': 2010, 'site': 1, 'flow': None}, True)
res = light_orm.get_or_make_rec(cur, 'est', {'date': 2010, 'site': 2})
assert res == ({'est': 2, 'date': 2010, 'site': 2, 'flow': None}, True)
res = light_orm.get_or_make_rec(cur, 'est', {'date': 2010, 'site': 1})
assert res == ({'est': 1, 'date': 2010, 'site': 1, 'flow': None}, False)
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 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_read_only(dbpath):
if USE_POSTGRES:
pytest.skip("Read only only supported for sqlite")
with pytest.raises(Exception):
con, cur = light_orm.get_con_cur(dbpath, DB_SQL, read_only=True)
def test_open(dbpath):
con, cur = light_orm.get_con_cur(dbpath, DB_SQL)
check_db(cur)
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}
