python自定义实现链式sql

class StrBuffer(array):
	# 将字符串放入缓冲队列
	def extend(self,*args,**kwargs):
		super().extend(*args,**kwargs)
		return self
	def to_string(self,*args,**kwargs):
		super().tounicode(*args,**kwargs)
		return self

class GenQuerySQL(object):
	def __init__(self,table,alias,**kwargs):
		self.raw_sql=kwargs.get('raw_sql')
		self.table=table
		self.alias=alias
		self.group_by_fields=""
		self.where_conditions=" 1=1 AND "+ alias + ".delflag=FALSE"
		self.fields=""
		self.join_sql=""
		self.order_by_fields=""
		self.order_by_field="created"
		self.having_conditions=" 1=1 "
		
	def having(self,*having_conditions,extra_sql='',alias=''):
		if not alias:
			alias=self.alias
		if having_conditions:
			for having_condition in having_conditions:
				self.having_conditions +=' AND '+ alias + '.' + having_condition + ' =%s'+'\n'
		if extra_sql:
			self.having_conditions+=' AND '+extra_sql
		return self
		
	def where(self,*where_conditions,extra_sql='',mode=0,alias=''):
		if not alias:
			alias=self.alias
		if where_conditions:
			for where_condition in where_conditions:
				if mode:
					self.where_conditions += ' AND '+alias+'.'+where_condition+' like "%%" %s "%%"'+'\n'
		if extra_sql:
			self.where_conditions+=' AND '+extra_sql
		return self
		
	def add_separator(self,separator=',',attr='fields'):
		if hasattr(self,attr):
			setattr(self,attr,getattr(self,attr)+separator)
		return self
		
	def add_fields(self,*args,alias='',allow_alias=True):
		if not allow_alias:
			self.fields+=','.join(args)
			return self
		if not alias:
			alias=self.alias
		multi_fields=[]
		for item in args:
			multi_fields.append(alias+'.'+item)
		self.fields+=','.join(multi_fields)
		return self
		
	def group_by(self,*group_by_fields,alias=''):
		if not alias:
			alias=self.alias
		multi_fields=[]
		for item in group_by_fields:
			multi_fields.append(alias+'.'+item)
		self.group_by_fields+=','.join(multi_fields)
		return self
		
	def order_by(self,*order_by_fields,order='',alias='',allow_alias=True):
		if not allow_alias:
			if order_by_fields:
				multi_fields=[]
				for item in order_by_fields:
					multi_fields.append(item+str(order).upper())
				self.order_by_fields+=','.join(multi_fields)+'\n'
			return self
		if not alias:
			alias=self.alias
		if order_by_fields:
			multi_fields=[]
			for item in order_by_fields:
				multi_fields.append(alias+'.'+item+str(order).upper())
			self.order_by_fields+=','.join(multi_fields)+'\n'
		else:
			self.order_by_fields+=alias+'.'+self.order_by_field+' '+str(order).upper()+'\n'
		return self
		
	def add_join(self,join_sql):
		self.join_sql+=join_sql+'\n'
		return self
		
	def sql(self):
		if self.raw_sql:
			return self.raw_sql
		if not self.order_by_fields:
			self.order_by()
		sql_str_buffer=StrBuffer('u','')
		sql=sql_str_buffer.extend(' SELECT ').extend(self.fields)
		.extend(' FROM ').extend(self.table).extend(' ').extend(self.alias).extend('\n')
		.extend(self.join_sql)
		.extend(' WHERE ').extend(self.where_conditions)
		if self.having_conditions:
			sql.extend(' HAVING ').extend(self.having_conditions)
		sql.extend(' ORDER BY ').extend(self.order_by_fields)
		if self.group_by_fields:
			sql.extend(' GROUP BY ').extend(self.group_by_fields)
		return sql.to_string()

• 具体使用方式

# 查询主表表名,以及对应别名
gen_sql=GenQuerySQL(table='student',alias='stu')
join_sql='left join teacher tea on tea.stu_id=stu.id'
params=[]
fields=('id','name')
model_condition=gen_sql.add_fields(*fields).add_separator(',')
.add_fields('COUNT(*) as cnt',allow_alias=False).add_join(join_sql)
# name可从外部传参
if name:
	# mode为1表示模糊查询,默认精确查询 
	# 默认主表别名,可不启用别名排序
	model_condition.where('name',mode=1)
	params.append(name)
sort=('name','cnt')
# 默认主表别名,可不启用别名排序
model_condition.order_by(*sort_tuple,allow_alias=False)
sql=model_condition.sql()