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 insert_torrent(self, torrent): db = self.get_db() try: if not db.isOpen(): db.open() query = QSqlQuery(db=db) query.prepare( 'update torrents set forum=:f, title=:t, seed=:seed, leech=:leech, published=:pub, last_modified=:lm, ' 'hash=:hash, downloads=:d, last_checked=:lc, user_id=:user where id=:id' ) query.bindValue(':id', torrent['id']) query.bindValue(':f', torrent['forum']) query.bindValue(':t', torrent['title']) query.bindValue(':seed', torrent['seed']) query.bindValue(':leech', torrent['leech']) query.bindValue(':pub', str(torrent['published'])) query.bindValue(':lm', str(torrent['last_modified'])) query.bindValue(':hash', torrent['hash']) query.bindValue(':d', torrent['downloads']) query.bindValue(':lc', str(torrent['last_checked'])) query.bindValue(':user', torrent['user_id']) return query.exec_() except Exception as ex: return False finally: db.close()
def setValue(self, key, value): set_query = QSqlQuery(self.db) set_query.prepare("INSERT OR REPLACE INTO settings(id, name, value) " "VALUES((SELECT id FROM settings WHERE name=:key), :key, :value)") set_query.bindValue(":key", key) set_query.bindValue(":value", value) if not set_query.exec_(): logging.fatal(f"Failed to set settings key='{key}' to value='{value}'") self.db.commit()
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 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 save_torrent(self, torrent): db = self.get_db() try: if not db.isOpen(): db.open() query = QSqlQuery(db=db) query.prepare( 'insert into torrents(id, forum, title) values(:id, :f, :t)') query.bindValue(':id', torrent[0]) query.bindValue(':f', torrent[1]) query.bindValue(':t', torrent[2]) return query.exec_() except Exception as ex: return False finally: db.close()
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 generate_match_records(self): match_rekords = [] csoport_tabla = [6, 5, 4, 3, 2, 1] # todo táblához rendeléshez kell majd for cs in range(self.csoportok_szama): for sor in range(self.sorok_szama): for oszlop in range(sor + 1, self.sorok_szama): if self.eredmenyek[cs][sor][oszlop]._get_p1_id( ) != 0 and self.eredmenyek[cs][sor][oszlop]._get_p2_id( ) != 0: match_id = (10000 * self.torna_id) + ( 100 * int(self.eredmenyek[cs][sor][oszlop]._get_p1_id()) ) + int(self.eredmenyek[cs][sor][oszlop]._get_p2_id()) match_rekord = [] match_rekord.append(self.torna_id) match_rekord.append(match_id) match_rekord.append( self.eredmenyek[cs][sor][oszlop]._get_p1_id()) match_rekord.append( self.eredmenyek[cs][sor][oszlop]._get_p2_id()) match_rekord.append(self.variant) match_rekord.append(self.sets) match_rekord.append(self.legsperset) match_rekord.append(csoport_tabla[cs]) match_rekords.append(match_rekord) insertDataQuery = QSqlQuery() insertDataQuery.prepare(""" insert into torna_match ( torna_id, match_id, player1_id, player2_id, variant, sets, legsperset, tabla ) values (?, ?, ?, ?, ?, ?, ?, ?) """) for x in range(len(match_rekords)): for i in range(len(match_rekords[x])): insertDataQuery.addBindValue(match_rekords[x][i]) insertDataQuery.exec_() self.write_tables()
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 contextMenuEvent(self, event): print("context-menü") contextMenu = QMenu(self) firstAction = contextMenu.addAction("Egy") secondAction = contextMenu.addAction("Kettő") action = contextMenu.exec_(self.mapToGlobal(event.pos())) if action == firstAction: if self._csoport_sor < self._csoport_oszlop: match_id = (10000 * self.parent.torna_id) + ( 100 * self._player1_id) + self._player2_id elif self._csoport_sor > self._csoport_oszlop: match_id = (10000 * self.parent.torna_id) + ( 100 * self._player2_id) + self._player1_id # print("match_id: ", match_id, "player1: ", self._player1_id, self.parent.torna_id, "hány leg", self.parent.nyereshez_kell) winner_id = self._player1_id set_id = 1 now = datetime.now().strftime("%Y-%m-%d %H.%M.%S") query3 = QSqlQuery( f"delete from matches where match_id={match_id} and winner_id={winner_id}", db=db) query3.exec_() insertDataQuery = QSqlQuery() insertDataQuery.prepare(""" insert into matches ( match_id, leg_id, set_id, winner_id, timestamp ) values (?, ?, ?, ?, ?) """) for x in range(1, self.parent.nyereshez_kell + 1): insertDataQuery.addBindValue(match_id) insertDataQuery.addBindValue(x) insertDataQuery.addBindValue(1) insertDataQuery.addBindValue(winner_id) insertDataQuery.addBindValue(now) insertDataQuery.exec_() query2 = QSqlQuery( f"update torna_match set match_status=2 where match_id={match_id}", db=db) query2.exec_()
def update_user(self, user): db = self.get_db() try: if not db.isOpen(): db.open() query = QSqlQuery(db=db) query.prepare( 'update users set name=:name, registered=:reg, nation=:nation where id=:id' ) query.bindValue(':id', user['id']) query.bindValue(':name', user['name']) query.bindValue(':reg', user['registered']) query.bindValue(':nation', user['nation']) return query.exec_() except Exception as ex: return False finally: db.close()
def insert_user(self, user): db = self.get_db() try: if not db.isOpen(): db.open() query = QSqlQuery(db=db) query.prepare( 'insert into users(id, name, registered, nation) values(:id, :name, :reg, :nation)' ) query.bindValue(':id', user['id']) query.bindValue(':name', user['name']) query.bindValue(':reg', user['registered']) query.bindValue(':nation', user['nation']) return query.exec_() except Exception as ex: return False finally: db.close()
def __init__(self): super().__init__() self.table = QTableView() self.model = QSqlQueryModel() self.table.setModel(self.model) query = QSqlQuery(db=db) query.prepare("SELECT Name, Composer, Album.Title FROM Track " "INNER JOIN Album ON Track.AlbumId = Album.AlbumId " "WHERE Album.Title LIKE '%' || :album_title || '%' ") query.bindValue(":album_title", "Sinatra") query.exec_() self.model.setQuery(query) self.setMinimumSize(QSize(1024, 600)) self.setCentralWidget(self.table)
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 update_rss(self, f, d, ls): db = self.get_db() try: if not db.isOpen(): db.open() query = QSqlQuery(db=db) query.prepare( 'update forums_rss set last_scanned=:ls, delta=:d where id=:id' ) query.bindValue(':ls', str(ls)) query.bindValue(':d', d) query.bindValue(':id', f) query.exec_() db.close() return True except Exception as ex: return False 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()
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 registerStudent(self, id, rollno, name, stclass, section): 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("Insert Into Student (id, rollno, name, class, section)" "Values (:id, :rollno, :name, :class, :section)") query.bindValue(":id", id) query.bindValue(":rollno", rollno) query.bindValue(":name", name) query.bindValue(":class", stclass) query.bindValue(":section", section) query.exec_() # take photos for training self.startCapture(id) self.db.close()
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 execute(self, *args): """ @type query: str @type params: list """ query = QSqlQuery(db=self.db) query.prepare(args[0]) if len(args) == 1: text = args[0] elif len(args) == 2: text = args[0] % args[1] for value in args[1]: query.addBindValue(value) query.exec_(args[0]) statusWindow = self.applicationWindow.mainWindow.txtStatus # 状态窗口打印执行的SQL statusWindow.append("%s;" % text) return query
def executeSQL(db, sql_text, params = [], forward_only = True): query = QSqlQuery(db) query.setForwardOnly(forward_only) 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 return query
def create_password() -> str: """Prompt for a password to be created and return it.""" dialogue = CreatePassword() dialogue.exec_() if dialogue.result() != QDialog.Accepted: sys.exit() password = dialogue.new_password # upsert query = QSqlQuery(QSqlDatabase.database()) query.prepare(""" insert into credentials (id, password) values (0, :password) on conflict (id) do update set password=:password """) query.bindValue(":password", password) query.exec_() query.finish() return password
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
index = connn.primaryIndex(table) print( "name", index.name(), "Count of fields in index", index.count(), "isDescending", index.isDescending(0), ) query = QSqlQuery() if 'good' not in tables: if query.exec_( "create table good(id integer primary key autoincrement, goodname text, goodcount integer) " ): query.finish() query.prepare("insert into good values (null, ?, ?)") query.addBindValue('FlashDrive') query.addBindValue(10) if not query.exec_(): print("Error with insert record flashdrive", e.lastError().text()) query.finish() query.prepare("insert into good values (null, ?, ?)") query.bindValue(0, 'Paper for printer') query.bindValue(1, 3) if not query.exec_(): print("Error with insert record paper", e.lastError().text()) query.finish() query.prepare("insert into good values (null, :name, :count)") query.bindValue(':name', 'Cartridge for printer') query.bindValue(':count', 8)
def run(self): self.inform.emit(7, "Testing...") # Initialize and start realtime video capture cam = cv2.VideoCapture(0) # set video width cam.set(3, 640) # set video height cam.set(4, 480) # Define min window size to be recognized as a face # minW = 0.1*cam.get(3) # minH = 0.1*cam.get(4) i = 0 # font = cv2.FONT_HERSHEY_SIMPLEX self.db = QSqlDatabase.addDatabase("QSQLITE") n = 0 while True: ret, img = cam.read() cv2.imwrite("temp" + str(n) + ".jpg", img) gray = cv2.cvtColor(img, cv2.COLOR_BGR2GRAY) faces = self.detector.detectMultiScale(gray, 1.4, 8) for (x, y, w, h) in faces: cv2.rectangle(img, (x, y), (x + w, y + h), (0, 255, 0), 2) id, confidence = self.recognizer.predict(gray[y:y + h, x:x + w]) # print(id, confidence) # Check if confidence is less then 100 ==> "0" is perfect match if (confidence > 40): self.db.setDatabaseName("face.db") if not self.db.open(): self.inform.emit( 99, "Critical Error: Database failed to open.") else: cDate = QDate().currentDate().toString('yyyy-MM-dd') # query = QSqlQuery(self.db) qstr = "SELECT * FROM Attendance WHERE id = " + str( id ) + " AND date = '" + cDate + "' AND lecture_no = " + str( self.lectureNo) query = QSqlQuery(qstr) query.next() if not query.value(0): query.prepare( "INSERT INTO Attendance (id, date, lecture_no)" "Values (:id, :date, :lectureNo)") query.bindValue(":id", id) query.bindValue(":date", str(cDate)) query.bindValue(":lectureNo", str(self.lectureNo)) query.exec_() self.inform.emit( 8, "Attendance marked for ID = " + str(id) + " for Lecture = " + str(self.lectureNo)) else: self.inform.emit( 8, "Attendance for ID = " + str(id) + " for Lecture = " + str(self.lectureNo) + " is already marked.") i += 1 break # self.db.close() # count.append(id) if n == 0: n = 1 else: n = 0 if i > 0: break cam.release() cv2.destroyAllWindows()
class ConnectPostgreSQL: def __init__(self): self.db = QSqlDatabase.addDatabase('QPSQL') self.db.setHostName('localhost') self.db.setPort(5432) self.db.setDatabaseName('database_name') self.db.setUserName('dbuser') self.db.setPassword('123456') self.db.open() self.query = QSqlQuery() # Removendo a tabela. self.drop_table(table='table_name') # Criando a tabela. self.create_table() def create_table(self): sql = '''CREATE TABLE IF NOT EXISTS table_name ( id SERIAL NOT NULL, name VARCHAR(100) NOT NULL, age INTEGER NOT NULL, gender VARCHAR(10) NOT NULL, PRIMARY KEY(id) );''' self.query.exec_(sql) def drop_table(self, table): sql = f'DROP TABLE IF EXISTS {table};' self.query.exec_(sql) def insert_row(self, data): sql = 'INSERT INTO table_name (name, age, gender) VALUES (?, ?, ?)' self.query.prepare(sql) for index, value in enumerate(data): self.query.addBindValue(data[index]) if self.query.exec_(): self.query.clear() return True print(self.query.lastError()) return False def insert_rows(self, data): for row in data: self.insert_row(data=row) def find_by_id(self, rowid): sql = 'SELECT * FROM table_name WHERE id = ?' self.query.prepare(sql) self.query.addBindValue(rowid) self.query.exec_() if self.query.first(): data = (self.query.value(0), self.query.value(1), self.query.value(2), self.query.value(3)) self.query.clear() return data print(self.query.lastError()) return False def find(self, limit=10): sql = 'SELECT * FROM table_name LIMIT ?' self.query.prepare(sql) self.query.addBindValue(limit) self.query.exec_() result = [] while self.query.next(): data = (self.query.value(0), self.query.value(1), self.query.value(2), self.query.value(3)) result.append(data) self.query.clear() return result def update_row(self, rowid, name, age, gender): sql = 'UPDATE table_name SET name=?, age=?, gender=? WHERE id=?' self.query.prepare(sql) self.query.addBindValue(name) self.query.addBindValue(age) self.query.addBindValue(gender) self.query.addBindValue(rowid) if self.query.exec_(): return True print(self.query.lastError()) return False def remove_row(self, rowid): sql = 'DELETE FROM table_name WHERE id=?' self.query.prepare(sql) self.query.addBindValue(rowid) if self.query.exec_(): return True self.db.rollback() print(self.query.lastError()) return False
class ConnectSQLite: def __init__(self): self.db = QSqlDatabase.addDatabase('QSQLITE') self.db.setDatabaseName('DataBaseName.sqlite3') # self.db.setDatabaseName(':memory:') self.db.open() self.query = QSqlQuery() # Removendo a tabela. self.drop_table(table='table_name') # Criando a tabela. self.create_table() def create_table(self): sql = '''CREATE TABLE IF NOT EXISTS table_name ( id INTEGER PRIMARY KEY, name TEXT, age INTEGER, gender TEXT );''' self.query.exec_(sql) def drop_table(self, table): sql = f'DROP TABLE IF EXISTS {table};' self.query.exec_(sql) def insert_row(self, data): sql = 'INSERT INTO table_name (name, age, gender) VALUES (?, ?, ?)' self.query.prepare(sql) for index, value in enumerate(data): self.query.addBindValue(data[index]) self.query.exec_() def insert_rows(self, data): for row in data: self.insert_row(data=row) def find_by_id(self, rowid): sql = 'SELECT * FROM table_name WHERE id = ?' self.query.prepare(sql) self.query.addBindValue(rowid) self.query.exec_() if self.query.first(): return (self.query.value(0), self.query.value(1), self.query.value(2), self.query.value(3)) return False def find(self, limit=10): sql = 'SELECT * FROM table_name LIMIT ?' self.query.prepare(sql) self.query.addBindValue(limit) self.query.exec_() result = [] while self.query.next(): data = (self.query.value(0), self.query.value(1), self.query.value(2), self.query.value(3)) result.append(data) return result def update_row(self, rowid, name, age, gender): sql = 'UPDATE table_name SET name=?, age=?, gender=? WHERE id=?' self.query.prepare(sql) self.query.addBindValue(name) self.query.addBindValue(age) self.query.addBindValue(gender) self.query.addBindValue(rowid) if self.query.exec_(): return True self.db.rollback() print(self.query.lastError()) return False def remove_row(self, rowid): sql = 'DELETE FROM table_name WHERE id=?' self.query.prepare(sql) self.query.addBindValue(rowid) if self.query.exec_(): return True self.db.rollback() print(self.query.lastError()) return False
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())
class OperationsModel(QAbstractTableModel): PAGE_SIZE = 100 COL_TYPE = 0 COL_SUBTYPE = 1 COL_ID = 2 COL_TIMESTAMP = 3 COL_ACCOUNT_ID = 4 COL_ACCOUNT = 5 COL_NUMBER_PEER = 6 COL_ASSET_ID = 7 COL_ASSET = 8 COL_ASSET_NAME = 9 COL_NOTE = 10 COL_NOTE2 = 11 COL_AMOUNT = 12 COL_QTY = 13 COL_PRICE = 14 COL_FEE_TAX = 15 COL_TOTAL_AMOUNT = 16 COL_TOTAL_QTY = 17 COL_CURRENCY = 18 COL_RECONCILED = 19 _columns = [ " ", g_tr('OperationsModel', "Timestamp"), g_tr('OperationsModel', "Account"), g_tr('OperationsModel', "Notes"), g_tr('OperationsModel', "Amount"), g_tr('OperationsModel', "Balance"), g_tr('OperationsModel', "Currency") ] _tables = { TransactionType.Action: "actions", TransactionType.Dividend: "dividends", TransactionType.Trade: "trades", TransactionType.Transfer: "transfers", TransactionType.CorporateAction: "corp_actions" } OperationSign = { (TransactionType.Action, -1): ('—', CustomColor.DarkRed), (TransactionType.Action, +1): ('+', CustomColor.DarkGreen), (TransactionType.Dividend, DividendSubtype.Dividend): ('Δ', CustomColor.DarkGreen), (TransactionType.Dividend, DividendSubtype.BondInterest): ('%', CustomColor.DarkGreen), (TransactionType.Trade, -1): ('S', CustomColor.DarkRed), (TransactionType.Trade, +1): ('B', CustomColor.DarkGreen), (TransactionType.Transfer, TransferSubtype.Outgoing): ('<', CustomColor.DarkBlue), (TransactionType.Transfer, TransferSubtype.Incoming): ('>', CustomColor.DarkBlue), (TransactionType.Transfer, TransferSubtype.Fee): ('=', CustomColor.DarkRed), (TransactionType.CorporateAction, CorporateAction.Merger): ('âƒ', CustomColor.Black), (TransactionType.CorporateAction, CorporateAction.SpinOff): ('⎇', CustomColor.DarkGreen), (TransactionType.CorporateAction, CorporateAction.Split): ('á—•', CustomColor.Black), (TransactionType.CorporateAction, CorporateAction.SymbolChange): ('🡘', CustomColor.Black), (TransactionType.CorporateAction, CorporateAction.StockDividend): ('Δ\ns', CustomColor.DarkGreen) } CorpActionNames = { CorporateAction.SymbolChange: g_tr('OperationsModel', "Symbol change {old} -> {new}"), CorporateAction.Split: g_tr('OperationsModel', "Split {old} {before} into {after}"), CorporateAction.SpinOff: g_tr('OperationsModel', "Spin-off {after} {new} from {before} {old}"), CorporateAction.Merger: g_tr('OperationsModel', "Merger {before} {old} into {after} {new}"), CorporateAction.StockDividend: g_tr('OperationsModel', "Stock dividend: {after} {new}") } def __init__(self, parent_view): super().__init__(parent_view) self._view = parent_view self._amount_delegate = None self._data = [] self._row_count = 0 self._query = QSqlQuery(db_connection()) self._begin = 0 self._end = 0 self._account = 0 self._text_filter = '' self.prepareData() def rowCount(self, parent=None): return len(self._data) def columnCount(self, parent=None): return len(self._columns) def canFetchMore(self, index): return len(self._data) < self._row_count def fetchMore(self, index): new_size = len(self._data) + self.PAGE_SIZE new_size = new_size if new_size < self._row_count else self._row_count self.beginInsertRows(index, len(self._data), new_size - 1) i = 0 indexes = range(self._query.record().count()) while (i < self.PAGE_SIZE) and self._query.next(): values = list(map(self._query.value, indexes)) self._data.append(values) i += 1 self.endInsertRows() def headerData(self, section, orientation, role=Qt.DisplayRole): if orientation == Qt.Horizontal and role == Qt.DisplayRole: return self._columns[section] return None def get_operation(self, row): if (row >= 0) and (row < len(self._data)): return self._data[row][self.COL_TYPE], self._data[row][self.COL_ID] else: return [0, 0] def data(self, index, role=Qt.DisplayRole): if not index.isValid(): return None if role == Qt.DisplayRole: return self.data_text(index.row(), index.column()) if role == Qt.FontRole and index.column() == 0: font = QFont() font.setBold(True) return font if role == Qt.ForegroundRole: return self.data_foreground(index.row(), index.column()) if role == Qt.TextAlignmentRole: if index.column() == 0: return Qt.AlignCenter if index.column() == 4 or index.column() == 5: return Qt.AlignRight return Qt.AlignLeft def data_text(self, row, column): if column == 0: try: return self.OperationSign[self._data[row][self.COL_TYPE], self._data[row][self.COL_SUBTYPE]][0] except KeyError: return '?' elif column == 1: if (self._data[row][self.COL_TYPE] == TransactionType.Trade) or (self._data[row][self.COL_TYPE] == TransactionType.Dividend) \ or (self._data[row][self.COL_TYPE] == TransactionType.CorporateAction): return f"{datetime.utcfromtimestamp(self._data[row][self.COL_TIMESTAMP]).strftime('%d/%m/%Y %H:%M:%S')}\n# {self._data[row][self.COL_NUMBER_PEER]}" else: return datetime.utcfromtimestamp(self._data[row][ self.COL_TIMESTAMP]).strftime('%d/%m/%Y %H:%M:%S') elif column == 2: if self._data[row][self.COL_TYPE] == TransactionType.Action: return self._data[row][self.COL_ACCOUNT] elif (self._data[row][self.COL_TYPE] == TransactionType.Trade) \ or (self._data[row][self.COL_TYPE] == TransactionType.Dividend) \ or (self._data[row][self.COL_TYPE] == TransactionType.CorporateAction): return self._data[row][self.COL_ACCOUNT] + "\n" + self._data[ row][self.COL_ASSET_NAME] elif self._data[row][self.COL_TYPE] == TransactionType.Transfer: if self._data[row][self.COL_SUBTYPE] == TransferSubtype.Fee: return self._data[row][self.COL_ACCOUNT] elif self._data[row][ self.COL_SUBTYPE] == TransferSubtype.Outgoing: return self._data[row][ self.COL_ACCOUNT] + " -> " + self._data[row][ self.COL_NOTE2] elif self._data[row][ self.COL_SUBTYPE] == TransferSubtype.Incoming: return self._data[row][ self.COL_ACCOUNT] + " <- " + self._data[row][ self.COL_NOTE2] elif column == 3: if self._data[row][self.COL_TYPE] == TransactionType.Action: note = self._data[row][self.COL_NUMBER_PEER] if self._data[row][self.COL_ASSET] != '' and self._data[row][ self.COL_FEE_TAX] != 0: note += "\n" + g_tr('OperationsModel', "Rate: ") if self._data[row][self.COL_FEE_TAX] >= 1: note += f"{self._data[row][self.COL_FEE_TAX]:.4f} " \ f"{self._data[row][self.COL_ASSET]}/{self._data[row][self.COL_CURRENCY]}" else: note += f"{1/self._data[row][self.COL_FEE_TAX]:.4f} " \ f"{self._data[row][self.COL_CURRENCY]}/{self._data[row][self.COL_ASSET]}" return note elif self._data[row][self.COL_TYPE] == TransactionType.Transfer: rate = 0 if self._data[row][ self.COL_PRICE] == '' else self._data[row][self.COL_PRICE] if self._data[row][self.COL_CURRENCY] != self._data[row][ self.COL_NUMBER_PEER]: if rate != 0: if rate > 1: return self._data[row][ self. COL_NOTE] + f" [1 {self._data[row][self.COL_CURRENCY]} = {rate:.4f} {self._data[row][self.COL_NUMBER_PEER]}]" elif rate < 1: rate = 1 / rate return self._data[row][ self. COL_NOTE] + f" [{rate:.4f} {self._data[row][self.COL_CURRENCY]} = 1 {self._data[row][self.COL_NUMBER_PEER]}]" else: return self._data[row][self.COL_NOTE] else: return g_tr('OperationsModel', "Error. Zero rate") else: return self._data[row][self.COL_NOTE] elif self._data[row][self.COL_TYPE] == TransactionType.Dividend: return self._data[row][self.COL_NOTE] + "\n" + g_tr( 'OperationsModel', "Tax: ") + self._data[row][self.COL_NOTE2] elif self._data[row][self.COL_TYPE] == TransactionType.Trade: if self._data[row][self.COL_FEE_TAX] != 0: text = f"{self._data[row][self.COL_QTY]:+.2f} @ {self._data[row][self.COL_PRICE]:.4f}\n({self._data[row][self.COL_FEE_TAX]:.2f}) " else: text = f"{self._data[row][self.COL_QTY]:+.2f} @ {self._data[row][self.COL_PRICE]:.4f}\n" text = text + self._data[row][ self.COL_NOTE] if self._data[row][self.COL_NOTE] else text return text elif self._data[row][ self.COL_TYPE] == TransactionType.CorporateAction: basis = 100.0 * self._data[row][self.COL_PRICE] if self._data[row][ self.COL_SUBTYPE] == CorporateAction.StockDividend: qty_after = self._data[row][ self.COL_QTY] - self._data[row][self.COL_AMOUNT] else: qty_after = self._data[row][self.COL_QTY] text = self.CorpActionNames[self._data[row][ self.COL_SUBTYPE]].format( old=self._data[row][self.COL_ASSET], new=self._data[row][self.COL_NOTE], before=self._data[row][self.COL_AMOUNT], after=qty_after) if self._data[row][ self.COL_SUBTYPE] == CorporateAction.SpinOff: text += f"; {basis:.2f}% " + g_tr( 'OperationsModel', " cost basis") + "\n" + self._data[row][self.COL_NOTE2] return text else: assert False elif column == 4: if self._data[row][self.COL_TYPE] == TransactionType.Trade: return [ self._data[row][self.COL_AMOUNT], self._data[row][self.COL_QTY] ] elif self._data[row][self.COL_TYPE] == TransactionType.Dividend: return [ self._data[row][self.COL_AMOUNT], -self._data[row][self.COL_FEE_TAX] ] elif self._data[row][self.COL_TYPE] == TransactionType.Action: if self._data[row][self.COL_ASSET] != '': return [ self._data[row][self.COL_AMOUNT], self._data[row][self.COL_PRICE] ] else: return [self._data[row][self.COL_AMOUNT]] elif self._data[row][self.COL_TYPE] == TransactionType.Transfer: return [self._data[row][self.COL_AMOUNT]] elif self._data[row][ self.COL_TYPE] == TransactionType.CorporateAction: if self._data[row][ self. COL_SUBTYPE] == CorporateAction.SpinOff or self._data[ row][self. COL_SUBTYPE] == CorporateAction.StockDividend: return [ None, self._data[row][self.COL_QTY] - self._data[row][self.COL_AMOUNT] ] else: return [ -self._data[row][self.COL_AMOUNT], self._data[row][self.COL_QTY] ] else: assert False elif column == 5: upper_part = f"{self._data[row][self.COL_TOTAL_AMOUNT]:,.2f}" if self._data[ row][self.COL_TOTAL_AMOUNT] != '' else "-.--" lower_part = f"{self._data[row][self.COL_TOTAL_QTY]:,.2f}" if self._data[ row][self.COL_TOTAL_QTY] != '' else '' if self._data[row][ self.COL_TYPE] == TransactionType.CorporateAction: qty_before = self._data[row][ self.COL_AMOUNT] if self._data[row][ self.COL_SUBTYPE] == CorporateAction.SpinOff else 0 qty_after = self._data[row][self.COL_TOTAL_QTY] if self._data[row][ self. COL_SUBTYPE] == CorporateAction.StockDividend else self._data[ row][self.COL_QTY] if self._data[row][ self.COL_SUBTYPE] == CorporateAction.StockDividend: text = f"\n{qty_after:,.2f}" if qty_after != '' else "\n-.--" else: text = f"{qty_before:,.2f}\n{qty_after:,.2f}" return text elif self._data[row][ self.COL_TYPE] == TransactionType.Action or self._data[ row][self.COL_TYPE] == TransactionType.Transfer: return upper_part else: return upper_part + "\n" + lower_part elif column == 6: if self._data[row][ self.COL_TYPE] == TransactionType.CorporateAction: asset_before = self._data[row][ self.COL_ASSET] if self._data[row][ self. COL_SUBTYPE] != CorporateAction.StockDividend else "" return f" {asset_before}\n {self._data[row][self.COL_NOTE]}" else: if self._data[row][self.COL_ASSET] != '': return f" {self._data[row][self.COL_CURRENCY]}\n {self._data[row][self.COL_ASSET]}" else: return f" {self._data[row][self.COL_CURRENCY]}" else: assert False def data_foreground(self, row, column): if column == 0: try: return QBrush( self.OperationSign[self._data[row][self.COL_TYPE], self._data[row][self.COL_SUBTYPE]][1]) except KeyError: return QBrush(CustomColor.LightRed) if column == 5: if self._data[row][self.COL_RECONCILED] == 1: return QBrush(CustomColor.Blue) def configureView(self): self._view.setColumnWidth(0, 10) self._view.setColumnWidth( 1, self._view.fontMetrics().width("00/00/0000 00:00:00") * 1.1) self._view.setColumnWidth(2, 300) self._view.horizontalHeader().setSectionResizeMode( 3, QHeaderView.Stretch) font = self._view.horizontalHeader().font() font.setBold(True) self._view.horizontalHeader().setFont(font) self._amount_delegate = ColoredAmountsDelegate(self._view) self._view.setItemDelegateForColumn(4, self._amount_delegate) self._view.verticalHeader().setSectionResizeMode( QHeaderView.ResizeToContents) @Slot() def setAccount(self, account_id): if self._account != account_id: self._account = account_id self.prepareData() def getAccount(self): return self._account @Slot() def setDateRange(self, start, end=0): self._begin = start if end: self._end = end else: self._end = QDate.currentDate().endOfDay(Qt.UTC).toSecsSinceEpoch() self.prepareData() @Slot() def filterText(self, filter): if filter: self._text_filter = f" AND (num_peer LIKE '%{filter}%' COLLATE NOCASE "\ f"OR note LIKE '%{filter}%' COLLATE NOCASE "\ f"OR note2 LIKE '%{filter}%' COLLATE NOCASE "\ f"OR asset LIKE '%{filter}%' COLLATE NOCASE "\ f"OR asset_name LIKE '%{filter}%' COLLATE NOCASE)" else: self._text_filter = '' self.prepareData() def update(self): self.prepareData() def get_operation_type(self, row): if (row >= 0) and (row < len(self._data)): return self._data[row][self.COL_TYPE] else: return 0 def reconcile_operation(self, row): if (row >= 0) and (row < len(self._data)): timestamp = self._data[row][self.COL_TIMESTAMP] account_id = self._data[row][self.COL_ACCOUNT_ID] _ = executeSQL( "UPDATE accounts SET reconciled_on=:timestamp WHERE id = :account_id", [(":timestamp", timestamp), (":account_id", account_id)]) self.prepareData() @Slot() def refresh(self): idx = self._view.selectionModel().selection().indexes() self.prepareData() if idx: self._view.setCurrentIndex(idx[0]) def prepareData(self): self._data = [] if self._begin == 0 and self._end == 0: self._row_count = 0 else: count_pfx = "SELECT COUNT(*) " query_pfx = "SELECT * " query_suffix = f"FROM all_operations AS o WHERE o.timestamp>={self._begin} AND o.timestamp<={self._end}" + \ self._text_filter if self._account: query_suffix = query_suffix + f" AND o.account_id = {self._account}" self._row_count = readSQL(count_pfx + query_suffix) self._query.prepare(query_pfx + query_suffix) self._query.setForwardOnly(True) self._query.exec_() self.fetchMore(self.createIndex(0, 0)) self.modelReset.emit() def deleteRows(self, rows): for row in rows: if (row >= 0) and (row < len(self._data)): table_name = self._tables[self._data[row][self.COL_TYPE]] query = f"DELETE FROM {table_name} WHERE id={self._data[row][self.COL_ID]}" _ = executeSQL(query) self.prepareData()
class MainWindow(QMainWindow): def __init__(self): super().__init__() container = QWidget() layout_search = QHBoxLayout() self.track = QLineEdit() self.track.setPlaceholderText("Track name...") self.track.textChanged.connect(self.update_query) self.composer = QLineEdit() self.composer.setPlaceholderText("Artist name...") self.composer.textChanged.connect(self.update_query) self.album = QLineEdit() self.album.setPlaceholderText("Album name...") self.album.textChanged.connect(self.update_query) layout_search.addWidget(self.track) layout_search.addWidget(self.composer) layout_search.addWidget(self.album) layout_view = QVBoxLayout() layout_view.addLayout(layout_search) self.table = QTableView() layout_view.addWidget(self.table) container.setLayout(layout_view) self.model = QSqlQueryModel() self.table.setModel(self.model) self.query = QSqlQuery(db=db) self.query.prepare( "SELECT Name, Composer, Album.Title FROM Track " "INNER JOIN Album ON Track.AlbumId=Album.AlbumId WHERE " "Track.Name LIKE '%' || :track_name || '%' AND " "Track.Composer LIKE '%' || :track_composer || '%' AND " "Album.Title LIKE '%' || :album_title || '%'" ) self.update_query() self.setMinimumSize(QSize(1024, 600)) self.setCentralWidget(container) def update_query(self, s=None): # Get the text values from the widgets. track_name = self.track.text() track_composer = self.composer.text() album_title = self.album.text() self.query.bindValue(":track_name", track_name) self.query.bindValue(":track_composer", track_composer) self.query.bindValue(":album_title", album_title) self.query.exec_() self.model.setQuery(self.query)