def test_select_with_models(self):
sq = SelectQuery(Blog, {Blog: '*'})
self.assertEqual(sq.sql(), ('SELECT * FROM blog', []))
sq = SelectQuery(Blog, {Blog: ['title', 'id']})
self.assertEqual(sq.sql(), ('SELECT title, id FROM blog', []))
sq = SelectQuery(Blog, {Blog: ['title', 'id']}).join(Entry)
self.assertEqual(sq.sql(), (
'SELECT t1.title, t1.id FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id',
[]))
sq = SelectQuery(Blog, {
Blog: ['title', 'id'],
Entry: [peewee.Count('pk')]
}).join(Entry)
self.assertEqual(sq.sql(), (
'SELECT t1.title, t1.id, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id',
[]))
sq = SelectQuery(Blog, {
Blog: ['title', 'id'],
Entry: [peewee.Max('pk')]
}).join(Entry)
self.assertEqual(sq.sql(), (
'SELECT t1.title, t1.id, MAX(t2.pk) AS max FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id',
[]))
def test_selecting_across_joins(self):
sq = SelectQuery(Entry,
'*').where(title='a1').join(Blog).where(title='a')
self.assertEqual(sq._joins, [(Blog, None, None)])
self.assertEqual(sq.sql(), (
'SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE t1.title = ? AND t2.title = ?',
['a1', 'a']))
sq = SelectQuery(Blog, '*').join(Entry).where(title='a1')
self.assertEqual(sq._joins, [(Entry, None, None)])
self.assertEqual(sq.sql(), (
'SELECT t1.* FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id WHERE t2.title = ?',
['a1']))
sq = SelectQuery(EntryTag, '*').join(Entry).join(Blog).where(title='a')
self.assertEqual(sq._joins, [(Entry, None, None), (Blog, None, None)])
self.assertEqual(sq.sql(), (
'SELECT t1.* FROM entrytag AS t1 INNER JOIN entry AS t2 ON t1.entry_id = t2.pk\nINNER JOIN blog AS t3 ON t2.blog_id = t3.id WHERE t3.title = ?',
['a']))
sq = SelectQuery(Blog, '*').join(Entry).join(EntryTag).where(tag='t2')
self.assertEqual(sq._joins, [(Entry, None, None),
(EntryTag, None, None)])
self.assertEqual(sq.sql(), (
'SELECT t1.* FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id\nINNER JOIN entrytag AS t3 ON t2.pk = t3.entry_id WHERE t3.tag = ?',
['t2']))
def test_selecting_with_aggregation(self):
sq = SelectQuery(
Blog, 't1.*, COUNT(t2.pk) AS count').group_by('id').join(Entry)
self.assertEqual(sq._where, {})
self.assertEqual(sq._joins, [(Entry, None, None)])
self.assertEqual(sq.sql(), (
'SELECT t1.*, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id',
[]))
sq = sq.having('count > 2')
self.assertEqual(sq.sql(), (
'SELECT t1.*, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id HAVING count > 2',
[]))
sq = SelectQuery(Blog, {
Blog: ['*'],
Entry: [peewee.Count('pk')]
}).group_by('id').join(Entry)
self.assertEqual(sq.sql(), (
'SELECT t1.*, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id',
[]))
sq = sq.having('count > 2')
self.assertEqual(sq.sql(), (
'SELECT t1.*, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id HAVING count > 2',
[]))
sq = sq.order_by(('count', 'desc'))
self.assertEqual(sq.sql(), (
'SELECT t1.*, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id HAVING count > 2 ORDER BY count desc',
[]))
def test_select_with_models(self):
sq = SelectQuery(Blog, {Blog: "*"})
self.assertEqual(sq.sql(), ("SELECT * FROM blog", []))
sq = SelectQuery(Blog, {Blog: ["title", "id"]})
self.assertEqual(sq.sql(), ("SELECT title, id FROM blog", []))
sq = SelectQuery(Blog, {Blog: ["title", "id"]}).join(Entry)
self.assertEqual(
sq.sql(), ("SELECT t1.title, t1.id FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id", [])
)
sq = SelectQuery(Blog, {Blog: ["title", "id"], Entry: [peewee.Count("pk")]}).join(Entry)
self.assertEqual(
sq.sql(),
(
"SELECT t1.title, t1.id, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id",
[],
),
)
sq = SelectQuery(Blog, {Blog: ["title", "id"], Entry: [peewee.Max("pk")]}).join(Entry)
self.assertEqual(
sq.sql(),
(
"SELECT t1.title, t1.id, MAX(t2.pk) AS max FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id",
[],
),
)
def test_pagination(self):
sq = SelectQuery(Blog).paginate(1, 20)
self.assertEqual(sq.sql(),
('SELECT * FROM blog LIMIT 20 OFFSET 0', []))
sq = SelectQuery(Blog).paginate(3, 30)
self.assertEqual(sq.sql(),
('SELECT * FROM blog LIMIT 30 OFFSET 60', []))
def test_selecting_with_ordering(self):
sq = SelectQuery(Blog).order_by('title')
self.assertEqual(sq.sql(), ('SELECT * FROM blog ORDER BY title ASC', []))
sq = SelectQuery(Blog).order_by(peewee.desc('title'))
self.assertEqual(sq.sql(), ('SELECT * FROM blog ORDER BY title DESC', []))
sq = SelectQuery(Entry).order_by(peewee.desc('title')).join(Blog).where(title='a')
self.assertEqual(sq.sql(), ('SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE t2.title = ? ORDER BY t1.title DESC', ['a']))
sq = SelectQuery(Entry).join(Blog).order_by(peewee.desc('title'))
self.assertEqual(sq.sql(), ('SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id ORDER BY t2.title DESC', []))
def test_selecting_with_ordering(self):
sq = SelectQuery(Blog).order_by('title')
self.assertEqual(sq.sql(), ('SELECT * FROM blog ORDER BY title ASC', []))
sq = SelectQuery(Blog).order_by(peewee.desc('title'))
self.assertEqual(sq.sql(), ('SELECT * FROM blog ORDER BY title DESC', []))
sq = SelectQuery(Entry).order_by(peewee.desc('title')).join(Blog).where(title='a')
self.assertEqual(sq.sql(), ('SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE t2.title = ? ORDER BY t1.title DESC', ['a']))
sq = SelectQuery(Entry).join(Blog).order_by(peewee.desc('title'))
self.assertEqual(sq.sql(), ('SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id ORDER BY t2.title DESC', []))
def test_select_with_models(self):
sq = SelectQuery(Blog, {Blog: '*'})
self.assertEqual(sq.sql(), ('SELECT * FROM blog', []))
sq = SelectQuery(Blog, {Blog: ['title', 'id']})
self.assertEqual(sq.sql(), ('SELECT title, id FROM blog', []))
sq = SelectQuery(Blog, {Blog: ['title', 'id']}).join(Entry)
self.assertEqual(sq.sql(), ('SELECT t1.title, t1.id FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id', []))
sq = SelectQuery(Blog, {Blog: ['title', 'id'], Entry: [peewee.Count('id')]}).join(Entry)
self.assertEqual(sq.sql(), ('SELECT t1.title, t1.id, COUNT(t2.id) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id', []))
sq = SelectQuery(Blog, {Blog: ['title', 'id'], Entry: [peewee.Max('id')]}).join(Entry)
self.assertEqual(sq.sql(), ('SELECT t1.title, t1.id, MAX(t2.id) AS max FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id', []))
def test_selecting_across_joins(self):
sq = SelectQuery(Entry, '*').where(title='a1').join(Blog).where(title='a')
self.assertEqual(sq._joins, [(Blog, None, None)])
self.assertEqual(sq.sql(), ('SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE t1.title = ? AND t2.title = ?', ['a1', 'a']))
sq = SelectQuery(Blog, '*').join(Entry).where(title='a1')
self.assertEqual(sq._joins, [(Entry, None, None)])
self.assertEqual(sq.sql(), ('SELECT t1.* FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id WHERE t2.title = ?', ['a1']))
sq = SelectQuery(EntryTag, '*').join(Entry).join(Blog).where(title='a')
self.assertEqual(sq._joins, [(Entry, None, None), (Blog, None, None)])
self.assertEqual(sq.sql(), ('SELECT t1.* FROM entrytag AS t1 INNER JOIN entry AS t2 ON t1.entry_id = t2.id\nINNER JOIN blog AS t3 ON t2.blog_id = t3.id WHERE t3.title = ?', ['a']))
sq = SelectQuery(Blog, '*').join(Entry).join(EntryTag).where(tag='t2')
self.assertEqual(sq._joins, [(Entry, None, None), (EntryTag, None, None)])
self.assertEqual(sq.sql(), ('SELECT t1.* FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id\nINNER JOIN entrytag AS t3 ON t2.id = t3.entry_id WHERE t3.tag = ?', ['t2']))
def test_ordering_on_aggregates(self):
sq = SelectQuery(Blog,
't1.*, COUNT(t2.pk) as count').join(Entry).order_by(
peewee.desc('count'))
self.assertEqual(sq.sql(), (
'SELECT t1.*, COUNT(t2.pk) as count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id ORDER BY count DESC',
[]))
def test_selecting_with_aggregation(self):
sq = SelectQuery(Blog, "t1.*, COUNT(t2.pk) AS count").group_by("id").join(Entry)
self.assertEqual(sq._where, {})
self.assertEqual(sq._joins, [(Entry, None, None)])
self.assertEqual(
sq.sql(),
(
"SELECT t1.*, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id",
[],
),
)
sq = sq.having("count > 2")
self.assertEqual(
sq.sql(),
(
"SELECT t1.*, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id HAVING count > 2",
[],
),
)
sq = SelectQuery(Blog, {Blog: ["*"], Entry: [peewee.Count("pk")]}).group_by("id").join(Entry)
self.assertEqual(
sq.sql(),
(
"SELECT t1.*, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id",
[],
),
)
sq = sq.having("count > 2")
self.assertEqual(
sq.sql(),
(
"SELECT t1.*, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id HAVING count > 2",
[],
),
)
sq = sq.order_by(("count", "desc"))
self.assertEqual(
sq.sql(),
(
"SELECT t1.*, COUNT(t2.pk) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id HAVING count > 2 ORDER BY count desc",
[],
),
)
def test_select(self):
sq = SelectQuery(Blog, '*')
self.assertEqual(sq.sql(), ('SELECT * FROM blog', []))
sq = SelectQuery(Blog, '*').where(title='a')
self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE title = ?', ['a']))
sq = SelectQuery(Blog, '*').where(title='a', id=1)
self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (id = ? AND title = ?)', [1, 'a']))
# check that chaining works as expected
sq = SelectQuery(Blog, '*').where(title='a').where(id=1)
self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE title = ? AND id = ?', ['a', 1]))
# check that IN query special-case works
sq = SelectQuery(Blog, '*').where(title__in=['a', 'b'])
self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE title IN (?,?)', ['a', 'b']))
def test_select(self):
sq = SelectQuery(Blog, '*')
self.assertEqual(sq.sql(), ('SELECT * FROM blog', []))
sq = SelectQuery(Blog, '*').where(title='a')
self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE title = ?', ['a']))
sq = SelectQuery(Blog, '*').where(title='a', id=1)
self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (id = ? AND title = ?)', [1, 'a']))
# check that chaining works as expected
sq = SelectQuery(Blog, '*').where(title='a').where(id=1)
self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE title = ? AND id = ?', ['a', 1]))
# check that IN query special-case works
sq = SelectQuery(Blog, '*').where(title__in=['a', 'b'])
self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE title IN (?,?)', ['a', 'b']))
def test_select(self):
sq = SelectQuery(Blog, "*")
self.assertEqual(sq.sql(), ("SELECT * FROM blog", []))
sq = SelectQuery(Blog, "*").where(title="a")
self.assertEqual(sq.sql(), ("SELECT * FROM blog WHERE title = ?", ["a"]))
sq = SelectQuery(Blog, "*").where(title="a", id=1)
self.assertEqual(sq.sql(), ("SELECT * FROM blog WHERE (id = ? AND title = ?)", [1, "a"]))
# check that chaining works as expected
sq = SelectQuery(Blog, "*").where(title="a").where(id=1)
self.assertEqual(sq.sql(), ("SELECT * FROM blog WHERE title = ? AND id = ?", ["a", 1]))
# check that IN query special-case works
sq = SelectQuery(Blog, "*").where(title__in=["a", "b"])
self.assertEqual(sq.sql(), ("SELECT * FROM blog WHERE title IN (?,?)", ["a", "b"]))
def test_ordering_on_aggregates(self):
sq = SelectQuery(Blog, "t1.*, COUNT(t2.pk) as count").join(Entry).order_by(peewee.desc("count"))
self.assertEqual(
sq.sql(),
(
"SELECT t1.*, COUNT(t2.pk) as count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id ORDER BY count DESC",
[],
),
)
def test_selecting_with_aggregation(self):
sq = SelectQuery(Blog, 't1.*, COUNT(t2.id) AS count').group_by('id').join(Entry)
self.assertEqual(sq._where, {})
self.assertEqual(sq._joins, [(Entry, None, None)])
self.assertEqual(sq.sql(), ('SELECT t1.*, COUNT(t2.id) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id', []))
sq = sq.having('count > 2')
self.assertEqual(sq.sql(), ('SELECT t1.*, COUNT(t2.id) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id HAVING count > 2', []))
sq = SelectQuery(Blog, {
Blog: ['*'],
Entry: [peewee.Count('id')]
}).group_by('id').join(Entry)
self.assertEqual(sq.sql(), ('SELECT t1.*, COUNT(t2.id) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id', []))
sq = sq.having('count > 2')
self.assertEqual(sq.sql(), ('SELECT t1.*, COUNT(t2.id) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id HAVING count > 2', []))
sq = sq.order_by(('count', 'desc'))
self.assertEqual(sq.sql(), ('SELECT t1.*, COUNT(t2.id) AS count FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id GROUP BY t1.id HAVING count > 2 ORDER BY count desc', []))
def test_select_with_q(self):
sq = SelectQuery(Blog, "*").where(Q(title="a") | Q(id=1))
self.assertEqual(sq.sql(), ("SELECT * FROM blog WHERE (title = ? OR id = ?)", ["a", 1]))
sq = SelectQuery(Blog, "*").where(Q(title="a") | Q(id=1) | Q(id=3))
self.assertEqual(sq.sql(), ("SELECT * FROM blog WHERE (title = ? OR id = ? OR id = ?)", ["a", 1, 3]))
# test simple chaining
sq = SelectQuery(Blog, "*").where(Q(title="a") | Q(id=1)).where(Q(id=3))
self.assertEqual(sq.sql(), ("SELECT * FROM blog WHERE (title = ? OR id = ?) AND id = ?", ["a", 1, 3]))
sq = SelectQuery(Blog, "*").where(Q(title="a") | Q(id=1)).where(id=3)
self.assertEqual(sq.sql(), ("SELECT * FROM blog WHERE (title = ? OR id = ?) AND id = ?", ["a", 1, 3]))
# test chaining with Q objects
sq = SelectQuery(Blog, "*").where(Q(title="a") | Q(id=1)).where((Q(title="c") | Q(id=3)))
self.assertEqual(
sq.sql(), ("SELECT * FROM blog WHERE (title = ? OR id = ?) AND (title = ? OR id = ?)", ["a", 1, "c", 3])
)
# test mixing it all up
sq = SelectQuery(Blog, "*").where(Q(title="a") | Q(id=1)).where((Q(title="c") | Q(id=3)), title="b")
self.assertEqual(
sq.sql(),
(
"SELECT * FROM blog WHERE (title = ? OR id = ?) AND (title = ? OR id = ?) AND title = ?",
["a", 1, "c", 3, "b"],
),
)
def test_select_with_negation(self):
sq = SelectQuery(Blog, "*").where(~Q(title="a"))
self.assertEqual(sq.sql(), ("SELECT * FROM blog WHERE NOT title = ?", ["a"]))
sq = SelectQuery(Blog, "*").where(~Q(title="a") | Q(title="b"))
self.assertEqual(sq.sql(), ("SELECT * FROM blog WHERE (NOT title = ? OR title = ?)", ["a", "b"]))
sq = SelectQuery(Blog, "*").where(~Q(title="a") | ~Q(title="b"))
self.assertEqual(sq.sql(), ("SELECT * FROM blog WHERE (NOT title = ? OR NOT title = ?)", ["a", "b"]))
sq = SelectQuery(Blog, "*").where(~(Q(title="a") | Q(title="b")))
self.assertEqual(sq.sql(), ("SELECT * FROM blog WHERE (NOT (title = ? OR title = ?))", ["a", "b"]))
# chaining?
sq = SelectQuery(Blog, "*").where(~(Q(title="a") | Q(id=1))).where(Q(id=3))
self.assertEqual(sq.sql(), ("SELECT * FROM blog WHERE (NOT (title = ? OR id = ?)) AND id = ?", ["a", 1, 3]))
# mix n'match?
sq = SelectQuery(Blog, "*").where(Q(title="a") | Q(id=1)).where(~(Q(title="c") | Q(id=3)), title="b")
self.assertEqual(
sq.sql(),
(
"SELECT * FROM blog WHERE (title = ? OR id = ?) AND (NOT (title = ? OR id = ?)) AND title = ?",
["a", 1, "c", 3, "b"],
),
)
def test_selecting_across_joins(self):
sq = SelectQuery(Entry, "*").where(title="a1").join(Blog).where(title="a")
self.assertEqual(sq._joins, [(Blog, None, None)])
self.assertEqual(
sq.sql(),
(
"SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE t1.title = ? AND t2.title = ?",
["a1", "a"],
),
)
sq = SelectQuery(Blog, "*").join(Entry).where(title="a1")
self.assertEqual(sq._joins, [(Entry, None, None)])
self.assertEqual(
sq.sql(),
("SELECT t1.* FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id WHERE t2.title = ?", ["a1"]),
)
sq = SelectQuery(EntryTag, "*").join(Entry).join(Blog).where(title="a")
self.assertEqual(sq._joins, [(Entry, None, None), (Blog, None, None)])
self.assertEqual(
sq.sql(),
(
"SELECT t1.* FROM entrytag AS t1 INNER JOIN entry AS t2 ON t1.entry_id = t2.pk\nINNER JOIN blog AS t3 ON t2.blog_id = t3.id WHERE t3.title = ?",
["a"],
),
)
sq = SelectQuery(Blog, "*").join(Entry).join(EntryTag).where(tag="t2")
self.assertEqual(sq._joins, [(Entry, None, None), (EntryTag, None, None)])
self.assertEqual(
sq.sql(),
(
"SELECT t1.* FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id\nINNER JOIN entrytag AS t3 ON t2.pk = t3.entry_id WHERE t3.tag = ?",
["t2"],
),
)
def test_selecting_across_joins_with_q(self):
sq = SelectQuery(Entry,
'*').where(Q(title='a')
| Q(pk=1)).join(Blog).where(title='e')
self.assertEqual(sq.sql(), (
'SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.pk = ?) AND t2.title = ?',
['a', 1, 'e']))
sq = SelectQuery(Entry,
'*').where(Q(title='a') | Q(pk=1)
| Q(title='b')).join(Blog).where(title='e')
self.assertEqual(sq.sql(), (
'SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.pk = ? OR t1.title = ?) AND t2.title = ?',
['a', 1, 'b', 'e']))
# test simple chaining
sq = SelectQuery(Entry, '*').where(Q(title='a') | Q(pk=1)).where(
Q(title='b')).join(Blog).where(title='e')
self.assertEqual(sq.sql(), (
'SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.pk = ?) AND t1.title = ? AND t2.title = ?',
['a', 1, 'b', 'e']))
sq = SelectQuery(Entry, '*').where(Q(title='a') | Q(pk=1)).where(
title='b').join(Blog).where(title='e')
self.assertEqual(sq.sql(), (
'SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.pk = ?) AND t1.title = ? AND t2.title = ?',
['a', 1, 'b', 'e']))
# test q on both models
sq = SelectQuery(
Entry,
'*').where(Q(title='a')
| Q(pk=1)).join(Blog).where(Q(title='e') | Q(id=2))
self.assertEqual(sq.sql(), (
'SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.pk = ?) AND (t2.title = ? OR t2.id = ?)',
['a', 1, 'e', 2]))
# test q on both with nesting
sq = SelectQuery(
Entry,
'*').where(Q(title='a')
| Q(pk=1)).join(Blog).where((Q(title='e') | Q(id=2))
& (Q(title='f') | Q(id=3)))
self.assertEqual(sq.sql(), (
'SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.pk = ?) AND ((t2.title = ? OR t2.id = ?) AND (t2.title = ? OR t2.id = ?))',
['a', 1, 'e', 2, 'f', 3]))
def test_select_with_negation(self):
sq = SelectQuery(Blog, '*').where(~Q(title='a'))
self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE NOT title = ?', ['a']))
sq = SelectQuery(Blog, '*').where(~Q(title='a') | Q(title='b'))
self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (NOT title = ? OR title = ?)', ['a', 'b']))
sq = SelectQuery(Blog, '*').where(~Q(title='a') | ~Q(title='b'))
self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (NOT title = ? OR NOT title = ?)', ['a', 'b']))
sq = SelectQuery(Blog, '*').where(~(Q(title='a') | Q(title='b')))
self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (NOT (title = ? OR title = ?))', ['a', 'b']))
# chaining?
sq = SelectQuery(Blog, '*').where(~(Q(title='a') | Q(id=1))).where(Q(id=3))
self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (NOT (title = ? OR id = ?)) AND id = ?', ['a', 1, 3]))
# mix n'match?
sq = SelectQuery(Blog, '*').where(Q(title='a') | Q(id=1)).where(~(Q(title='c') | Q(id=3)), title='b')
self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (title = ? OR id = ?) AND (NOT (title = ? OR id = ?)) AND title = ?', ['a', 1, 'c', 3, 'b']))
def test_select_with_negation(self):
sq = SelectQuery(Blog, '*').where(~Q(title='a'))
self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE NOT title = ?', ['a']))
sq = SelectQuery(Blog, '*').where(~Q(title='a') | Q(title='b'))
self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (NOT title = ? OR title = ?)', ['a', 'b']))
sq = SelectQuery(Blog, '*').where(~Q(title='a') | ~Q(title='b'))
self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (NOT title = ? OR NOT title = ?)', ['a', 'b']))
sq = SelectQuery(Blog, '*').where(~(Q(title='a') | Q(title='b')))
self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (NOT (title = ? OR title = ?))', ['a', 'b']))
# chaining?
sq = SelectQuery(Blog, '*').where(~(Q(title='a') | Q(id=1))).where(Q(id=3))
self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (NOT (title = ? OR id = ?)) AND id = ?', ['a', 1, 3]))
# mix n'match?
sq = SelectQuery(Blog, '*').where(Q(title='a') | Q(id=1)).where(~(Q(title='c') | Q(id=3)), title='b')
self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (title = ? OR id = ?) AND (NOT (title = ? OR id = ?)) AND title = ?', ['a', 1, 'c', 3, 'b']))
def test_selecting_across_joins_with_q(self):
sq = SelectQuery(Entry, '*').where(Q(title='a') | Q(id=1)).join(Blog).where(title='e')
self.assertEqual(sq.sql(), ('SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.id = ?) AND t2.title = ?', ['a', 1, 'e']))
sq = SelectQuery(Entry, '*').where(Q(title='a') | Q(id=1) | Q(title='b')).join(Blog).where(title='e')
self.assertEqual(sq.sql(), ('SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.id = ? OR t1.title = ?) AND t2.title = ?', ['a', 1, 'b', 'e']))
# test simple chaining
sq = SelectQuery(Entry, '*').where(Q(title='a') | Q(id=1)).where(Q(title='b')).join(Blog).where(title='e')
self.assertEqual(sq.sql(), ('SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.id = ?) AND t1.title = ? AND t2.title = ?', ['a', 1, 'b', 'e']))
sq = SelectQuery(Entry, '*').where(Q(title='a') | Q(id=1)).where(title='b').join(Blog).where(title='e')
self.assertEqual(sq.sql(), ('SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.id = ?) AND t1.title = ? AND t2.title = ?', ['a', 1, 'b', 'e']))
# test q on both models
sq = SelectQuery(Entry, '*').where(Q(title='a') | Q(id=1)).join(Blog).where(Q(title='e') | Q(id=2))
self.assertEqual(sq.sql(), ('SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.id = ?) AND (t2.title = ? OR t2.id = ?)', ['a', 1, 'e', 2]))
# test q on both with nesting
sq = SelectQuery(Entry, '*').where(Q(title='a') | Q(id=1)).join(Blog).where((Q(title='e') | Q(id=2)) & (Q(title='f') | Q(id=3)))
self.assertEqual(sq.sql(), ('SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.id = ?) AND ((t2.title = ? OR t2.id = ?) AND (t2.title = ? OR t2.id = ?))', ['a', 1, 'e', 2, 'f', 3]))
def test_select_with_q(self):
sq = SelectQuery(Blog, '*').where(Q(title='a') | Q(id=1))
self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (title = ? OR id = ?)', ['a', 1]))
sq = SelectQuery(Blog, '*').where(Q(title='a') | Q(id=1) | Q(id=3))
self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (title = ? OR id = ? OR id = ?)', ['a', 1, 3]))
# test simple chaining
sq = SelectQuery(Blog, '*').where(Q(title='a') | Q(id=1)).where(Q(id=3))
self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (title = ? OR id = ?) AND id = ?', ['a', 1, 3]))
sq = SelectQuery(Blog, '*').where(Q(title='a') | Q(id=1)).where(id=3)
self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (title = ? OR id = ?) AND id = ?', ['a', 1, 3]))
# test chaining with Q objects
sq = SelectQuery(Blog, '*').where(Q(title='a') | Q(id=1)).where((Q(title='c') | Q(id=3)))
self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (title = ? OR id = ?) AND (title = ? OR id = ?)', ['a', 1, 'c', 3]))
# test mixing it all up
sq = SelectQuery(Blog, '*').where(Q(title='a') | Q(id=1)).where((Q(title='c') | Q(id=3)), title='b')
self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (title = ? OR id = ?) AND (title = ? OR id = ?) AND title = ?', ['a', 1, 'c', 3, 'b']))
def test_select_with_q(self):
sq = SelectQuery(Blog, '*').where(Q(title='a') | Q(id=1))
self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (title = ? OR id = ?)', ['a', 1]))
sq = SelectQuery(Blog, '*').where(Q(title='a') | Q(id=1) | Q(id=3))
self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (title = ? OR id = ? OR id = ?)', ['a', 1, 3]))
# test simple chaining
sq = SelectQuery(Blog, '*').where(Q(title='a') | Q(id=1)).where(Q(id=3))
self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (title = ? OR id = ?) AND id = ?', ['a', 1, 3]))
sq = SelectQuery(Blog, '*').where(Q(title='a') | Q(id=1)).where(id=3)
self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (title = ? OR id = ?) AND id = ?', ['a', 1, 3]))
# test chaining with Q objects
sq = SelectQuery(Blog, '*').where(Q(title='a') | Q(id=1)).where((Q(title='c') | Q(id=3)))
self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (title = ? OR id = ?) AND (title = ? OR id = ?)', ['a', 1, 'c', 3]))
# test mixing it all up
sq = SelectQuery(Blog, '*').where(Q(title='a') | Q(id=1)).where((Q(title='c') | Q(id=3)), title='b')
self.assertEqual(sq.sql(), ('SELECT * FROM blog WHERE (title = ? OR id = ?) AND (title = ? OR id = ?) AND title = ?', ['a', 1, 'c', 3, 'b']))
#! /usr/bin/python
# -*- coding:utf-8 -*-
"""
@author: abc
@file: main.py
@date: 2017-02-04
"""
__author__ = "abc"
from peewee import SelectQuery
from model.db.tb_raw import TbRaw
if __name__ == "__main__":
# query = TbRaw.select().where(TbRaw.id > 0)
query = SelectQuery(TbRaw).where(TbRaw.id > 0)
print query.sql()[0] % tuple(query.sql()[1])
for item in query.dicts().execute():
print item
# UNION查询
query = TbRaw.select().where(TbRaw.id >= 2) | TbRaw.select().where(
TbRaw.id < 2)
print query.sql()[0] % tuple(query.sql()[1])
for item in query.dicts().execute():
print item
def test_selecting_with_switching(self):
sq = SelectQuery(Blog, "*").join(Entry).switch(Blog).where(title="a")
self.assertEqual(
sq.sql(),
("SELECT t1.* FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id WHERE t1.title = ?", ["a"]),
)
def test_selecting_with_switching(self):
sq = SelectQuery(Blog, '*').join(Entry).switch(Blog).where(title='a')
self.assertEqual(sq.sql(), (
'SELECT t1.* FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id WHERE t1.title = ?',
['a']))
def test_selecting_across_joins_with_q(self):
sq = SelectQuery(Entry, "*").where(Q(title="a") | Q(pk=1)).join(Blog).where(title="e")
self.assertEqual(
sq.sql(),
(
"SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.pk = ?) AND t2.title = ?",
["a", 1, "e"],
),
)
sq = SelectQuery(Entry, "*").where(Q(title="a") | Q(pk=1) | Q(title="b")).join(Blog).where(title="e")
self.assertEqual(
sq.sql(),
(
"SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.pk = ? OR t1.title = ?) AND t2.title = ?",
["a", 1, "b", "e"],
),
)
# test simple chaining
sq = SelectQuery(Entry, "*").where(Q(title="a") | Q(pk=1)).where(Q(title="b")).join(Blog).where(title="e")
self.assertEqual(
sq.sql(),
(
"SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.pk = ?) AND t1.title = ? AND t2.title = ?",
["a", 1, "b", "e"],
),
)
sq = SelectQuery(Entry, "*").where(Q(title="a") | Q(pk=1)).where(title="b").join(Blog).where(title="e")
self.assertEqual(
sq.sql(),
(
"SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.pk = ?) AND t1.title = ? AND t2.title = ?",
["a", 1, "b", "e"],
),
)
# test q on both models
sq = SelectQuery(Entry, "*").where(Q(title="a") | Q(pk=1)).join(Blog).where(Q(title="e") | Q(id=2))
self.assertEqual(
sq.sql(),
(
"SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.pk = ?) AND (t2.title = ? OR t2.id = ?)",
["a", 1, "e", 2],
),
)
# test q on both with nesting
sq = (
SelectQuery(Entry, "*")
.where(Q(title="a") | Q(pk=1))
.join(Blog)
.where((Q(title="e") | Q(id=2)) & (Q(title="f") | Q(id=3)))
)
self.assertEqual(
sq.sql(),
(
"SELECT t1.* FROM entry AS t1 INNER JOIN blog AS t2 ON t1.blog_id = t2.id WHERE (t1.title = ? OR t1.pk = ?) AND ((t2.title = ? OR t2.id = ?) AND (t2.title = ? OR t2.id = ?))",
["a", 1, "e", 2, "f", 3],
),
)
def test_selecting_with_switching(self):
sq = SelectQuery(Blog, '*').join(Entry).switch(Blog).where(title='a')
self.assertEqual(sq.sql(), ('SELECT t1.* FROM blog AS t1 INNER JOIN entry AS t2 ON t1.id = t2.blog_id WHERE t1.title = ?', ['a']))
def test_pagination(self):
sq = SelectQuery(Blog).paginate(1, 20)
self.assertEqual(sq.sql(), ("SELECT * FROM blog LIMIT 20 OFFSET 0", []))
sq = SelectQuery(Blog).paginate(3, 30)
self.assertEqual(sq.sql(), ("SELECT * FROM blog LIMIT 30 OFFSET 60", []))
#! /usr/bin/python
# -*- coding:utf-8 -*-
"""
@author: abc
@file: main.py
@date: 2017-02-04
"""
__author__ = "abc"
from peewee import SelectQuery
from model.db.tb_raw import TbRaw
if __name__ == "__main__":
# query = TbRaw.select().where(TbRaw.id > 0)
query = SelectQuery(TbRaw).where(TbRaw.id > 0)
print query.sql()[0] % tuple(query.sql()[1])
for item in query.dicts().execute():
print item
# UNION查询
query = TbRaw.select().where(TbRaw.id >= 2) | TbRaw.select().where(TbRaw.id < 2)
print query.sql()[0] % tuple(query.sql()[1])
for item in query.dicts().execute():
print item