How to use PyPika - 10 common examples

def test__coalesce(self):
        q = Q.from_('abc').select(fn.Coalesce('foo', 0))

        self.assertEqual('SELECT COALESCE(foo,0) FROM abc', str(q))

def test_where__equality(self):
        t = Table('abc')
        subquery = Query.from_('efg').select('fiz').where(F('buz') == 0)
        q = Query.from_(t).select(t.foo, t.bar).where(t.bar == subquery)

        self.assertEqual('SELECT foo,bar FROM abc WHERE bar=(SELECT fiz FROM efg WHERE buz=0)', str(q))

def test_orderby_single_str(self):
        q = Query.from_('abc').orderby('foo').select('foo')

        self.assertEqual('SELECT foo FROM abc ORDER BY foo', str(q))

def test_groupby__count_star(self):
        q = Query.from_(self.t).groupby(self.t.foo).select(self.t.foo, fn.Count('*'))

        self.assertEqual('SELECT foo,COUNT(*) FROM abc GROUP BY foo', str(q))

def test_static_from_function_removed_after_called(self):
        with self.assertRaises(AttributeError):
            Query.from_('abc').from_('efg')

def test_select__star_for_table__replacement(self):
        q = Query.from_(self.t0).join(self.t1).on(
            self.t0.foo == self.t1.bar
        ).join(self.t2).on(
            self.t0.buz == self.t2.bam
        ).select(
            self.t0.foo, self.t1.bar, self.t2.bam
        ).select(
            self.t0.star, self.t1.star, self.t2.star
        )

        self.assertEqual('SELECT t0.*,t1.*,t2.* FROM abc t0 '
                         'JOIN efg t1 ON t0.foo=t1.bar '
                         'JOIN hij t2 ON t0.buz=t2.bam', str(q))

def test_empty_query(self):
        q = Query.from_('abc')

        self.assertEqual('', str(q))

def test_where_field_is_n_chars_long(self):
        q = Query.from_(self.t).select(self.t.star).where(self.t.foo.like('___'))

        self.assertEqual("SELECT * FROM abc WHERE foo LIKE '___'", str(q))

def test__lower__field(self):
        q = Q.from_(self.t).select(fn.Lower(self.t.foo))

        self.assertEqual("SELECT LOWER(foo) FROM abc", str(q))

def test_case_using_as(self):
        q = Query.from_(self.t).select(Case().when(self.t.foo == 1, 'a').else_('b').as_('bar'))

        self.assertEqual("SELECT CASE WHEN foo=1 THEN 'a' ELSE 'b' END bar FROM abc", str(q))