def build(model_type='select'): # Дополнительный путь к SQL драйверу QApplication.setLibraryPaths(['./platforms', './plugins']) db = QSqlDatabase.addDatabase('QSQLITE') db.setDatabaseName('db25.db3') if not db.open(): print('БД не существует') sys.exit(-1) if model_type == 'query': return QSqlQueryModel(), db else: model = MyTableModel(None, db) # model = MyTableModel(None, db, model_type) if 'mytable' not in db.tables(): query = QSqlQuery() query.exec_( "create table mytable(id text primary key, title text, podsborka text, izdelie text)") model.setTable('mytable') model.setEditStrategy(QSqlTableModel.OnFieldChange) model.select() model.setHeaderData(0, Qt.Horizontal, "id") model.setHeaderData(1, Qt.Horizontal, "title") model.setHeaderData(2, Qt.Horizontal, "podsborka") model.setHeaderData(3, Qt.Horizontal, "izdelie") return model
def createTable(): if table_name in QSqlDatabase.database().tables(): return query = QSqlQuery() if not query.exec_(""" CREATE TABLE IF NOT EXISTS 'Conversations' ( 'author' TEXT NOT NULL, 'recipient' TEXT NOT NULL, 'timestamp' TEXT NOT NULL, 'message' TEXT NOT NULL, FOREIGN KEY('author') REFERENCES Contacts ( name ), FOREIGN KEY('recipient') REFERENCES Contacts ( name ) ) """): logging.error("Failed to query database") # This adds the first message from the Bot # and further development is required to make it interactive. query.exec_(""" INSERT INTO Conversations VALUES( 'machine', 'Me', '2019-01-07T14:36:06', 'Hello!' ) """) logging.info(query)
def accept(self): query = QSqlQuery( f"update torna_match set match_status=1 where match_id={self.match_id}" ) query.exec_() #self.match_id super().accept()
def build(): # Дополнительный путь к SQL драйверу QApplication.setLibraryPaths( ['./platforms', './plugins', QApplication.libraryPaths()[2]]) db = QSqlDatabase.addDatabase('QSQLITE') db.setDatabaseName('db_lib.db3') if not db.open(): print('БД не существует') sys.exit(-1) model = MyTableModel(None, db) if 'mytable' not in db.tables(): query = QSqlQuery() query.exec_( "create table mytable(id integer primary key autoincrement, author text, title text, year text)" ) model.setTable('mytable') model.setEditStrategy(QSqlTableModel.OnFieldChange) model.select() model.setHeaderData(0, Qt.Horizontal, "id") model.setHeaderData(1, Qt.Horizontal, "author") model.setHeaderData(2, Qt.Horizontal, "title") model.setHeaderData(3, Qt.Horizontal, "year") return model
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 __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 write_tables(self): tabla_rekordok = [] for cs in range(len(self.csoportok)): for sor in range(len(self.csoportok[cs])): if self.csoportok[cs][sor]._get_player_id() != 0: tabla_rekord = [] tabla_rekord.append(self.torna_id) tabla_rekord.append( self.csoportok[cs][sor]._get_player_id()) tabla_rekord.append( self.csoportok[cs][sor]._get_csoport_number()) tabla_rekord.append( self.csoportok[cs][sor]._get_csoport_sor()) tabla_rekordok.append(tabla_rekord) insertDataQuery = QSqlQuery() insertDataQuery.prepare(""" insert into torna_tablak ( torna_id, player_id, csoport_number, csoport_sor ) values (?, ?, ?, ?) """) for x in range(len(tabla_rekordok)): for i in range(len(tabla_rekordok[x])): insertDataQuery.addBindValue(tabla_rekordok[x][i]) insertDataQuery.exec_() query = QSqlQuery( f"update torna_settings set aktiv=1 where torna_id={self.torna_id}" ) query.exec_()
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 add_resztvevo(self, item): new_item = QListWidgetItem(item) new_item.setData(Qt.UserRole, item.data(Qt.UserRole)) self.current_players.addItem(new_item) query = QSqlQuery( f"insert into torna_resztvevok (player_id, player_name, torna_id) values ({new_item.data(Qt.UserRole)}, '{new_item.text()}', {self.torna_id})" ) query.exec_()
def remove_resztvevo(self, item): self.current_players.takeItem( self.current_players.row(self.current_players.selectedItems()[0])) # print(item.data(Qt.UserRole), item.text()) query = QSqlQuery( f"delete from torna_resztvevok where player_id={item.data(Qt.UserRole)} and torna_id={self.torna_id}" ) query.exec_()
def select_table(self): query = QSqlQuery(self.db) sql = f'''SELECT LOGGER_SN, LOGGER_NAME, CHONE_HIGH , CHONE_LOW, CHTWO_HIGH, CHTWO_LOW FROM TO_LOGGER_INFO where LOGGER_SN in {str(tuple(self.tb_name.keys()))} ORDER BY CHONE_HIGH,LOGGER_NAME ''' query.exec_(sql) # print('显示表' + sql) # print(str(tuple(self.tb_name.keys()))) return query
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 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 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 up_data(self, tb, high, logs_time, x): query = QSqlQuery(self.db) if x == 'one': sql = f"update {tb} set LOGS_CHONE={high} where LOGS_TIME = #{logs_time}#" # print('修改详细温度:' + sql) query.exec_(sql) elif x == 'two': sql = f"update {tb} set LOGS_CHTWO={high} where LOGS_TIME = #{logs_time}#" # print('修改详细湿度:' + sql) query.exec_(sql)
def selectModel(self): conn = self.db.getConnection() model = QSqlQueryModel() query = QSqlQuery(conn) self.buildSqlStatement(query) query.exec_() if query.lastError().driverText(): print("Query error msg:", query.lastError().driverText()) model.setQuery(query) self.ui.tblStats.setModel(model) self.ui.tblStats.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeToContents)
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 DelDownloadDB(self, bookId): query = QSqlQuery(self.db) sql = "delete from download where bookId='{}'".format(bookId) suc = query.exec_(sql) if not suc: Log.Warn(query.lastError().text()) sql = "delete from download_eps where bookId='{}'".format(bookId) suc = query.exec_(sql) if not suc: Log.Warn(query.lastError().text()) return
def show_data(self, SN, start_date, end_date): tb_sn = 'LOGS_' + SN query = QSqlQuery(self.db) sql = f'''SELECT LOGS_TIME, LOGS_CHONE, LOGS_CHTWO FROM {tb_sn} where LOGS_TIME between #{start_date}# and #{end_date}# order by LOGS_TIME ''' query.exec_(sql) print('显示超标数据' + sql) return query
def show_errdata(self, SN, start_date, end_date, one_high, one_low, two_high, two_low): tb_sn = 'LOGS_' + SN query = QSqlQuery(self.db) sql = f'''SELECT LOGS_TIME, LOGS_CHONE, LOGS_CHTWO FROM {tb_sn} where LOGS_TIME between #{start_date}# and #{end_date}# and (LOGS_CHONE >= {one_high} OR LOGS_CHONE <= {one_low} OR LOGS_CHTWO >= {two_high} OR LOGS_CHTWO <={two_low}) ''' query.exec_(sql) print('显示超标数据' + sql) return query
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()
def uj_ember(self): ujember, ok = QInputDialog.getText( self, "Új versenyző", '<html style="font-size: 15px;">Írd be a versenyző nevét!</html>') if ok and len(ujember): item = QListWidgetItem(ujember) item.setData(Qt.UserRole, self.first_new_id) self.current_players.addItem(item) self.first_new_id += 1 query = QSqlQuery( f"insert into torna_resztvevok (player_id, player_name, torna_id) values ({item.data(Qt.UserRole)}, '{item.text()}', {self.torna_id})" ) query.exec_()
def deregisterStudent(self, id): self.db = QSqlDatabase.addDatabase("QSQLITE") self.db.setDatabaseName("face.db") if self.db.open() is False: print("Failed opening db") query = QSqlQuery(self.db) query.prepare("DELETE FROM Student WHERE id = :id") query.bindValue(":id", int(id)) query.exec_() path = 'dataset' imagePaths = [os.path.join(path, f) for f in os.listdir(path)] [os.remove(x) for x in imagePaths if ('/' + str(id) + ".") in x] self.getThreadSignal(9, "Requested Record removed completely. Kindly retrain.")
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 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)
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 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 AddHistory(self, bookId, name, epsId, index, url, path): tick = int(time.time()) info = self.history.get(bookId) if not info: info = QtHistoryData() self.history[bookId] = info info.bookId = bookId info.name = name info.epsId = epsId info.picIndex = index info.url = url info.path = path info.tick = tick query = QSqlQuery(self.db) sql = "INSERT INTO history(bookId, name, epsId, picIndex, url, path, tick) " \ "VALUES ('{0}', '{1}', {2}, {3}, '{4}', '{5}', {6}) " \ "ON CONFLICT(bookId) DO UPDATE SET name='{1}', epsId={2}, picIndex={3}, url = '{4}', path='{5}', tick={6}".\ format(bookId, name, epsId, index, url, path, tick) suc = query.exec_(sql) if not suc: Log.Warn(query.lastError().text()) return
def DelHistory(self, bookId): query = QSqlQuery(self.db) sql = "delete from history where bookId='{}'".format(bookId) suc = query.exec_(sql) if not suc: Log.Warn(query.lastError().text()) return
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