def test_multiple_orders(self):
db = PseudoAPI().connect()
m = define_model(table1, [CRUDMixin])
db.reserve([[1, "abc", 10], [2, "def", 20]])
rs = m.fetch_where(db, orders = dict(c1 = True, c3 = False))
assert db.query_list[0] == "SELECT c1, c2, c3 FROM t1 ORDER BY c1 ASC, c3 DESC"
def test_empty(self):
db = PseudoAPI().connect()
m = define_model(table1, [CRUDMixin])
db.reserve([])
r = m.fetch(db, 1)
assert r is None
def test_all_args(self):
db = PseudoAPI().connect()
m = define_model(table1, [CRUDMixin])
db.reserve([[1, "abc", 10], [2, "def", 20]])
rs = m.fetch_where(db, lambda m: Q.of(f"c3 > {m()}", 5), dict(c1 = True, c3 = False), limit = 10, offset = 20, lock = "FOR UPDATE")
assert db.query_list[0] == "SELECT c1, c2, c3 FROM t1 WHERE c3 > ? ORDER BY c1 ASC, c3 DESC LIMIT ? OFFSET ? FOR UPDATE"
assert list(db.params_list[0]) == [5, 10, 20]
def test_lock(self):
db = PseudoAPI().connect()
m = define_model(table1, [CRUDMixin])
db.reserve([[1, "abc", 10], [2, "def", 20]])
rs = m.fetch_where(db, lambda m: Q.of(f"c3 > {m()}", 5), lock="FOR UPDATE")
assert db.query_list[0] == "SELECT c1, c2, c3 FROM t1 WHERE c3 > ? FOR UPDATE"
assert list(db.params_list[0]) == [5]
def test_offset(self):
db = PseudoAPI().connect()
m = define_model(table1, [CRUDMixin])
db.reserve([[1, "abc", 10], [2, "def", 20]])
rs = m.fetch_where(db, offset = 20)
assert db.query_list[0] == "SELECT c1, c2, c3 FROM t1 OFFSET ?"
assert list(db.params_list[0]) == [20]
def test_with_condition_simple(self):
db = PseudoAPI().connect()
m = define_model(table1, [CRUDMixin])
db.reserve([[1, "abc", 10], [2, "def", 20]])
rs = m.fetch_where(db, Q.of(f"c3 > ?", 5))
assert db.query_list[0] == "SELECT c1, c2, c3 FROM t1 WHERE c3 > ?"
assert list(db.params_list[0]) == [5]
def test_fetch(self):
db = PseudoAPI().connect()
m = define_model(table1, [CRUDMixin])
db.reserve([[1, "abc", 3]])
r = m.fetch(db, 1)
assert db.query_list[0] == "SELECT c1, c2, c3 FROM t1 WHERE c1 = ?"
assert list(db.params_list[0]) == [1]
assert (r.c1, r.c2, r.c3) == (1, "abc", 3)
def test_count_where(self):
db = PseudoAPI().connect()
m = define_model(table1, [CRUDMixin])
db.reserve([[3]])
r = m.count(db, lambda m: Q.of(f"c3 > {m()}", 5))
assert db.query_list[0] == "SELECT COUNT(*) FROM t1 WHERE c3 > ?"
assert list(db.params_list[0]) == [5]
assert r == 3
def test_count_all(self):
db = PseudoAPI().connect()
m = define_model(table1, [CRUDMixin])
db.reserve([[3]])
r = m.count(db)
assert db.query_list[0] == "SELECT COUNT(*) FROM t1 "
assert list(db.params_list[0]) == []
assert r == 3
def test_multiple_pks(self):
db = PseudoAPI().connect()
m = define_model(table2, [CRUDMixin])
db.reserve([[1, "abc", 3]])
r = m.fetch(db, dict(c1 = 1, c2 = "abc"))
assert db.query_list[0] == "SELECT c1, c2, c3 FROM t2 WHERE c1 = ? AND c2 = ?"
assert list(db.params_list[0]) == [1, "abc"]
assert (r.c1, r.c2, r.c3) == (1, "abc", 3)
def test_no_condition(self):
db = PseudoAPI().connect()
m = define_model(table1, [CRUDMixin])
db.reserve([[1, "abc", 10], [2, "def", 20]])
rs = m.fetch_where(db)
assert db.query_list[0] == "SELECT c1, c2, c3 FROM t1"
assert list(db.params_list[0]) == []
assert (rs[0].c1, rs[0].c2, rs[0].c3) == (1, "abc", 10)
assert (rs[1].c1, rs[1].c2, rs[1].c3) == (2, "def", 20)
class TestValues:
api = PseudoAPI()
def test_qmark(self):
self.api.paramstyle = 'qmark'
h = QueryHelper(self.api)
assert h.values(2, 3) == "(?, ?), (?, ?), (?, ?)"
def test_numeric(self):
self.api.paramstyle = 'numeric'
h = QueryHelper(self.api)
assert h.values(2, 3) == "(:1, :2), (:3, :4), (:5, :6)"
def test_named(self):
self.api.paramstyle = 'named'
h = QueryHelper(self.api)
assert h.values(["a", "b"], 3) == "(:a, :b), (:a, :b), (:a, :b)"
def test_format(self):
self.api.paramstyle = 'format'
h = QueryHelper(self.api)
assert h.values(2, 3) == "(%s, %s), (%s, %s), (%s, %s)"
def test_pyformat(self):
self.api.paramstyle = 'pyformat'
h = QueryHelper(self.api)
assert h.values(["a", None],
3) == "(%(a)s, %s), (%(a)s, %s), (%(a)s, %s)"
def test_pyformat(self):
self.api.paramstyle = 'pyformat'
h = QueryHelper(self.api)
assert h.values(2, 3) == "(%s, %s), (%s, %s), (%s, %s)"
def test_qualifier(self):
self.api.paramstyle = 'qmark'
h = QueryHelper(self.api)
assert h.values(2, 3, {1: lambda x: f"{x}::text"
}) == "(?, ?::text), (?, ?::text), (?, ?::text)"
class TestHolders:
api = PseudoAPI()
def test_qmark(self):
self.api.paramstyle = 'qmark'
h = QueryHelper(self.api)
assert h.holders(3) == "?, ?, ?"
def test_numeric(self):
self.api.paramstyle = 'numeric'
h = QueryHelper(self.api)
assert h.holders(3) == ":1, :2, :3"
def test_named(self):
self.api.paramstyle = 'named'
h = QueryHelper(self.api)
assert h.holders(["a", "b", "c"]) == ":a, :b, :c"
def test_format(self):
self.api.paramstyle = 'format'
h = QueryHelper(self.api)
assert h.holders(3) == "%s, %s, %s"
def test_pyformat(self):
self.api.paramstyle = 'pyformat'
h = QueryHelper(self.api)
assert h.holders(["a", "b", "c"]) == "%(a)s, %(b)s, %(c)s"
def test_pyformat_intkey(self):
self.api.paramstyle = 'pyformat'
h = QueryHelper(self.api)
assert h.holders(3) == "%s, %s, %s"
def test_qualifier(self):
self.api.paramstyle = 'qmark'
h = QueryHelper(self.api)
assert h.holders(3, {1: lambda x: f"{x}::text"}) == "?, ?::text, ?"
def _db(self):
return PseudoAPI().connect()
class TestMarker:
api = PseudoAPI()
def test_qmark(self):
self.api.paramstyle = 'qmark'
h = QueryHelper(self.api)
m = h.marker()
assert [m(), m(), m()] == ["?", "?", "?"]
def test_numeric(self):
self.api.paramstyle = 'numeric'
h = QueryHelper(self.api)
m = h.marker()
assert [m(0), m(1), m(3)] == [":0", ":1", ":3"]
def test_numeric_counter(self):
self.api.paramstyle = 'numeric'
h = QueryHelper(self.api)
m = h.marker()
assert [m(), m(), m()] == [":1", ":2", ":3"]
m(1)
assert m.index == 1
assert [m(), m(1), m()] == [":2", ":1", ":2"]
def test_named(self):
self.api.paramstyle = 'named'
h = QueryHelper(self.api)
m = h.marker()
assert [m("a"), m("b"), m("c")] == [":a", ":b", ":c"]
def test_gen_name(self):
self.api.paramstyle = 'named'
h = QueryHelper(self.api)
m = h.marker()
assert [m("a"), m(), m("")] == [":a", ":key1", ":key2"]
def test_format(self):
self.api.paramstyle = 'format'
h = QueryHelper(self.api)
m = h.marker()
assert [m(), m(), m()] == ["%s", "%s", "%s"]
def test_pyformat(self):
self.api.paramstyle = 'pyformat'
h = QueryHelper(self.api)
m = h.marker()
assert [m("a"), m("b"), m("c")] == ["%(a)s", "%(b)s", "%(c)s"]
def test_pyformat_mixed_ok(self):
self.api.paramstyle = 'pyformat'
h = QueryHelper(self.api)
m = h.marker()
assert [m("a"), m(), m("c")] == ["%(a)s", "%(key1)s", "%(c)s"]
def test_pyformat_mixed_ng(self):
self.api.paramstyle = 'pyformat'
h = QueryHelper(self.api)
m = h.marker()
with pytest.raises(ValueError):
ms = [m(), m("b"), m("c")]
def test_qmark_params(self):
self.api.paramstyle = 'qmark'
h = QueryHelper(self.api)
m = h.marker()
m()
m()
m()
assert m.params((1, 2, 3)) == [1, 2, 3]
def test_qmark_params_ng(self):
self.api.paramstyle = 'qmark'
h = QueryHelper(self.api)
m = h.marker()
m()
m()
m()
with pytest.raises(ValueError):
m.params({0: 1, 1: 2, 2: 3})
def test_numeric_params(self):
self.api.paramstyle = 'numeric'
h = QueryHelper(self.api)
m = h.marker()
m()
m()
m()
assert m.params((1, 2, 3)) == [1, 2, 3]
def test_numeric_params_ng(self):
self.api.paramstyle = 'numeric'
h = QueryHelper(self.api)
m = h.marker()
m()
m()
m()
with pytest.raises(ValueError):
m.params({0: 1, 1: 2, 2: 3})
def test_named_params(self):
self.api.paramstyle = 'named'
h = QueryHelper(self.api)
m = h.marker()
m("a")
m("b")
m("c")
assert m.params(dict(a=1, b=2, c=3)) == dict(a=1, b=2, c=3)
assert m.params((1, 2, 3)) == dict(a=1, b=2, c=3)
def test_named_gen_params(self):
self.api.paramstyle = 'named'
h = QueryHelper(self.api)
m = h.marker()
m("a")
m()
m()
assert m.params(dict(a=1, b=2, c=3)) == dict(a=1, b=2, c=3)
assert m.params((1, 2, 3)) == dict(a=1, key1=2, key2=3)
def test_named_params_ng(self):
self.api.paramstyle = 'named'
h = QueryHelper(self.api)
m = h.marker()
m("a")
m("b")
m("c")
with pytest.raises(ValueError):
m.params("abc")
def test_format_params(self):
self.api.paramstyle = 'format'
h = QueryHelper(self.api)
m = h.marker()
m()
m()
m()
assert m.params((1, 2, 3)) == [1, 2, 3]
def test_format_params_ng(self):
self.api.paramstyle = 'format'
h = QueryHelper(self.api)
m = h.marker()
m()
m()
m()
with pytest.raises(ValueError):
m.params({0: 1, 1: 2, 2: 3})
def test_pyformat_params(self):
self.api.paramstyle = 'pyformat'
h = QueryHelper(self.api)
m = h.marker()
m()
m()
m()
assert m.params((1, 2, 3)) == [1, 2, 3]
def test_pyformat_params_ng(self):
self.api.paramstyle = 'pyformat'
h = QueryHelper(self.api)
m = h.marker()
m()
m()
m()
with pytest.raises(ValueError):
m.params({0: 1, 1: 2, 2: 3})
def test_pyformat_named_params(self):
self.api.paramstyle = 'pyformat'
h = QueryHelper(self.api)
m = h.marker()
m("a")
m("b")
m("c")
assert m.params(dict(a=1, b=2, c=3)) == dict(a=1, b=2, c=3)
def test_pyformat_named_params_ng(self):
self.api.paramstyle = 'pyformat'
h = QueryHelper(self.api)
m = h.marker()
m("a")
m("b")
m("c")
assert m.params((1, 2, 3)) == dict(a=1, b=2, c=3)