def checkCountCond(self, tableName, field, count, fieldAr, valueAr, symbol='>'): sen = [] for i in range(0, len(fieldAr)): sen.append(fieldAr[i] + '="' + valueAr[i] + '"') sen = ms.mergeInString(sen, ' and ') sqlFormula = 'select * from {0} where {1} group by {2} having count({3}){4}{5}'.format( tableName, sen, field, field, symbol, count) self.mycursor.execute(sqlFormula) res = self.mycursor.fetchall() data = ms.modifySqlResult(res) if (data is None): return False else: return True
def selectFieldLikeData(self, tableName, reqField, field, like): sqlFormula = 'select {0} from {1} where {2} like "{3}"'.format( reqField, tableName, field, like) self.mycursor.execute(sqlFormula) res = self.mycursor.fetchall() res = ms.modifySqlResult(res) return res
def countValues(self, tableName): sqlFormula = 'select count(*) from {0}'.format(tableName) self.mycursor.execute(sqlFormula) res = self.mycursor.fetchall() data = ms.modifySqlResult(res) return data[0]
def selectAllLikeData(self, tableName, field, like): sqlFormula = 'select * from {0} where {1} like "{2}"'.format( tableName, field, like) self.mycursor.execute(sqlFormula) res = self.mycursor.fetchall() res = ms.modifySqlResult(res) return res
def selectChat(self,userName1,userName2,rangeIn,rangeOut): tName='C_'+userName1+'_'+userName2 count=self.cb.countValues(tName) if int(count)>=int(rangeOut): data=self.cb.selectRangeData(tName,'sno',rangeIn,rangeOut,'notifications') data=ms.modifySqlResult(data) return data
def selectNotification(self,userName,rangeIn,rangeOut): tName='N_'+userName count=self.cb.countValues(tName) #if int(count)>=int(rangeOut): #data=self.cb.selectRangeData(tName,'sno',rangeIn,rangeOut,'notifications') data=self.cb.selectFieldData(tName,'notifications') data=ms.modifySqlResult(data) return data
def checkCount(self, tableName, field, count, symbol='>'): sqlFormula = 'select * from {0} group by {1} having count({2}){3}{4}'.format( tableName, field, field, symbol, count) self.mycursor.execute(sqlFormula) res = self.mycursor.fetchall() data = ms.modifySqlResult(res) if (data is None): return False else: return True
def getColByData(self, tableName, word): cb = db.cb1(self.mycursor, self.database) cols = cb.getColName(tableName) data = [] for i in cols: cond = cb.checkData(tableName, i, word) if (cond): data.append(i) data = ms.modifySqlResult(data) return data
def countByCond(self, tableName, fields, values): sen = [] for i in range(0, len(fields)): sen.append(fields[i] + '="' + values[i] + '"') sen = ms.mergeInString(sen, ' and ') sqlFormula = 'select count(*) from {0} where {1}'.format( tableName, sen) self.mycursor.execute(sqlFormula) res = self.mycursor.fetchall() data = ms.modifySqlResult(res) return data[0]
def selectRandomWithLike(self, tableName, field, like, reqField='*', limit=2): sqlFormula = f'select {reqField} from {tableName} where {field} like "{like}" order by rand() limit {limit}' self.mycursor.execute(sqlFormula) res = self.mycursor.fetchall() res = ms.modifySqlResult(res) return res
def change2Dict(self, tName, data): cols = self.cb.getColName(tName) cols = ms.modifySqlResult(cols) data = ad.cvtArr2Dict(cols, data) return data
def selectFriendRequest(self, userName): tName = 'FR_' + userName data = self.cb.selectFieldData(tName, 'userName') data = ms.modifySqlResult(data) return data
def selectRangeData(self, time, r1, r2, _TimeInfo='_TimeInfo'): data = cb.selectRangeData('timedata', _TimeInfo, r1, r2) data = ms.modifySqlResult(data) return data
def showTables(self): sqlFormula = 'show tables' self.mycursor.execute(sqlFormula) res = self.mycursor.fetchall() res = ms.modifySqlResult(res) return res
def selectBlock(self, userName): tName = 'B_' + userName data = self.cb.selectFieldData(tName, 'userName') data = ms.modifySqlResult(data) return data
def select1Data(self, time, _TimeInfo='_TimeInfo'): data = cb.selectAllCheckData('timedata', _TimeInfo, time) data = ms.modifySqlResult(data) return data
def showTablesByLike(self, like): sqlFormula = 'show tables like "{0}"'.format(like) self.mycursor.execute(sqlFormula) res = self.mycursor.fetchall() res = ms.modifySqlResult(res) return res
def getSomeProfile(self, userName): data = self.cb.selectFieldByCond('profile', ['userName', 'name', 'locProfilePic'], ['userName'], [userName]) data = ms.modifySqlResult(data) return data
def selectByType(self, typeVal, typeIn='_T1'): data = cb.selectAllCheckData('timedata', typeIn, typeVal) data = ms.modifySqlResult(data) return data