Beispiel #1
0
    def test_select_join(self):
        """Test the DBSelects' *_join mehtods"""

        self._helper.query('CREATE TABLE a (x INTEGER)')
        self._helper.query('CREATE TABLE b (y INTEGER)')
        self._helper.insert('a', ({'x': 1}, {'x': 2}, {'x': 3}))
        self._helper.insert('b', ({'y': 2}, {'y': 3}, {'y': 4}))
        sql = DBSelect('a', {'n': 'COUNT(*)'}).inner_join('b', 'a.x = b.y', ())
        self.assertTrue(self._helper.query(sql.render()).fetchone()['n'] == 2)
        sql = DBSelect('a', {'n': 'COUNT(*)'}).left_join('b', 'a.x = b.y', ())
        self.assertTrue(self._helper.query(sql.render()).fetchone()['n'] == 3)
Beispiel #2
0
    def test_select_from(self):
        """Test the SELECT <columns> FROM part of DBSelect"""

        select = DBSelect('a')
        sql = re.sub(r'\s+', ' ', select.render())
        self.assertEqual(sql, 'SELECT "a".* FROM "a"')

        select.set_from('b', ())
        sql = re.sub(r'\s+', ' ', select.render())
        self.assertEqual(sql, 'SELECT "a".* FROM "b" INNER JOIN "a"')

        select.set_from('c', ('d', 'e'))
        sql = re.sub(r'\s+', ' ', select.render())
        self.assertEqual(
            sql,
            'SELECT "a".*, "c"."d", "c"."e" ' +
                'FROM "c" INNER JOIN "b" INNER JOIN "a"'
        )

        count = self._helper.query(
            DBSelect('test_table', {'count': 'COUNT(*)'}).render()
        ).fetchone()
        self.assertTrue(type(count['count']) == int)
        self.assertTrue(count['count'] >= 0)