A simple db class that let you create raw sql calls in a nicer way (Active record style).

db.py

""" DB class """
"""
A simple db class that let you create raw sql calls in a nicer way.
The result is cached until reset() or select() is called.
Eg.
db.select('id', 'table AS t1').run()
db.join('join_table AS t2', 't1.id = t2.t1_id').run() // Gives the same result as above (if it didn't return null) because reset() is never called
db.select('id', 'table AS t1').join('join_table AS t2', 't1.id = t2.t1_id').run() // Gives the wanted result

db.select('id', 'table AS t')
db.join('table2 AS t2', 't1.id > t2.t1_id', 'RIGHT')
db.join('table3 AS t3', 't1.id = t3.t1_id', 'LEFT')
db.where('t1.enable', '1')
db.where('t1.enable', [1, 2, 3], 'IN', 'OR')
db.limit(limit='100', offset='5')
db.order('t1.order')
db.group_by('t1.type')
db.run(Model)

It doesn't matter in which order you run the methods (except for reset(), select() and get()).
"""

class DB(object):

    def __init__(self):
        self.reset()

    def reset(self):
        self.where_and = {
            'where': [],
            'value': []
        }
        self.where_or = {
            'where': [],
            'value': []
        }
        self._join = []
        self._select = ''
        self._limit = ''
        self._order = ''
        self._group_by = ''
        self.result = None

    def select(self, column, table):
        self.reset()
        self._select = 'SELECT ' + column + ' FROM ' + table
        return self

    def join(self, table, on='', type='LEFT'):
        str = type + ' JOIN ' + table
        if on != '':
            str += ' ON ' + on
        self._join.append(str)
        return self

    def where(self, column, value, operator='=', type='and'):
        if type.lower() == 'or':
            self.or_where(column, value, operator)
        elif type.lower() == 'and':
            if operator.lower() == 'in':
                self.where_and['where'].append(column + ' in ' + '( ' + ', '.join(value) + ' )')
            elif operator.lower() == 'is' and value.lower() == 'null':
                self.where_and['where'].append(column + ' is null')
            else:
                self.where_and['where'].append(column + ' ' + operator + ' %s')
                self.where_and['value'].append(value)
        return self

    def or_where(self, column, value, operator='='):
        if operator.lower() == 'in':
            self.where_or['where'].append(column + ' in ' + '( ' + ', '.join(value) + ' )')
        elif operator.lower() == 'is' and value.lower() == 'null':
            self.where_or['where'].append(column + ' is null')
        else:
            self.where_or['where'].append(column + ' ' + operator + ' %s')
            self.where_or['value'].append(value)

    def limit(self, limit='0', offset='0'):
        self._limit = 'LIMIT ' + limit + ' OFFSET ' + offset
        return self

    def order(self, order):
        self._order = 'ORDER BY ' + order
        return self

    def group_by(self, group_by):
        self._group_by = 'GROUP BY ' + group_by

    def run(self, model):
        if not self.result is None:
            return self.result

        args = []
        sql = self._select + ' ' + ' '.join(self._join)

        if len(self.where_and['where']) > 0 or len(self.where_or['where']) > 0:
            sql += ' WHERE'

        if len(self.where_and['where']) > 0:
            sql += ' ' + ' AND '.join(self.where_and['where'])
            args += self.where_and['value']
        if len(self.where_or['where']) > 0:
            if len(self.where_and['where']) > 0:
                sql += ' AND '
            sql += ' (' + ' OR '.join(self.where_or['where']) + ') '
            args += self.where_or['value']

        if self._group_by != '':
            sql += ' ' + self._group_by
        if self._order != '':
            sql += ' ' + self._order
        if self._limit != '':
            sql += ' ' + self._limit

        # print sql
        # print args
        self.result = model.objects.raw(sql, args)
        return self.result