def get_page(page_number, page_size, bom_name=None, bom_code=None): count_sql = "select count(1) AS num from %s where 1>0 " % (table_name, ) if int(page_number) <= 1: pager_sql = "select top %d * from %s WHERE bom_id>0 " % ( int(page_size), table_name) else: pager_sql = "select top %d * from %s WHERE bom_id not in(select top %d bom_id from %s) " %\ (int(page_size), table_name, int(page_number-1) * int(page_size), table_name) if is_not_blank(bom_name): pager_sql += " and bom_name like '%%%s%%'" % (bom_name, ) count_sql += " and bom_name like '%%%s%%'" % (bom_name, ) if is_not_blank(bom_code): pager_sql += " and bom_code like '%%%s%%'" % (bom_code, ) count_sql += " and bom_code like '%%%s%%'" % (bom_code, ) # select top 分页条数 * from 表名 where 主键 not in(select top 20 主键 from 表名) res = dict() cur = get_conn().cursor() cur.execute(count_sql) res["total"] = cur.fetchone()["num"] print(cur.rowcount) print(pager_sql) cur.execute(pager_sql) data = get_result_dict(cur) cur.close() res["pageNumber"] = page_number res["pageSize"] = page_size res["data"] = data return res
def get_by_code(bom_code=None): sql = "select * from %s " % (table_name, ) if is_not_blank(bom_code): sql += " where bom_code = '%s'" % (bom_code, ) res = dict() cur = get_conn().cursor() cur.execute(sql) data = get_result_dict(cur) cur.close() return data