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 executeQuery(self):
"""
Public slot to execute the entered query.
"""
model = QSqlQueryModel(self.table)
model.setQuery(QSqlQuery(self.sqlEdit.toPlainText(), self.connections.currentDatabase()))
self.table.setModel(model)
if model.lastError().type() != QSqlError.NoError:
self.statusMessage.emit(model.lastError().text())
elif model.query().isSelect():
self.statusMessage.emit(self.tr("Query OK."))
else:
self.statusMessage.emit(
self.tr("Query OK, number of affected rows: {0}").format(model.query().numRowsAffected())
)
self.table.resizeColumnsToContents()
self.updateActions()
def carregarComboBox(self):
db = QSqlDatabase().addDatabase('QSQLITE')
db.setDatabaseName('Litterarius.db')
conexao = db.connectionName()
if db.open():
query = QSqlQueryModel(self)
query.setQuery("SELECT" " fornecedor" " FROM fornecedores")
model = QSqlTableModel(self, db)
model.setQuery(query.query())
model.select()
self.ui.cbxFornecedores.setModel(model)
self.ui.cbxFornecedores.show()
db.close()
def executeQuery(self):
"""
Public slot to execute the entered query.
"""
model = QSqlQueryModel(self.table)
model.setQuery(
QSqlQuery(self.sqlEdit.toPlainText(),
self.connections.currentDatabase()))
self.table.setModel(model)
if model.lastError().type() != QSqlError.NoError:
self.statusMessage.emit(model.lastError().text())
elif model.query().isSelect():
self.statusMessage.emit(self.tr("Query OK."))
else:
self.statusMessage.emit(
self.tr("Query OK, number of affected rows: {0}").format(
model.query().numRowsAffected()))
self.table.resizeColumnsToContents()
self.updateActions()
def carregarListView(self):
db = QSqlDatabase().addDatabase('QSQLITE')
db.setDatabaseName('Litterarius.db')
if db.open():
self.model = QSqlTableModel(self, db)
# self.model.setTable ("autores")
# self.model.select ()
query = QSqlQueryModel(self)
query.setQuery("select(autor) from autores")
self.model.setQuery(query.query())
self.ui.lvAutor.setModel(self.model)
self.ui.lvAutor.show()
db.close()
def carregarComboBox(self):
db = QSqlDatabase ().addDatabase ('QSQLITE')
db.setDatabaseName ('Litterarius.db')
if db.open ():
query = QSqlQueryModel (self)
query.setQuery ("SELECT parcelas_id FROM recebimentos WHERE vendas_id = %s"
% (self.ui.txtVendaId.text()))
model = QSqlTableModel (self, db)
model.setQuery (query.query ())
model.select ()
self.ui.cbParcela.setModel (model)
self.ui.cbParcela.show ()
db.close ()
QSqlDatabase ().removeDatabase ('Litterarius.db')
def carregarTable(self):
db = QSqlDatabase ().addDatabase ('QSQLITE')
db.setDatabaseName ('Litterarius.db')
if db.open ():
query = QSqlQueryModel (self)
query.setQuery ("SELECT vendas_id as venda, dataVenda as data,"
" precoVenda as preco"
" FROM vendas")
model = QSqlTableModel (self, db)
model.setQuery (query.query ())
model.select ()
self.ui.tableView.setModel (model)
self.ui.tableView.show ()
db.close ()
QSqlDatabase ().removeDatabase ('Litterarius.db')
def carregarListView(self):
db = QSqlDatabase().addDatabase('QSQLITE')
db.setDatabaseName('Litterarius.db')
if db.open():
self.model = QSqlTableModel(self, db)
# self.model.setTable ("autores")
# self.model.select ()
query = QSqlQueryModel(self)
query.setQuery("select(genero) from generos")
self.model.setQuery(query.query())
self.ui.lvGenero.setModel(self.model)
self.ui.lvGenero.show()
self.ui.btnIncluir.clicked.connect(self.clickedIncluir)
db.close()
def carregarTable(self):
db = QSqlDatabase().addDatabase('QSQLITE')
db.setDatabaseName('Litterarius.db')
conexao = db.connectionName()
if db.open():
query = QSqlQueryModel(self)
query.setQuery(
"SELECT"
" titulo, editoras.editora,"
" 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.tvLivros.setModel(model)
self.ui.tvLivros.show()
db.close()
def carregarTableByGenero(self):
db = QSqlDatabase().addDatabase('QSQLITE')
db.setDatabaseName('Litterarius.db')
conexao = db.connectionName()
if db.open():
query = QSqlQueryModel(self)
query.setQuery(
"SELECT"
" titulo, editoras.editora,"
" qtde_estoque, vl_unitario, consignado"
" FROM livros"
" INNER JOIN editoras ON livros.editoras_fk = editoras.editoras_id"
" WHERE livros_id =("
" SELECT livros_id FROM livros_generos WHERE generos_id=("
" SELECT generos_id FROM generos WHERE genero LIKE '%s'))"
% (self.ui.txtPesquisar.text() + "%"))
model = QSqlTableModel(self, db)
model.setQuery(query.query())
model.select()
self.ui.tvLivros.setModel(model)
self.ui.tvLivros.show()
db.close()
class ProjectAlleles(FilterableTable):
"""a widget to display all alleles of one project,
with their most important data
"""
changed_allele = pyqtSignal(str, int, str)
change_view = pyqtSignal(int)
def __init__(self, log, mydb):
super().__init__(log, mydb, add_color_proxy = (4,5))
self.proxy.setFilterKeyColumn(2)
self.filter_cb.setCurrentIndex(2)
self.header_lbl.setText("Alleles:")
self.table.verticalHeader().hide()
self.table.customContextMenuRequested.connect(self.open_menu)
self.project = ""
self.filter(self.project)
def create_model(self):
"""creates the table model
"""
self.log.debug("Creating the table model...")
self.model = QSqlQueryModel()
q = QSqlQuery()
query = """SELECT project_name, project_nr,
(sample_id_int || ' #' || allele_nr || ' (' || gene || ')'),
local_name,
allele_status, lab_status,
sample_id_int, allele_nr
FROM alleles
order by project_nr
"""
q.exec_(query)
self.check_error(q)
self.model.setQuery(q)
self.model.setHeaderData(1, Qt.Horizontal, "Nr")
self.model.setHeaderData(2, Qt.Horizontal, "Target Allele")
self.model.setHeaderData(3, Qt.Horizontal, "Allele Name")
self.model.setHeaderData(4, Qt.Horizontal, "Allele Status")
self.model.setHeaderData(5, Qt.Horizontal, "Lab Status")
self.log.debug("\t=> Done!")
def filter(self, project):
self.project = project
self.proxy.layoutAboutToBeChanged.emit()
self.proxy.setFilterKeyColumn(0)
self.proxy.setFilterFixedString(project)
self.proxy.layoutChanged.emit()
for col in [0, 6, 7]:
self.table.hideColumn(col)
@pyqtSlot()
def refresh(self):
"""refreshes the table's content
"""
self.log.debug("\tRefreshing ProjectView's allele list...")
self.model.setQuery(self.model.query().lastQuery())
@pyqtSlot(QPoint)
def open_menu(self, pos):
"""provides a context menu
"""
try:
menu = QMenu()
open_allele_act = menu.addAction("Open Allele View")
action = menu.exec_(self.table.mapToGlobal(pos))
if action:
row = self.table.indexAt(pos).row()
sample = self.proxy.data(self.proxy.index(row, 6))
allele_nr = int(self.proxy.data(self.proxy.index(row, 7)))
if action == open_allele_act:
self.changed_allele.emit(sample, allele_nr, self.project)
self.change_view.emit(4)
self.log.debug("ProjectAlleles emitted changed_allele to {} #{} ({}) & change_view to AlleleView".format(sample, allele_nr, self.project))
except Exception as E:
self.log.exception(E)
class QmyMainWindow(QtWidgets.QMainWindow):
def __init__(self, parent=None):
super().__init__(parent)
self.ui = Ui_MainWindow()
self.ui.setupUi(self)
self.setCentralWidget(self.ui.tableView)
self.ui.tableView.setAlternatingRowColors(True)
self.ui.tableView.verticalHeader().setDefaultSectionSize(22)
self.ui.tableView.horizontalHeader().setDefaultSectionSize(60)
def __getFieldNames(self):
emptyRec = self.qryModel.record()
self.fldNum = {}
for i in range(emptyRec.count()):
fieldName = emptyRec.fieldName(i)
self.fldNum.setdefault(fieldName)
self.fldNum[fieldName] = i
print(self.fldNum)
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 __updateRecord(self, recNo):
curRec = self.qryModel.record(recNo)
empNo = curRec.value("EmpNo")
query = QSqlQuery(self.DB)
query.prepare("SELECT * FROM employee WHERE EmpNo = :ID")
query.bindValue(":ID", empNo)
query.exec()
query.first()
if (not query.isValid()):
return
curRec = query.record()
dlgData = QmyDialogData(self)
dlgData.setUpdateRecord(curRec)
ret = dlgData.exec()
if (ret != QDialog.Accepted):
return
recData = dlgData.getRecordData()
query.prepare('''UPDATE employee SET Name=:Name, Gender=:Gender,
Birthday=:Birthday, Province=:Province,
Department=:Department, Salary=:Salary,
Memo=:Memo, Photo=:Photo WHERE EmpNo = :ID''')
query.bindValue(":Name", recData.value("Name"))
query.bindValue(":Gender", recData.value("Gender"))
query.bindValue(":Birthday", recData.value("Birthday"))
query.bindValue(":Province", recData.value("Province"))
query.bindValue(":Department", recData.value("Department"))
query.bindValue(":Salary", recData.value("Salary"))
query.bindValue(":Memo", recData.value("Memo"))
query.bindValue(":Photo", recData.value("Photo"))
query.bindValue(":ID", empNo)
if (not query.exec()):
QMessageBox.critical(self, "错误",
"记录更新错误\n" + query.lastError().text())
else:
self.qryModel.query().exec()
@pyqtSlot()
def on_actOpenDB_triggered(self):
dbFilename, flt = QFileDialog.getOpenFileName(
self, "选择数据库文件", "", "SQL Lite数据库(*.db *.db3)")
if (dbFilename == ''):
return
self.DB = QSqlDatabase.addDatabase("QSQLITE")
self.DB.setDatabaseName(dbFilename)
if self.DB.open():
self.__openTable()
else:
QMessageBox.warning(self, "错误", "打开数据库失败")
@pyqtSlot()
def on_actRecInsert_triggered(self):
query = QSqlQuery(self.DB)
query.exec("select * from employee where EmpNo = -1")
curRec = query.record()
curRec.setValue("EmpNo", self.qryModel.rowCount() + 3000)
dlgData = QmyDialogData(self)
dlgData.setInsertRecord(curRec)
ret = dlgData.exec()
if (ret != QDialog.Accepted):
return
recData = dlgData.getRecordData()
query.prepare('''INSERT INTO employee (EmpNo,Name,Gender,Birthday,
Province,Department,Salary,Memo,Photo)
VALUES(:EmpNo,:Name, :Gender,:Birthday,:Province,
:Department,:Salary,:Memo,:Photo)''')
query.bindValue(":EmpNo", recData.value("EmpNo"))
query.bindValue(":Name", recData.value("Name"))
query.bindValue(":Gender", recData.value("Gender"))
query.bindValue(":Birthday", recData.value("Birthday"))
query.bindValue(":Province", recData.value("Province"))
query.bindValue(":Department", recData.value("Department"))
query.bindValue(":Salary", recData.value("Salary"))
query.bindValue(":Memo", recData.value("Memo"))
query.bindValue(":Photo", recData.value("Photo"))
res = query.exec()
if (res == False):
QMessageBox.critical(self, "错误",
"插入记录错误\n" + query.lastError().text())
else:
sqlStr = self.qryModel.query().executedQuery()
self.qryModel.setQuery(sqlStr)
@pyqtSlot()
def on_actRecDelete_triggered(self):
curRecNo = self.selModel.currentIndex().row()
curRec = self.qryModel.record(curRecNo)
if (curRec.isEmpty()):
return
empNo = curRec.value("EmpNo")
query = QSqlQuery(self.DB)
query.prepare("DELETE FROM employee WHERE EmpNo = :ID")
query.bindValue(":ID", empNo)
if (query.exec() == False):
QMessageBox.critical(self, "错误",
"删除记录出现错误\n" + query.lastError().text())
else:
sqlStr = self.qryModel.query().executedQuery()
self.qryModel.setQuery(sqlStr)
@pyqtSlot()
def on_actRecEdit_triggered(self):
curRecNo = self.selModel.currentIndex().row()
self.__updateRecord(curRecNo)
def on_tableView_doubleClicked(self, index):
curRecNo = index.row()
self.__updateRecord(curRecNo)
@pyqtSlot()
def on_actScan_triggered(self):
qryEmpList = QSqlQuery(self.DB)
qryEmpList.exec("SELECT empNo,Salary FROM employee ORDER BY empNo")
qryUpdate = QSqlQuery(self.DB)
qryUpdate.prepare(
'''UPDATE employee SET Salary=:Salary WHERE EmpNo = :ID''')
qryEmpList.first()
while (qryEmpList.isValid()):
empID = qryEmpList.value("empNo")
salary = qryEmpList.value("Salary")
salary = salary + 500
qryUpdate.bindValue(":ID", empID)
qryUpdate.bindValue(":Salary", salary)
qryUpdate.exec()
if not qryEmpList.next():
break
self.qryModel.query().exec()
QMessageBox.information(self, "提示", "涨工资计算完毕")
@pyqtSlot()
def on_actTestSQL_triggered(self):
query = QSqlQuery(self.DB)
query.exec('''UPDATE employee SET Salary=500+Salary''')
sqlStr = self.qryModel.query().executedQuery()
self.qryModel.setQuery(sqlStr)
print("SQL OK")
def do_currentRowChanged(self, current, previous):
if (current.isValid() == False):
return
curRec = self.qryModel.record(current.row())
empNo = curRec.value("EmpNo")
self.ui.statusBar.showMessage("当前记录：工号 = %d" % empNo)
class FileToSQL(QMainWindow):
def __init__(self):
super(FileToSQL, self).__init__()
uic.loadUi('pot.ui', self)
self.setWindowTitle('Hugh Chungus')
self.load_param()
self.thread = File2SQLThread(self.param)
self.progress_bar = self.findChild(QProgressBar, 'progressBar')
self.sql_table = self.findChild(PotTableRightClick, 'sql_table')
self.sql_table.open_location_signal.connect(self.open_in_explorer)
self.sql_table.doubleClicked.connect(self.playlist)
self.sql_table.clicked.connect(self.launched_notif)
self.sql_table.setSelectionBehavior(QTableView.SelectRows)
self.queryModel = QSqlQueryModel()
self.sql_table.setModel(self.queryModel)
self.log_textedit = self.findChild(QTextEdit, 'log_textedit')
self.random_check = self.findChild(QCheckBox, 'random_check')
self.sql_line = QLineEdit(self)
self.query_button = self.findChild(QPushButton, 'query_button')
self.query_button.clicked.connect(self.query)
self.playlist_button = self.findChild(QPushButton, 'playlist_button')
self.playlist_button.clicked.connect(self.playlist)
self.tasksMenu = self.menuBar().addMenu('&Tasks')
self.ins_menu_action('Set Path', self.set_path, self.tasksMenu)
self.ins_menu_action('Scan Path', self.scan_db, self.tasksMenu)
self.combobox = self.findChild(QComboBox, 'comboBox')
self.combobox.addItems([
'',
'ORDER BY ctime DESC',
# 'SELECT DISTINCT a.path AS path1, b.path AS path2, a.size FROM dedup AS a, dedup as b WHERE a.size == b.size AND a.path != b.path;',
'SELECT DISTINCT a.path AS path1, b.path AS path2, a.size FROM dedup AS a JOIN dedup as b ON a.size == b.size AND a.path != b.path',
])
self.combobox.setLineEdit(self.sql_line)
self.column_combobox = self.findChild(QComboBox, 'column_combobox')
self.tablename_combobox = self.findChild(QComboBox,
'tablename_comboBox')
self.final_query_lineedit = self.findChild(QLineEdit,
'final_query_lineEdit')
self.tablename_combobox.addItems([
'media_path',
'dedup',
'path_tags',
])
self.tablename_combobox.setCurrentIndex(0)
#textChanged(str)
self.tablename_combobox.editTextChanged.connect(self.guess_query)
self.column_combobox.editTextChanged.connect(self.guess_query)
self.tablename_combobox.currentIndexChanged.connect(
self.update_column_selection)
self.sql_line.textChanged.connect(self.guess_query)
self.show()
self.refresh_view()
self.thread.tableview_refresh.connect(self.refresh_view)
self.thread.progressbar_update.connect(self.progress_bar_update)
self.thread.log_update.connect(self.log_update)
self.update_column_selection()
self.guess_query()
def update_column_selection(self, index=0):
self.column_combobox.clear()
table = self.tablename_combobox.currentText()
if table == 'media_path':
self.column_combobox.addItems([
'name, path',
'name',
'path',
'ctime',
])
elif table == 'dedup':
self.column_combobox.addItems([
'path',
'size',
'key1',
'key2',
])
elif table == 'path_tags':
self.column_combobox.addItems([
'path',
'class',
'value',
])
self.guess_query()
def log_update(self, text):
self.log_textedit.insertPlainText(text + '\n')
def open_in_explorer(self, index):
#print(r'explorer /select,"{}"'.format(self.get_data(index, 1)))
subprocess.Popen(r'explorer /select,"{}"'.format(
self.get_data(index, 1)))
def launched_notif(self):
self.progress_bar_update(0)
def refresh_view(self, query=None):
if query:
self.set_query(query)
else:
self.set_query(self.thread.SELECT_MEDIA_PATH_SQL)
self.resize_column()
def progress_bar_update(self, i):
self.progress_bar.setValue(int(i))
def resize_column(self):
self.sql_table.setColumnWidth(
self.thread.MEDIA_PATH_SQL_COL_NUM['name'], self.sql_table.width())
def resizeEvent(self, event):
self.resize_column()
def set_query(self, query):
self.queryModel.setQuery(query, self.thread.database())
def ins_menu_action(self, name, func, menu):
action = QAction(name, self)
action.triggered.connect(func)
menu.addAction(action)
def set_path(self):
self.gc_placeholder = VertTextDiag(self.param)
def scan_db(self):
self.thread.start()
def query(self):
self.refresh_view(self.guess_query())
def guess_query(self, *args): #self.final_query_lineedit
string = ''
after_where = self.sql_line.text()
b4_where = self.tablename_combobox.currentText()
columns = self.column_combobox.currentText()
if columns == '':
columns = '*'
#logic of guess query: if b4where == '', if after where doesnt start by SELECT then return nothing; else retunr after where
#if len(b4_where) == 0:
if after_where.find("SELECT") == 0:
string = after_where
elif len(
b4_where) > 0: #then string starts with SELECT * FROM b4_where
string = "SELECT {} FROM {}".format(columns, b4_where)
if len(after_where) > 0:
if after_where.find('ORDER BY') != -1 or after_where.find(
'WHERE') != -1:
string += ' ' + after_where
else:
string += ' WHERE name LIKE "%{}%";'.format(after_where)
if string[-1] != ';':
string += ';'
self.final_query_lineedit.setText(string)
return string
if len(string) == 0:
return string
if not string.find('ORDER BY') == -1:
return 'SELECT * FROM media_path ' + string
elif string.find("SELECT") == -1:
return 'SELECT * FROM media_path WHERE name LIKE "%{}%"'.format(
string)
return string
def keyPressEvent(self, e):
if (e.key() == QtCore.Qt.Key_Return or e.key() == QtCore.Qt.Key_Enter):
self.query()
def playlist(self):
play = None
if self.get_current_row() > -1:
play = self.get_current_data(
self.thread.MEDIA_PATH_SQL_COL_NUM['path'])
make_potplayer_playlist(self.tableview_to_list(), play)
subprocess.Popen([
r'C:\Program Files\DAUM\PotPlayer\potplayermini64.exe',
r'{}\playlist.dpl'.format(os.getcwd())
])
self.progress_bar_update(100)
def get_current_data(self, col=0):
return self.get_data(self.get_current_row(), col)
#return self.queryModel.data(self.mk_querymodel_index(self.get_current_row(), col))
def get_data(self, row, col=0):
return self.queryModel.data(self.mk_querymodel_index(row, col))
def mk_querymodel_index(self, row, col):
return self.queryModel.createIndex(row, col)
def get_current_row(self):
return self.sql_table.selectionModel().currentIndex().row()
def tableview_to_list(self):
a = self.queryModel.query()
a.first()
r = []
while a.isValid():
r.append(a.value(self.thread.MEDIA_PATH_SQL_COL_NUM['path']))
if not a.next():
break
if self.random_check.checkState() == Qt.Checked:
random.shuffle(r)
return r
def load_param(self):
self.param = {
'gallery_path': [],
'sub_folder': False,
}
if os.path.exists('settings.json'):
try:
with open('settings.json') as json_file:
paramR = json.load(json_file)
for i in paramR.keys():
self.param[i] = paramR[i]
except:
'okay'
def save_param(self):
with open('settings.json', 'w') as outfile:
json.dump(self.param, outfile)
def closeEvent(self, event):
self.save_param()
try:
self.gc_placeholder.close()
except:
'okay'
class QmyMainWindow(QMainWindow):
def __init__(self, parent=None):
super().__init__(parent) #调用父类构造函数，创建窗体
self.ui = Ui_MainWindow() #创建UI对象
self.ui.setupUi(self) #构造UI界面
self.setCentralWidget(self.ui.tableView)
# tableView显示属性设置
## self.ui.tableView.setSelectionBehavior(QAbstractItemView.SelectRows)
## self.ui.tableView.setSelectionMode(QAbstractItemView.SingleSelection)
self.ui.tableView.setAlternatingRowColors(True)
self.ui.tableView.verticalHeader().setDefaultSectionSize(22)
self.ui.tableView.horizontalHeader().setDefaultSectionSize(60)
## self.ui.tableView.resizeColumnsToContents()
## ==============自定义功能函数============
def __getFieldNames(self): ##获取所有字段名称
emptyRec = self.qryModel.record() #获取空记录，只有字段名
self.fldNum = {} #字段名与序号的字典
for i in range(emptyRec.count()):
fieldName = emptyRec.fieldName(i)
self.fldNum.setdefault(fieldName)
self.fldNum[fieldName] = i
print(self.fldNum)
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.qryModel.setHeaderData(self.fldNum["empNo"], Qt.Horizontal,"工号")
## self.qryModel.setHeaderData(self.fldNum["Name"], Qt.Horizontal,"姓名")
## self.qryModel.setHeaderData(self.fldNum["Gender"], Qt.Horizontal,"性别")
## self.qryModel.setHeaderData(self.fldNum["Birthday"], Qt.Horizontal,"出生日期")
## self.qryModel.setHeaderData(self.fldNum["Province"], Qt.Horizontal,"省份")
## self.qryModel.setHeaderData(self.fldNum["Department"],Qt.Horizontal,"部门")
## self.qryModel.setHeaderData(self.fldNum["Salary"], 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 __updateRecord(self, recNo): ##更新一条记录
curRec = self.qryModel.record(recNo) #获取当前记录
empNo = curRec.value("EmpNo") #获取EmpNo
query = QSqlQuery(self.DB) #查询出当前记录的所有字段
query.prepare("SELECT * FROM employee WHERE EmpNo = :ID")
query.bindValue(":ID", empNo)
query.exec() #
query.first()
if (not query.isValid()): #是否为有效记录
return
curRec = query.record() #获取当前记录的数据，QSqlRecord类型
dlgData = QmyDialogData(self) #创建对话框
dlgData.setUpdateRecord(curRec) #调用对话框函数更新数据和界面
ret = dlgData.exec() # 以模态方式显示对话框
if (ret != QDialog.Accepted):
return
recData = dlgData.getRecordData() #获得对话框返回的记录
query.prepare('''UPDATE employee SET Name=:Name, Gender=:Gender,
Birthday=:Birthday, Province=:Province,
Department=:Department, Salary=:Salary,
Memo=:Memo, Photo=:Photo WHERE EmpNo = :ID''')
query.bindValue(":Name", recData.value("Name"))
query.bindValue(":Gender", recData.value("Gender"))
query.bindValue(":Birthday", recData.value("Birthday"))
query.bindValue(":Province", recData.value("Province"))
query.bindValue(":Department", recData.value("Department"))
query.bindValue(":Salary", recData.value("Salary"))
query.bindValue(":Memo", recData.value("Memo"))
query.bindValue(":Photo", recData.value("Photo"))
query.bindValue(":ID", empNo)
## if (not query.exec_()):
if (not query.exec()): #PyQt 5.11.2以前应该使用exec_()函数
QMessageBox.critical(self, "错误",
"记录更新错误\n" + query.lastError().text())
else:
self.qryModel.query().exec() #数据模型重新查询数据，更新tableView显示
## ==========由connectSlotsByName() 自动连接的槽函数==================
@pyqtSlot() ##打开数据库
def on_actOpenDB_triggered(self):
dbFilename, flt = QFileDialog.getOpenFileName(
self, "选择数据库文件", "", "SQL Lite数据库(*.db *.db3)")
if (dbFilename == ''):
return
#打开数据库
self.DB = QSqlDatabase.addDatabase("QSQLITE") #添加 SQL LITE数据库驱动
self.DB.setDatabaseName(dbFilename) #设置数据库名称
## DB.setHostName()
## DB.setUserName()
## DB.setPassword()
if self.DB.open(): #打开数据库
self.__openTable() #查询数据
else:
QMessageBox.warning(self, "错误", "打开数据库失败")
@pyqtSlot() ##插入记录
def on_actRecInsert_triggered(self):
query = QSqlQuery(self.DB)
query.exec("select * from employee where EmpNo =-1") #实际不查询出记录，只查询字段信息
curRec = query.record() #获取当前记录,实际为空记录,但有字段信息
curRec.setValue("EmpNo", self.qryModel.rowCount() + 3000)
dlgData = QmyDialogData(self)
dlgData.setInsertRecord(curRec) #插入记录
ret = dlgData.exec() #以模态方式显示对话框
if (ret != QDialog.Accepted):
return
recData = dlgData.getRecordData()
query.prepare('''INSERT INTO employee (EmpNo,Name,Gender,Birthday,
Province,Department,Salary,Memo,Photo)
VALUES(:EmpNo,:Name, :Gender,:Birthday,:Province,
:Department,:Salary,:Memo,:Photo)''')
query.bindValue(":EmpNo", recData.value("EmpNo"))
query.bindValue(":Name", recData.value("Name"))
query.bindValue(":Gender", recData.value("Gender"))
query.bindValue(":Birthday", recData.value("Birthday"))
query.bindValue(":Province", recData.value("Province"))
query.bindValue(":Department", recData.value("Department"))
query.bindValue(":Salary", recData.value("Salary"))
query.bindValue(":Memo", recData.value("Memo"))
query.bindValue(":Photo", recData.value("Photo"))
res = query.exec() #执行SQL语句
if (res == False):
QMessageBox.critical(self, "错误",
"插入记录错误\n" + query.lastError().text())
else: #插入，删除记录后需要重新设置SQL语句查询
sqlStr = self.qryModel.query().executedQuery() #执行过的SELECT语句
self.qryModel.setQuery(sqlStr) #reset 重新查询数据
@pyqtSlot() ##删除记录
def on_actRecDelete_triggered(self):
curRecNo = self.selModel.currentIndex().row()
curRec = self.qryModel.record(curRecNo) #获取当前记录
if (curRec.isEmpty()): #当前为空记录
return
empNo = curRec.value("EmpNo") #获取员工编号
query = QSqlQuery(self.DB)
query.prepare("DELETE FROM employee WHERE EmpNo = :ID")
query.bindValue(":ID", empNo)
if (query.exec() == False):
QMessageBox.critical(self, "错误",
"删除记录出现错误\n" + query.lastError().text())
else: #插入，删除记录后需要重新设置SQL语句查询
sqlStr = self.qryModel.query().executedQuery() #执行过的SELECT语句
self.qryModel.setQuery(sqlStr) #reset 重新查询数据
@pyqtSlot() ##编辑记录
def on_actRecEdit_triggered(self):
curRecNo = self.selModel.currentIndex().row()
self.__updateRecord(curRecNo)
## @pyqtSlot() ##双击编辑记录
def on_tableView_doubleClicked(self, index):
curRecNo = index.row()
self.__updateRecord(curRecNo)
@pyqtSlot() ##遍历记录，涨工资
def on_actScan_triggered(self):
qryEmpList = QSqlQuery(self.DB) #员工工资信息列表
qryEmpList.exec("SELECT empNo,Salary FROM employee ORDER BY empNo")
qryUpdate = QSqlQuery(self.DB) #临时 QSqlQuery
qryUpdate.prepare(
'''UPDATE employee SET Salary=:Salary WHERE EmpNo = :ID''')
qryEmpList.first()
while (qryEmpList.isValid()): #当前记录有效
empID = qryEmpList.value("empNo") #获取empNo
salary = qryEmpList.value("Salary") #获取Salary
salary = salary + 500 #涨工资
qryUpdate.bindValue(":ID", empID)
qryUpdate.bindValue(":Salary", salary) #设置SQL语句参数
qryUpdate.exec() #执行update语句
if not qryEmpList.next(): #移动到下一条记录，并判断是否到末尾了
break
self.qryModel.query().exec() #数据模型重新查询数据，更新tableView的显示
QMessageBox.information(self, "提示", "涨工资计算完毕")
@pyqtSlot() ##SQL语句测试
def on_actTestSQL_triggered(self):
query = QSqlQuery(self.DB)
## # SQL语句测试1， exec_() 和exec()都可以直接执行不带参数的SQL语句
## query.exec('''UPDATE employee SET Salary=3000 where Gender="女" ''')
## query.exec_('''UPDATE employee SET Salary=4500 where Gender="女" ''')
# SQL语句测试2，执行带参数的SQL语句，只能用 exec_()，不能用exec()
## query.prepare('''UPDATE employee SET Salary=9000 where Gender=:Gender ''')
## query.bindValue(":Gender","男")
## query.exec()
query.exec('''UPDATE employee SET Salary=500+Salary ''')
## query.bindValue(":Gender","男")
## query.exec()
## query.prepare("UPDATE employee SET Department=?, Salary=? where Name=?")
## query.bindValue(0, "技术部")
## query.bindValue(1, 5500)
## query.bindValue(2, "张三")
## query.exec_() #只能用exec_()，而不能用exec()函数
## self.qryModel.query().exec() #不增减记录时更新显示
## 增减记录后的更新显示
sqlStr = self.qryModel.query().executedQuery() #执行过的SELECT语句
self.qryModel.setQuery(sqlStr) #reset 重新查询数据
print("SQL OK")
## =============自定义槽函数===============================
def do_currentRowChanged(self, current, previous): ##行切换时触发
if (current.isValid() == False):
return
curRec = self.qryModel.record(current.row()) #获取当前记录,QSqlRecord类型
empNo = curRec.value("EmpNo") #不需要加 toInt()函数
self.ui.statusBar.showMessage("当前记录：工号=%d" % empNo)
class WarehouseController(QObject):
def __init__(self, list, table, addButton, editButton, deleteButton, dbase):
super().__init__()
self.list = list
self.table = table
self.addButton = addButton
self.editButton = editButton
self.deleteButton = deleteButton
self.dbase = dbase
self.shopModel = QSqlTableModel(db = dbase)
self.shopModel.setTable("shop")
self.shopModel.select()
self.list.setModel(ComplexListModel(self.shopModel, "{name}"))
self.list.selectionModel().currentChanged.connect(self.listSelectionChanged)
self._checkPrivileges()
if self.only_select:
self.addButton.setEnabled(False)
self.deleteButton.setEnabled(False)
self.addButton.clicked.connect(self.addButtonClicked)
self.editButton.clicked.connect(self.editButtonClicked)
self.deleteButton.clicked.connect(self.deleteButtonClicked)
def _checkPrivileges(self):
query = QSqlQuery("SHOW GRANTS")
only_select = None
table_pattern = "`{}`".format("shop_detail").lower()
while query.next():
s = query.value(0).lower()
if table_pattern in s:
if "select" in s and only_select is None:
only_select = True
else:
only_select = False
self.only_select = bool(only_select)
def listSelectionChanged(self, cur, prev):
if not cur.isValid():
return self.table.setModel(None)
else:
self.setShopIndex(cur.row())
def setShopIndex(self, row):
record = self.shopModel.record(row)
self.detailModel = QSqlQueryModel()
query = "SELECT detail.id as id, CONCAT(detail.article, \": \", detail.name) as dtl, shop_detail.quantity as qnt \
FROM shop_detail INNER JOIN detail \
ON shop_detail.detail_id = detail.id \
WHERE shop_detail.shop_id={} ORDER BY dtl".format(record.value("id"))
self.detailModel.setQuery(query)
self.detailModel.setHeaderData(1, Qt.Horizontal, "Наименование")
self.detailModel.setHeaderData(2, Qt.Horizontal, "Количество")
self.table.setModel(self.detailModel)
self.table.hideColumn(0)
self.table.resizeColumnsToContents()
self.table.selectionModel().currentChanged.connect(self.tableSelectionChanged)
if not self.detailModel.query().isActive():
print(self.detailModel.lastError().text())
self.deleteButton.setEnabled(False)
self.editButton.setEnabled(False)
def tableSelectionChanged(self, cur, prev):
if self.only_select: return
if cur.isValid():
self.deleteButton.setEnabled(True)
self.editButton.setEnabled(True)
else:
self.deleteButton.setEnabled(False)
self.editButton.setEnabled(False)
self.addButton.setEnabled(True)
def addButtonClicked(self):
shop = self.shopModel.record(self.list.currentIndex().row())
query = QSqlQuery("SELECT detail.id as id, CONCAT(detail.article, \": \", detail.name) as name \
FROM detail WHERE NOT(detail.id IN (SELECT detail_id FROM shop_detail \
WHERE shop_id={}))".format(shop.value("id")))
details = {}
while query.next():
details[query.value("name")] = query.value("id")
if not details:
return QMessageBox.warning(None, "Ошибка добавления",
"Не удалось добавить новый товар на склад: все возможные товары уже добавлены.")
choice, ok = QInputDialog.getItem(None, "Товар", "Укажите товар:",
list(details.keys()), 0, False)
if not ok: return
qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:",
1, 1)
if not ok: return
detail_id = details[choice]
shop_id = shop.value("id")
query = QSqlQuery("INSERT INTO shop_detail (shop_id, detail_id, quantity) \
VALUES ({}, {}, {})".format(shop_id, detail_id, qnt))
if not query.isActive():
print(query.lastError().text())
self.setShopIndex(self.list.currentIndex().row())
self.table.selectionModel().clearSelection()
def editButtonClicked(self):
detail = self.detailModel.record(self.table.currentIndex().row())
qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:",
detail.value("qnt"), 0)
if not ok: return
shop = self.shopModel.record(self.list.currentIndex().row())
if qnt > 0:
query = QSqlQuery("UPDATE shop_detail SET quantity={} \
WHERE shop_id={} AND detail_id={}".format(qnt,
shop.value("id"), detail.value("id")))
else:
query = QSqlQuery("DELETE FROM shop_detail WHERE \
shop_id={} AND detail_id={} LIMIT 1".format(
shop.value("id"), detail.value("id")))
if not query.isActive():
print(query.lastError().text())
self.setShopIndex(self.list.currentIndex().row())
def deleteButtonClicked(self):
if not self.table.currentIndex().isValid(): return
detail = self.detailModel.record(self.table.currentIndex().row())
shop = self.shopModel.record(self.list.currentIndex().row())
query = QSqlQuery("DELETE FROM shop_detail WHERE \
shop_id={} AND detail_id={} LIMIT 1".format(
shop.value("id"), detail.value("id")))
if not query.isActive():
print(query.lastError().text())
self.setShopIndex(self.list.currentIndex().row())
def update(self):
cur = self.list.currentIndex()
if cur.isValid():
row = cur.row()
else:
row = 0
self.shopModel.select()
self.list.reset()
self.selectRow(row)
def selectRow(self, row):
self.list.selectionModel().clearSelection()
self.list.selectionModel().setCurrentIndex(
self.shopModel.index(row, 0), QItemSelectionModel.Select)
class OrderController(QObject):
def __init__(self, form, orderTable, addButton, editButton, deleteButton, dbase, mainwindow):
super().__init__()
self.form = form
self.orderTable = orderTable
self.addButton = addButton
self.editButton = editButton
self.deleteButton = deleteButton
self.dbase = dbase
self.mainwindow = mainwindow
form.currentRecordChanged.connect(self.recordChanged)
form.recordInserted.connect(lambda: self.recordChanged(None))
form.recordDeleted.connect(lambda: self.recordChanged(None))
if form.only_select:
self.addButton.setEnabled(False)
self.editButton.setEnabled(False)
self.deleteButton.setEnabled(False)
self.addButton.clicked.connect(self.addButtonClicked)
self.editButton.clicked.connect(self.editButtonClicked)
self.deleteButton.clicked.connect(self.deleteButtonClicked)
def recordChanged(self, record):
#print("record changed", record)
if record is None:
for button in (self.addButton, self.editButton, self.deleteButton):
button.setEnabled(False)
self.orderTable.setModel(None)
self._hiddingHack(True)
else:
if not self.form.only_select:
self.addButton.setEnabled(True)
self.detailModel = QSqlQueryModel()
query = "SELECT detail.id as id, detail.article as article, detail.name as name, order_detail.quantity as qnt, \
detail.price as sole_price, detail.price*order_detail.quantity as total_price\
FROM order_detail INNER JOIN detail \
ON order_detail.detail_id = detail.id \
WHERE order_detail.order_id={} ORDER BY article".format(record.value("id"))
self.detailModel.setQuery(query)
self.detailModel.setHeaderData(1, Qt.Horizontal, "Артикул")
self.detailModel.setHeaderData(2, Qt.Horizontal, "Наименование")
self.detailModel.setHeaderData(3, Qt.Horizontal, "Количество")
self.detailModel.setHeaderData(4, Qt.Horizontal, "Цена за штуку")
self.detailModel.setHeaderData(5, Qt.Horizontal, "Суммарная цена")
self.orderTable.setModel(self.detailModel)
self.orderTable.hideColumn(0)
self.orderTable.resizeColumnsToContents()
self.orderTable.selectionModel().currentChanged.connect(self.tableSelectionChanged)
if not self.detailModel.query().isActive():
print(self.detailModel.lastError().text())
self.deleteButton.setEnabled(False)
self.editButton.setEnabled(False)
self._hiddingHack(False)
def _hiddingHack(self, val):
ui = self.mainwindow.ui
if val is True:
ui.client_hack.setCurrentWidget(ui.client_hide_page)
ui.shop_hack.setCurrentWidget(ui.shop_hide_page)
ui.emp_hack.setCurrentWidget(ui.emp_hide_page)
else:
ui.client_hack.setCurrentWidget(ui.client_ok_page)
ui.shop_hack.setCurrentWidget(ui.shop_ok_page)
ui.emp_hack.setCurrentWidget(ui.emp_ok_page)
def tableSelectionChanged(self, cur, prev):
if self.form.only_select: return
if cur.isValid():
self.deleteButton.setEnabled(True)
self.editButton.setEnabled(True)
else:
self.deleteButton.setEnabled(False)
self.editButton.setEnabled(False)
self.addButton.setEnabled(True)
def addButtonClicked(self):
order = self.form.currentRecord()
query = QSqlQuery("SELECT detail.id as id, CONCAT(detail.article, \": \", detail.name) as name \
FROM detail WHERE NOT(detail.id IN (SELECT detail_id FROM order_detail \
WHERE order_id={}))".format(order.value("id")))
details = {}
while query.next():
details[query.value("name")] = query.value("id")
if not details:
return QMessageBox.warning(None, "Ошибка добавления",
"Не удалось добавить новый товар к заказу: все возможные товары уже добавлены.")
choice, ok = QInputDialog.getItem(None, "Товар", "Укажите товар:",
list(details.keys()), 0, False)
if not ok: return
qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:", 1, 1)
if not ok: return
detail_id = details[choice]
order_id = order.value("id")
query = QSqlQuery("INSERT INTO order_detail (order_id, detail_id, quantity) \
VALUES ({}, {}, {})".format(order_id, detail_id, qnt))
if not query.isActive():
print(query.lastError().text())
self.form.update()
def editButtonClicked(self):
detail = self.detailModel.record(self.orderTable.currentIndex().row())
qnt, ok = QInputDialog.getInt(None, "Количество", "Укажите количество товара:",
detail.value("qnt"), 0)
if not ok: return
order = self.form.currentRecord()
if qnt > 0:
query = QSqlQuery("UPDATE order_detail SET quantity={} \
WHERE order_id={} AND detail_id={}".format(qnt,
order.value("id"), detail.value("id")))
else:
query = QSqlQuery("DELETE FROM order_detail WHERE \
order_id={} AND detail_id={} LIMIT 1".format(
order.value("id"), detail.value("id")))
query.exec_()
if not query.isActive():
print(query.lastError().text())
self.form.update()
def deleteButtonClicked(self):
if not self.orderTable.currentIndex().isValid(): return
detail = self.detailModel.record(self.orderTable.currentIndex().row())
order = self.form.currentRecord()
query = QSqlQuery("DELETE FROM order_detail WHERE \
order_id={} AND detail_id={} LIMIT 1".format(
order.value("id"), detail.value("id")))
query.exec_()
if not query.isActive():
print(query.lastError().text())
self.form.update()
def selectRow(self, row):
self.form.selectRow(row)
def update(self):
self.form.update()
class DatabaseSearchWidget(QWidget, Ui_DatabaseSearchWidget):
# Signal to be emitted when user wants item to be added
signal_add_item = QtCore.pyqtSignal(int)
# Signal to be emitted when user selection changes
signal_current_selection = QtCore.pyqtSignal(int)
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)
@QtCore.pyqtSlot()
def update_query(self):
""" Update the query with the search term
Returns
-------
None
"""
LOGGER.debug("Updating search query..")
query = self.queries[self.combo_search_options.currentText()]
# Todo: Slow query setting freezes ui
self.databaseModel.setQuery(
query.format(input=self.lineEdit_search_input.text().strip()))
LOGGER.debug(str(self.databaseModel.query().executedQuery()))
LOGGER.debug("Search complete.")
def selected_id(self):
""" Return the id of the currently selected item
Returns
-------
int
"""
selection = self.dataView_search_results.get_selected_rows()
if len(selection) != 1:
return -1
else:
return int(
self.databaseModel.data(
self.databaseModel.index(selection[0], 0)))
def eventFilter(self, source, event):
if event.type() == QtCore.QEvent.KeyPress and event.key() in (
QtCore.Qt.Key_Return, QtCore.Qt.Key_Enter):
self.update_query()
return True
return False
@QtCore.pyqtSlot()
def slot_emit_selection_changed(self):
""" Emit database id of the currently selected item
Returns
-------
None
"""
self.signal_current_selection.emit(self.selected_id())
@QtCore.pyqtSlot()
def slot_emit_add_item(self):
""" Emit database id of the currently selected item
Returns
-------
None
"""
self.signal_add_item.emit(self.selected_id())
class ProjectsOverview(FilterableTable):
"""a widget to display a complete overview over
all projects
"""
changed_projects = pyqtSignal(str)
change_view = pyqtSignal(int)
deleted_project = pyqtSignal()
submit_to_ENA = pyqtSignal(str)
submit_to_IPD = pyqtSignal(str)
open_new_allele_form = pyqtSignal(str)
def __init__(self, log, mydb, parent = None):
super().__init__(log, mydb)
if parent:
self.settings = parent.settings
else:
import GUI_login
self.settings = GUI_login.get_settings("admin", self.log)
self.enhance_UI()
self.show_closed = False
self.filter_closed()
self.table.resizeColumnsToContents()
self.table.horizontalHeader().setSectionResizeMode(6, QHeaderView.Stretch)
self.table.horizontalHeader().setSectionResizeMode(7, QHeaderView.Stretch)
self.add_headers()
self.update_filterbox()
def enhance_UI(self):
self.toggle_btn = QPushButton(self)
self.toggle_btn.setText("Show closed projects!")
self.toggle_btn.setCheckable(True)
self.show_closed = False
self.grid.addWidget(self.toggle_btn, 1, 0)
self.toggle_btn.toggled.connect(self.on_toggleBtn_clicked)
self.table.customContextMenuRequested.connect(self.open_menu)
def add_headers(self):
headers = ["Project Name", "Project Status", "Creation Date", "User Name",
"Gene", "Pool", "Title", "Description", "Number of Alleles"]
for (i, column) in enumerate(headers):
self.proxy.setHeaderData(i, Qt.Horizontal, column)
def create_model(self):
"""creates the table model
"""
self.log.debug("Creating the table model...")
self.model = QSqlQueryModel()
self.query_open = """
SELECT projects.project_name, project_status, creation_date,
username, projects.gene, pool, title, description,
count(alleles.local_name) as nr_alleles
FROM projects
LEFT OUTER JOIN alleles
ON projects.project_name = alleles.project_name
WHERE project_status = 'Open'
GROUP BY projects.project_name, project_status, creation_date,
username, projects.gene, pool, title, description
ORDER BY projects.project_name desc
"""
self.query_all = """
SELECT projects.project_name, project_status, creation_date,
username, projects.gene, pool, title, description,
count(alleles.local_name) as nr_alleles
FROM projects
LEFT OUTER JOIN alleles
ON projects.project_name = alleles.project_name
GROUP BY projects.project_name, project_status, creation_date,
username, projects.gene, pool, title, description
ORDER BY projects.project_name desc
"""
self.q = QSqlQuery()
self.log.debug("\t=> Done!")
@pyqtSlot()
def filter_closed(self):
"""filters the table either for only open projects (default)
or all projects
"""
if self.show_closed:
self.log.debug("Filtering for all projects...")
query = self.query_all
else:
self.log.debug("Filtering for only open projects...")
query = self.query_open
self.q.exec_(query)
self.check_error(self.q)
self.model.setQuery(self.q)
def on_toggleBtn_clicked(self, state):
"""when "ShowAll!" button is toggled, reset query
to appropriate filter
"""
self.show_closed = state
self.filter_closed()
def open_menu(self, pos):
"""provides a context menu
"""
menu = QMenu()
new_act = menu.addAction("Add new Target Allele")
open_act = menu.addAction("Open Project View")
select_act = menu.addAction("Select as current Project")
ena_act = menu.addAction("Submit Project to ENA")
ipd_act = menu.addAction("Submit Project to IPD")
del_act = menu.addAction("Delete project if empty")
action = menu.exec_(self.table.mapToGlobal(pos))
if action:
row = self.table.indexAt(pos).row()
myindex = self.model.index(row, 0)
project = self.model.data(myindex)
if action == select_act:
self.changed_projects.emit(project)
self.log.debug("ProjectsOverview emitted changed_projects")
elif action == open_act:
self.log.debug("ProjectsOverview emitted changed_projects & change_view")
self.changed_projects.emit(project)
self.change_view.emit(3)
elif action == new_act:
self.open_new_allele_form.emit(project)
elif action == ena_act:
self.submit_to_ENA.emit(project)
elif action == ipd_act:
self.submit_to_IPD.emit(project)
elif action == del_act:
self.delete_project(project, row)
@pyqtSlot(str, int)
def delete_project(self, project, row):
"""delete a project from the database & file system if it's empty
"""
self.log.debug("Attempting to delete project '{}' from database...".format(project))
q = QSqlQuery()
count_query = "select count(*) from alleles where project_name = '{}'".format(project)
q.exec_(count_query)
check_error(q, self.mydb, self.log)
alleles = 0
while q.next():
alleles = q.value(0)
if alleles == 0:
delete_query = "delete from projects where project_name = '{}'".format(project)
q.exec_(delete_query)
self.log.debug("\t=> Successfully deleted empty project")
self.log.debug("Attempting to delete project '{}' from file system...".format(project))
project_dir = os.path.join(self.settings["projects_dir"], project)
try:
shutil.rmtree(project_dir)
except Exception as E:
self.log.debug("=> File deletion did not work:")
self.log.error(E)
self.log.exception(E)
self.log.debug("=> Project '{}' successfully deleted from database and file system".format(project))
self.deleted_project.emit()
else:
self.log.debug("\t=> Project contains {} alleles => cannot delete!".format(alleles))
def refresh(self):
"""refreshes the table using setQuery
=> take care, this might lead to performance issues
"""
self.model.setQuery(self.model.query().lastQuery())
