def delete(self, condition, limit=1): where = dict2where(condition) if where is not None: sql_query = 'DELETE FROM ' + self._tb + ' WHERE ' + where + ' LIMIT 1' db.query(sql_query) else: return None
def update(self, condition, values=None): where = dict2where(condition) update = dict2update(values) if values is not None: sql_query = 'UPDATE ' + self._tb + ' SET ' + update + ' WHERE ' + where + ' LIMIT 1' db.query(sql_query) return True
def delete(self, condition, limit = 1): where = dict2where(condition) if where is not None: sql_query = 'DELETE FROM ' + self._tb + ' WHERE ' + where + ' LIMIT 1' db.query(sql_query) else: return None
def update(self, condition, values = None): where = dict2where(condition) update = dict2update(values) if values is not None: sql_query = 'UPDATE ' + self._tb + ' SET ' + update + ' WHERE ' + where + ' LIMIT 1' db.query(sql_query) return True
def insert(self, values = None): def q(x): return "(" + x + ")" if values: sql_query = 'INSERT INTO '+ self._tb + q(', '.join(values.keys())) + ' VALUES' + q(', '.join('\''+str(_value)+'\'' for _value in values.values())) db.query(sql_query) return self.last_insert_id() else: return None
def editImgLine(self, lineId, imgOrd, imgNameS): dbImgLine = db.select("imgLine", where="lineId=$lineId", vars={"lineId":lineId}).list()[0] if imgOrd is not None and imgOrd != "": imgOrd = int(imgOrd) dbImgOrd = dbImgLine["imgOrd"] headId = dbImgLine.get("headId") if dbImgOrd > imgOrd: db.query("update imgLine set imgOrd = imgOrd + 1 where headId = " + str(headId) + " and imgOrd >= " + str(imgOrd) + " and imgOrd <" + str(dbImgOrd)) elif dbImgOrd < imgOrd: db.query("update imgLine set imgOrd = imgOrd - 1 where headId = " + str(headId) + " and imgOrd > " + str(dbImgOrd) + " and imgOrd <=" + str(imgOrd)) dbImgLine["imgOrd"] = imgOrd if imgNameS is not None and imgNameS != "": dbImgLine["imgNameS"] = imgNameS db.update("imgLine", where="lineId=$lineId", vars={"lineId":dbImgLine.lineId}, **dbImgLine) return dbImgLine["headId"]
def find(self, page, cond=None): results = db.query("SELECT COUNT(*) AS count FROM uploadTaskLog") page.setTotal(results[0].count) sql = "SELECT uploadTaskLog.*,invGroup.groupNum,invGroup.scanTime,invGroup.scanBy,invGroup.imgQty,invGroup.groupSize FROM uploadTaskLog JOIN invGroup on uploadTaskLog.groupId = invGroup.groupId where 1=1" # if cond: # c = copy.deepcopy(cond) # if c.get('status'): # sql += ' and status=' + c.get('status') # if c.get('groupNum'): # c['groupNum'] = "%" + c['groupNum'].upper() + "%" # sql += ' and upper(groupNum) like ' + c['groupNum'] sql += " limit " + str(page.getOffset()) + "," + str(page.size) sql += " order by uploadTaskLog.startTime DESC" page.data = db.query(sql).list() return page
def all(self): categories = [] nodes = {} for node in db.query("SELECT * FROM `node`"): category_id = node["category_id"] if not nodes.has_key(category_id): nodes[category_id] = [] nodes[category_id].append(node) for category in db.query("SELECT * FROM `category`"): category["nodes"] = nodes[category["id"]] categories.append(category) return categories
def count_table(self, conditions=None): sql = 'SELECT count(*) as rows FROM ' + self._tb where = '' if conditions is not None: where = dict2where(conditions) sql += ' WHERE ' + where return db.query(sql)[0].rows
def findTodoTask(self): statuss = [taskStatus.WAITING,taskStatus.UPLOADING] objs = db.query("select headId from uploadTask where status in $statuss",vars={"statuss":statuss}).list() headIds=[] for obj in objs: headIds.append(obj.headId) return headIds
def recent(self): post_query = db.query( """SELECT p.id, p.title, p.time, p.views, p.comments, p.last_update, n.id as node_id, n.name as node_slug, n.display_name as node_name, u.id as user_id, u.name as user_name, u.avatar as user_avatar, l.id as last_user_id, l.name as last_user_name FROM `post` as p LEFT JOIN `node` as n ON ( p.node_id = n.id ) LEFT JOIN `user` as u ON ( p.user_id = u.id ) LEFT JOIN `user` as l on ( p.last_user_id = l.id ) ORDER BY last_update DESC LIMIT 20""" ) posts = [] for post in post_query: post["node"] = Storage({"id": post["node_id"], "slug": post["node_slug"], "name": post["node_name"]}) post["user"] = Storage({"id": post["user_id"], "name": post["user_name"], "avatar": post["user_avatar"]}) post["last_user"] = Storage({"id": post["last_user_id"], "name": post["last_user_name"]}) for d in [ "node_id", "node_slug", "node_name", "user_id", "user_name", "user_avatar", "last_user_id", "last_user_name", ]: del post[d] posts.append(post) return posts
def count_table(self, conditions = None): sql = 'SELECT count(*) as rows FROM ' + self._tb where = '' if conditions is not None: where = dict2where(conditions) sql += ' WHERE ' + where return db.query(sql)[0].rows
def findByPage(self, page): pageSize = page.get("pageSize", constants.pageSize) sql = " FROM imgLine JOIN ocrResult on imgLine.lineId=ocrResult.lineId where 1=1" order = None cond = page.get("cond") if cond: if cond.get("userNum"): sql = sql + " and imgLine.userNum = $userNum " if cond.get("isChecked"): sql = sql + " and imgLine.isChecked = $isChecked " if cond.get("headNum"): sql = sql + " and imgLine.headNum like '%' || $headNum || '%' " if cond.get("order"): order = cond.get("order") print "where: " + str(sql) print "cond: " + str(cond) count = db.query('select count(*) as count ' + sql, vars=cond).list()[0].count/2 offset = pageSize * (page.get("pageNum", 1) - 1) pRet = db.query('select * ' + sql + " and ocrType='PROGRAM'" + " limit " + str(offset) + "," + str(pageSize), vars=cond).list() cRet = db.query('select * ' + sql + " and ocrType='CHECK'" + " limit " + str(offset) + "," + str(pageSize), vars=cond).list() cRetDict = {} for ocr_result in cRet: cRetDict[ocr_result['lineId']] = ocr_result; for ocr_result in pRet: cR = cRetDict[ocr_result['lineId']] for key, val in ocr_result.items(): if key[-1] == 'R' and val == '0': ocr_result[key[:-1] + "C"] = 'label label-warning' for key, val in cR.items(): if key[-1] == 'R' and val == '0': ocr_result[key[:-1]] = cR[key[:-1]] ocr_result[key[:-1] + "C"] = 'label label-danger' page["pageCount"] = (count +pageSize - 1) / pageSize page["data"] = pRet return page
def uploading2FailuerByTimeout(self): #24*60=1440 db.query("update imgHead set status='FAILURE', errorMsg='超时,任务终止' where status='UPLOADING' and ((uploadSize<1024 and (julianday('now','localtime')-julianday(uploadTime))*1440>1) or (julianday('now','localtime')-julianday(uploadTime))*1440>20)")
def addUploadSize(self, headId, uploadSize): if headId: db.query("update imgHead set uploadSize = uploadSize +"+str(uploadSize)+" where headId = " + str(headId))
def last_insert_id(self): return db.query('select last_insert_id() as id')[0].id
def getHeadSize(self, headId): for o in db.query("select sum(imgSize) as headSize from imgLine where headId=$headId", vars={"headId":headId}): return int(o.headSize) if o.headSize else 0 return 0
def query(self, sql): db.query(sql) return True
def query_result(self, sql): return db.query(sql)
def getImgQty(self, headId): for o in db.query("select count(*) as imgQty from imgLine where headId=$headId", vars={"headId":headId}): return int(o.imgQty) if o.imgQty else 0 return 0
def getByHeadId(self, headId): for uploadTask in db.query("select * from uploadTask where headId=$headId", vars={"headId":headId}).list(): return uploadTask
def getMaxOrd(self, cond): for o in db.query("select max(headOrd) as headOrd from imgHead where userNum=$userNum and belong = $belong and status in ('TODO','WAIT')", vars=cond): return int(o.headOrd) if o.headOrd else 0 return 0
def addUploadSize(self, lineId,uploadSize): if lineId: db.query("update imgLine set uploadSize = uploadSize +"+str(uploadSize)+" where lineId = " + str(lineId)) db.query("update imgHead set uploadSize = (select sum(l2.uploadSize) from imgLine l1, imgLine l2 where l1.headId=l2.headId and l1.lineId="+ str(lineId)+") where headId = (select headId from imgLine where lineId=" + str(lineId)+")")
def getById(self, uploadTaskId): for uploadTask in db.query("select * from uploadTask where uploadTaskId=$uploadTaskId", vars={"uploadTaskId":uploadTaskId}).list(): return uploadTask
def findAll(self): return db.query("SELECT uploadTask.*,imgHead.headNum,imgHead.imgQty,imgHead.headSize,imgHead.belong FROM uploadTask JOIN imgHead on uploadTask.headId = imgHead.headId order by uploadTask.status DESC").list()
def uploadFail(self, headId, errorMsg): if headId: db.query("update imgHead set status='FAILURE', errorMsg='"+str(errorMsg)+"' where headId = " + str(headId))
def getMaxOrd(self, headId): for o in db.query("select max(imgOrd) as imgOrd from imgLine where headId=$headId", vars={"headId":headId}): return int(o.imgOrd) if o.imgOrd else 0 return 0