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_tree():
# 以后每次需要数据库连接就是用connection（）函数获取连接就好了
conn = db_util.get_conn()
cur = conn.cursor()
sql = "select * from t_menu WHERE pid=%d AND display=1 ORDER BY disorder" % (
0, )
cur.execute(sql)
result_list = db_util.get_result_dict(cur)
for result_dict in result_list:
sql = "select * from t_menu WHERE pid=%d AND display=1 ORDER BY disorder" % (
result_dict['id'], )
cur.execute(sql)
r_2 = db_util.get_result_dict(cur)
result_dict['children'] = r_2
# cur.close()
# conn.close()
return result_list
def get_rate_s():
sql = "select distinct bom_rate from %s " % (table_name, )
res = dict()
cur = get_conn().cursor()
cur.execute(sql)
data = get_result_dict(cur)
cur.close()
return data
def get_all_for_tree():
sql = "select * from %s " % (table_name, )
res = dict()
cur = get_conn().cursor()
cur.execute(sql)
data = get_result_dict(cur)
cur.close()
return data
def get_by_bom_code(bom_code):
conn = get_conn()
cur = conn.cursor()
sql = "select * from " + table_name + " where bom_code='%s'" % (bom_code,)
cur.execute(sql)
res = get_result_dict(cur)
cur.close()
# conn.close()
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