def get_by_filter(cls,
offset=0,
limit=1000,
order_by=None,
order='asc',
filter_str=''):
if order_by is None:
order_by = cls._primary_key
sql_stmt = ("SELECT * FROM " + cls._table_name + " ORDER BY " +
order_by + " " + order + " LIMIT %(offset)s, %(limit)s")
sql_stmt_count = ("SELECT count(" + cls._primary_key + ") FROM " +
cls._table_name)
where_str = Filter.filter_str_to_sql(
allow_keywords=cls.get_filter_keywords(), filter_str=filter_str)
if where_str != '':
sql_stmt = ("SELECT * FROM " + cls._table_name + " WHERE " +
where_str + " ORDER BY " + order_by + " " + order +
" LIMIT %(offset)s, %(limit)s")
sql_stmt_count = ("SELECT count(" + cls._primary_key + ") FROM " +
cls._table_name + " WHERE " + where_str)
cnx = db.cnxpool.get_connection()
cursor = cnx.cursor(dictionary=True, buffered=True)
try:
cursor.execute(sql_stmt, {'offset': offset, 'limit': limit})
rows = cursor.fetchall()
cursor.execute(sql_stmt_count)
count = cursor.fetchone()
return rows, count["count(" + cls._primary_key + ")"]
finally:
cursor.close()
cnx.close()
def update_by_filter(cls, kv, filter_str=''):
# 过滤掉不予支持批量更新的字段
_kv = {}
for k, v in kv.iteritems():
if k in cls.get_allow_update_keywords():
_kv[k] = v
if _kv.__len__() < 1:
return
# set_str = ', '.join(map(lambda x: x + ' = %(' + x + ')s', _kv.keys()))
# 上面为通过map实现的方式
set_str = ', '.join(
['{0} = %({0})s'.format(key) for key in _kv.keys()])
where_str = Filter.filter_str_to_sql(
allow_keywords=cls.get_filter_keywords(), filter_str=filter_str)
sql_stmt = ("UPDATE " + cls._table_name + " SET " + set_str +
" WHERE " + where_str)
cnx = db.cnxpool.get_connection()
cursor = cnx.cursor(dictionary=True, buffered=True)
try:
cursor.execute(sql_stmt, _kv)
cnx.commit()
finally:
cursor.close()
cnx.close()
def delete_by_filter(cls, filter_str=''):
where_str = Filter.filter_str_to_sql(allow_keywords=cls.get_filter_keywords(), filter_str=filter_str)
sql_stmt = ("DELETE FROM " + cls._table_name + " WHERE " + where_str)
cnx = db.cnxpool.get_connection()
cursor = cnx.cursor(dictionary=True, buffered=True)
try:
cursor.execute(sql_stmt)
cnx.commit()
finally:
cursor.close()
cnx.close()