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)
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)