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_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 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 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 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 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 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 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 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 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 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 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_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 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()
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 get_forums(self): db = self.get_db() try: if not db.isOpen(): db.open() query = QSqlQuery(db=db) query.prepare( "SELECT *, c.title as ctitle FROM forums as f INNER JOIN categories as c ON f.category=c.id;" ) query.exec_() forums = [] while query.next(): forum: Dict[str, Optional[Any]] = {} forum['id'] = query.value('id') forum['category'] = query.value('ctitle') forum['title'] = query.value('title') forums.append(forum) return forums finally: db.close()
def myinsert(self, tb_sn, start_date, end_date): # timedelta用于计算日期,查询出来前一天的数据,然后把日期修改成后一天,最好写入到数据库 days = (datetime.strptime(end_date, '%Y/%m/%d %H:%M:%S') - datetime.strptime(start_date, '%Y/%m/%d %H:%M:%S')).days print(days) print(start_date, end_date) if days == 0: start_date = datetime.strptime( start_date, '%Y/%m/%d %H:%M:%S') - timedelta(days=1) end_date = datetime.strptime( end_date, '%Y/%m/%d %H:%M:%S') - timedelta(days=1) else: start_date = datetime.strptime( start_date, '%Y/%m/%d %H:%M:%S') - timedelta(days=days) end_date = datetime.strptime( end_date, '%Y/%m/%d %H:%M:%S') - timedelta(days=days) query = QSqlQuery(self.db) # print(f"插入函数初始化{id(query)}") 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}#''' print(start_date, end_date) # print(sql) query.exec_(sql) while query.next(): # print('开始插入') # print(query.value(0)) if days == 0: LOGS_TIME = query.value('LOGS_TIME').toPython() + timedelta( days=days + 1) else: LOGS_TIME = query.value('LOGS_TIME').toPython() + timedelta( days=days) q = QSqlQuery() sql = f'''INSERT INTO {tb_sn} (LOGS_TIME, LOGS_CHONE, LOGS_CHTWO, LOGS_CHTHREE, LOGS_CHFOUR, BAT_DC_STATE) VALUES(#{LOGS_TIME}#,{query.value('LOGS_CHONE')},{query.value('LOGS_CHTWO')},{query.value('LOGS_CHTHREE')},{query.value('LOGS_CHFOUR')},{query.value('BAT_DC_STATE')})''' # 把数据的日期加一天,insert数据 #print(sql) q.exec_(sql) print('数据写入完毕!')
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
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 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 main(): db = QSqlDatabase.addDatabase("QSQLITE") db.setDatabaseName("foo.sqlite") if not db.open(): sys.exit(-1) lib = CDLL(os.path.join(CURRENT_DIR, "libqsqlite.so")) lib.enable_extension(c_void_p(shiboken2.getCppPointer(db.driver())[0])) load_spatialite() query = QSqlQuery() query.exec_("CREATE TABLE my_line(id INTEGER PRIMARY KEY)") query.exec_( """SELECT AddGeometryColumn("my_line","geom" , 4326, "LINESTRING", 2)""" ) polygon_wkt = "POLYGON ((11 50,11 51,12 51,12 50,11 50))" XA = 11 YA = 52 XB = 12 YB = 49 line_wkt = "LINESTRING({0} {1}, {2} {3})".format(XA, YA, XB, YB) query.prepare("""INSERT INTO my_line VALUES (?,GeomFromText(?, 4326))""") query.addBindValue(1) query.addBindValue(line_wkt) query.exec_() query.prepare( """SELECT astext(st_intersection(geom, GeomFromText(?, 4326))) from my_line WHERE st_intersects(geom, GeomFromText(?, 4326))""" ) query.addBindValue(polygon_wkt) query.addBindValue(polygon_wkt) query.exec_() while query.next(): for i in range(query.record().count()): print(query.value(i))
def test_can_create_customer(self, db, title, first_name, last_name, company_name, address): with rollback(): db.create_customer(title, first_name, last_name, company_name, address) query = QSqlQuery("SELECT currval('customers_customer_id_seq');") if not query.next(): raise AssertionError( f"Couldn't retrieve customer_id: {query.lastError().text()}" ) customer_id = query.value(0) rec = db.get_customer_record(customer_id) assert_that(rec.field(0).value(), is_(customer_id)) assert_that(rec.field(1).value(), is_(title)) assert_that(rec.field(2).value(), is_(first_name)) assert_that(rec.field(3).value(), is_(last_name)) assert_that(rec.field(4).value(), is_(company_name)) assert_that(rec.field(5).value(), is_(address))
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 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 update_statusz(self): legek = [] query = QSqlQuery( f"SELECT matches.*,torna_match.player1_id as p1,torna_match.player2_id as p2 FROM matches,\ torna_match WHERE matches.match_id=torna_match.match_id and torna_match.torna_id={self.torna_id}" ) query.exec_() while query.next(): akt_leg = [] akt_leg.append(query.value(0)) # match_id akt_leg.append(query.value(5)) # p1 akt_leg.append(query.value(6)) # p2 akt_leg.append(query.value(1)) # leg akt_leg.append(query.value(2)) # set akt_leg.append(query.value(3)) # winner legek.append(akt_leg) # Egy listában meg van az adott torna összes lejátszott leg-je # Kinullázzuk az eredményeket, mert a lekérdezés az összeset tudja lekérni, nem csak a legfrissebbet!!! for x in range(self.csoportok_szama): for y in range(self.sorok_szama): for z in range(self.sorok_szama): self.eredmenyek[x][y][z]._set_leg1(0) self.eredmenyek[x][y][z]._set_leg2(0) # Először összegezzük mindenhol a nyert legeket for k in legek: for x in range(self.csoportok_szama): for y in range(self.sorok_szama): for z in range(self.sorok_szama): if self.eredmenyek[x][y][z]._get_p1_id( ) == k[1] and self.eredmenyek[x][y][z]._get_p2_id( ) == k[2]: if self.eredmenyek[x][y][z]._get_p1_id() == k[5]: self.eredmenyek[x][y][z]._set_leg1( self.eredmenyek[x][y][z]._get_leg1() + 1) self.eredmenyek[x][y][z].change_leg1_number() else: self.eredmenyek[x][y][z]._set_leg2( self.eredmenyek[x][y][z]._get_leg2() + 1) self.eredmenyek[x][y][z].change_leg2_number() # ellenfél szempontjából: if self.eredmenyek[x][y][z]._get_p1_id( ) == k[2] and self.eredmenyek[x][y][z]._get_p2_id( ) == k[1]: if self.eredmenyek[x][y][z]._get_p1_id() == k[5]: self.eredmenyek[x][y][z]._set_leg1( self.eredmenyek[x][y][z]._get_leg1() + 1) self.eredmenyek[x][y][z].change_leg1_number() else: self.eredmenyek[x][y][z]._set_leg2( self.eredmenyek[x][y][z]._get_leg2() + 1) self.eredmenyek[x][y][z].change_leg2_number() # Megnézzük, hogy valaki megnyerte-e. Ha igen akkor a nyerésért/vereségért járó pontot rögzítjük for x in range(self.csoportok_szama): for y in range(self.sorok_szama): for z in range(self.sorok_szama): if self.eredmenyek[x][y][z]._get_leg1( ) == self.nyereshez_kell: self.eredmenyek[x][y][z]._set_pontszam( int(self.pont_gyozelem)) self.eredmenyek[x][y][z].update_osszes_pont() if self.eredmenyek[x][y][z]._get_leg2( ) == self.nyereshez_kell: self.eredmenyek[x][y][z]._set_pontszam( int(self.pont_vereseg)) self.eredmenyek[x][y][z].update_osszes_pont() # helyezések helyezesek = [] for cs in range(self.csoportok_szama): helyezes_oszlop = [] for oszlop in range(self.sorok_szama): # self.szum_eredmenyek[self._csoport_number][4][self._csoport_sor]. self.szum_eredmenyek[cs][4][oszlop]._set_ertek(3)
class VentanaPrincipal(QTabWidget): def __init__(self): super(VentanaPrincipal, self).__init__() self.ui = Ui_TabWidget() self.ui.setupUi(self) # DB self.db = QSqlDatabase.addDatabase("QSQLITE") self.db.setDatabaseName("cuentas.db") self.conector = QSqlDatabase.database() self.general_query = QSqlQuery() self.organizar_db() # Querys para psw_tool self.master_query = QSqlQuery() self.save_query = QSqlQuery() self.passwords_query_retrieve = QSqlQuery() self.passwords_query_write = QSqlQuery() self.popular = QSqlQuery() self.comboBoxes_query_categoria = QSqlQuery() self.comboBoxes_query_mail = QSqlQuery() self.comboBoxes_query_usuario = QSqlQuery() self.verificar_columna_contrasenas = QSqlQuery() self.filtro = QSqlQuery() # Querys para pswItemDelegate self.all_data = QSqlQuery() # BASE DE DATOS en UI self.model = QSqlTableModel() # pswItemDelegate.CustomSqlModel() self.organizar_tabla_ui() self.model.setEditStrategy(QSqlTableModel.OnManualSubmit) # Va aca abajo por self.model.setTable('passwords') # Para cuando se cambian datos self.model.dataChanged.connect(self.celdas_cambiadas) self.ui.tabla_db.setContextMenuPolicy(Qt.CustomContextMenu) self.ui.tabla_db.customContextMenuRequested.connect(self.context_menu) # Filtro DB # .connect(lambda: self. self.tabBarClicked.connect(self.actualizar_tabs) # Iconos DB en UI self.icon_seguridad = QIcon() self.icon_seguridad.addPixmap(QPixmap(":/media/iconografia/locked.png"), QIcon.Normal, QIcon.Off) self.icon_desbloqueado = QIcon() self.icon_desbloqueado.addPixmap(QPixmap(":/media/iconografia/unlocked.png"), QIcon.Normal, QIcon.Off) self.icon_editar = QIcon() self.icon_editar.addPixmap(QPixmap(":/media/iconografia/pencil.png"), QIcon.Normal, QIcon.Off) self.icon_guardar = QIcon() self.icon_guardar.addPixmap(QPixmap(":/media/iconografia/broken-pencil.png"), QIcon.Normal, QIcon.Off) # Procesos iniciales self.cargar_config() self.master_key = None self.seguridad_alterada = False self.candado = "cerrado" self.modo_boton_editar_guardar = "editar" self.contrasenas_nuevas = {} self.edits = {} self.revisar_columna_contrasenas() self.cargar_opciones_combo_boxes() # Alertas # Iconos para las alertas sin UI self.icon_ventana = QIcon() self.icon_ventana.addPixmap(QPixmap(":/media/imagenes/main_frame.png"), QIcon.Normal, QIcon.Off) self.alerta_config = QMessageBox(QMessageBox.Warning, "Problema con la configuración actual", "Existen dos posibilidades para este error:\n\n1. El archivo de configuración está dañando\n2. Usted tiene todas las opciones desmarcadas (el amor no cuenta)\n\nPara solucionarlo, borre el archivo de configuración ('opciones.ini'),\no marque alguna opción en la pestaña de configuración y guarde su selección\n" ) self.alerta_master_psw_mala = QMessageBox(QMessageBox.Warning, "Problema con la contraseña ingresada", "Por favor tome precauciones con la elección de la contraseña maestra.\nPara poder proseguir debe ingresar una contraseña con más de 5 y menos de 17 caracteres, o sino presione cancelar." ) self.alerta_master_psw_incorrecta = QMessageBox(QMessageBox.Warning, "Problema con la contraseña ingresada", "La contraseña que ingresaste es incorrecta" ) self.alerta_guardado_exitoso = QMessageBox(QMessageBox.Information, "Información guardada", "Toda la información que ingresaste se guardó con éxito." ) self.alerta_config.setWindowIcon(self.icon_ventana) self.alerta_master_psw_mala.setWindowIcon(self.icon_ventana) self.alerta_master_psw_incorrecta.setWindowIcon(self.icon_ventana) self.alerta_guardado_exitoso.setWindowIcon(self.icon_ventana) # Alertas con su propia UI # Dialog info self.dialogo_info = QDialog() self.info_app = Ui_dialogo_info() self.info_app.setupUi(self.dialogo_info) # Alerta master psw self.dialogo_master_psw = QDialog() self.alerta_master_psw = Ui_dialogo_master_psw() self.alerta_master_psw.setupUi(self.dialogo_master_psw) # Botones self.ui.boton_guardar_config.clicked.connect(self.guardar_config) self.ui.boton_info.clicked.connect(self.cargar_info) self.ui.boton_generar.clicked.connect(self.llamar_generar_contrasena) # Boton generar contrasñea self.ui.boton_guardar.clicked.connect(self.guardar_contrasena) # boton guardar data # Si presionan el boton revelar contraseña self.ui.reveal_psw.clicked.connect(self.mostrar_contrasena) self.alerta_master_psw.reveal_master_psw.clicked.connect(self.mostrar_contrasena_maestra) # Icono del boton revelar contraseña self.icon_not_view = QIcon() self.icon_not_view.addPixmap(QPixmap(":/media/iconografia/not_view.png"), QIcon.Normal, QIcon.Off) self.icon_view = QIcon() self.icon_view.addPixmap(QPixmap(":/media/iconografia/view.png"), QIcon.Normal, QIcon.Off) # Click en botones copiar # Otra manera de hacerlo: partial(self.llamar_copiar(n)) using functools.partial self.ui.cp1.clicked.connect(lambda: self.llamar_copiar(1)) self.ui.cp2.clicked.connect(lambda: self.llamar_copiar(2)) self.ui.cp3.clicked.connect(lambda: self.llamar_copiar(3)) # Botones DB self.ui.boton_filtro.clicked.connect(lambda: self.filtrar()) self.ui.boton_editar.clicked.connect(lambda: self.gestor_boton_editar_guardar()) self.ui.boton_seguridad.clicked.connect(lambda: self.mostrar_contrasenas()) # Botones Info self.info_app.boton_steam.clicked.connect(lambda: backend.abrir_link("https://steamcommunity.com/id/JosephKm")) self.info_app.boton_discord.clicked.connect(lambda: backend.abrir_link("https://discord.gg/wYuXPQS")) self.info_app.boton_github.clicked.connect( lambda: backend.abrir_link("https://github.com/kuttz-dev/Password-manager")) # Si se presiona la pestaña de configuracion # self.ui.tab_3.connect(self.cargar_config) # SETEAR COMBOBOXES self.ui.comboBox_usuario.setInsertPolicy(QComboBox.InsertAlphabetically) self.ui.comboBox_mail.setInsertPolicy(QComboBox.InsertAlphabetically) self.ui.comboBox_categoria.setInsertPolicy(QComboBox.InsertAlphabetically) self.ui.comboBox_usuario.setDuplicatesEnabled(False) self.ui.comboBox_mail.setDuplicatesEnabled(False) self.ui.comboBox_categoria.setDuplicatesEnabled(False) self.ui.comboBox_usuario.clearEditText() self.ui.comboBox_mail.clearEditText() self.ui.comboBox_categoria.clearEditText() def organizar_db(self): self.general_query.exec_( 'CREATE TABLE IF NOT EXISTS passwords (id INTEGER PRIMARY KEY ASC, categoria TEXT, icono BLOB, servicio TEXT, mail TEXT, usuario TEXT, contraseña_encriptada BLOB);' ) self.general_query.exec_( 'CREATE TABLE IF NOT EXISTS maestra (id INTEGER PRIMARY KEY, muestra BLOB);' ) self.general_query.exec_( 'DELETE FROM passwords WHERE usuario = "" AND mail = "" AND contraseña_encriptada = ""' ) self.db.commit() def borrar_columna_contrasenas(self): self.general_query.exec_( 'DROP TABLE IF EXISTS temporal' ) self.general_query.exec_( 'CREATE TABLE temporal (id INTEGER PRIMARY KEY ASC, categoria TEXT, icono BLOB, servicio TEXT, mail TEXT, usuario TEXT, contraseña_encriptada BLOB);' ) self.popular.exec_( 'INSERT INTO temporal(id, categoria, icono, servicio, mail, usuario, contraseña_encriptada) SELECT id, categoria, icono, servicio, mail, usuario, contraseña_encriptada FROM passwords' ) self.db.commit() self.general_query.exec_( 'DROP TABLE IF EXISTS passwords' ) self.popular.exec_( 'ALTER TABLE temporal RENAME TO passwords' ) self.general_query.exec_( 'DROP TABLE IF EXISTS temporal' ) self.db.commit() def cargar_config(self): largo, mayus, minus, numeros, special, icono = backend.obtener_cfg() self.ui.spinBox_largo.setProperty("value", int(largo)) self.ui.check_mayus.setChecked(backend.string2bool(mayus)) self.ui.check_min.setChecked(backend.string2bool(minus)) self.ui.check_numeros.setChecked(backend.string2bool(numeros)) self.ui.check_caracteres.setChecked(backend.string2bool(special)) self.ui.check_amor.setChecked(backend.string2bool(icono)) def guardar_config(self): hay_opcion_verdadera = False largo = self.ui.spinBox_largo.value() mayus = self.ui.check_mayus.checkState() minus = self.ui.check_min.checkState() numeros = self.ui.check_numeros.checkState() special = self.ui.check_caracteres.checkState() icono = self.ui.check_amor.checkState() estado_checkeado = [mayus, minus, numeros, special, icono] for i in range(len(estado_checkeado)): if str(estado_checkeado[i]) == "PySide2.QtCore.Qt.CheckState.Checked": if i != 4: # len de estado_checkeado - 1 hay_opcion_verdadera = True estado_checkeado[i] = True else: estado_checkeado[i] = False if hay_opcion_verdadera is False: return self.alerta_config.exec() backend.generar_cfg(largo, estado_checkeado[0], estado_checkeado[1], estado_checkeado[2], estado_checkeado[3], estado_checkeado[4] ) self.cargar_config() def cargar_info(self): return self.dialogo_info.exec() def llamar_generar_contrasena(self): # Primero obtenemos que tipo de contraseña quiere el usuario largo, mayus, minus, numeros, special, icono = backend.obtener_cfg() mayus = backend.string2bool(mayus) minus = backend.string2bool(minus) numeros = backend.string2bool(numeros) special = backend.string2bool(special) if mayus is False and minus is False and numeros is False and special is False: self.alerta_config.exec() texto_contrasena = backend.generar_contraseña(int(largo), mayus, minus, numeros, special) self.ui.input_psw.setText(str(texto_contrasena)) # Ponemos la contraseña en la aplicacion def mostrar_contrasena(self): # Si esta en password a normal y viceversa if self.ui.input_psw.echoMode() == QLineEdit.EchoMode.Password: self.ui.reveal_psw.setIcon(self.icon_not_view) self.ui.input_psw.setEchoMode(QLineEdit.Normal) else: self.ui.reveal_psw.setIcon(self.icon_view) self.ui.input_psw.setEchoMode(QLineEdit.Password) def mostrar_contrasena_maestra(self, modo_echo=False): # Si no se cambio el estado va a estar en False y se va a usar el echoMode del input if modo_echo is False: modo_echo = self.alerta_master_psw.input_master_psw.echoMode() # Si esta en password a normal y viceversa if modo_echo == QLineEdit.EchoMode.Password: self.alerta_master_psw.reveal_master_psw.setIcon(self.icon_not_view) self.alerta_master_psw.input_master_psw.setEchoMode(QLineEdit.Normal) else: self.alerta_master_psw.reveal_master_psw.setIcon(self.icon_view) self.alerta_master_psw.input_master_psw.setEchoMode(QLineEdit.Password) def llamar_copiar(self, numero_boton): if numero_boton == 1: backend.copiar(str(self.ui.input_psw.text())) if numero_boton == 2: backend.copiar(str(self.ui.comboBox_usuario.currentText())) if numero_boton == 3: backend.copiar(str(self.ui.comboBox_mail.currentText())) def preparar_favicon(self, url): try: archivo_ico = backend.descargar_favico(url) except Exception: # Si lo que se ingreso era un link pero no se consiguio favicon with open("media/favicons/domain.ico") as ico: return QByteArray(ico.read()) # Si no se consiguio la imagen if archivo_ico is None: return None with open(archivo_ico, "rb") as ico: return QByteArray(ico.read()) def guardar_contrasena(self): # self.ui.comboBox_usuario.currentText() / self.ui.comboBox_mail.currentText() # self.ui.comboBox_categoria.currentText() / self.ui.input_url.text() # self.ui.input_psw.text() if self.master_key is None: try: self.master_key = self.pedir_contrasena_maestra() except Exception: return if self.ui.input_psw.text() != "": contrasena_ingresada_encriptada = QByteArray(pswCrypto.encriptar(self.ui.input_psw.text(), self.master_key)) else: contrasena_ingresada_encriptada = "" try: fav_icon = self.preparar_favicon(self.ui.input_url.text()) self.save_query.prepare( 'INSERT INTO passwords (categoria, icono, servicio, mail, usuario, contraseña_encriptada) VALUES(?,?,?,?,?,?)' ) self.save_query.addBindValue(self.ui.comboBox_categoria.currentText()) self.save_query.addBindValue(fav_icon) self.save_query.addBindValue(self.ui.input_url.text()) self.save_query.addBindValue(self.ui.comboBox_mail.currentText()) self.save_query.addBindValue(self.ui.comboBox_usuario.currentText()) self.save_query.addBindValue(contrasena_ingresada_encriptada) self.save_query.exec_() self.db.commit() self.model.select() self.ui.tabla_db.resizeColumnsToContents() self.cargar_opciones_combo_boxes() return self.alerta_guardado_exitoso.exec() except Exception as ex: template = "An exception of type {0} occurred. Arguments:\n{1!r}" message = template.format(type(ex).__name__, ex.args) print(message) def pedir_contrasena_maestra(self): self.dialogo_master_psw.exec() contrasena_maestra = self.alerta_master_psw.input_master_psw.text() # Borrarmos el texto porque no se borra solo, y lo volvemos secreto de nuevo self.alerta_master_psw.input_master_psw.setText("") self.mostrar_contrasena_maestra(QLineEdit.EchoMode.Normal) # Si le dio a cancelar if bool(self.dialogo_master_psw.result()) is False: raise Exception("Accion canelada") # Comprobamos que cumpla requisitos if contrasena_maestra == "" or len(contrasena_maestra) < 6 or len(contrasena_maestra) > 16: self.alerta_master_psw_mala.exec() return self.pedir_contraseña_maestra() # Encriptacion key_contrasena_maestra = pswCrypto.generar_key(contrasena_maestra) # La convertimos en una key # Verificacion # Obtenemos la muestra guardada en la db muestra_db = backend.obtener_muestra_db() # Si no habia guardamos una nueva con esta contraseña maestra if muestra_db is None: array = QByteArray(backend.generar_muestra(key_contrasena_maestra)) self.master_query.prepare("INSERT INTO maestra (id, muestra) VALUES(1, ?)") self.master_query.addBindValue(array) self.master_query.exec_() return key_contrasena_maestra else: # Ahora si verificamos psw_correcta = backend.verificar_key(muestra_db, key_contrasena_maestra) if psw_correcta is True: return key_contrasena_maestra else: self.alerta_master_psw_incorrecta.exec_() raise Exception("Contraseña maestra incorrecta") def filtrar(self): if self.ui.input_filtro.text() == "": self.model.setFilter("") return self.model.select() else: self.model.setFilter( "{} LIKE '{}%'".format(self.ui.combobox_filtro.currentText().lower(), self.ui.input_filtro.text()) ) return self.model.select() def mostrar_contrasenas(self): if self.master_key is None: try: self.master_key = self.pedir_contrasena_maestra() except Exception: return if self.candado == "abierto": # Si esta abierto self.borrar_columna_contrasenas() self.ui.combobox_filtro.removeItem(4) self.organizar_tabla_ui() self.ui.boton_seguridad.setIcon(self.icon_seguridad) self.candado = "cerrado" # lo cerramos return else: # Si estaba cerrado self.candado = "abierto" # lo abrimos # y se abre asi: self.ui.boton_seguridad.setIcon(self.icon_desbloqueado) # Conseguimos las contraseñas encriptadas self.passwords_query_retrieve.exec_('SELECT id, contraseña_encriptada FROM passwords') # Creamos una columna para las contraseñas descifradas self.general_query.exec_('ALTER TABLE passwords ADD contraseña TEXT') self.db.commit() # Para cada contraseña while self.passwords_query_retrieve.next(): # Si se guardo un texto vacio contrasena_descifrada = self.passwords_query_retrieve.value(1) if type(contrasena_descifrada) != str: contrasena_descifrada = pswCrypto.descifrar(contrasena_descifrada.data(), self.master_key) self.passwords_query_write.prepare('UPDATE passwords SET contraseña = ? WHERE id= ?') self.passwords_query_write.addBindValue(contrasena_descifrada) self.passwords_query_write.addBindValue(self.passwords_query_retrieve.value(0)) self.passwords_query_write.exec_() self.ui.combobox_filtro.addItem("Contraseña") self.organizar_tabla_ui() # Las hacemos visibles el la tabla def actualizar_tabs(self, index): if index == 0: self.resize(437, 200) self.cargar_opciones_combo_boxes() if index == 1 and self.ui.boton_seguridad.isEnabled() is True: self.organizar_tabla_ui() elif index == 2: self.resize(437, 200) self.cargar_config() def organizar_tabla_ui(self): self.model.setTable('passwords') self.model.setSort(1, Qt.AscendingOrder) self.model.select() self.ui.tabla_db.setModel(self.model) self.ui.tabla_db.setItemDelegateForColumn(2, pswItemDelegate.ImageDelegate(self.ui.tabla_db)) self.ui.tabla_db.hideColumn(0) # Escondemos id self.ui.tabla_db.hideColumn(6) # Escondemos las contraseñas encriptadas self.ui.tabla_db.setWindowTitle("Lista de cuentas") # Tamaño columnas #self.ui.tabla_db.resizeColumnsToContents() self.ui.tabla_db.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch) self.ui.tabla_db.horizontalHeader().setSectionResizeMode(2, QHeaderView.Fixed) # iconos self.ui.tabla_db.setColumnWidth(2, 32) # tamaño iconos self.ui.tabla_db.verticalHeader().setVisible(False) self.ui.tabla_db.setSortingEnabled(True) def cargar_opciones_combo_boxes(self): a = self.ui.comboBox_categoria.currentText() b = self.ui.comboBox_mail.currentText() c = self.ui.comboBox_usuario.currentText() self.comboBoxes_query_categoria.exec_( 'SELECT DISTINCT categoria FROM passwords' ) self.comboBoxes_query_mail.exec_( 'SELECT DISTINCT mail FROM passwords' ) self.comboBoxes_query_usuario.exec_( 'SELECT DISTINCT usuario FROM passwords' ) # Borramos las anteriores self.ui.comboBox_usuario.clear() self.ui.comboBox_mail.clear() self.ui.comboBox_categoria.clear() # Cargamos las nuevas while self.comboBoxes_query_categoria.next(): if self.comboBoxes_query_categoria.value(0) == "" or self.comboBoxes_query_categoria.value(0) is None: continue self.ui.comboBox_categoria.addItem(self.comboBoxes_query_categoria.value(0)) while self.comboBoxes_query_mail.next(): if self.comboBoxes_query_mail.value(0) == "" or self.comboBoxes_query_mail.value(0) is None: continue self.ui.comboBox_mail.addItem(self.comboBoxes_query_mail.value(0)) while self.comboBoxes_query_usuario.next(): if self.comboBoxes_query_usuario.value(0) == "" or self.comboBoxes_query_usuario.value(0) is None: continue self.ui.comboBox_usuario.addItem(self.comboBoxes_query_usuario.value(0)) self.ui.comboBox_categoria.setCurrentText(a) self.ui.comboBox_mail.setCurrentText(b) self.ui.comboBox_usuario.setCurrentText(c) def revisar_columna_contrasenas(self, borrar=True): existe = self.verificar_columna_contrasenas.exec_('SELECT contraseña FROM passwords LIMIT 1') if existe is True and borrar is True: self.verificar_columna_contrasenas.finish() self.borrar_columna_contrasenas() return self.organizar_tabla_ui() elif existe is True: return "Existe" def gestor_boton_editar_guardar(self): if self.modo_boton_editar_guardar == "editar": try: self.editar_tabla_ui() except Exception: return self.modo_boton_editar_guardar = "guardar" # Cambiamos al proximo modo return self.ui.boton_editar.setIcon(self.icon_guardar) elif self.modo_boton_editar_guardar == "guardar": self.borrar_columna_contrasenas() self.organizar_tabla_ui() self.candado = "cerrado" self.ui.boton_seguridad.setDisabled(False) self.ui.boton_seguridad.setIcon(self.icon_seguridad) self.ui.boton_editar.setIcon(self.icon_editar) self.modo_boton_editar_guardar = "editar" # Cambiamos al proximo modo return def editar_tabla_ui(self): if self.master_key is None: try: self.master_key = self.pedir_contrasena_maestra() except Exception: raise Exception("No se pudo conseguir master key") self.ui.boton_seguridad.setDisabled(True) if self.candado == "cerrado": self.mostrar_contrasenas() def celdas_cambiadas(self, top_left, bottom_right): if self.modo_boton_editar_guardar == "editar": return else: if top_left.column() == 7: contrasena_editada_encriptada = "" if self.model.record(top_left.row()).value('contraseña') != "": contrasena_editada_encriptada = QByteArray( pswCrypto.encriptar(self.model.record(top_left.row()).value('contraseña'), self.master_key)) casilla_editada = QSqlField("contraseña_encriptada") casilla_editada.setValue(contrasena_editada_encriptada) valores_fila = self.model.record(top_left.row()) valores_fila.replace(6, casilla_editada) return self.model.updateRowInTable(top_left.row(), valores_fila) else: self.model.updateRowInTable(top_left.row(), self.model.record(top_left.row())) return self.db.commit() def borrar_linea(self, row): self.model.deleteRowFromTable(row) return self.model.select() def context_menu(self): # Menu con click derecho if self.ui.tabla_db.selectedIndexes() and self.modo_boton_editar_guardar == "guardar": menu = QMenu() borrar_data = menu.addAction("Borrar linea de la base de datos") borrar_data.triggered.connect(lambda: self.borrar_linea(self.ui.tabla_db.currentIndex().row())) cursor = QCursor() menu.exec_(cursor.pos())