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())