def addItem(self, data): hash = createHash(data) if self.lastAddedHash == hash: return self.lastAddedHash = hash query = QSqlQuery() prepareQuery(query, """ delete from clipboardItems where itemHash = :hash; """) query.bindValue(':hash', hash) executeQuery(query) text = data.get(mimeText, '') record = self.record() record.setValue('itemHash', hash) record.setValue('itemText', text) record.setValue('itemData', QByteArray(serializeData(data))) record.setValue('copyTime', QDateTime.currentDateTime()) if not self.insertRecord(0, record): raise ValueError('Failed to insert item: ' + self.lastError().text()) self.submitChanges()
def DateA(self): text1, ok1 = QInputDialog.getText(self, 'Приход препаратов', 'Введите ID препарата. ') if ok1: now = datetime.datetime.now() text2 = now.strftime("%Y-%m-%d") text3, ok3 = QInputDialog.getText( self, 'Приход препаратов', 'Введите количество препаратов в партии. ') if ok3: text4, ok4 = QInputDialog.getText( self, 'Приход препаратов', 'Введите срок годности препаратов в партии.(В формате: Год-месяц-день. Пример:2018-12-17) ' ) if ok4: db = QtSql.QSqlDatabase.addDatabase('QSQLITE') db.setDatabaseName("db.db") db.open() queryAdd = QSqlQuery() queryAdd.prepare( "INSERT INTO batch (b_mId, bDateA, bCol, bDateEx ) " "VALUES (?, ?, ?, ?)") queryAdd.bindValue(0, text1) queryAdd.bindValue(1, text2) queryAdd.bindValue(2, text3) queryAdd.bindValue(3, text4) queryAdd.exec() db.close()
def anime_selection(self, text): assert isinstance(text, str) self.ui.comboBox_Songs_Anime.clear() self.ui.label_Songs_Anime_Count.setText("(0)") if len(text) != 0: text_split = text.split(" ") anime_words = [] for split in text_split: if len(split) != 0: anime_words.append(split) query_string = 'SELECT t_anime.id, t_anime.name FROM t_anime WHERE' for i in range(len(anime_words)): if i == 0: query_string += " t_anime.name LIKE '%'||?||'%'" else: query_string += " AND t_anime.name LIKE '%'||?||'%'" query = QSqlQuery() query.prepare(query_string) for i in range(len(anime_words)): query.bindValue(i, anime_words[i]) query.setForwardOnly(1) query.exec_() query_count = 0 while query.next(): query_count += 1 self.ui.comboBox_Songs_Anime.addItem(str(query.value(0)) + " - " + query.value(1)) self.ui.label_Songs_Anime_Count.setText("(" + str(query_count) + ")")
def insertar(self): estado = self.trafico.open() if estado == False: QMessageBox.warning( self, "No se pudo abrir la base de datos", self.trafico.lastError().text(), QMessageBox.Discard) #se guardan los errores que saca else: id_ciudad = self.id_ciudad_persona.text() Nombre_ciudad = self.Nombre_ciudad_persona.text() sql_ciudad = "INSERT INTO Ciudad(id_ciudad_persona,Nombre_ciudad_persona) VALUES(:id_ciudad_persona,:Nombre_ciudad_persona)" consulta_ciudad = QSqlQuery() consulta_ciudad.prepare(sql_ciudad) consulta_ciudad.bindValue(":id_ciudad_persona", id_ciudad) consulta_ciudad.bindValue(":Nombre_ciudad_persona", Nombre_ciudad) estado = consulta_ciudad.exec_() if estado == False: QMessageBox.warning( self, "No se pudo realizar la consulta", self.trafico.lastError().text(), QMessageBox.Discard) #se guardan los errores que saca else: QMessageBox.information( self, " Correcto", " Se han insertado los datos correctamente !", QMessageBox.Discard) self.trafico.close() # se cierra la base de datos
def register(self): name = self.nameLineEdit.text() username = self.usernameLineEdit.text() password = self.passwordLineEdit.text() re_password = self.rePasswordLineEdit.text() if name == "" or username == "" or password == "" or re_password == "": criticalMessage("Empty field is not allowed!") return if password != re_password: criticalMessage("Password confirmation failed") return if self.uniqueUsername(username) == False: criticalMessage("Repetitive username is not allowed!") return query = QSqlQuery() query.prepare( "insert into User(name,username,password,isAdmin) values(:name,:username,:password,0)" ) query.bindValue(":name", name) query.bindValue(":username", username) query.bindValue(":password", password) if query.exec_() == False: criticalMessage("Registration Failed!") else: self.close()
def insert_row(self): record = QtSql.QSqlRecord() record.append(QtSql.QSqlField('id', QtCore.QVariant.Int)) record.append(QtSql.QSqlField('rfid_id', QtCore.QVariant.String)) record.append(QtSql.QSqlField('description', QtCore.QVariant.String)) record.setValue('id', 0) if self.onEvents: record.append(QtSql.QSqlField('date', QtCore.QVariant.DateTime)) record.setValue('date', QDateTime.currentDateTime()) record.setValue('rfid_id', self.label_2.text()) else: if self.rfidIDField.text().strip(" ") == "": QMessageBox.warning( self, 'Error!', "Need to fill ID of marker field to insert new marker.") return query = QSqlQuery(self.db) query.prepare("SELECT * FROM markers WHERE rfid_id = :rfid_id") query.bindValue(":rfid_id", self.rfidIDField.text().strip(" ")) query.exec() found = query.next() if found: QMessageBox.warning(self, 'Error!', "Marker with such id already exists.") return record.setValue('rfid_id', self.rfidIDField.text().strip(" ")) self.tableModel.insertRecord(-1, record) self.tableModel.select()
def show_records(self): # createDBConnection() query = QSqlQuery() try: emp_id_txt = self.ln_ID.text() except: pass if emp_id_txt: emp_id = int(emp_id_txt) query.prepare( "SELECT emp_id, name, Designation, dob from employee1 where emp_id = (:emp_id) " ) query.bindValue(":emp_id", emp_id) if query.exec_(): print("show_records Successful") # self.lb_msg.SetText("showing current record !! ") else: print("show_records Error: ", query.lastError().text()) model = QSqlTableModel() self.show_records_View("Title", model) while query.next(): print("query show_records ", query.value(0), query.value(1), query.value(2), query.value(3)) else: QMessageBox.critical(self, 'Error', 'Please Enter Associate ID', QMessageBox.Retry) self.ln_ID.setFocus()
def bt_add(self): """ Вызывается по нажатию кнопки "Добавить строку" """ dialog_add = Ed_win(self) # Устанавливаем пустые значения в поля на форме dialog_add.edit_text.setText = '' dialog_add.cb_ser_name.setCurrentIndex(-1) dialog_add.cb_fio.setCurrentIndex(-1) # открываем окно добавления dialog_add.show() # Если нажата кнопка ОК на форме добавления if dialog_add.exec() == QDialog.Accepted: ser = dialog_add.cb_ser_name.currentText() user = dialog_add.cb_fio.currentText() self.db = QtSql.QSqlDatabase.addDatabase( "QSQLITE") # создаём подключение self.db.setDatabaseName("ProblemDB.db") if self.db.open(): qry1 = QSqlQuery(self.db) qry1.prepare("""SELECT s.id FROM services s where s.name =:ser""") qry1.bindValue(":ser", ser) qry1.exec() qry1.next() servId = qry1.value(0) qry1.prepare("""SELECT u.id FROM User u where u.FIO =:user""") qry1.bindValue(":user", user) qry1.exec() qry1.next() userId = qry1.value(0) qry1.prepare( """INSERT INTO def_message(idServices, text,idUser) VALUES(:idService, :text,:idUser)""") qry1.bindValue(":idService", servId) qry1.bindValue(":text", dialog_add.edit_text.text()) qry1.bindValue(":idUser", userId) qry1.exec() self.qry = QSqlQuery(self.db) self.qry.prepare("""SELECT d.id, dat, s.name, d.text, u.FIO FROM def_message d Left Join services s on d.idServices = s.id left join User u on d.idUser = u.id""") self.qry.exec() self.model.setQuery(self.qry) self.tableView.setModel(self.model) else: QMessageBox.critical(self, 'error', self.model.lastError().text())
def __parse_config(self, line): line = line.strip('[]') record = line.split("=") if len(record) == 2: key = record[0].strip() value = record[1].strip() if key.lower() == 'LastDrawNumberFromSiteUrl'.lower(): self.LastDrawNumberFromSiteUrl = value elif key.lower() == 'GameName'.lower(): self.GameName = value elif key.lower() == 'DefaultWinCost'.lower(): self.DefaultWinCost = str(value) elif key.lower() == 'NumberOfBalls1'.lower(): self.NumberOfBalls1 = int(value) elif key.lower() == 'NumberOfBalls2'.lower(): self.NumberOfBalls2 = int(value) elif key.lower() == 'EndOfBalls1'.lower(): self.EndOfBalls1 = int(value) elif key.lower() == 'EndOfBalls2'.lower(): self.EndOfBalls2 = int(value) elif key.lower() == 'StartOfBalls1'.lower(): self.StartOfBalls1 = int(value) elif key.lower() == 'StartOfBalls2'.lower(): self.StartOfBalls2 = int(value) elif key.lower() == 'DefaultVariantCost'.lower(): self.DefaultVariantCost = string_to_float(value) query = QSqlQuery() query.prepare( "INSERT INTO [Config] ([Key],[Value]) VALUES (:key, :value)") query.bindValue(":key", key) query.bindValue(":value", value) query.exec_() pass #end
def saquery_to_qtquery(sa_query): statement = sa_query.statement.compile() qtquery = QSqlQuery() qtquery.prepare(str(statement)) for name, value in statement.params.items(): qtquery.bindValue(':' + name, value) return qtquery
def recargar(total_transaccion): print(total_transaccion) estado = db.open() if estado == False: print("no se pudo conectar") #QMessageBox.warning("error",db.lastError().text(),QMessageBox.Discard) print("error", db.lastError().text()) else: print("conexion a base de datos correcta") print("valor", total_transaccion) #total_transaccion=entradaganancia.text() #aqui se hace una inserccion a la base da datos en la tabla transacion la tabla se define despues del "insert into " sql = "INSERT INTO transacciones(total_transaccion) VALUES(:total_transaccion)" consulta = QSqlQuery() consulta.prepare(sql) consulta.bindValue(":total_transaccion", total_transaccion) #print(total_transaccion) #aqui se ejecuta la consulta estado = consulta.exec_() if estado == True: #QMessageBox.warning("correcto","datos guardados",QMessageBox.Discard) print("correcto", "datos guardados") else: #QMessageBox.warning("error",db.lastError().text(),QMessageBox.Discard) print("error", db.lastError().text()) db.close()
def Delete(self): dltxt, dlok = QInputDialog.getText( self, 'Удаление', 'Введите ID препарата, который вы хотите удалить:') if dlok: db = QtSql.QSqlDatabase.addDatabase('QSQLITE') db.setDatabaseName("db.db") db.open() querydl = QSqlQuery() querydl.prepare("DELETE FROM medications WHERE mId= ( ? )") querydl.bindValue(0, dltxt) querydl.exec() qry = QSqlQuery() qry = db.exec("SELECT mId AS 'ID'," "mName AS 'Название'," "mDescription AS 'Описание'," "mPrice AS 'Цена' FROM medications") tabmodel = QSqlQueryModel() self.ui.tableView_2.setModel(tabmodel) tabmodel.setQuery(qry) self.ui.tableView_2.setColumnWidth(2, 310) header = self.ui.tableView_2.horizontalHeader() Lheader = self.ui.tableView_2.verticalHeader() Lheader.setVisible(False) header.setStretchLastSection(True) self.ui.tableView_2.show db.close()
def accept(self): unit = '' username = self.lineEdit_user.text() password = self.lineEdit_passwd.text() unit = self.comboBox_gongdui.currentText() if (username == "" or password == ""): print( QtWidgets.QMessageBox.warning(self, "警告", "用户名和密码不可为空!", QtWidgets.QMessageBox.Yes, QtWidgets.QMessageBox.Yes)) return False query = QSqlQuery() query.prepare( "select * from user where name == ? and password == ? and unit ==?" ) query.bindValue(0, username) query.bindValue(1, password) query.bindValue(2, unit) query.exec_() if (not query.next()): QtWidgets.QMessageBox.information(self, "提示", "该账号不存在!", QtWidgets.QMessageBox.Yes, QtWidgets.QMessageBox.Yes) self.db.close() else: #验证正确,隐藏当前窗口,给主窗口发消息 self.db.close() print(query.value(3)) self.hide() self.login_success_signal.emit(unit) return True
def insertPixMapByteArray(self, datamodel): changed_light_bulb = datamodel.get_changed_light_bulb() changed_ot_light = datamodel.get_changed_ot_light() low_light_bulb = datamodel.get_low_light_bulb() low_ot_light = datamodel.get_low_ot_light() changed_low_color = datamodel.get_changed_low_color() # print("Changed Light Bulb: "+str(changed_light_bulb)) # print("Changed Low Light Color: " + str(changed_low_color)) query = QSqlQuery() # query.exec_(f"""insert into image_table( changed_light_bulb) # values( '{ckjk}')""") query.prepare( "INSERT INTO image_table (changed_light_bulb, changed_ot_light, " "low_light_bulb, low_ot_light, changed_low_color) " "VALUES ( :changed_light_bulb, :changed_ot_light, :low_light_bulb," " :low_ot_light, :changed_low_color)") query.bindValue(":changed_light_bulb", changed_light_bulb) query.bindValue(":changed_ot_light", changed_ot_light) query.bindValue(":low_light_bulb", low_light_bulb) query.bindValue(":low_ot_light", low_ot_light) query.bindValue(":changed_low_color", changed_low_color) if not query.exec(): qDebug( ) << "Error inserting image into table:\n" << query.lastError() '''
def artist_selection(self, text): assert isinstance(text, str) self.ui.listWidget_Songs_Artist_Main.clear() if len(text) != 0: text_split = text.split(" ") artist_words = [] for split in text_split: if len(split) != 0: artist_words.append(split) query_string = 'SELECT t_artist.id, t_artist.name FROM t_artist WHERE' for i in range(len(artist_words)): if i == 0: query_string += " t_artist.name LIKE '%'||?||'%'" else: query_string += " AND t_artist.name LIKE '%'||?||'%'" query = QSqlQuery() query.prepare(query_string) for i in range(len(artist_words)): query.bindValue(i, artist_words[i]) query.setForwardOnly(1) query.exec_() while query.next(): self.ui.listWidget_Songs_Artist_Main.addItem(str(query.value(0)) + " - " + query.value(1))
def reset_task_progress(item_id): query = QSqlQuery() query.prepare( "UPDATE items SET rename=0, derivatives=0, copy=0, complete=0 WHERE id=:item_id" ) query.bindValue(":item_id", item_id) query.exec_()
def uniqueUsername(self, username): index = 0 query = QSqlQuery() query.prepare("select count(*) from User where username = :username") query.bindValue(":username", username) query.exec_() query.next() return query.value(0) == 0
def _blank_start_time_query(self): query = QSqlQuery() query.prepare("SELECT COUNT(*) " "FROM tracks " "WHERE power_hour_id = :power_hour_id AND " " url != '' AND " " trim(start_time) = ''") query.bindValue(':power_hour_id', self.current_power_hour_id) return query
def deleteBook(self, index_book: int): query = QSqlQuery() query.prepare(''' DELETE FROM books WHERE book_id = :id_book ''') query.bindValue(":id_book", self.books[index_book].idBook) query.exec_() self.beginRemoveRows(QModelIndex(), index_book, index_book) del self.books[index_book] self.endRemoveRows()
def SupprimerClient(self, indiceClient): query = QSqlQuery() query.prepare('''DELETE FROM Client WHERE idClient = :idclient''') query.bindValue(":idclient", self.client[indiceClient].idClient) query.exec_() self.beginRemoveRows(QModelIndex(), indiceClient, indiceClient) del self.client[indiceClient] self.endRemoveRows()
def _start_query(self): self.setCursor(Qt.WaitCursor) QCoreApplication.processEvents() # noinspection PyBroadException try: self.model.clear() QCoreApplication.processEvents() line = self.text.text().strip() if not line: self.model.setQuery("SELECT media_id from Media;") # self.model.setQuery("SELECT DISTINCT media_id from ManifoldItems NATURAL JOIN Manifolds WHERE ready;") return if line == '@import': _source_dir = QFileDialog.getExistingDirectory( self, "Select a directory to import", "", QFileDialog.DontResolveSymlinks | QFileDialog.ReadOnly) _source_dir = os.path.abspath(_source_dir) assert os.path.isdir(_source_dir) self._logger.info(f"Starting ingestion of {_source_dir}") self._py_db.ingest_file_directory( _source_dir ) # NOTE: this will freeze the GUI for a long time! self._py_db.notify_bg_manifold_build() self._py_db.thumbs_load() self._logger.info(f"Ingestion finished. ") _source_dir_url_like = 'file:' + pathname2url( _source_dir) + '%' query = QSqlQuery() query.prepare( 'SELECT media_id FROM MediaLocations WHERE url LIKE ? ORDER BY url' ) query.bindValue(0, _source_dir_url_like) self.model.setQuery(query) return results_id, inserted = self._py_db.search(line, n=1000, search_k=-1) query_s = (f'SELECT media_id FROM results.ResultsMediaFiltered ' f'WHERE results_id = {results_id:d} ORDER BY rank ASC') query = QSqlQuery() query.exec(query_s) if not query.isActive(): self._logger.debug(f'results query: {query.lastQuery():s}') self._logger.error( f'results query error: {query.lastError().text():s}') self.model.setQuery(query) except Exception: import traceback traceback.print_exc() finally: self.setCursor(Qt.ArrowCursor)
def ExcluirVeiculo(codigoVeic): conn = ConexaoSQL db = conn.getConexao() db.open() query = QSqlQuery() query.prepare("DELETE FROM Veiculo WHERE CodigoVeic=:codigoVeic") query.bindValue(":codigoVeic", codigoVeic) query.exec_() db.commit()
def supprimeLivre(self, indiceLivre): query = QSqlQuery() query.prepare(''' DELETE FROM books WHERE book_id = :idLivre ''') query.bindValue(":idLivre", self.livres[indiceLivre].idLivre) query.exec_() self.beginRemoveRows(QModelIndex(), indiceLivre, indiceLivre) del self.livres[indiceLivre] self.endRemoveRows()
def update(self, table, values, id): sql = "UPDATE " + table + " SET " for value in values: sql += str(value[0]) + " = :" +str(value[0]) + ", "; sql = sql[:-2]; sql += " WHERE id = :id" query = QSqlQuery(); query.prepare(sql); for value in values: query.bindValue(":"+value[0], value[1]); query.bindValue(":id", id); query.exec_();
def ExcluirCliente(codigoCli): conn = ConexaoSQL db = conn.getConexao() db.open() query = QSqlQuery() query.prepare("DELETE FROM Cliente WHERE CodigoCli=:codigoCli") query.bindValue(":codigoCli", codigoCli) query.exec_() db.commit()
def isValid(self, table, rowName, value): sql = "SELECT " + rowName + " FROM " + table + " WHERE " + rowName + " = :value"; query = QSqlQuery(); query.prepare(sql); query.bindValue(":value", value); query.exec_(); query.next(); return value == query.value(0);
def delete_book(id): q = QSqlQuery(config.db) q.prepare(queries.DELETE_BOOK) q.bindValue(0, id) if not q.exec_(): print(q.lastError().text()) config.db.rollback() else: config.db.commit()
def fetchRelevantSongOffline(self, SongPath): # print("in fetchRelevantSongOffline") metadataDict = {} year = "" # list of path of all the relevant songs relevantSongPathList = [] # dict that contains metadata of songs in paragraph as value and songPath as key relevantSongDict = {} # print("test") # print(SongPath) metadataDict = ManageMetaData.ReadMetaData(self, SongPath) # print(metadataDict) # get year out of metadata TDRC tag if metadataDict.get("TDRC"): match = re.match(r'\d{4}', metadataDict.get("TDRC")) if match is not None: # it found a match! year = match.group(0) # print(year) TPE1 = metadataDict.get("TPE1") # print(metadataDict.get("TPE1")) # print(self.mls) # print(self.mls.db.databaseName()) query = QSqlQuery(self.mls.db) queryString = "SELECT SPath FROM songs WHERE TPE1 = :TPE1 OR year = :year" if self.mls.db.open(): # print("db is open") query.prepare(queryString) query.bindValue(":TPE1", TPE1) query.bindValue(":year", year) # print("executing queryString") record = query.exec_() # print(record) if record: # print("read successful, it seems") while query.next(): relevantSongPathList.append(query.value(0)) else: print("read not successful") # print("error") # print(query.lastError().text()) return False else: print("could not read from the database, connection not found") return False # build relevantSongDict for path in relevantSongPathList: # print(path) relevantSongDict[path] = self.metadataToPara(path) # predict # if SongPath: # self.predict(SongPath, relevantSongDict) # else: # print("problem with SongPath so can't call predict") return relevantSongDict
def show_graph(self, items, dates, stat_type): print("ITEMS:", items) start, end = dates print("{} - {} {}".format(start, end, stat_type)) query_sql = graph_queries[stat_type] if query_sql is not None: query_sql = query_sql.replace( ":ITEMS:", ", ".join('"{}"'.format(x) for x in items)) #print (query_sql) query_ = QSqlQuery() query_.prepare(query_sql) query_.bindValue(":start", str(start)) query_.bindValue(":end", str(end)) min_max = {} min_max_all = {"min": 99999, "max": 0} for item in items: min_max[item] = {"min": 99999, "max": 0} hash = defaultdict(dict) #TODO: does it make sense to search for mean max of specific items #Wouldn't one min/max be better? if query_.exec_(): while query_.next(): item = query_.value(0) weight = query_.value(1) item_date = query_.value(2) min_max[item]["min"] = min(min_max[item]["min"], weight) min_max[item]["max"] = max(min_max[item]["max"], weight) min_max_all["max"] = max(min_max_all["max"], weight) min_max_all["min"] = min(min_max_all["min"], weight) hash[item_date][item] = weight hash.default_factory = None print(min_max) print(min_max_all) print(hash) #Scales values from min to max to 0-1 for values in hash.values(): for item, weight in values.items(): min_val = min_max[item]["min"] max_val = min_max[item]["max"] #min_val = min_max_all["min"] #max_val = min_max_all["max"] #if min_val == max_val: values[item] = weight / max_val #Not sure where is this needed it makes wrong chart: #else: #values[item]=(weight-min_val+1)/(max_val-min_val) #print (hash) cd = ChartDialog(self) cd.set_calendar_chart(items, hash) cd.show() else: print(query_.lastError().text())
def dateConflict(self,busId,dptDateTimeText,arvDateTimeText): query = QSqlQuery() query.prepare("select count(*) from Plan where busId = :busId and not (:dptinp > arvDate or :dptarv < dptDate)") query.bindValue(":busId" , busId) query.bindValue(":dptinp",dptDateTimeText) query.bindValue(":dptarv",arvDateTimeText) if query.exec_() == False: print("Execution failed") return False query.next() return query.value(0) != 0
def check_table_exist(table_name): rows = -1 q = QSqlQuery(config.db) q.prepare(queries.CHECK_TABLE_EXISTS) q.bindValue(0, table_name) q.exec_() if q.next(): rows = q.value(0) return rows == 1
def removeRow(self, position, *args, **kwargs): query = QSqlQuery() query.prepare( 'DELETE FROM tracks ' 'WHERE position = :position AND power_hour_id = :power_hour_id') query.bindValue(":position", position) query.bindValue(":power_hour_id", self.current_power_hour_id) return query.exec_()
def insert(db, table, values): query = QSqlQuery(db) col_str = ', '.join(f'`{col}`' for col in values) val_str = ', '.join(f':{col}' for col in values) query.prepare(f"INSERT INTO {table} ({col_str}) VALUES ({val_str})") for col, val in values.items(): query.bindValue(f':{col}', val) if not query.exec(): raise SyntaxError(query.lastError().text()) return query.lastInsertId()
def addCompetitor(self): if not self.lineEdit_Name.text(): QMessageBox.warning(None, "Bad values", "Improper values in dialog box.", QMessageBox.Ok) else: query = QSqlQuery() query.prepare("INSERT INTO t_competitor (name, entry_date) VALUES (?, ?)") query.bindValue(0, self.lineEdit_Name.text()) query.bindValue(1, self.dateEdit_EntryDate.date().toJulianDay()) if not query.exec_(): QMessageBox.warning(None, "DB Error", "Cannot insert to DB: " + query.lastError().text(), QMessageBox.Ok) pass
def addAnime(self): if not self.lineEdit_Name.text(): QMessageBox.warning(None, "Bad values", "Improper values in dialog box.", QMessageBox.Ok) else: id_anime_serie = self.comboBox_Serie.currentText()[:self.comboBox_Serie.currentText().find(" -")] query = QSqlQuery() query.prepare("INSERT INTO t_anime (name, id_anime_serie) VALUES (?, ?)") query.bindValue(0, self.lineEdit_Name.text()) query.bindValue(1, int(id_anime_serie)) if not query.exec_(): QMessageBox.warning(None, "DB Error", "Cannot insert to DB: " + query.lastError().text(), QMessageBox.Ok) pass
def addSerie(self): if not self.lineEdit_Serie.text(): QMessageBox.warning(None, "Bad values", "Improper values in dialog box.", QMessageBox.Ok) else: query = QSqlQuery() query.prepare("INSERT INTO t_anime_serie (name) VALUES (?)") query.bindValue(0, self.lineEdit_Serie.text()) if not query.exec_(): QMessageBox.warning(None, "DB Error", "Cannot insert to DB: " + query.lastError().text(), QMessageBox.Ok) else: self.reloadSerie() pass
def CreateUser(username, password): qry = QSqlQuery() qry.prepare("""INSERT INTO users (username, password, deleted) VALUES (:username, :password, 0)""") qry.bindValue(":username", Encrypt(username)) qry.bindValue(":password", Encrypt(password)) if qry.exec_() is False: print("Query failed in users table\n\t" + qry.lastError().text(), file=sys.stderr) return False print( "Query to users table succeeded", file=sys.stdout) return True
def addEvent(self): if (not self.lineEdit_Name.text() or not self.lineEdit_Place.text() or self.dateEdit_StartDate.date() > self.dateEdit_EndDate.date()): QMessageBox.warning(None, "Bad values", "Improper values in dialog box.", QMessageBox.Ok) else: query = QSqlQuery() query.prepare("INSERT INTO t_event (name, place, start_date, end_date) VALUES (?, ?, ?, ?)") query.bindValue(0, self.lineEdit_Name.text()) query.bindValue(1, self.lineEdit_Place.text()) query.bindValue(2, self.dateEdit_StartDate.date().toJulianDay()) query.bindValue(3, self.dateEdit_EndDate.date().toJulianDay()) if not query.exec_(): QMessageBox.warning(None, "DB Error", "Cannot insert to DB: " + query.lastError().text(), QMessageBox.Ok) pass
def addAttraction(self): if ( not self.lineEdit_Name.text() or not self.comboBox_Event.currentText() or self.dateTimeEdit_StartTime.dateTime() > self.dateTimeEdit_EndTime.dateTime() ): QMessageBox.warning(None, "Bad values", "Improper values in dialog box.", QMessageBox.Ok) else: id_event = self.comboBox_Event.currentText()[: self.comboBox_Event.currentText().find(" -")] query = QSqlQuery() query.prepare("INSERT INTO t_attraction (name, id_event, start_time, end_time) VALUES (?, ?, ?, ?)") query.bindValue(0, self.lineEdit_Name.text()) query.bindValue(1, int(id_event)) query.bindValue(2, self.dateTimeEdit_StartTime.dateTime().toTime_t()) query.bindValue(3, self.dateTimeEdit_EndTime.dateTime().toTime_t()) if not query.exec_(): QMessageBox.warning( None, "DB Error", "Cannot insert to DB: " + query.lastError().text(), QMessageBox.Ok ) pass
def addSong(self): if not self.lineEdit_Name.text() or not self.lineEdit_File.text() or len(self.modelleft.stringList()) == 0: QMessageBox.warning(None, "Bad values", "Improper values in dialog box.", QMessageBox.Ok) else: id_song_type = self.comboBox_Type.currentText()[:self.comboBox_Type.currentText().find(" -")] id_anime = self.comboBox_Anime.currentText()[:self.comboBox_Anime.currentText().find(" -")] query = QSqlQuery() query.prepare("INSERT INTO t_song (name, file, id_song_type, id_anime) VALUES (?, ?, ?, ?)") query.bindValue(0, self.lineEdit_Name.text()) query.bindValue(1, self.lineEdit_File.text()) query.bindValue(2, int(id_song_type)) query.bindValue(3, int(id_anime)) if not query.exec_(): QMessageBox.warning(None, "DB Error", "Cannot insert to DB: " + query.lastError().text(), QMessageBox.Ok) else: lastinsertid = query.lastInsertId() query = QSqlQuery() statement = "INSERT INTO fk_song_artist (id_song, id_artist) VALUES " artistlist = self.modelleft.stringList() artist = artistlist.pop() artistid = artist[:artist.find(" -")] statement += "( " + str(lastinsertid) + ", " + artistid + ")" try: while artistlist[0]: artist = artistlist.pop() artistid = artist[:artist.find(" -")] statement += ", ( " + str(lastinsertid) + ", " + artistid + ")" except: pass query.prepare(statement) if not query.exec_(): QMessageBox.warning(None, "DB Error", "Cannot insert to DB: " + query.lastError().text(), QMessageBox.Ok) pass
class SQLFeeder(): def __init__(self, messenger: Messenger, backend: str, db_name: str) -> None: #print("connecting to DB %s" % db_name) self.messenger = messenger self.backend = backend if backend == 'PGSQL': self.database = QSqlDatabase.addDatabase("QPSQL7", db_name) #TODO find out if I want QPSQL or QPSQL7 self.database.setHostName("localhost") self.database.setDatabaseName(db_name) self.database.setUserName("postgres") #self.database.setPassword("") elif backend == 'SQLITE': self.database = QSqlDatabase.addDatabase("QSQLITE", db_name) self.database.setDatabaseName('%s.sqlite3' % db_name) else: self.messenger.message_fatal("invalid DB backend: %s" % backend) connected = False ok = self.database.open() if not ok: self.messenger.message_fatal("DB open error:" + self.database.lastError().text()) else: self.cursor = QSqlQuery(self.database) ok = self.cursor.exec_() #self.execute('SET time_zone = "+00:00";') self.messenger.message_debug("Connected to DB %s" % db_name) connected = True #self.execute("SET time_zone = 'Europe/Berlin'") def _attach_params(self, param_dict) -> None: for key, value in param_dict.items(): if type(value) == datetime: #TODO: time self.cursor.bindValue(':'+key, value.strftime('%Y-%m-%d %H:%M:%S')) elif type(value) == date: self.cursor.bindValue(':'+key, value.strftime('%Y-%m-%d') + " 00:00:00") else: self.cursor.bindValue(':'+key, value) #if key == 'stackable': # print(value) def _attach_where_params(self, where_param_dicts) -> None: #TODO pretty sure this is an *exact* dupe of _attach_params if where_param_dicts: for key, comparator, value in where_param_dicts: if type(value) == datetime: #TODO: time self.cursor.bindValue(':'+key, value.strftime('%Y-%m-%d %H:%M:%S')) elif type(value) == date: self.cursor.bindValue(':'+key, value.strftime('%Y-%m-%d') + " 00:00:00") else: self.cursor.bindValue(':'+key, value) #def create_table(self, name: str, comment: str, dict_list: List[Dict[str,str]], debug=False) -> None: def create_table(self, name: str, comment: str, dict_list, debug=False) -> None: id_found = False for dict_to_check in dict_list: if dict_to_check['name'] == 'id': id_found = True break if not id_found: dict_list = ({'not_null':True, 'name':'id', 'type':'PKEY'}, ) + dict_list field_part = '' dict_list += ({'not_null':True, 'name':'row_created_ts', 'type':'DATETIME', 'default':'NOW()'}, {'not_null':True, 'name':'row_modified_ts', 'type':'DATETIME', 'default':'NOW()'}) if len(dict_list) > 0: for field in dict_list: if field['type'] == 'PKEY': if self.backend == 'SQLITE': field_type = 'INTEGER PRIMARY KEY' else: field_type = 'BIGSERIAL, PRIMARY KEY (%s)' % field['name'] elif field['type'] == 'FKEY': if self.backend == 'SQLITE': field_type = 'INTEGER REFERENCES %s(id)' % field['fkey_table'] else: if field['not_null']:#TODO unite with normal not_null code not_null = ' NOT NULL' else: not_null = '' if 'default' in field: #TODO unite with normal default code and add to sqlite, this probably fixes TabHave default = "DEFAULT '%s'" % field['default'] else: default = '' field_type = 'BIGINT%s, FOREIGN KEY (%s) REFERENCES %s(id)' % (not_null, field['name'], field['fkey_table']) elif field['type'].startswith('ENUM'): if self.backend == 'SQLITE': field_type = 'TEXT' self.messenger.message_debug("SQLite doesnt know data type ENUM, setting TEXT instead") else: self._execute('DROP TYPE', 'DROP TYPE IF EXISTS %s_enum CASCADE' % (field['name']), True) #TODO convert to method self._execute('CREATE TYPE', 'CREATE TYPE %s_enum AS %s' % (field['name'], field['type']), True) #TODO convert to method field_type = '%s_enum' % field['name'] elif field['type'].startswith('VARCHAR') or field['type'].startswith('CHAR'): if self.backend == 'SQLITE': field_type = 'TEXT' else: field_type = field['type'] elif field['type'] == 'DATETIME': if self.backend == 'SQLITE': pass else: field_type = 'TIMESTAMP' else: if field['type']: if self.backend == 'PGSQL' and field['type'] == 'INT16': #TODO move into one layer up in the code structure field_type = 'INT2' elif self.backend == 'PGSQL' and field['type'] == 'INT32': field_type = 'INT4' elif self.backend == 'PGSQL' and field['type'] == 'INT64': field_type = 'INT8' elif self.backend == 'SQLITE' and field['type'] in ('INT16', 'INT32', 'INT64'): field_type = 'INTEGER' else: field_type = field['type'] else: print("empty type for", field) #TODO better warning if field['not_null'] and field['type'] != 'PKEY' and field['type'] != 'FKEY': #PKEYs don't have this explicitly, fkey does it further above not_null = ' NOT NULL' else: not_null = '' if 'default' in field and field['type'] != 'FKEY': #fkey sets this further above default = " DEFAULT '%s'" % field['default'] else: default = '' field_part += ' %s %s%s%s,\n' % (field['name'], field_type, not_null, default) else: print('Need to have params for create_table. Called it with:', name, dict_list, debug) return query = 'CREATE TABLE %s (\n%s\n)' % (name, field_part[:-2]) if self.backend == 'PGSQL': self._execute('DROP TABLE', 'DROP TABLE IF EXISTS %s CASCADE' % name, debug) #TODO move into separate method else: self._execute('DROP TABLE', 'DROP TABLE IF EXISTS %s' % name, debug) #TODO move into separate method self._execute('CREATE TABLE', query, debug) #TODO comment on table and on rows def _execute(self, operation: str, query: str, debug: bool) -> None: if debug: print('exec:', query) #TODO restore this functionality: 'with params:', param_dict, "where", where_param_dict, "\n") if operation in ('CREATE TABLE', 'CREATE TYPE', 'DROP TABLE', 'DROP TYPE', '\dt', 'JUST RUN IT'): ok = self.cursor.exec_(query) else: ok = self.cursor.exec_() if not ok: print("sql error on execute:", self.cursor.lastError().text()) print(" on this query:", self.cursor.lastQuery()) raise Exception("invalid vals in db.execute_safe\n") def export(self, path: str) -> None: #TODO improve #print("started export") proc = subprocess.call(['./pg_dumper.sh', path]) if proc != 0: self.messenger.message_warning(self, "Error trying to dump DB") print("done export") #def _fetch(self, param_list: List[str]) -> List[Dict[str,Any]]: def _fetch(self, param_list): result = [] #print('fetch start') while self.next(): row = {} counter = 0 for key in param_list: row[key] = self.value(counter) #print(row[key]) counter += 1 result.append(row) #print('fetch end') return result def insert(self, table: str, param_dict, debug=False) -> None: into_part = '' values_part = '' if len(param_dict) > 0: for key in param_dict.keys(): into_part += key + ', ' values_part += ':' + key + ', ' into_part = into_part[:-2] values_part = values_part[:-2] else: print('Need to have params for insert. Called it with:', table, param_dict, debug) return query = 'INSERT INTO %s (%s) VALUES(%s)' % (table, into_part, values_part) self._prepare(query) self._attach_params(param_dict) self._execute('INSERT', query, debug) def last_insert_id(self) -> int: return self.cursor.lastInsertId() def next(self) -> bool: #TODO check return type in API doc return self.cursor.next() #TODO error handling def _prepare(self, query: str) -> None: if not self.cursor.prepare(query): print("sql error on prepare:", self.cursor.lastError().text()) print(" on this query:", self.cursor.lastQuery()) #def select(self, table: str, param_list: List[str], where_param_dicts: List[Dict[str,Any]], where_literal="", debug=False) -> List[Dict[str,Any]]: #TODO limit def select(self, table: str, param_list, where_param_dicts=[], where_literal="", debug=False): #TODO limit #print("where_param_dicts", where_param_dicts) field_list = '' if len(param_list) > 0: for key in param_list: field_list += key + ', ' field_list = field_list[:-2] where_part = '' #TODO move into separate method and convert other method to use that if not where_param_dicts: where_part = None elif len(where_param_dicts) == 0: where_part = None else: for param_dict in where_param_dicts: #TODO apply this to all other methods, or better, unify code if type(param_dict['value']) == str: param_dict['value'] = param_dict['value'].replace("'", "") #TODO remove once i attach params properly if self.backend == 'SQLITE': param_dict['value'] = '"' + param_dict['value'] + '"' else: param_dict['value'] = "'" + param_dict['value'] + "'" where_part += '%s %s %s AND ' % (param_dict['field'], param_dict['comparator'], param_dict['value']) where_part = where_part[:-4] else: print('Need to have params for select. Called it with:', table, param_list, where_param_dicts, debug) return query = 'SELECT %s FROM %s' % (field_list, table) if where_part or where_literal: query = '%s WHERE' % (query) if where_part: query = '%s %s' % (query, where_part) if where_literal: if len(where_literal)>0: query = '%s %s' % (query, where_literal) self._prepare(query) self._attach_where_params(where_param_dicts) self._execute('SELECT', query, debug) return self._fetch(param_list) def table_list(self): if self.backend == 'SQLITE': self._execute('\dt', """SELECT name FROM sqlite_master WHERE type='table' ORDER BY name""", False) else: self._execute('\dt', """SELECT c.relname as "Name" FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ('r','') AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' AND n.nspname !~ '^pg_toast' AND pg_catalog.pg_table_is_visible(c.oid)""", False) fetched = self._fetch(('Name', )) result = [] for item in fetched: result.append(item['Name']) return result def transaction_begin(self): self.database.transaction() def transaction_commit(self): if not self.database.commit(): self.msg.message_warning("failed DB commit") #def update(self, table: str, param_dict: Dict[str,Any], where_param_dicts: List[Dict[str,Any]], debug=False) -> None: #TODO rename where_param_dict to where_param_tuples def update(self, table: str, param_dict, where_param_dicts=[], debug=False) -> None: #TODO rename where_param_dict to where_param_tuples set_part = '' if len(param_dict) > 0: for key in param_dict.keys(): set_part += '%s = :%s, ' % (key, key) set_part = set_part[:-2] else: print('Need to have params for update. Called it with:', table, param_dict, where_param_dicts, debug) return where_part = '' if len(where_param_dicts) > 0: for where_param_dict in where_param_dicts: #TODO apply this to all other methods, or better, unify code if type(where_param_dict['value']) == str: where_param_dict['value'] = where_param_dict['value'].replace("'", "") #TODO remove once i attach params properly if self.backend == 'SQLITE': where_param_dict['value'] = '"' + where_param_dict['value'] + '"' else: where_param_dict['value'] = "'" + where_param_dict['value'] + "'" where_part += '%s %s %s AND ' % (where_param_dict['field'], where_param_dict['comparator'], where_param_dict['value']) where_part = where_part[:-4] else: print('Need to have where for update. Called it with:', table, param_dict, where_param_dicts, debug) return query = 'UPDATE %s SET %s WHERE %s' % (table, set_part, where_part) self._prepare(query) self._attach_params(param_dict) self._attach_where_params(where_param_dicts) self._execute('UPDATE', query, debug) def value(self, number: int): return self.cursor.value(number)
class Query: """A prepared SQL query.""" def __init__(self, querystr, forward_only=True): """Prepare a new SQL query. Args: querystr: String to prepare query from. forward_only: Optimization for queries that will only step forward. Must be false for completion queries. """ self.query = QSqlQuery(QSqlDatabase.database()) log.sql.debug('Preparing SQL query: "{}"'.format(querystr)) ok = self.query.prepare(querystr) self._check_ok('prepare', ok) self.query.setForwardOnly(forward_only) def __iter__(self): if not self.query.isActive(): raise BugError("Cannot iterate inactive query") rec = self.query.record() fields = [rec.fieldName(i) for i in range(rec.count())] rowtype = collections.namedtuple('ResultRow', fields) while self.query.next(): rec = self.query.record() yield rowtype(*[rec.value(i) for i in range(rec.count())]) def _check_ok(self, step, ok): if not ok: query = self.query.lastQuery() error = self.query.lastError() msg = 'Failed to {} query "{}": "{}"'.format(step, query, error.text()) raise_sqlite_error(msg, error) def _bind_values(self, values): for key, val in values.items(): self.query.bindValue(':{}'.format(key), val) if any(val is None for val in self.bound_values().values()): raise BugError("Missing bound values!") def run(self, **values): """Execute the prepared query.""" log.sql.debug('Running SQL query: "{}"'.format( self.query.lastQuery())) self._bind_values(values) log.sql.debug('query bindings: {}'.format(self.bound_values())) ok = self.query.exec_() self._check_ok('exec', ok) return self def run_batch(self, values): """Execute the query in batch mode.""" log.sql.debug('Running SQL query (batch): "{}"'.format( self.query.lastQuery())) self._bind_values(values) db = QSqlDatabase.database() ok = db.transaction() self._check_ok('transaction', ok) ok = self.query.execBatch() try: self._check_ok('execBatch', ok) except Error: # Not checking the return value here, as we're failing anyways... db.rollback() raise ok = db.commit() self._check_ok('commit', ok) def value(self): """Return the result of a single-value query (e.g. an EXISTS).""" if not self.query.next(): raise BugError("No result for single-result query") return self.query.record().value(0) def rows_affected(self): return self.query.numRowsAffected() def bound_values(self): return self.query.boundValues()
def song_edit(self): song_id = int(self.ui.label_Songs_Id.text()) song_name = self.ui.lineEdit_Songs_Name.text().strip() artist_count = self.ui.listWidget_Songs_Artist.count() anime = self.ui.comboBox_Songs_Anime.currentText().strip() song_type = self.ui.comboBox_Songs_Type.currentText().strip() song_file = self.ui.lineEdit_Songs_File.text().strip() if song_id != 0: if len(song_name) > 0 and artist_count > 0 and len(anime) > 0 and len(song_type) > 0 and len(song_file) > 0: if QMessageBox.question(None, "Confirm edit", "Are you sure to edit '%d' song id?" % song_id, QMessageBox.Yes | QMessageBox.No) == QMessageBox.Yes: db = QSqlDatabase() current_db = db.database() assert isinstance(current_db, QSqlDatabase) current_db.transaction() error_occurred = False error_text = "" artists = self.ui.listWidget_Songs_Artist.findItems("*", Qt.MatchWildcard) artists_id_list = [] for artist in artists: assert isinstance(artist, QListWidgetItem) artist_id, artist_name = artist.text().split(" - ", 1) if int(artist_id) == 0: artist_query = QSqlQuery() artist_query.prepare('INSERT INTO t_artist (name) VALUES (?)') artist_query.bindValue(0, artist_name) if not artist_query.exec_(): error_occurred = True error_text = artist_query.lastError().text() last_id = artist_query.lastInsertId() artists_id_list.append(last_id) else: artists_id_list.append(int(artist_id)) anime_id, anime_name = anime.split(" - ", 1) if int(anime_id) == 0: anime_query = QSqlQuery() anime_query.prepare('INSERT INTO t_anime (name) VALUES (?)') anime_query.bindValue(0, anime_name) if not anime_query.exec_(): error_occurred = True error_text = anime_query.lastError().text() anime_id = anime_query.lastInsertId() else: anime_id = int(anime_id) song_type_id, _ = song_type.split(" - ", 1) song_type_id = int(song_type_id) song_query = QSqlQuery() song_query.prepare('UPDATE t_song SET name = ?, file = ?, id_song_type = ?, id_anime = ? ' 'WHERE id = ?') song_query.bindValue(0, song_name) song_query.bindValue(1, song_file) song_query.bindValue(2, song_type_id) song_query.bindValue(3, anime_id) song_query.bindValue(4, song_id) if not song_query.exec_(): error_occurred = True error_text = song_query.lastError().text() song_artist_now_query = QSqlQuery() song_artist_now_query.prepare('DELETE FROM t_song_artist WHERE id_song = ' + str(song_id)) if not song_artist_now_query.exec_(): error_occurred = True error_text = song_artist_now_query.lastError().text() for artist_insert_id in artists_id_list: song_artist_query = QSqlQuery() song_artist_query.prepare('INSERT INTO t_song_artist (id_song, id_artist) VALUES (?, ?)') song_artist_query.bindValue(0, song_id) song_artist_query.bindValue(1, artist_insert_id) if not song_artist_query.exec_(): error_occurred = True error_text = song_artist_query.lastError().text() if not error_occurred: current_db.commit() self.ui.signals.update_views.emit() else: current_db.rollback() QMessageBox.critical(None, "Database transaction failed", "The database reported an error: %s" % error_text, QMessageBox.Ok) else: QMessageBox.critical(None, "Invalid data", "Fields cannot be empty", QMessageBox.Ok)
def song_add(self): song_name = self.ui.lineEdit_Songs_Name.text().strip() artist_count = self.ui.listWidget_Songs_Artist.count() anime = self.ui.comboBox_Songs_Anime.currentText().strip() song_type = self.ui.comboBox_Songs_Type.currentText().strip() song_file = self.ui.lineEdit_Songs_File.text().strip() if len(song_name) > 0 and artist_count > 0 and len(anime) > 0 and len(song_type) > 0 and len(song_file) > 0: db = QSqlDatabase() current_db = db.database() assert isinstance(current_db, QSqlDatabase) current_db.transaction() error_occurred = False error_text = "" artists = self.ui.listWidget_Songs_Artist.findItems("*", Qt.MatchWildcard) artists_id_list = [] for artist in artists: assert isinstance(artist, QListWidgetItem) artist_id, artist_name = artist.text().split(" - ", 1) if int(artist_id) == 0: artist_query = QSqlQuery() artist_query.prepare('INSERT INTO t_artist (name) VALUES (?)') artist_query.bindValue(0, artist_name) if not artist_query.exec_(): error_occurred = True error_text = artist_query.lastError().text() last_id = artist_query.lastInsertId() artists_id_list.append(last_id) else: artists_id_list.append(int(artist_id)) anime_id, anime_name = anime.split(" - ", 1) if int(anime_id) == 0: anime_query = QSqlQuery() anime_query.prepare('INSERT INTO t_anime (name) VALUES (?)') anime_query.bindValue(0, anime_name) if not anime_query.exec_(): error_occurred = True error_text = anime_query.lastError().text() anime_id = anime_query.lastInsertId() else: anime_id = int(anime_id) song_type_id, _ = song_type.split(" - ", 1) song_type_id = int(song_type_id) song_query = QSqlQuery() song_query.prepare('INSERT INTO t_song (name, file, id_song_type, id_anime) VALUES (?, ?, ?, ?)') song_query.bindValue(0, song_name) song_query.bindValue(1, song_file) song_query.bindValue(2, song_type_id) song_query.bindValue(3, anime_id) if not song_query.exec_(): error_occurred = True error_text = song_query.lastError().text() song_id = song_query.lastInsertId() for artist_insert_id in artists_id_list: song_artist_query = QSqlQuery() song_artist_query.prepare('INSERT INTO t_song_artist (id_song, id_artist) VALUES (?, ?)') song_artist_query.bindValue(0, song_id) song_artist_query.bindValue(1, artist_insert_id) if not song_artist_query.exec_(): error_occurred = True error_text = song_artist_query.lastError().text() if not error_occurred: current_db.commit() self.ui.signals.update_views.emit() self.ui.tableView_Songs.sortByColumn(0, Qt.DescendingOrder) self.ui.label_Songs_Id.setText(str(song_id)) else: current_db.rollback() QMessageBox.critical(None, "Database transaction failed", "The database reported an error: %s" % error_text, QMessageBox.Ok) else: QMessageBox.critical(None, "Invalid data", "Fields cannot be empty", QMessageBox.Ok)