def __init__(self, *args):
super().__init__(*args)
self.setupUi(self)
self.servM = QSqlQueryModel()
self.userM = QSqlQueryModel()
self.db = QtSql.QSqlDatabase.addDatabase(
"QSQLITE") # создаём подключение
self.db.setDatabaseName("ProblemDB.db")
if self.db.open():
qry = QSqlQuery(self.db)
qry.prepare('select name from services')
qry.exec()
# QMessageBox.information(self, 'Отладка', 'select name from services')
self.servM.setQuery(qry)
# QMessageBox.information(self, 'Отладка', '3')
self.cb_ser_name.setModel(self.servM)
qry.prepare('select FIO from User')
qry.exec()
self.userM.setQuery(qry)
self.cb_fio.setModel(self.userM)
else:
QMessageBox.critical(self, 'error', self.model.lastError().text())
self.btnOk.clicked.connect(self.bt_ok)
self.btnCancel.clicked.connect(self.bt_cancel)
def collect_data(ui, db): #! Please add cute Icons back, sir
num_model = QSqlQueryModel()
cust_model = QSqlQueryModel()
ui.treeView = QtWidgets.QTreeView(ui.comboBox)
db.open()
num_model.setQuery('SELECT Number FROM Room', db)
cust_model.setQuery('SELECT Name, ID, Sex FROM Customer', db)
db.close()
while cust_model.canFetchMore():
cust_model.fetchMore()
ui.comboBox_2.setModel(num_model)
ui.comboBox.setModel(cust_model)
ui.comboBox.setView(ui.treeView)
ui.treeView.setColumnHidden(2, True)
icon4 = QtGui.QIcon()
icon4.addPixmap(QtGui.QPixmap(":/ctmr/48px-Emblem-person-blue.svg.png"))
icon5 = QtGui.QIcon()
icon5.addPixmap(QtGui.QPixmap(":/ctmr/48px-User_icon_3.svg.png"))
i = 0
while i < ui.comboBox.count():
#print(cust_model.index(i, 1).data())
if cust_model.index(i, 2).data() == 'Male':
ui.comboBox.setItemIcon(i, icon4)
else:
ui.comboBox.setItemIcon(i, icon5)
i += 1
def carregarTable(self):
db = QSqlDatabase().addDatabase('QSQLITE')
db.setDatabaseName('Litterarius.db')
conexao = db.connectionName()
if db.open():
query = QSqlQueryModel(self)
query.setQuery(
"SELECT"
" livros_id, titulo, editoras.editora,"
" isbn, qtde_estoque, vl_unitario, consignado"
" FROM livros"
" INNER JOIN editoras ON livros.editoras_fk = editoras.editoras_id"
)
model = QSqlTableModel(self, db)
model.setQuery(query.query())
model.select()
self.ui.tableView.setModel(model)
self.ui.tableView.show()
# carregar combobox de editoras
query2 = QSqlQueryModel(self)
query2.setQuery("select(editora) from editoras")
model2 = QSqlTableModel(self, db)
model2.setQuery(query2.query())
model2.select()
self.ui.cbEditora.setModel(model2)
db.close()
def __init__(self):
super().__init__()
self.setupUi(self)
# Setting the SqlQuery model for the tables
self.student_sql_query_model: QSqlQueryModel = QSqlQueryModel()
self.employee_sql_query_model: QSqlQueryModel = QSqlQueryModel()
self.tblStudents.setModel(self.student_sql_query_model)
self.tblEmployees.setModel(self.employee_sql_query_model)
def updateAllPage(self, *args):
if args == ([], [], []) or args == ():
self.query.exec(
"SELECT FILENAME FROM FileLibrary WHERE SUFFIX != ('{}')".
format(""))
#如果是目录，会被排除掉
model = QSqlQueryModel()
model.setQuery(self.query)
self.allFileTab.setModel(model)
else:
core.FileOperator.unionQuery(self.query, args[0], args[1], args[2])
model = QSqlQueryModel()
model.setQuery(self.query)
self.allFileTab.setModel(model)
def threadSizeInit(self, index = 0):
self.sizeMapper = QDataWidgetMapper(self)
self.sizeModel = QSqlQueryModel(self)
form = self.threadFormLbl.text()
threadClass = self.threadClassLbl.text()
sizeSelect = "SELECT size, pitch, major_dia, \
min_major_dia, max_minor_dia, min_minor_dia, \
max_pitch_dia, min_pitch_dia FROM internal_threads WHERE form \
= '{}' AND class = '{}'".format(form, threadClass)
self.sizeModel.setQuery(sizeSelect)
self.sizeMapper.setModel(self.sizeModel)
self.sizeMapper.addMapping(self.threadSizeLbl, 0, b'text')
self.sizeMapper.addMapping(self.threadTPILbl, 1, b'text')
self.sizeMapper.addMapping(self.threadMajorDiaLbl, 2, b'text')
self.sizeMapper.addMapping(self.minMajorDiaLbl, 3, b'text')
self.sizeMapper.addMapping(self.maxMinorDiaLbl, 4, b'text')
self.sizeMapper.addMapping(self.minMinorDiaLbl, 5, b'text')
self.sizeMapper.addMapping(self.maxPitchDiaLbl, 6, b'text')
self.sizeMapper.addMapping(self.minPitchDiaLbl, 7, b'text')
self.sizeMapper.toLast()
self.sizeLast = self.sizeMapper.currentIndex()
self.sizeMapper.setCurrentIndex(index)
self.drillSizeInit()
self.threadSizeCalc()
self.numPassesCalc()
self.threadHeightCalc()
def setTableView(self):
print('*** step2 SetTableView' )
# 声明查询模型
self.queryModel = QSqlQueryModel(self)
# 设置当前页
self.currentPage = 1;
# 得到总记录数
self.totalRecrodCount = self.getTotalRecordCount()
# 得到总页数
self.totalPage = self.getPageCount()
# 刷新状态
self.updateStatus()
# 设置总页数文本
self.setTotalPageLabel()
# 设置总记录数
self.setTotalRecordLabel()
# 记录查询
self.recordQuery(0)
# 设置模型
self.tableView.setModel(self.queryModel)
print('totalRecrodCount=' + str(self.totalRecrodCount) )
print('totalPage=' + str(self.totalPage) )
# 设置表格表头
self.queryModel.setHeaderData(0,Qt.Horizontal,"编号")
self.queryModel.setHeaderData(1,Qt.Horizontal,"姓名")
self.queryModel.setHeaderData(2,Qt.Horizontal,"性别")
self.queryModel.setHeaderData(3,Qt.Horizontal,"年龄")
self.queryModel.setHeaderData(4,Qt.Horizontal,"院系")
def parentTagListBetaUpdate(self):
typesLink = ["videos_tags_link", "videos_yt_tags_link"]
parents = ["tags_parent", "yt_tags_parent"]
sort = ["P.parent_tag", "ct"]
sortDir = ["ASC", "DESC"]
queryTemplate = f"""SELECT P.parent_tag, P.parent_tag || ' (' || IFNULL(ct, 0) || ')', ct
FROM (
SELECT parent_tag
FROM {parents[self.tagType]}
WHERE child_tag = (?)
GROUP BY 1
) P LEFT JOIN (
SELECT tag_name, count(*) as ct
FROM {typesLink[self.tagType]}
WHERE tag_name = (?)
GROUP BY 1
) C
ON P.parent_tag = C.tag_name
ORDER BY {sort[self.tagSortDropDown.currentIndex()]} {sortDir[self.tagSortDirDropDown.currentIndex()]}"""
query = QSqlQuery()
query.prepare(queryTemplate)
childTag = self.childTag
query.addBindValue(childTag)
query.addBindValue(childTag)
query.exec()
model = QSqlQueryModel()
model.setQuery(query)
self.parentTagListBeta.setModel(model)
self.parentTagListBeta.setModelColumn(1)
self.parentTagListBeta.show()
def setupSearchComboBoxByQuery(self, editor, query, modelcolumn=0):
model = QSqlQueryModel(self)
model.setQuery(query, self.dbm.db)
tv = QTableView()
editor.setView(tv)
tv.setHorizontalScrollBarPolicy(Qt.ScrollBarAsNeeded)
#tv.setSelectionMode(QAbstractItemView.MultiSelection)
tv.setSelectionMode(QAbstractItemView.SingleSelection)
tv.setSelectionBehavior(QAbstractItemView.SelectRows)
tv.setAutoScroll(False)
editor.setModel(model)
editor.setModelColumn(modelcolumn)
editor.setInsertPolicy(QComboBox.NoInsert)
tv.resizeColumnsToContents()
tv.resizeRowsToContents()
tv.verticalHeader().setVisible(False)
tv.horizontalHeader().setVisible(True)
# tv.setMinimumWidth(tv.horizontalHeader().length())
tv.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
# FIXME PyQt5 AutoCompletion
#editor.setAutoCompletion(True)
editor.setCurrentIndex(-1)
def select(_):
query = QSqlQueryModel()
query.setQuery(
"SELECT p.Name AS [Товар], COUNT(p.Id) AS [Количество ингредиентов] "
"FROM Pastries AS p INNER JOIN Doses AS d ON d.Pastry = p.Id "
"GROUP BY p.Name")
return query
def tagListUpdate(self):
types = ["tags", "yt_tags"]
typesLink = ["videos_tags_link", "videos_yt_tags_link"]
sort = ["T.tag_name", "ct"]
sortDir = ["ASC", "DESC"]
queryTemplate = f"""SELECT T.tag_name, T.tag_name || ' (' || IFNULL(ct, 0) || ')', ct
FROM {types[self.tagType]} T LEFT JOIN (
SELECT tag_name, count(*) as ct
FROM {typesLink[self.tagType]}
GROUP BY 1
) C
ON T.tag_name = C.tag_name
WHERE T.tag_name LIKE (?)
ORDER BY {sort[self.tagSortDropDown.currentIndex()]} {sortDir[self.tagSortDirDropDown.currentIndex()]}"""
searchText = self.tagSearchBar.text()
query = QSqlQuery()
query.prepare(queryTemplate)
query.addBindValue(f"%{searchText}%")
query.exec()
model = QSqlQueryModel()
model.setQuery(query)
self.tagList.setModel(model)
self.tagList.setModelColumn(1)
self.tagList.show()
self.tagList.selectionModel().selectionChanged.connect(
self.tagListSelectionChanged)
def __init__(self):
super().__init__()
self.setupUi(self)
self.model = QSqlQueryModel()
self.connectToSql()
# Обработка действий по меню
# нажата кнопка меню "Справочники -> Подразделения"
self.unit_action.triggered.connect(self.unit_dialog)
# нажата кнопка меню "Справочники -> Сотрудники"
self.user_action.triggered.connect(self.user_dialog)
# нажата кнопка меню "Справочники -> Сервисные службы"
self.services_action.triggered.connect(self.services_dialog)
# Работа с основной формой
# Нажата кнопка "Добавить строку" на форме
self.btProblemAdd.clicked.connect(self.bt_add)
# Нажата кнопка "Обновить строку" на форме
self.btProblemUpdate.clicked.connect(self.bt_upd)
# Нажата кнопка "Удалить строку" на форме
self.btProblemDel.clicked.connect(self.bt_del)
# Найти заявку
self.btProblemFind.clicked.connect(self.findText)
# Сбросить фильтр
self.btProblemReset.clicked.connect(self.reset)
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 submission_models(self, db, table_name):
if db.open():
model = QSqlQueryModel()
model.setQuery(
"SELECT run_id,verdict,language,problem_number,time_stamp FROM my_submissions ORDER BY local_run_id DESC"
)
return model
def _createModel():
oi_get_query = QSqlQuery()
oi_get_query.prepare("""select symbol
,desc
,substr(oi_date, 1,4) || '-' || substr(oi_date, 5,2) || '-' || substr(oi_date,7,2)
,globex_volume
,volume
,open_interest
,change
from oi_reports
where symbol = "EUR"
order by cast(oi_date as int) desc
;
""")
#oi_get_query.addBindValue(symbol)
oi_get_query.exec()
queryModel = QSqlQueryModel()
queryModel.setQuery(oi_get_query)
headers = ['Symbol', 'Description', 'Date', 'Globex', 'Volume', 'Open Interest', 'Change']
for columnIndex, header in enumerate(headers):
queryModel.setHeaderData(columnIndex, Qt.Horizontal, header)
#queryModel.setHeaderData(0, Qt.Horizontal, 'Symbol')
#queryModel.setHeaderData(1, Qt.Horizontal, 'Description')
#queryModel.setHeaderData(2, Qt.Horizontal, 'Date')
#queryModel.setHeaderData(3, Qt.Horizontal, 'Globex')
#queryModel.setHeaderData(4, Qt.Horizontal, 'Volume')
#queryModel.setHeaderData(5, Qt.Horizontal, 'Open Interest')
#queryModel.setHeaderData(6, Qt.Horizontal, 'Change')
return queryModel
def setupMilitaryCombo(self):
model = QSqlQueryModel(self)
model.setQuery(
"SELECT DISTINCT militaernummer as milnum FROM film WHERE militaernummer IS NOT NULL UNION ALL SELECT DISTINCT militaernummer_alt as milnum FROM film WHERE militaernummer_alt IS NOT NULL ORDER BY milnum",
self.dbm.db)
tv = QTableView()
self.uiMilitaryNumberCombo.setView(tv)
tv.setHorizontalScrollBarPolicy(Qt.ScrollBarAsNeeded)
tv.setSelectionMode(QAbstractItemView.SingleSelection)
tv.setSelectionBehavior(QAbstractItemView.SelectRows)
tv.setAutoScroll(False)
self.uiMilitaryNumberCombo.setModel(model)
self.uiMilitaryNumberCombo.setModelColumn(0)
self.uiMilitaryNumberCombo.setInsertPolicy(QComboBox.NoInsert)
tv.resizeColumnsToContents()
tv.resizeRowsToContents()
tv.verticalHeader().setVisible(False)
tv.horizontalHeader().setVisible(True)
#tv.setMinimumWidth(tv.horizontalHeader().length())
tv.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
def new_addservice_dialog(self, window, db, res_index, thrd):
#Setup UI
ui = Ui_AddService()
new_addservice = QDialog()
ui.setupUi(new_addservice)
#Setup ComboBox view
db.open()
srv_model = QSqlQueryModel()
ui.treeView = QtWidgets.QTreeView(ui.comboBox)
srv_model.setQuery('SELECT Name, ID FROM Service', db)
db.close()
while srv_model.canFetchMore():
srv_model.fetchMore()
ui.comboBox.setModel(srv_model)
ui.comboBox.setView(ui.treeView)
db.close()
ui.pushButton.clicked.connect(lambda: thrd.tryStart(TableWorker(add_DB(ui, new_addservice, db,
"RoomService",
[res_index.siblingAtColumn(0).data(),res_index.siblingAtColumn(1).data(),res_index.siblingAtColumn(2).data(),ui.treeView.currentIndex().siblingAtColumn(1).data()],
"?, ?, ?, ?",
[]))))
ui.pushButton_2.clicked.connect(lambda: thrd.tryStart(TableWorker(del_DB(ui, new_addservice, db,
"RoomService", "ResID = ? AND SrvID = ?", [res_index.siblingAtColumn(0).data(), ui.treeView.currentIndex().siblingAtColumn(1).data()],
[]))))
new_addservice.setWindowTitle('Add or remove a Service')
new_addservice.exec()
def __init__(self, tag_number):
super().__init__()
print('processing query...')
qry = QSqlQuery(db)
query = 'SELECT name, ename, startno, starttime FROM name WHERE ecard = %i OR ecard2 = %i' % (
tag_number, tag_number)
qry.prepare(query)
qry.exec()
model = QSqlQueryModel()
model.setQuery(qry)
print(model.rowCount())
mapper = QDataWidgetMapper()
form = QFormLayout()
layout = QVBoxLayout()
first_name = QLineEdit()
start_number = QLineEdit()
form.addRow(QLabel("Startnummer"), start_number)
form.addRow(QLabel("Fornavn"), first_name)
mapper.setModel(model)
mapper.addMapping(first_name, 0)
mapper.addMapping(start_number, 2)
mapper.toFirst()
layout.addLayout(form)
widget = QWidget()
widget.setLayout(layout)
self.setCentralWidget(widget)
#controls = QHBoxLayout()
'''
def inventory_used(self):
# 通过进度条表示库存余量
currentQryModel = QSqlQueryModel(self)
currentQryModel.setQuery("select RFID from 工件信息表")
allnum = currentQryModel.rowCount()
progress = int((100 * allnum / 156) + 0.5)
self.ui.progressBar.setValue(progress)
def setTableView(self):
print('*** step2 SetTableView')
# 宣告查詢模型
self.queryModel = QSqlQueryModel(self)
# 設定目前頁
self.currentPage = 1
# 取得總記錄數
self.totalRecrodCount = self.getTotalRecordCount()
# 取得總頁數
self.totalPage = self.getPageCount()
# 刷新狀態
self.updateStatus()
# 設定總頁數標籤
self.setTotalPageLabel()
# 設定總記錄數標籤
self.setTotalRecordLabel()
# 記錄查詢
self.recordQuery(0)
# 設定模型
self.tableView.setModel(self.queryModel)
print('totalRecrodCount=' + str(self.totalRecrodCount))
print('totalPage=' + str(self.totalPage))
# 設定表格表頭
self.queryModel.setHeaderData(0, Qt.Horizontal, "編號")
self.queryModel.setHeaderData(1, Qt.Horizontal, "姓名")
self.queryModel.setHeaderData(2, Qt.Horizontal, "性別")
self.queryModel.setHeaderData(3, Qt.Horizontal, "年齡")
self.queryModel.setHeaderData(4, Qt.Horizontal, "院系")
def __openTable(self):
self.qryModel = QSqlQueryModel(self)
self.qryModel.setQuery(
'''SELECT empNo, Name, Gender, Birthday, Province, Department, Salary FROM employee ORDER BY empNo'''
)
if self.qryModel.lastError().isValid():
QMessageBox.critical(
self, "错误",
"数据表查询错误,错误信息\n" + self.qryModel.lastError().text())
return
self.__getFieldNames()
self.qryModel.setHeaderData(0, Qt.Horizontal, "工号")
self.qryModel.setHeaderData(1, Qt.Horizontal, "姓名")
self.qryModel.setHeaderData(2, Qt.Horizontal, "性别")
self.qryModel.setHeaderData(3, Qt.Horizontal, "出生日期")
self.qryModel.setHeaderData(4, Qt.Horizontal, "省份")
self.qryModel.setHeaderData(5, Qt.Horizontal, "部门")
self.qryModel.setHeaderData(6, Qt.Horizontal, "工资")
self.selModel = QItemSelectionModel(self.qryModel)
self.selModel.currentRowChanged.connect(self.do_currentRowChanged)
self.ui.tableView.setModel(self.qryModel)
self.ui.tableView.setSelectionModel(self.selModel)
self.ui.actOpenDB.setEnabled(False)
self.ui.actRecInsert.setEnabled(True)
self.ui.actRecDelete.setEnabled(True)
self.ui.actRecEdit.setEnabled(True)
self.ui.actScan.setEnabled(True)
self.ui.actTestSQL.setEnabled(True)
def show_demo(self):
def test(x):
print(len(x))
# Создаем модель
sqm = QSqlQueryModel(parent=self)
sqm.setQuery(
'SELECT id, lastName, firstName, patrName, birthDate, sex, notes '
'FROM Client WHERE deleted = 0 AND deathDate IS NULL '
'LIMIT 20')
# Задаем заголовки для столбцов модели
sqm.setHeaderData(1, Qt.Horizontal, 'Фамилия')
sqm.setHeaderData(2, Qt.Horizontal, 'Имя')
sqm.setHeaderData(3, Qt.Horizontal, 'Отчество')
sqm.setHeaderData(4, Qt.Horizontal, 'Дата рождения')
sqm.setHeaderData(5, Qt.Horizontal, 'Пол')
sqm.setHeaderData(6, Qt.Horizontal, 'Примечание')
# Задаем для таблицы только что созданную модель
self.clients_table.setModel(sqm)
self.clients_table.hideColumn(0)
self.clients_table.resizeColumnsToContents()
self.clients_table.horizontalHeader().setStretchLastSection(True)
self.clients_table.horizontalHeader().setHighlightSections(False)
self.clients_table.verticalHeader().hide()
self.clients_table.clearSelection()
self.lbl_suitable_customers_count.setText(str(sqm.rowCount()))
self.lbl_selected_customers_count.setText('0')
self.clients_table.selectionModel().selectionChanged.connect(
self.show_selected_customers_count)
def obtain_recipe(self):
"""
PURPOSE
-----
To obtain all information pertaining to the recipe clicked on by user, i.e. title, author, cuisine, etc. and
create a model to store that information
OUTPUT
-----
The model with the stored information
"""
recipe_info_cmd = \
f"""
SELECT R.`Recipe Title`, R.Author, R.Cuisine, R.Ingredients, R.`Recipe Procedure`, R.Tags, R.'Website URL'
FROM recipes R
WHERE R.`Recipe Title` = "{self.recipe_title}"
"""
# Query to get title, author, ingredients, and recipe procedure from desired recipe title
recipe_query = QSqlQuery(self.conn) # Same process as the title_query
recipe_query.prepare(recipe_info_cmd)
recipe_query.exec_()
recipe_model = QSqlQueryModel()
recipe_model.setQuery(recipe_query)
return recipe_model
def streets_popup(self):
if 'db1' in dir(self):
self.open_sql_for_table_view()
sql_txt = """
SELECT R.name AS 'Road Name', R.ID AS 'Road ID', I1.X AS X1, I1.Y AS Y1, I2.X AS X2, I2.Y AS Y2
FROM Roads as R, Intersections as I1, Intersections as I2
WHERE R.startNodeID = I1.ID
AND R.endNodeID = I2.ID
"""
query = QSqlQuery()
query.exec(sql_txt)
self.model = QSqlQueryModel()
self.model.setQuery(query)
self.tableview.setModel(self.model)
self.tableview.show()
self.db.close()
return
else:
msg = QMessageBox()
msg.setWindowTitle("Database Error")
txt = "There is no connected database"
msg.setText(txt)
msg.setIcon(QMessageBox.Critical)
msg.setStandardButtons(QMessageBox.Ok)
msg.exec_()
return
def select(max_price):
query = QSqlQueryModel()
query.setQuery("SELECT * "
"FROM Pastries "
"WHERE Price <= %s" % max_price)
LoaderModelsService.processingModelPastries(query, False)
return query
def createCentralWidget(self):
self.model = QSqlQueryModel(self)
self.model.setQuery(
"SELECT id, name, job, email,phone,age FROM employee")
self.model.setHeaderData(0, Qt.Horizontal, "ID")
self.model.setHeaderData(1, Qt.Horizontal, "Name")
self.model.setHeaderData(2, Qt.Horizontal, "Job")
self.model.setHeaderData(3, Qt.Horizontal, "Email")
self.model.setHeaderData(4, Qt.Horizontal, "Phone")
self.model.setHeaderData(5, Qt.Horizontal, "Age")
self.view = QTableView()
self.view.setModel(self.model)
# self.view.setColumnCount(6)
# self.view.setHorizontalHeaderLabels(["ID", "Name", "Job", "Email","Phone","Age"])
# employeesData = self.fetchEmployees()
# while employeesData.next():
# rows = self.view.rowCount()
# self.view.setRowCount(rows+1)
# self.view.setItem(rows,0,QTableWidgetItem(str(employeesData.value(0))))
# self.view.setItem(rows,1,QTableWidgetItem(employeesData.value(1)))
# self.view.setItem(rows,2,QTableWidgetItem(employeesData.value(2)))
# self.view.setItem(rows,3,QTableWidgetItem(employeesData.value(3)))
# self.view.setItem(rows,4,QTableWidgetItem(employeesData.value(4)))
# self.view.setItem(rows,5,QTableWidgetItem(str(employeesData.value(5))))
self.view.resizeColumnsToContents()
self.setCentralWidget(self.view)
def parentTagListAlphaUpdate(self):
types = ["tags", "yt_tags"]
typesLink = ["videos_tags_link", "videos_yt_tags_link"]
sort = ["T.tag_name", "ct"]
sortDir = ["ASC", "DESC"]
queryTemplate = f"""SELECT T.tag_name, T.tag_name || ' (' || IFNULL(ct, 0) || ')', ct
FROM {types[self.tagType]} T LEFT JOIN (
SELECT tag_name, count(*) as ct
FROM {typesLink[self.tagType]}
GROUP BY 1
) C
ON T.tag_name = C.tag_name
WHERE T.tag_name LIKE (?)
ORDER BY {sort[self.tagSortDropDown.currentIndex()]} {sortDir[self.tagSortDirDropDown.currentIndex()]}"""
searchText = self.parentTagSearchBar.text()
query = QSqlQuery()
query.prepare(queryTemplate)
query.addBindValue(f"%{searchText}%")
query.exec()
model = QSqlQueryModel()
model.setQuery(query)
pmodel = FilterProxyModel()
pmodel.setSourceModel(model)
filteredSet = self.parentTags
filteredSet.add(self.childTag)
pmodel.setFilteredSet(filteredSet)
self.parentTagListAlpha.setModel(pmodel)
self.parentTagListAlpha.setModelColumn(1)
self.parentTagListAlpha.show()
def remove_rule(self, rule):
query = QSqlQueryModel()
query.setQuery("delete from rules "\
"where id={}"\
.format(rule.idx))
if query.lastError().isValid():
print(query.lastError().text(), file=sys.stderr)
def __init__(self, queries, headers, parent=None):
super(DatabaseSearchWidget, self).__init__(parent)
self.setupUi(self)
# Setup database connection
self.database = pyqt_database_connection()
self.database.open()
self.databaseModel = QSqlQueryModel(self)
self.dataView_search_results.setModel(self.databaseModel)
# Store bound queries for usage
self.queries = queries
self.combo_search_options.addItems(sorted(queries.keys()))
# Connect signals
self.dataView_search_results.selectionModel().selectionChanged.connect(
self.slot_emit_selection_changed)
self.dataView_search_results.doubleClicked.connect(
self.slot_emit_add_item)
self.pushButton_search.clicked.connect(self.update_query)
self.combo_search_options.currentIndexChanged.connect(
self.update_query)
# Setup header
for element in headers:
self.databaseModel.setHeaderData(0, QtCore.Qt.Horizontal, element)
self.installEventFilter(self)
def __init__(self, ctx, par, *args, **kwargs):
super(DBViewer, self).__init__(*args, **kwargs)
self.setAttribute(Qt.WA_DeleteOnClose)
self.setWindowFlags(self.windowFlags() | Qt.WindowSystemMenuHint
| Qt.WindowMinMaxButtonsHint)
self.ctx = ctx
self.par = par
self.layout = QVBoxLayout()
self.query_model = QSqlQueryModel()
self.toolbar = QToolBar()
self.table_view = QTableView()
self.table_view.setSelectionBehavior(QAbstractItemView.SelectRows)
self.export_excel = QPushButton(
self.ctx.export_icon,
'Export to Excel',
)
self.refresh_btn = QPushButton("Refresh")
self.close_btn = QPushButton("Close")
self.delete_rows_btn = QPushButton("Delete Selected Row(s)")
self.delete_rows_btn.setEnabled(False)
self.initModel()
self.initUI()
self.sigConnect()
