def get_adatok(self, para): print(para) # self.adatok[0] : p1_id # self.adatok[1] : name1 # self.adatok[2] : p2_id # self.adatok[3] : name2 # self.adatok[4] : start_score1 # self.adatok[5] : start_score2 # self.adatok[6] : match # self.adatok[7] : legs # self.adatok[8] : sets # self.adatok[9] : dátum self.adatok = [] name1_id = int(para[0]) self.adatok.append(name1_id) query_name1 = QSqlQuery(f"select player_name from players where player_id={name1_id}", db=db) query_name1.exec_() while query_name1.next(): name1 = query_name1.value(0) self.adatok.append(name1) name2_id = int(para[1]) self.adatok.append(name2_id) query_name2 = QSqlQuery(f"select player_name from players where player_id={name2_id}", db=db) query_name2.exec_() while query_name2.next(): name2 = query_name2.value(0) self.adatok.append(name2) start_score1 = int(para[2]) start_score2 = int(para[3]) match = int(para[7]) setek = int(para[5]) self.adatok.append(start_score1) self.adatok.append(start_score2) self.adatok.append(match) # Kell a max set-number, ezt beállítani a sets változóba # Ciklussal minden set-ben megnézni a max leg-numbert, és ezeket append-elni a legs[]-hez # Végül leg, set sorrendben append-elni az adatokhoz legs = [] sets = 0 query2 = QSqlQuery( f"select max(set_id) as max_set from matches where match_id={match}", db=db) query2.exec_() while query2.next(): sets = int(query2.value(0)) for i in range(1, sets + 1): query = QSqlQuery(f"select max(leg_id) as max_leg from matches where match_id={match} and set_id={i}", db=db) query.exec_() while query.next(): legs.append(int(query.value(0))) # sets.append(int(query.value(1))) self.adatok.append(legs) self.adatok.append(sets) datum = para[6][:16] self.adatok.append(datum) print(self.adatok)
def readSQL(sql_text, params=None, named=False, check_unique=False): if params is None: params = [] query = QSqlQuery(db_connection()) query.setForwardOnly(True) if not query.prepare(sql_text): logging.error( f"SQL prep: '{query.lastError().text()}' for query '{sql_text}' | '{params}'" ) return None for param in params: query.bindValue(param[0], param[1]) if not query.exec_(): logging.error( f"SQL exec: '{query.lastError().text()}' for query '{sql_text}' | '{params}'" ) return None if query.next(): res = readSQLrecord(query, named=named) if check_unique and query.next(): logging.warning( g_tr('DB', "More than 1 record matched SQL query ") + f"'{sql_text}' | '{params}'") return None # More then one record in result when only one expected return res else: return None
def getStatusCode(self): if self.statusCode == 8: query = QSqlQuery("SELECT COUNT(*) FROM Student JOIN " "Attendance ON Student.id = Attendance.id " "AND Attendance.date = (SELECT date('now')) " "AND Attendance.lecture_no = 1") query.next() self.ocount = query.value(0) self.oCount_changed.emit() return self.statusCode
def change_data(self, szum=None): self.data = None self.data = szum self.name1 = self.data[1] self.name2 = self.data[3] match_id = self.data[6] p1_id = self.data[0] p2_id = self.data[2] # Végeredmény self.won1 = 0 self.won2 = 0 self.avg1 = self.avg2 = 0 eredmenyek_model = QSqlQueryModel() eredmenyek_query = QSqlQuery(f"select * from matches where match_id={match_id}", db=db) eredmenyek_model.setQuery(eredmenyek_query) for x in range(1, self.data[8] + 1): l1 = l2 = 0 for i in range(eredmenyek_model.rowCount()): # csak set-eket összesítünk if eredmenyek_model.record(i).value(2) == x: if eredmenyek_model.record(i).value(3) == p1_id: l1 += 1 else: l2 += 1 # print("Set: ", x, "L1: ", l1, "L2: ", l2) if self.data[8] == 1: self.won1 = l1 self.won2 = l2 else: if l1 > l2: self.won1 += 1 else: self.won2 += 1 # Átlagok db1 = db2 = sum1 = sum2 = 0 for x in range(1, self.data[8] + 1): for leg in range(1, self.data[7][x - 1] + 1): query = QSqlQuery(f"select max(round_number) as maxround, sum(points) as sumpont from dobas where leg_id={leg} and set_id={x} and match_id={match_id} and player_id={p1_id}") query.exec_() while query.next(): db1 += query.value(0) sum1 += query.value(1) query2 = QSqlQuery(f"select max(round_number) as maxround, sum(points) as sumpont from dobas where leg_id={leg} and set_id={x} and match_id={match_id} and player_id={p2_id}") query2.exec_() while query2.next(): db2 += query2.value(0) sum2 += query2.value(1) self.avg1 = round(sum1 / db1 * 3, 2) self.avg2 = round(sum2 / db2 * 3, 2) self.update()
def ins_tb(self, start_date, end_date): q_list = [] for key in self.tb_name.keys(): tb_sn = 'LOGS_' + key query = QSqlQuery(self.db) sql = f'''SELECT LOGS_TIME, LOGS_CHONE, LOGS_CHTWO, LOGS_CHTHREE, LOGS_CHFOUR, BAT_DC_STATE FROM {tb_sn} where LOGS_TIME > #{start_date}# AND LOGS_TIME < #{end_date}# ORDER BY LOGS_TIME; ''' query.exec_(sql) # q_list.append(query) # print(sql) # return q_list # 如果这个时间段没有数据,就insert数据 if not query.next(): # print(self.tb_name[sn][0], self.f_dt(start_time), self.f_dt(end_time)) # self.myinsert(tb_sn, start_time, end_time) print(f'{self.tb_name[key][0]}没有数据,开始写入数据……') self.myinsert(tb_sn, start_date, end_date) else: print( tb_sn, self.tb_name[key][0], query.value('LOGS_TIME').toPython().strftime( '%Y/%#m/%d %H:%M'))
def get_torrent(self): db = self.get_db() try: if not db.isOpen(): db.open() query = QSqlQuery( query= "select * from torrents where (hash is null) and (last_checked is null) order by id desc limit 1;", db=db) topic = {} while query.next(): topic['id'] = query.value('id') topic['forum'] = query.value('forum') topic['title'] = query.value('title') topic['seed'] = query.value('seed') topic['leech'] = query.value('leech') topic['published'] = query.value('published') topic['last_modified'] = query.value('last_modified') topic['hash'] = query.value('hash') topic['downloads'] = query.value('downloads') topic['last_checked'] = query.value('last_checked') topic['user_id'] = query.value('user_id') return topic finally: db.close()
def importSteamLibrary(self): apiKey, ok = QInputDialog.getText(self, "Import Steam Library", "Enter Steam API Key:") if ok and not (apiKey.isspace() or apiKey == ""): steamID, ok = QInputDialog.getText(self, "Import Steam Library", "Enter Steam User ID:") if ok and not (steamID.isspace() or steamID == ""): try: games = getSteamLibrary(apiKey, steamID) except (PermissionError, ValueError) as e: msgBox = QMessageBox(QMessageBox.Critical, "Error", "An error occured.") msgBox.setInformativeText(str(e)) msgBox.exec_() else: if "Steam" not in self.allPlatforms: self.allPlatforms.add("Steam") self.allRegions.add("Steam") self.filterDock.updatePlatforms(sorted(self.allPlatforms, key=str.lower)) self.filterDock.updateRegions(sorted(self.allRegions, key=str.lower)) self.gamesTableView.addData(games) else: # Only add games not already in collection existingGames = [] query = QSqlQuery() query.exec_("SELECT Name from games WHERE Region='Steam'") while query.next(): existingGames.append(query.value(0)) for game in games: if game["name"] not in existingGames: self.gamesTableView.addData(game) self.overview.updateData(self.gamesTableView) self.randomizer.updateLists(self.gamesTableView.ownedItems(), sorted(self.allPlatforms, key=str.lower), sorted(self.allGenres, key=str.lower)) self.search()
def __init__(self, db_path): self.tb_name = {} self.db = '' # 连接数据库 self.db = QSqlDatabase.addDatabase("QODBC") self.db.setDatabaseName( "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};FIL={MS Access};DBQ=" + db_path) # db.setUserName('sa') self.db.setPassword('20121110') # 根据ok的值判断数据库是否连接成功 # self.ok = db.open() if self.db.open(): query = QSqlQuery(self.db) query.exec_( "SELECT LOGGER_SN, LOGGER_NAME, CHONE_HIGH , CHONE_LOW, CHTWO_HIGH, CHTWO_LOW FROM TO_LOGGER_INFO" ) while query.next(): # if self.tb_name[query.value(0)]!='H201403057' and self.tb_name[query.value(0)]!='HT20143221': self.tb_name[query.value(0)] = (query.value(1), query.value(2), query.value(3), query.value(4), query.value(5)) del (self.tb_name['H201403057']) del (self.tb_name['HT20143221'])
def get_new_offer_number(user_id: int) -> int: text = f"SELECT public.get_new_offer_number({user_id})" query = QSqlQuery(text) if not query.next(): logging.error(f"Query failed: {text}") logging.error(query.lastError().text()) raise RuntimeError("Error accessing database") return query.record().value(0)
def get_merchandise_record(merchandise_id: int) -> QSqlRecord: text = f"select * from merchandise_view('{date.today()}') where merchandise_id = '{merchandise_id}'" query = QSqlQuery(text) if not query.next(): logging.error(f"Query failed: {text}") logging.error(query.lastError().text()) raise RuntimeError("Error accessing database") return query.record()
def LoadDownload(self, owner): query = QSqlQuery(self.db) suc = query.exec_(""" select * from download """) if not suc: Log.Warn(query.lastError().text()) downloads = {} while query.next(): # bookId, downloadEpsIds, curDownloadEpsId, curConvertEpsId, title, savePath, convertPath info = DownloadInfo(owner) info.bookId = query.value(0) data = json.loads(query.value(1)) info.downloadEpsIds = [int(i) for i in data] info.curDownloadEpsId = query.value(2) info.curConvertEpsId = query.value(3) info.title = query.value(4) info.savePath = query.value(5) info.convertPath = query.value(6) info.status = query.value(7) info.convertStatus = query.value(8) downloads[info.bookId] = info query = QSqlQuery(self.db) suc = query.exec_(""" select * from download_eps """) if not suc: Log.Warn(query.lastError().text()) while query.next(): # bookId, epsId, epsTitle, picCnt, curPreDownloadIndex, curPreConvertId bookId = query.value(0) task = downloads.get(bookId) if not task: continue info = DownloadEpsInfo(task) info.epsId = query.value(1) info.epsTitle = query.value(2) info.picCnt = query.value(3) info.curPreDownloadIndex = query.value(4) info.curPreConvertId = query.value(5) task.epsInfo[info.epsId] = info return downloads
def get_var(key: str) -> str: text = f"SELECT value FROM public.vars WHERE key = '{key}'" query = QSqlQuery(text) if not query.next(): logging.error(f"Query failed: {text}") logging.error(query.lastError().text()) raise RuntimeError("Error accessing database") record = query.record() return record.value("value")
def load_data(self, tornaid): self.clear() query = QSqlQuery( f"select * from torna_resztvevok where torna_id={tornaid}") query.exec_() while query.next(): item = QListWidgetItem(query.value(1)) item.setData(1, query.value(0)) self.addItem(item)
def get_password() -> str: """Return the password from the database.""" query = QSqlQuery(QSqlDatabase.database()) query.exec_("select password from credentials") if query.next(): password = query.value(0) query.finish() return password
def select(self, query): if self.db.open(): res = [] sql_query = QSqlQuery() sql_query.exec_(query) while sql_query.next(): res.append(sql_query.value(0)) self.db.close() return res
def torna_valasztas(self, i): self.torna_id = self.tournaments.itemData(i) torna = QSqlQuery( f"select * from torna_settings where torna_id={self.torna_id}") torna.exec_() while torna.next(): self.csoportok_szama = torna.value(3) self.sorok_szama = torna.value(4) self.variant = torna.value(5) self.sets = torna.value(7) self.legsperset = torna.value(8) self.nyereshez_kell = torna.value(8) self.pont_gyozelem = torna.value(11) self.pont_vereseg = torna.value(13) self.tablak = [] for cs in range(self.csoportok_szama): tablasor = [] for sor in range(self.sorok_szama): tablasor.append(0) self.tablak.append(tablasor) query2 = QSqlQuery( f"select * from torna_tablak where torna_id={self.torna_id}") query2.exec_() while query2.next(): self.tablak[query2.value(2)][query2.value(3)] = query2.value(1) self.nevek = [] for cs in range(self.csoportok_szama): neveksor = [] for sor in range(self.sorok_szama): neveksor.append("") self.nevek.append(neveksor) query2 = QSqlQuery( f"select player_id, player_name from torna_resztvevok where torna_id={self.torna_id}" ) query2.exec_() while query2.next(): for x in range(self.csoportok_szama): for y in range(self.sorok_szama): if query2.value(0) == self.tablak[x][y]: self.nevek[x][y] = query2.value(1) self.create_widgets() self.set_layout()
def load(self, lst): query = QSqlQuery() query.exec_("SELECT task from " + lst + ";") while query.next(): print(query.value(0)) if lst == "priority": self.ui.priority.addItem(query.value(0)) elif lst == "urgent": self.ui.urgent.addItem(query.value(0)) elif lst == "dueDate": self.ui.dueDate.addItem(query.value(0))
def create_merchandise(code: str, description: str, by_metre: bool, discount_group: str, price: float) -> int: unit = 'm' if by_metre else 'pc.' query_text = f"SELECT public.create_merchandise('{code}', '{description}', '{unit}', '{discount_group}', {price})" query = QSqlQuery(query_text) if not query.next(): raise RuntimeError( f"Query {query_text} failed with:\n{query.lastError().text()}") merchandise_id = query.value(0) if merchandise_id < 0: raise RuntimeError(f"Query {query_text} failed on server side") return merchandise_id
def getValue(self, key): get_query = QSqlQuery(self.db) get_query.setForwardOnly(True) get_query.prepare("SELECT value FROM settings WHERE name=:key") value = None get_query.bindValue(":key", key) if not get_query.exec_(): logging.fatal(f"Failed to get settings for key='{key}'") return value if get_query.next(): value = get_query.value(0) return value
def torna_valasztas(self, i): self.torna_id = self.tournaments.itemData(i) torna = QSqlQuery( f"select * from torna_settings where torna_id={self.torna_id}") torna.exec_() while torna.next(): self.csoportok_szama = torna.value(3) self.sorok_szama = torna.value(4) self.variant = torna.value(5) self.sets = torna.value(7) self.legsperset = torna.value(8) self.create_widgets() self.set_layout()
class DataBase: def __init__(self): db_path = os.path.join(os.path.dirname(os.path.realpath(__file__)), 'data.db') self.db = QSqlDatabase.addDatabase('QSQLITE') self.db.setDatabaseName(db_path) self.db.open() self.query = QSqlQuery() def select(self, fields: tuple, table: str, condition=None): """ select items from table For example, to execute the sql state `select name from Teacher where tid=1`, use `select(fields=('name',), table='Teacher', condition='tid=1')` params fields: tuple of str, such as ('id', 'name', 'type') table: str, name of target table condition: str, such as 'id=1' returns a list of dict """ state = 'select {fields} from {table}'.format(fields=', '.join(fields), table=table) if condition is not None: state += ' where {con}'.format(con=condition) res = [] self.query.clear() self.query.exec_(state) print(state) while self.query.next(): item = {f: self.query.value(f) for f in fields} res.append(item) return res def insert(self, table: str, kv: dict): state = 'insert into {t} ({f}) values ({v})'.format( t=table, f=', '.join(kv.keys()), v=', '.join(kv.values())) print(state) return self.query.exec_(state) def __add_quote(self, s: str): return '"{}"'.format(s) def last_error(self): return self.query.lastError()
def readSQL(db, sql_text, params = []): query = QSqlQuery(db) query.setForwardOnly(True) if not query.prepare(sql_text): logging.error(f"SQL prep: '{query.lastError().text()}' for query '{sql_text}' with params '{params}'") return None for param in params: query.bindValue(param[0], param[1]) if not query.exec_(): logging.error(f"SQL exec: '{query.lastError().text()}' for query '{sql_text}' with params '{params}'") return None if query.next(): return readSQLrecord(query) else: return None
def show_current_players(self): query = QSqlQuery("select max(player_id) from torna_resztvevok") query.exec_() while query.next(): self.first_new_id = int(query.value(0)) + 1 print(self.first_new_id) self.add_new = QPushButton("Új") self.add_new.clicked.connect(self.uj_ember) self.current_players = QListWidget() self.current_players.setFixedHeight(470) self.current_players.setFixedWidth(150) self.current_players.setSortingEnabled(True) self.gomb_nev_layout.addWidget(self.add_new) self.current_players.itemDoubleClicked.connect(self.remove_resztvevo) self.gomb_nev_layout.addWidget(self.current_players)
def LoadHistory(self): query = QSqlQuery(self.db) query.exec_(""" select * from history """) while query.next(): # bookId, name, epsId, index, url, path info = QtHistoryData() info.bookId = query.value(0) info.name = query.value(1) info.epsId = query.value(2) info.picIndex = query.value(3) info.url = query.value(4) info.path = query.value(5) info.tick = query.value(6) self.history[info.bookId] = info pass
def get_forum_to_scan(self) -> object: db = self.get_db() try: if not db.isOpen(): db.open() query = QSqlQuery(query="select * from forums_to_scan_2 limit 1;", db=db) forum = {} while query.next(): forum['id'] = query.value(0) forum['title'] = query.value(3) forum['delta'] = query.value('delta') return forum except Exception as ex: print(str(ex)) finally: db.close()
def get_forum(self, id): db = self.get_db() try: if not db.isOpen(): db.open() query = QSqlQuery(db=db) query.prepare("select * from forums where id=:id;") query.bindValue(':id', id) query.exec_() forum: Dict[str, Optional[Any]] = {} while query.next(): forum['id'] = query.value('id') forum['category'] = query.value('category') forum['title'] = query.value('title') return forum finally: db.close()
def up_table(self, start_date, end_date): query = QSqlQuery(self.db) for sn in self.tb_name.keys(): tb_sn = 'LOGS_' + sn # one_high最高温度,one_low最低温度,two_high最高湿度,two_low最低湿度 one_high = float(self.tb_name[sn][1]) one_low = float(self.tb_name[sn][2]) two_high = float(self.tb_name[sn][3]) two_low = float(self.tb_name[sn][4]) # 查询出温度大于或者小于范围 1 度的或者湿度大于、小于1%。 sql = f'''SELECT LOGS_TIME, LOGS_CHONE, LOGS_CHTWO FROM {tb_sn} where (LOGS_CHONE > {one_high + 1} or LOGS_CHONE < {one_low - 1} or LOGS_CHTWO > {two_high + 1} OR LOGS_CHTWO < {two_low - 1}) and LOGS_TIME between #{start_date}# and #{end_date}#' ''' if (query.exec_(sql)): print('修改所有表' + sql) q = QSqlQuery(self.db) j = 0 while query.next(): t = query.value(0).toString('yyyy/M/d h:mm:ss') one = float(query.value(1)) two = float(query.value(2)) # 温度超标 if (one > one_high or one < one_low) and (two < two_high and two > two_low): if abs(one_low - one) < abs(one_high - one): sql = f"update {tb_sn} set LOGS_CHONE = {random.uniform(one_low, one_low + 2)} WHERE LOGS_TIME = #{t}#" q.exec_(sql) print(sql) else: sql = f"update {tb_sn} set LOGS_CHONE = {random.uniform(one_high - 1, one_high)} WHERE LOGS_TIME = #{t}#" q.exec_(sql) print(sql) # 湿度超标 elif (one < one_high and one > one_low) and (two > two_high or two < two_low): if abs(one_low - one) < abs(one_high - one): sql = f"update {tb_sn} set LOGS_CHTWO = {random.uniform(two_low, two_low + 2)} WHERE LOGS_TIME = #{t}#" q.exec_(sql) print(sql) else: sql = f"update {tb_sn} set LOGS_CHTWO = {random.uniform(two_high - 1, two_high)} WHERE LOGS_TIME = #{t}#" q.exec_(sql) print(sql)
def get_categories(self): db = self.get_db() try: if not db.isOpen(): db.open() query = QSqlQuery(db=db) query.prepare("select * from categories;") query.exec_() categories: List[Dict[str, None]] = [] while query.next(): category = { 'id': query.value('id'), 'title': query.value('title') } categories.append(category) return categories finally: db.close()
def get_new_user(self): db = self.get_db() try: if not db.isOpen(): db.open() query = QSqlQuery( query= 'select distinct user_id from torrents as t left join users as u on t.user_id=u.id where u.id is null and user_id > 0 limit 1;', db=db) user = {} while query.next(): user['id'] = query.value('user_id') # user['name'] = query.value('name') # user['registered'] = query.value('registered') # user['nation'] = query.value('nation') return user finally: db.close()
def get_category(self, id): category: Dict[str, Optional[Any]] = {} if id is None: category['id'] = id category['title'] = '<UNKNOWN>' db = self.get_db() try: if not db.isOpen(): db.open() query = QSqlQuery(db=db) query.prepare("select * from categories where id=:id;") query.bindValue(':id', id) query.exec_() while query.next(): category['id'] = query.value('id') category['title'] = query.value('title') return category finally: db.close()