class Builder:
wheres = []
orders = []
tables = []
fields = []
groups = []
havings = []
limits = {}
distinct = False
def __init__(self, config):
self.connection = Connection(config)
self.grammar = Grammar()
"""
Select the table.
@param string table
@return self
"""
def table(self, table):
if self.connection.config.has_key('prefix'):
table = self.connection.config['prefix']+table
if not table in self.tables:
self.tables.append(table)
return self
"""
Add an "order by".
@param dict|string sort
@param string way
@return self
"""
def order(self, sort, way="asc"):
if isinstance(sort, dict):
for k, v in sort.items():
self.orders.append({'sort': k, 'way': v})
else:
self.orders.append({'sort': sort, 'way': way.lower()})
return self
"""
Select the fields.
@param tuple args
@return self
"""
def select(self, *args):
args = list(args)
if len(args) == 0:
self.fields = ["*"]
else:
for item in args:
self.fields += item if isinstance(item, list) else [item]
self.fields = list(set(self.fields))
return self
"""
Add a "distinct".
@return self
"""
def distinct(self):
self.distinct = True
return self
"""
Limit the number of results.
@param int offset
@param int num
@return self
"""
def limit(self, offset, num=0):
self.limits = {'num': offset, 'offset': 0} if num==0 else {'offset': offset, 'num': num}
return self
"""
Add a basic "where".
@param string field
@param string operator
@param string value
@param string boolean
@return self
"""
def where(self, field, operator=None, value=None, boolean='and'):
if not value:
value = operator
operator = '='
whereType = 'basic'
self.wheres.append({'field': field, 'value': value, 'operator': operator, 'boolean': boolean, 'type': whereType})
return self
"""
Add a "or where".
@param string field
@param string operator
@param string value
@return self
"""
def orWhere(self, field, operator=None, value=None):
return self.where(field, operator, value, 'or')
"""
Add a where between.
@param string field
@param string value
@param string boolean
@param boolean isNot
@return self
"""
def whereBetween(self, field, value, boolean='and', isNot=False):
if not isinstance(value, list):
value = list(value)
whereType = 'between'
self.wheres.append({'field': field, 'value': value, 'boolean': boolean, 'not': isNot, 'type': whereType})
return self
"""
Add a or where between.
@param string field
@param string value
@return self
"""
def orwhereBetween(self, field, value):
return self.whereBetween(field, value, 'or')
"""
Add a where not between.
@param string field
@param string value
@return self
"""
def whereNotBetween(self, field, value):
return self.whereBetween(field, value, 'and', True)
"""
Add a or where not between.
@param string field
@param string value
@return self
"""
def orWhereNotBetween(self, field, value):
return self.whereBetween(field, value, 'or', True)
"""
Add a where in.
@param string field
@param string value
@param string boolean
@param boolean isNot
@return self
"""
def whereIn(self, field, value, boolean='and', isNot=False):
if not isinstance(value, list):
value = list(value)
whereType = 'in'
self.wheres.append({'field': field, 'value': value, 'boolean': boolean, 'not': isNot, 'type': whereType})
return self
"""
Add a or where in.
@param string field
@param string value
@return self
"""
def orWhereIn(self, field, value):
return self.whereIn(field, value, 'or')
"""
Add a where not in.
@param string field
@param string value
@return self
"""
def whereNotIn(self, field, value):
return self.whereIn(field, value, 'and', True)
"""
Add a or where not in.
@param string field
@param string value
@return self
"""
def orWhereNotIn(self, field, value):
return self.whereIn(field, value, 'or', True)
"""
Add a where is null.
@param string field
@param string boolean
@param boolean isNot
@return self
"""
def whereNull(self, field, boolean='and', isNot=False):
whereType = 'null'
self.wheres.append({'field': field, 'boolean': boolean, 'not': isNot, 'type': whereType})
return self
"""
Add a or where is null.
@param string field
@return self
"""
def orWhereNull(self, field):
return self.whereNull(field, 'or')
"""
Add a where is not null.
@param string field
@return self
"""
def whereNotNull(self, field):
return self.whereNull(field, 'and', True)
"""
Add a or where is not null.
@param string field
@return self
"""
def orWhereNotNull(self, field):
return self.whereNull(field, 'or', True)
"""
Add a raw where.
@param string sql
#param string boolean
@return self
"""
def whereRaw(self, sql, boolean='and'):
whereType = 'raw'
self.wheres.append({'sql': sql, 'boolean': boolean, 'type': whereType})
return self
"""
Add a or raw where.
@param string sql
@return self
"""
def orWhereRaw(self, sql):
return self.whereRaw(sql, 'or')
"""
Add a group by.
@param tuple args
@return self
"""
def groupBy(self, *args):
args = list(args)
for item in args:
self.groups += item if isinstance(item, list) else [item]
self.groups = list(set(self.groups))
return self
"""
Add a having.
@param string field
@param string operator
@param string value
@param string boolean
@return self
"""
def having(self, field, operator=None, value=None, boolean='and'):
whereType = 'basic'
self.havings.append({'field': field, 'value': value, 'operator': operator, 'boolean': boolean, 'type': whereType})
return self
"""
Add a or having.
@param string field
@param string operator
@param string value
@return self
"""
def orHaving(self, field, operator=None, value=None):
return self.having(field, operator, value, 'or')
"""
Add a raw having.
@param string sql
@param string boolean
@return self
"""
def havingRaw(self, sql, boolean='and'):
whereType = 'raw'
self.havings.append({'sql': sql, 'boolean': boolean, 'type': whereType})
return self
"""
Add a raw or having.
@param string sql
@return self
"""
def orHavingRaw(self, sql):
return self.havingRaw(sql, 'or')
"""
def join(self):
return self.whereNull(field, 'or')
def leftJoin(self):
pass
"""
"""
get all of results
@return tuple
"""
def get(self):
results = self.connection.select(self.__toSql())
self.__optEnd()
return results
"""
get the first column of results
@return dict
"""
def first(self):
results = self.connection.select(self.__toSql())
self.__optEnd()
return results[0] if len(results) != 0 else results
"""
get the field from the first column of results
@param string field
@return mixed
"""
def pluck(self, field):
self.fields = [field]
results = self.connection.select(self.__toSql())
self.__optEnd()
return results[0][field] if len(results) != 0 else None
"""
get list of the field from results
"""
def lists(self, field):
self.fields = [field]
results = self.connection.select(self.__toSql())
self.__optEnd()
if len(results) == 0:
return None
else:
lists = []
for item in results:
lists.append(item[field])
return lists
"""
Return the sum of results of a given field.
@return numeric
"""
def count(self):
return self.aggregate(self.count.__name__, '*')
"""
Return the max of results of a given field.
@param string field
@return numeric
"""
def max(self, field):
return self.aggregate(self.max.__name__, field)
"""
Return the min of results of a given field.
@param string field
@return numeric
"""
def min(self, field):
return self.aggregate(self.min.__name__, field)
"""
Return the avg of results of a given field.
@param string field
@return numeric
"""
def avg(self, field):
return self.aggregate(self.avg.__name__, field)
"""
Return the sum of results of a given field.
@param string field
@return numeric
"""
def sum(self, field):
return self.aggregate(self.sum.__name__, field)
"""
Deal aggregate query.
@param string func
@param string field
@return numeric
"""
def aggregate(self, func, field):
self.fields = [func+"("+field+") as "+func]
results = self.connection.select(self.__toSql())
self.__optEnd()
return results[0][func] if len(results) != 0 else 0
"""
Insert a recode.
@return boolean
"""
def insert(self, values):
sql = self.grammar.compileInsert(self, values)
self.__optEnd()
return self.connection.insert(sql)
"""
Get the insert id
@return int
"""
def getInsertId(self):
return self.connection.getInsertId()
"""
Update a record.
@param dict values
@return boolean
"""
def update(self, values):
sql = self.grammar.compileUpdate(self, values)
self.__optEnd()
return self.connection.update(sql)
"""
Delete a record.
@return boolean
"""
def delete(self):
sql = self.grammar.compileDelete(self)
self.__optEnd()
return self.connection.delete(sql)
"""
Create the select sql
@return string
"""
def __toSql(self):
return self.grammar.compileSelect(self)
"""
clean up record
"""
def __optEnd(self):
self.wheres = []
self.orders = []
self.tables = []
self.fields = []
self.groups = []
self.havings = []
self.limits = {}
self.distinct = False
class Builder:
wheres = []
orders = []
tables = []
fields = []
groups = []
havings = []
limits = {}
distinct = False
def __init__(self, config):
self.connection = Connection(config)
self.grammar = Grammar()
"""
Select the table.
@param string table
@return self
"""
def table(self, table):
if self.connection.config.has_key('prefix'):
table = self.connection.config['prefix'] + table
if not table in self.tables:
self.tables.append(table)
return self
"""
Add an "order by".
@param dict|string sort
@param string way
@return self
"""
def order(self, sort, way="asc"):
if isinstance(sort, dict):
for k, v in sort.items():
self.orders.append({'sort': k, 'way': v})
else:
self.orders.append({'sort': sort, 'way': way.lower()})
return self
"""
Select the fields.
@param tuple args
@return self
"""
def select(self, *args):
args = list(args)
if len(args) == 0:
self.fields = ["*"]
else:
for item in args:
self.fields += item if isinstance(item, list) else [item]
self.fields = list(set(self.fields))
return self
"""
Add a "distinct".
@return self
"""
def distinct(self):
self.distinct = True
return self
"""
Limit the number of results.
@param int offset
@param int num
@return self
"""
def limit(self, offset, num=0):
self.limits = {
'num': offset,
'offset': 0
} if num == 0 else {
'offset': offset,
'num': num
}
return self
"""
Add a basic "where".
@param string field
@param string operator
@param string value
@param string boolean
@return self
"""
def where(self, field, operator=None, value=None, boolean='and'):
if not value:
value = operator
operator = '='
whereType = 'basic'
self.wheres.append({
'field': field,
'value': value,
'operator': operator,
'boolean': boolean,
'type': whereType
})
return self
"""
Add a "or where".
@param string field
@param string operator
@param string value
@return self
"""
def orWhere(self, field, operator=None, value=None):
return self.where(field, operator, value, 'or')
"""
Add a where between.
@param string field
@param string value
@param string boolean
@param boolean isNot
@return self
"""
def whereBetween(self, field, value, boolean='and', isNot=False):
if not isinstance(value, list):
value = list(value)
whereType = 'between'
self.wheres.append({
'field': field,
'value': value,
'boolean': boolean,
'not': isNot,
'type': whereType
})
return self
"""
Add a or where between.
@param string field
@param string value
@return self
"""
def orwhereBetween(self, field, value):
return self.whereBetween(field, value, 'or')
"""
Add a where not between.
@param string field
@param string value
@return self
"""
def whereNotBetween(self, field, value):
return self.whereBetween(field, value, 'and', True)
"""
Add a or where not between.
@param string field
@param string value
@return self
"""
def orWhereNotBetween(self, field, value):
return self.whereBetween(field, value, 'or', True)
"""
Add a where in.
@param string field
@param string value
@param string boolean
@param boolean isNot
@return self
"""
def whereIn(self, field, value, boolean='and', isNot=False):
if not isinstance(value, list):
value = list(value)
whereType = 'in'
self.wheres.append({
'field': field,
'value': value,
'boolean': boolean,
'not': isNot,
'type': whereType
})
return self
"""
Add a or where in.
@param string field
@param string value
@return self
"""
def orWhereIn(self, field, value):
return self.whereIn(field, value, 'or')
"""
Add a where not in.
@param string field
@param string value
@return self
"""
def whereNotIn(self, field, value):
return self.whereIn(field, value, 'and', True)
"""
Add a or where not in.
@param string field
@param string value
@return self
"""
def orWhereNotIn(self, field, value):
return self.whereIn(field, value, 'or', True)
"""
Add a where is null.
@param string field
@param string boolean
@param boolean isNot
@return self
"""
def whereNull(self, field, boolean='and', isNot=False):
whereType = 'null'
self.wheres.append({
'field': field,
'boolean': boolean,
'not': isNot,
'type': whereType
})
return self
"""
Add a or where is null.
@param string field
@return self
"""
def orWhereNull(self, field):
return self.whereNull(field, 'or')
"""
Add a where is not null.
@param string field
@return self
"""
def whereNotNull(self, field):
return self.whereNull(field, 'and', True)
"""
Add a or where is not null.
@param string field
@return self
"""
def orWhereNotNull(self, field):
return self.whereNull(field, 'or', True)
"""
Add a raw where.
@param string sql
#param string boolean
@return self
"""
def whereRaw(self, sql, boolean='and'):
whereType = 'raw'
self.wheres.append({'sql': sql, 'boolean': boolean, 'type': whereType})
return self
"""
Add a or raw where.
@param string sql
@return self
"""
def orWhereRaw(self, sql):
return self.whereRaw(sql, 'or')
"""
Add a group by.
@param tuple args
@return self
"""
def groupBy(self, *args):
args = list(args)
for item in args:
self.groups += item if isinstance(item, list) else [item]
self.groups = list(set(self.groups))
return self
"""
Add a having.
@param string field
@param string operator
@param string value
@param string boolean
@return self
"""
def having(self, field, operator=None, value=None, boolean='and'):
whereType = 'basic'
self.havings.append({
'field': field,
'value': value,
'operator': operator,
'boolean': boolean,
'type': whereType
})
return self
"""
Add a or having.
@param string field
@param string operator
@param string value
@return self
"""
def orHaving(self, field, operator=None, value=None):
return self.having(field, operator, value, 'or')
"""
Add a raw having.
@param string sql
@param string boolean
@return self
"""
def havingRaw(self, sql, boolean='and'):
whereType = 'raw'
self.havings.append({
'sql': sql,
'boolean': boolean,
'type': whereType
})
return self
"""
Add a raw or having.
@param string sql
@return self
"""
def orHavingRaw(self, sql):
return self.havingRaw(sql, 'or')
"""
def join(self):
return self.whereNull(field, 'or')
def leftJoin(self):
pass
"""
"""
get all of results
@return tuple
"""
def get(self):
results = self.connection.select(self.__toSql())
self.__optEnd()
return results
"""
get the first column of results
@return dict
"""
def first(self):
results = self.connection.select(self.__toSql())
self.__optEnd()
return results[0] if len(results) != 0 else results
"""
get the field from the first column of results
@param string field
@return mixed
"""
def pluck(self, field):
self.fields = [field]
results = self.connection.select(self.__toSql())
self.__optEnd()
return results[0][field] if len(results) != 0 else None
"""
get list of the field from results
"""
def lists(self, field):
self.fields = [field]
results = self.connection.select(self.__toSql())
self.__optEnd()
if len(results) == 0:
return None
else:
lists = []
for item in results:
lists.append(item[field])
return lists
"""
Return the sum of results of a given field.
@return numeric
"""
def count(self):
return self.aggregate(self.count.__name__, '*')
"""
Return the max of results of a given field.
@param string field
@return numeric
"""
def max(self, field):
return self.aggregate(self.max.__name__, field)
"""
Return the min of results of a given field.
@param string field
@return numeric
"""
def min(self, field):
return self.aggregate(self.min.__name__, field)
"""
Return the avg of results of a given field.
@param string field
@return numeric
"""
def avg(self, field):
return self.aggregate(self.avg.__name__, field)
"""
Return the sum of results of a given field.
@param string field
@return numeric
"""
def sum(self, field):
return self.aggregate(self.sum.__name__, field)
"""
Deal aggregate query.
@param string func
@param string field
@return numeric
"""
def aggregate(self, func, field):
self.fields = [func + "(" + field + ") as " + func]
results = self.connection.select(self.__toSql())
self.__optEnd()
return results[0][func] if len(results) != 0 else 0
"""
Insert a recode.
@return boolean
"""
def insert(self, values):
sql = self.grammar.compileInsert(self, values)
self.__optEnd()
return self.connection.insert(sql)
"""
Get the insert id
@return int
"""
def getInsertId(self):
return self.connection.getInsertId()
"""
Update a record.
@param dict values
@return boolean
"""
def update(self, values):
sql = self.grammar.compileUpdate(self, values)
self.__optEnd()
return self.connection.update(sql)
"""
Delete a record.
@return boolean
"""
def delete(self):
sql = self.grammar.compileDelete(self)
self.__optEnd()
return self.connection.delete(sql)
"""
Create the select sql
@return string
"""
def __toSql(self):
return self.grammar.compileSelect(self)
"""
clean up record
"""
def __optEnd(self):
self.wheres = []
self.orders = []
self.tables = []
self.fields = []
self.groups = []
self.havings = []
self.limits = {}
self.distinct = False
