コード例 #1
0
ファイル: tests.py プロジェクト: anarcher/peewee
    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',
            []))
コード例 #2
0
ファイル: tests.py プロジェクト: anarcher/peewee
    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']))
コード例 #3
0
ファイル: tests.py プロジェクト: anarcher/peewee
    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',
            []))
コード例 #4
0
ファイル: tests.py プロジェクト: strogo/peewee
    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",
                [],
            ),
        )
コード例 #5
0
ファイル: tests.py プロジェクト: anarcher/peewee
    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', []))
コード例 #6
0
ファイル: tests.py プロジェクト: Nassty/peewee
 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', []))
コード例 #7
0
ファイル: tests.py プロジェクト: Walnuts/peewee
 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', []))
コード例 #8
0
ファイル: tests.py プロジェクト: Walnuts/peewee
    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', []))
コード例 #9
0
ファイル: tests.py プロジェクト: Walnuts/peewee
    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']))
コード例 #10
0
ファイル: tests.py プロジェクト: anarcher/peewee
 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',
         []))
コード例 #11
0
ファイル: tests.py プロジェクト: strogo/peewee
    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",
                [],
            ),
        )
コード例 #12
0
ファイル: tests.py プロジェクト: Walnuts/peewee
    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']))
コード例 #13
0
ファイル: tests.py プロジェクト: Nassty/peewee
    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']))
コード例 #14
0
ファイル: tests.py プロジェクト: strogo/peewee
    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"]))
コード例 #15
0
ファイル: tests.py プロジェクト: strogo/peewee
 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",
             [],
         ),
     )
コード例 #16
0
ファイル: tests.py プロジェクト: Walnuts/peewee
 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', []))
コード例 #17
0
ファイル: tests.py プロジェクト: strogo/peewee
    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"],
            ),
        )
コード例 #18
0
ファイル: tests.py プロジェクト: strogo/peewee
    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"],
            ),
        )
コード例 #19
0
ファイル: tests.py プロジェクト: strogo/peewee
    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"],
            ),
        )
コード例 #20
0
ファイル: tests.py プロジェクト: anarcher/peewee
    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]))
コード例 #21
0
ファイル: tests.py プロジェクト: Nassty/peewee
 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']))
コード例 #22
0
ファイル: tests.py プロジェクト: Walnuts/peewee
 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']))
コード例 #23
0
ファイル: tests.py プロジェクト: Walnuts/peewee
    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]))
コード例 #24
0
ファイル: tests.py プロジェクト: Nassty/peewee
    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']))
コード例 #25
0
ファイル: tests.py プロジェクト: Walnuts/peewee
    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']))
コード例 #26
0
ファイル: main.py プロジェクト: noogel/xyzStudyPython
#! /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
コード例 #27
0
ファイル: tests.py プロジェクト: strogo/peewee
 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"]),
     )
コード例 #28
0
ファイル: tests.py プロジェクト: anarcher/peewee
 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']))
コード例 #29
0
ファイル: tests.py プロジェクト: strogo/peewee
    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],
            ),
        )
コード例 #30
0
ファイル: tests.py プロジェクト: Walnuts/peewee
 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']))
コード例 #31
0
ファイル: tests.py プロジェクト: strogo/peewee
    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", []))
コード例 #32
0
ファイル: main.py プロジェクト: noogel/xyzStudyPython
#! /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