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)
