def select(_): query = QSqlQueryModel() query.setQuery( "SELECT p.Name AS [Товар], SUM(s.Count) AS [Количество], SUM(s.Price) AS [Цена] " "FROM Sales AS s INNER JOIN Pastries AS p ON s.Pastry = p.Id " "GROUP BY p.Name") return query
def parentTagListBetaUpdate(self): typesLink = ["videos_tags_link", "videos_yt_tags_link"] parents = ["tags_parent", "yt_tags_parent"] sort = ["P.parent_tag", "ct"] sortDir = ["ASC", "DESC"] queryTemplate = f"""SELECT P.parent_tag, P.parent_tag || ' (' || IFNULL(ct, 0) || ')', ct FROM ( SELECT parent_tag FROM {parents[self.tagType]} WHERE child_tag = (?) GROUP BY 1 ) P LEFT JOIN ( SELECT tag_name, count(*) as ct FROM {typesLink[self.tagType]} WHERE tag_name = (?) GROUP BY 1 ) C ON P.parent_tag = C.tag_name ORDER BY {sort[self.tagSortDropDown.currentIndex()]} {sortDir[self.tagSortDirDropDown.currentIndex()]}""" query = QSqlQuery() query.prepare(queryTemplate) childTag = self.childTag query.addBindValue(childTag) query.addBindValue(childTag) query.exec() model = QSqlQueryModel() model.setQuery(query) self.parentTagListBeta.setModel(model) self.parentTagListBeta.setModelColumn(1) self.parentTagListBeta.show()
class TabTableNonEditable(InvertedTable): """an inverted table presenting a QSqlQueryModel """ def __init__(self, log, db, tab_nr, query, headers = None, myfilter = ""): super().__init__(log, db) self.nr = tab_nr self.filter = myfilter self.headers = headers self.query = query self.create_model() self.invert_model() self.add_headers() def create_model(self): """creates the table model """ q = QSqlQuery(self.query + " " + self.filter) self.model = QSqlQueryModel() q.exec_(self.query) self.model.setQuery(q) def add_headers(self): """adds headers """ if self.headers: for i in self.headers: column = self.headers[i] self.model.setHeaderData(i, Qt.Horizontal, column, Qt.DisplayRole) def refresh(self, myfilter = ""): """refreshes the displayed data after data changes in the model """ if myfilter: self.filter = myfilter self.model.setQuery(self.query + " where " + self.filter)
def tagListUpdate(self): types = ["tags", "yt_tags"] typesLink = ["videos_tags_link", "videos_yt_tags_link"] sort = ["T.tag_name", "ct"] sortDir = ["ASC", "DESC"] queryTemplate = f"""SELECT T.tag_name, T.tag_name || ' (' || IFNULL(ct, 0) || ')', ct FROM {types[self.tagType]} T LEFT JOIN ( SELECT tag_name, count(*) as ct FROM {typesLink[self.tagType]} GROUP BY 1 ) C ON T.tag_name = C.tag_name WHERE T.tag_name LIKE (?) ORDER BY {sort[self.tagSortDropDown.currentIndex()]} {sortDir[self.tagSortDirDropDown.currentIndex()]}""" searchText = self.tagSearchBar.text() query = QSqlQuery() query.prepare(queryTemplate) query.addBindValue(f"%{searchText}%") query.exec() model = QSqlQueryModel() model.setQuery(query) self.tagList.setModel(model) self.tagList.setModelColumn(1) self.tagList.show() self.tagList.selectionModel().selectionChanged.connect( self.tagListSelectionChanged)
def collect_data(ui, db): #! Please add cute Icons back, sir num_model = QSqlQueryModel() cust_model = QSqlQueryModel() ui.treeView = QtWidgets.QTreeView(ui.comboBox) db.open() num_model.setQuery('SELECT Number FROM Room', db) cust_model.setQuery('SELECT Name, ID, Sex FROM Customer', db) db.close() while cust_model.canFetchMore(): cust_model.fetchMore() ui.comboBox_2.setModel(num_model) ui.comboBox.setModel(cust_model) ui.comboBox.setView(ui.treeView) ui.treeView.setColumnHidden(2, True) icon4 = QtGui.QIcon() icon4.addPixmap(QtGui.QPixmap(":/ctmr/48px-Emblem-person-blue.svg.png")) icon5 = QtGui.QIcon() icon5.addPixmap(QtGui.QPixmap(":/ctmr/48px-User_icon_3.svg.png")) i = 0 while i < ui.comboBox.count(): #print(cust_model.index(i, 1).data()) if cust_model.index(i, 2).data() == 'Male': ui.comboBox.setItemIcon(i, icon4) else: ui.comboBox.setItemIcon(i, icon5) i += 1
def remove_rule(self, rule): query = QSqlQueryModel() query.setQuery("delete from rules "\ "where id={}"\ .format(rule.idx)) if query.lastError().isValid(): print(query.lastError().text(), file=sys.stderr)
def Delete(self): dltxt, dlok = QInputDialog.getText( self, 'Удаление', 'Введите ID препарата, который вы хотите удалить:') if dlok: db = QtSql.QSqlDatabase.addDatabase('QSQLITE') db.setDatabaseName("db.db") db.open() querydl = QSqlQuery() querydl.prepare("DELETE FROM medications WHERE mId= ( ? )") querydl.bindValue(0, dltxt) querydl.exec() qry = QSqlQuery() qry = db.exec("SELECT mId AS 'ID'," "mName AS 'Название'," "mDescription AS 'Описание'," "mPrice AS 'Цена' FROM medications") tabmodel = QSqlQueryModel() self.ui.tableView_2.setModel(tabmodel) tabmodel.setQuery(qry) self.ui.tableView_2.setColumnWidth(2, 310) header = self.ui.tableView_2.horizontalHeader() Lheader = self.ui.tableView_2.verticalHeader() Lheader.setVisible(False) header.setStretchLastSection(True) self.ui.tableView_2.show db.close()
def setupMilitaryCombo(self): model = QSqlQueryModel(self) model.setQuery( "SELECT DISTINCT militaernummer as milnum FROM film WHERE militaernummer IS NOT NULL UNION ALL SELECT DISTINCT militaernummer_alt as milnum FROM film WHERE militaernummer_alt IS NOT NULL ORDER BY milnum", self.dbm.db) tv = QTableView() self.uiMilitaryNumberCombo.setView(tv) tv.setHorizontalScrollBarPolicy(Qt.ScrollBarAsNeeded) tv.setSelectionMode(QAbstractItemView.SingleSelection) tv.setSelectionBehavior(QAbstractItemView.SelectRows) tv.setAutoScroll(False) self.uiMilitaryNumberCombo.setModel(model) self.uiMilitaryNumberCombo.setModelColumn(0) self.uiMilitaryNumberCombo.setInsertPolicy(QComboBox.NoInsert) tv.resizeColumnsToContents() tv.resizeRowsToContents() tv.verticalHeader().setVisible(False) tv.horizontalHeader().setVisible(True) #tv.setMinimumWidth(tv.horizontalHeader().length()) tv.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
def submission_models(self, db, table_name): if db.open(): model = QSqlQueryModel() model.setQuery( "SELECT run_id,verdict,language,problem_number,time_stamp FROM my_submissions ORDER BY local_run_id DESC" ) return model
def setupSearchComboBoxByQuery(self, editor, query, modelcolumn=0): model = QSqlQueryModel(self) model.setQuery(query, self.dbm.db) tv = QTableView() editor.setView(tv) tv.setHorizontalScrollBarPolicy(Qt.ScrollBarAsNeeded) #tv.setSelectionMode(QAbstractItemView.MultiSelection) tv.setSelectionMode(QAbstractItemView.SingleSelection) tv.setSelectionBehavior(QAbstractItemView.SelectRows) tv.setAutoScroll(False) editor.setModel(model) editor.setModelColumn(modelcolumn) editor.setInsertPolicy(QComboBox.NoInsert) tv.resizeColumnsToContents() tv.resizeRowsToContents() tv.verticalHeader().setVisible(False) tv.horizontalHeader().setVisible(True) # tv.setMinimumWidth(tv.horizontalHeader().length()) tv.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch) # FIXME PyQt5 AutoCompletion #editor.setAutoCompletion(True) editor.setCurrentIndex(-1)
def tagListAlphaUpdate(self): video_ids = self.mainWindow.getVideoTableSelectionVideoIds() typesLink = ["videos_tags_link", "videos_yt_tags_link"] sort = ["T.tag_name", "ct"] sortDir = ["ASC", "DESC"] queryTemplate = f"""SELECT T.tag_name, T.tag_name || ' (' || IFNULL(ct, 0) || ')', ct FROM ( SELECT tag_name FROM {typesLink[self.tagType]} WHERE video_id IN ({', '.join(['(?)' for video_id in video_ids])}) GROUP BY tag_name HAVING count(*) = (?) ) T LEFT JOIN ( SELECT tag_name, count(*) as ct FROM {typesLink[self.tagType]} GROUP BY 1 ) C ON T.tag_name = C.tag_name ORDER BY {sort[self.tagSortDropDown.currentIndex()]} {sortDir[self.tagSortDirDropDown.currentIndex()]}""" query = QSqlQuery() query.prepare(queryTemplate) for video_id in video_ids: query.addBindValue(video_id) query.addBindValue(len(video_ids)) query.exec() self.tagFilterSet = set() while query.next(): self.tagFilterSet.add(query.value(0)) model = QSqlQueryModel() model.setQuery(query) self.tagListAlpha.setModel(model) self.tagListAlpha.setModelColumn(1) self.tagListAlpha.show()
def select(_): query = QSqlQueryModel() query.setQuery( "SELECT p.Name AS [Должность], AVG(e.Salary) AS [Зарплата] " "FROM Employees AS e INNER JOIN Posts AS p ON e.Post = p.Id " "GROUP BY p.Name") return query
def __init__(self, tag_number): super().__init__() print('processing query...') qry = QSqlQuery(db) query = 'SELECT name, ename, startno, starttime FROM name WHERE ecard = %i OR ecard2 = %i' % ( tag_number, tag_number) qry.prepare(query) qry.exec() model = QSqlQueryModel() model.setQuery(qry) print(model.rowCount()) mapper = QDataWidgetMapper() form = QFormLayout() layout = QVBoxLayout() first_name = QLineEdit() start_number = QLineEdit() form.addRow(QLabel("Startnummer"), start_number) form.addRow(QLabel("Fornavn"), first_name) mapper.setModel(model) mapper.addMapping(first_name, 0) mapper.addMapping(start_number, 2) mapper.toFirst() layout.addLayout(form) widget = QWidget() widget.setLayout(layout) self.setCentralWidget(widget) #controls = QHBoxLayout() '''
def btn3f(self): conn = sqlite3.connect('company.db') curs = conn.cursor() curs.execute("select name from sqlite_master where type='table'") tabs = QTabWidget() table_name = 'stock_price' table_model = QSqlQueryModel() for i, column_data in enumerate(curs.description): table_model.setHeaderData(i, Qt.Horizontal, column_data[0]) table_model.setQuery(f"select * from {table_name}") table_view = QTableView() table_view.setModel(table_model) tabs.addTab(table_view, table_name) screen = QWidget() content = QVBoxLayout() line1 = QHBoxLayout() line1.addWidget(tabs) content.addLayout(line1) self.k = popup3() self.k.setGeometry(800, 350, 400, 200) self.k.setWindowTitle("Stock Prices") self.k.setLayout(content) self.k.show()
def select(min_salary): query = QSqlQueryModel() query.setQuery( "SELECT e.Id, e.Name, e.Surname, e.Patronymic, e.Salary, e.Passport, p.Name " "FROM Employees AS e INNER JOIN Posts AS p ON e.Post = p.Id " "WHERE e.Salary > %s" % min_salary) return query
def select(_): query = QSqlQueryModel() query.setQuery( "SELECT c.Name AS [Покупатель], SUM(s.Count) AS [Количество], SUM(s.Price) AS [Цена] " "FROM Customers AS c INNER JOIN Sales AS s ON s.Customer = c.Id " "GROUP BY c.Name") return query
class Widget(QtWidgets.QWidget): def __init__(self, parent=None): super(Widget, self).__init__(parent) self.label = QtWidgets.QLabel() self.button_left = QtWidgets.QPushButton('<') self.button_right = QtWidgets.QPushButton('>') blayout = QtWidgets.QHBoxLayout() blayout.addWidget(self.button_left) blayout.addWidget(self.button_right) layout = QtWidgets.QVBoxLayout(self) layout.addWidget(self.label) layout.addLayout(blayout) self.mapper = QtWidgets.QDataWidgetMapper(self) #self.db = create_db() self.db = open_db() self.model = QSqlQueryModel(self) self.model.setQuery('SELECT DISTINCT form FROM threads') self.mapper.setModel(self.model) self.mapper.addMapping(self.label, 0, b'text') self.mapper.toFirst() self.button_left.clicked.connect(self.mapper.toPrevious) self.button_right.clicked.connect(self.mapper.toNext)
def competitorView(self): model = QSqlQueryModel() model.setQuery('SELECT id, name FROM t_competitor') model.setHeaderData(0, Qt.Horizontal, "ID") model.setHeaderData(1, Qt.Horizontal, "Name") self.tableView_Competitor_List.setModel(model) pass
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 select(_): query = QSqlQueryModel() query.setQuery( "SELECT p.Name AS [Должность], COUNT(e.Id) AS [Количество] " "FROM Employees AS e INNER JOIN Posts AS p ON e.Post = p.Id " "GROUP BY p.Name") return query
def _createModel(): oi_get_query = QSqlQuery() oi_get_query.prepare("""select symbol ,desc ,substr(oi_date, 1,4) || '-' || substr(oi_date, 5,2) || '-' || substr(oi_date,7,2) ,globex_volume ,volume ,open_interest ,change from oi_reports where symbol = "EUR" order by cast(oi_date as int) desc ; """) #oi_get_query.addBindValue(symbol) oi_get_query.exec() queryModel = QSqlQueryModel() queryModel.setQuery(oi_get_query) headers = ['Symbol', 'Description', 'Date', 'Globex', 'Volume', 'Open Interest', 'Change'] for columnIndex, header in enumerate(headers): queryModel.setHeaderData(columnIndex, Qt.Horizontal, header) #queryModel.setHeaderData(0, Qt.Horizontal, 'Symbol') #queryModel.setHeaderData(1, Qt.Horizontal, 'Description') #queryModel.setHeaderData(2, Qt.Horizontal, 'Date') #queryModel.setHeaderData(3, Qt.Horizontal, 'Globex') #queryModel.setHeaderData(4, Qt.Horizontal, 'Volume') #queryModel.setHeaderData(5, Qt.Horizontal, 'Open Interest') #queryModel.setHeaderData(6, Qt.Horizontal, 'Change') return queryModel
def attractionView(self): model = QSqlQueryModel() model.setQuery('SELECT id, name FROM t_attraction') model.setHeaderData(0, Qt.Horizontal, "ID") model.setHeaderData(1, Qt.Horizontal, "Name") self.tableView_Attraction_List.setModel(model) pass
def inventory_used(self): # 通过进度条表示库存余量 currentQryModel = QSqlQueryModel(self) currentQryModel.setQuery("select RFID from 工件信息表") allnum = currentQryModel.rowCount() progress = int((100 * allnum / 156) + 0.5) self.ui.progressBar.setValue(progress)
def select(max_price): query = QSqlQueryModel() query.setQuery("SELECT * " "FROM Pastries " "WHERE Price <= %s" % max_price) LoaderModelsService.processingModelPastries(query, False) return query
def parentTagListAlphaUpdate(self): types = ["tags", "yt_tags"] typesLink = ["videos_tags_link", "videos_yt_tags_link"] sort = ["T.tag_name", "ct"] sortDir = ["ASC", "DESC"] queryTemplate = f"""SELECT T.tag_name, T.tag_name || ' (' || IFNULL(ct, 0) || ')', ct FROM {types[self.tagType]} T LEFT JOIN ( SELECT tag_name, count(*) as ct FROM {typesLink[self.tagType]} GROUP BY 1 ) C ON T.tag_name = C.tag_name WHERE T.tag_name LIKE (?) ORDER BY {sort[self.tagSortDropDown.currentIndex()]} {sortDir[self.tagSortDirDropDown.currentIndex()]}""" searchText = self.parentTagSearchBar.text() query = QSqlQuery() query.prepare(queryTemplate) query.addBindValue(f"%{searchText}%") query.exec() model = QSqlQueryModel() model.setQuery(query) pmodel = FilterProxyModel() pmodel.setSourceModel(model) filteredSet = self.parentTags filteredSet.add(self.childTag) pmodel.setFilteredSet(filteredSet) self.parentTagListAlpha.setModel(pmodel) self.parentTagListAlpha.setModelColumn(1) self.parentTagListAlpha.show()
def dbToTableView(self,commandSQL): try: QApplication.processEvents() if QSqlDatabase.contains("qt_sql_default_connection"): db = QSqlDatabase.database("qt_sql_default_connection") else: db = QSqlDatabase.addDatabase("QSQLITE") db.setDatabaseName(self.dbPath) db.open() projectModel = QSqlQueryModel() projectModel.setQuery(commandSQL, db) projectModel.setHeaderData(0, Qt.Horizontal, 'پلاک') projectModel.setHeaderData(1, Qt.Horizontal, 'متقاضی') projectModel.setHeaderData(2, Qt.Horizontal, 'نوع انجام کار') projectModel.setHeaderData(3, Qt.Horizontal, 'تاریخ بازدید') projectModel.setHeaderData(4, Qt.Horizontal, 'ساعت بازدید') projectModel.setHeaderData(5, Qt.Horizontal, 'نقشه بردار') projectModel.setHeaderData(6, Qt.Horizontal, 'نماینده') projectModel.setHeaderData(7, Qt.Horizontal, 'تاریخ ثبت') projectModel.setHeaderData(8, Qt.Horizontal, 'توضیحات') self.ui.tableView_result.setModel(projectModel) self.rowCount = projectModel.rowCount() self.tableResult = projectModel db.close() QApplication.processEvents() except: self.errorM('مشکل در ارتباط با دیتابیس\n {}')
def new_addservice_dialog(self, window, db, res_index, thrd): #Setup UI ui = Ui_AddService() new_addservice = QDialog() ui.setupUi(new_addservice) #Setup ComboBox view db.open() srv_model = QSqlQueryModel() ui.treeView = QtWidgets.QTreeView(ui.comboBox) srv_model.setQuery('SELECT Name, ID FROM Service', db) db.close() while srv_model.canFetchMore(): srv_model.fetchMore() ui.comboBox.setModel(srv_model) ui.comboBox.setView(ui.treeView) db.close() ui.pushButton.clicked.connect(lambda: thrd.tryStart(TableWorker(add_DB(ui, new_addservice, db, "RoomService", [res_index.siblingAtColumn(0).data(),res_index.siblingAtColumn(1).data(),res_index.siblingAtColumn(2).data(),ui.treeView.currentIndex().siblingAtColumn(1).data()], "?, ?, ?, ?", [])))) ui.pushButton_2.clicked.connect(lambda: thrd.tryStart(TableWorker(del_DB(ui, new_addservice, db, "RoomService", "ResID = ? AND SrvID = ?", [res_index.siblingAtColumn(0).data(), ui.treeView.currentIndex().siblingAtColumn(1).data()], [])))) new_addservice.setWindowTitle('Add or remove a Service') new_addservice.exec()
def select(_): query = QSqlQueryModel() query.setQuery( "SELECT p.Name AS [Товар], COUNT(p.Id) AS [Количество ингредиентов] " "FROM Pastries AS p INNER JOIN Doses AS d ON d.Pastry = p.Id " "GROUP BY p.Name") return query
class DatabaseQueryView(QTableView): def __init__(self, db, query, parent=None): super().__init__(parent) self.db = db self.query = query self._init_context_menu() self._init_model() def _init_context_menu(self): self.setContextMenuPolicy(Qt.CustomContextMenu) self.customContextMenuRequested.connect(self._on_context_menu) def _init_model(self): self.model = QSqlQueryModel() self.model.setQuery(QSqlQuery(self.query, self.db)) self.setModel(self.model) def set_query(self, query): self.query = query self._init_model() def set_header_labels(self, labels): for idx, label in enumerate(labels): self.model.setHeaderData(idx + 1, Qt.Horizontal, label) def _on_context_menu(self, pos): _context_menu = QMenu(self) _refresh_action = QAction("刷新") _context_menu.addAction(_refresh_action) action = _context_menu.exec_(self.viewport().mapToGlobal(pos)) if action == _refresh_action: self.set_query(self.query)
def obtain_recipe(self): """ PURPOSE ----- To obtain all information pertaining to the recipe clicked on by user, i.e. title, author, cuisine, etc. and create a model to store that information OUTPUT ----- The model with the stored information """ recipe_info_cmd = \ f""" SELECT R.`Recipe Title`, R.Author, R.Cuisine, R.Ingredients, R.`Recipe Procedure`, R.Tags, R.'Website URL' FROM recipes R WHERE R.`Recipe Title` = "{self.recipe_title}" """ # Query to get title, author, ingredients, and recipe procedure from desired recipe title recipe_query = QSqlQuery(self.conn) # Same process as the title_query recipe_query.prepare(recipe_info_cmd) recipe_query.exec_() recipe_model = QSqlQueryModel() recipe_model.setQuery(recipe_query) return recipe_model
def show_demo(self): def test(x): print(len(x)) # Создаем модель sqm = QSqlQueryModel(parent=self) sqm.setQuery( 'SELECT id, lastName, firstName, patrName, birthDate, sex, notes ' 'FROM Client WHERE deleted = 0 AND deathDate IS NULL ' 'LIMIT 20') # Задаем заголовки для столбцов модели sqm.setHeaderData(1, Qt.Horizontal, 'Фамилия') sqm.setHeaderData(2, Qt.Horizontal, 'Имя') sqm.setHeaderData(3, Qt.Horizontal, 'Отчество') sqm.setHeaderData(4, Qt.Horizontal, 'Дата рождения') sqm.setHeaderData(5, Qt.Horizontal, 'Пол') sqm.setHeaderData(6, Qt.Horizontal, 'Примечание') # Задаем для таблицы только что созданную модель self.clients_table.setModel(sqm) self.clients_table.hideColumn(0) self.clients_table.resizeColumnsToContents() self.clients_table.horizontalHeader().setStretchLastSection(True) self.clients_table.horizontalHeader().setHighlightSections(False) self.clients_table.verticalHeader().hide() self.clients_table.clearSelection() self.lbl_suitable_customers_count.setText(str(sqm.rowCount())) self.lbl_selected_customers_count.setText('0') self.clients_table.selectionModel().selectionChanged.connect( self.show_selected_customers_count)
def do_search(self): model = QSqlQueryModel() filterStr = "" current_status_int = self.STATUS_TO_I[self.selected_status] filterStr = f"WHERE status_id >= '{current_status_int}'" search_text = self.ui.leNameDescription.text().strip() if len(search_text) > 0: filterStr = f"{filterStr} AND (aircraft.name LIKE '%{search_text}%' OR aircraft.description LIKE '%{search_text}%')" sql = f''' SELECT aircraft.name as name, aircraft.description as description, status.name as status, aircraft.directory as directory FROM aircraft INNER JOIN status ON aircraft.status_id == status.id {filterStr} ORDER BY aircraft.status_id DESC, aircraft.name ASC; ''' model.setQuery(sql) self.ui.tableView.setModel(model) header = self.ui.tableView.horizontalHeader() header.setSectionResizeMode(0, QHeaderView.ResizeToContents) header.setSectionResizeMode(1, QHeaderView.ResizeToContents) header.setSectionResizeMode(2, QHeaderView.ResizeToContents) self.ui.tableView.setColumnHidden(3, True)
def filterListBetaUpdate(self): ''' Update filterListBeta to select the filters of category categoryIndex Displays the name of the filter with the count of how many videos the filter applies to ''' filterDicts = { "select": [ "A.playlist_id, B.playlist_title, ct, B.playlist_title", "A.channel_id, B.channel_title, ct, B.channel_title", "tag_name, tag_name, ct, tag_name", "tag_name, tag_name, ct, tag_name" ], "filterId": ["playlist_id", "channel_id", "tag_name", "tag_name"], "filterName": ["B.playlist_title", "B.channel_title", "tag_name", "tag_name"], "table": [ "videos_playlists_link", "videos", "videos_tags_link", "videos_yt_tags_link" ], "innerJoin": [ " A\nINNER JOIN playlists B ON\nA.playlist_id = B.playlist_id", " A\nINNER JOIN channels B ON\nA.channel_id = B.channel_id", "", "" ], "ct": [ "ct", "ct", "ct", "ct" ] # Looks stupid for the purpose of matching the rest of the data structure } sort = ["filterName", "ct"] sortDir = ["ASC", "DESC"] categoryIndex = self.filterTypeDropDown.currentIndex() sortStr = filterDicts[sort[ self.filterSortDropDown.currentIndex()]][categoryIndex] sortDirStr = sortDir[self.filterSortDirDropDown.currentIndex()] queryTemplate = f"""SELECT {filterDicts['select'][categoryIndex]} || ' (' || ct || ')' FROM ( SELECT {filterDicts['filterId'][categoryIndex]}, count(*) as ct FROM {filterDicts['table'][categoryIndex]} GROUP BY 1 ){filterDicts['innerJoin'][categoryIndex]} WHERE {filterDicts['filterName'][categoryIndex]} LIKE (?) ORDER BY {sortStr} {sortDirStr}""" filterSearch = self.filterSearchBar.text() query = QSqlQuery() query.prepare(queryTemplate) query.addBindValue(f"%{filterSearch}%") query.exec() model = QSqlQueryModel() model.setQuery(query) pmodel = FilterProxyModel() pmodel.setSourceModel(model) filteredSets = self.filterListAlphaModel.getFilterSets() pmodel.setFilteredSet(filteredSets[categoryIndex]) self.filterListBeta.setModel(pmodel) self.filterListBeta.setModelColumn(3) self.filterListBeta.show()
def songProperties(self): model = QSqlQueryModel() model.setQuery('SELECT t_artist.name FROM fk_song_artist ' 'INNER JOIN t_artist ON fk_song_artist.id_artist = t_artist.id ' 'WHERE fk_song_artist.id_song = ' + str(self.tableView_Song_List.currentIndex().sibling( self.tableView_Song_List.currentIndex().row(), 0).data())) model.setHeaderData(0, Qt.Horizontal, "Artist Name") self.tableView_Song_Properties.setModel(model) pass
def get_model(self): self.db = QSqlDatabase.addDatabase('QSQLITE') self.db.setDatabaseName('sports.db') self.db.open() model = QSqlQueryModel() query_str = """ select name, count(name) as counts from domains group by name order by counts desc """ model.setQuery(query_str, db=self.db) model.setHeaderData(0, Qt.Horizontal, "Word") model.setHeaderData(1, Qt.Horizontal, "Count") return model
class QueryDialog(QDialog): """a dialog to choose an item from a query """ choice = pyqtSignal(str) def __init__(self, query): super().__init__() self.query = query self.create_model() self.init_UI() def create_model(self): """creates the model as QSqlQueryModel, using the given query """ self.model = QSqlQueryModel() q = QSqlQuery() q.exec_(self.query) self.model.setQuery(q) def init_UI(self): """setup the UI """ layout = QVBoxLayout() self.setLayout(layout) self.resize(200,200) self.title = "Choose an existing project" self.list = QListView(self) layout.addWidget(self.list) self.list.setModel(self.model) self.list.setWhatsThis("Choose a project by clicking on it") self.btn = QPushButton("Accept", self) layout.addWidget(self.btn) self.btn.clicked.connect(self.on_btn_clicked) self.btn.setWhatsThis("Click here to accept your selection (works only if a project has been selected)") def on_btn_clicked(self): """when self.btn is clicked, accept the choice and emit it as self.choice """ selected = self.list.selectedIndexes() if selected: index = selected[0] chosen = self.model.data(index, Qt.DisplayRole) self.choice.emit(chosen) self.close() self.choice.emit("") self.close()
def songView(self): model = QSqlQueryModel() model.setQuery('SELECT t_song.id, t_song.name, t_song.file, t_song_type.name, t_anime.name, t_anime_serie.name ' 'FROM t_song ' 'INNER JOIN t_song_type ON t_song.id_song_type = t_song_type.id ' 'INNER JOIN t_anime ON t_song.id_anime = t_anime.id ' 'INNER JOIN t_anime_serie ON t_anime.id_anime_serie = t_anime_serie.id') model.setHeaderData(0, Qt.Horizontal, "Song ID") model.setHeaderData(1, Qt.Horizontal, "Song Name") model.setHeaderData(2, Qt.Horizontal, "Song File") model.setHeaderData(3, Qt.Horizontal, "Song Type") model.setHeaderData(4, Qt.Horizontal, "Anime Name") model.setHeaderData(5, Qt.Horizontal, "Anime Serie") self.tableView_Song_List.setModel(model) pass
class SalesForm(QDialog): def __init__(self, db): super(QWidget, self).__init__() self.ui = Ui_SalesDialog() self.ui.setupUi(self) self.db = db self.queryModel = QSqlQueryModel() self.ui.lstSales.setModel(self.queryModel) self.onUpdateSales() def onUpdateSales(self): query = "SELECT sales.id, slabs.name, slabs.num, strftime('%d-%m-%Y', datetime(sales.date, 'unixepoch')) as date, sales.amount, sales.buyer, sales.notes FROM `slabs`, `sales` on slabs.id = sales.slabid;" print(query) self.queryModel.setQuery(query, self.db)
def gameProperties(self): model = QSqlQueryModel() model.setQuery('SELECT t_song.name, t_song_type.name, t_anime.name, t_anime_serie.name, t_artist.name ' 'FROM t_song INNER JOIN t_song_type ON t_song.id_song_type = t_song_type.id ' 'INNER JOIN t_anime ON t_song.id_anime = t_anime.id ' 'INNER JOIN t_anime_serie ON t_anime.id_anime_serie = t_anime_serie.id ' 'INNER JOIN fk_song_artist ON t_song.id = fk_song_artist.id_song ' 'INNER JOIN t_artist ON fk_song_artist.id_artist = t_artist.id ' 'WHERE t_song.id = ' + str(self.tableView_Game_List.currentIndex().sibling( self.tableView_Game_List.currentIndex().row(), 3).data())) model.setHeaderData(0, Qt.Horizontal, "Song Name") model.setHeaderData(1, Qt.Horizontal, "Song Type") model.setHeaderData(2, Qt.Horizontal, "Anime Name") model.setHeaderData(3, Qt.Horizontal, "Anime Serie") model.setHeaderData(4, Qt.Horizontal, "Artist Name") self.tableView_Game_Properties.setModel(model) pass
def reloadGameCompetitors(self, id_attraction): model_Game_Competitors = QSqlQueryModel() model_Game_Competitors.setQuery('SELECT SUM(point), name FROM (SELECT t_point.point, t_competitor.name ' 'FROM t_point INNER JOIN t_competitor ON ' 't_point.id_competitor = t_competitor.id ' 'WHERE t_point.id_competitor_take = 1 ' 'AND t_point.id_attraction = ' + id_attraction + ' UNION ALL ' 'SELECT t_point.point, t_competitor.name ' 'FROM t_point INNER JOIN t_competitor ' 'ON t_point.id_competitor_take = t_competitor.id ' 'WHERE t_point.id_competitor_take != 1 ' 'AND t_point.id_attraction = ' + id_attraction + ') ' 'GROUP BY name ORDER BY point DESC') model_Game_Competitors.setHeaderData(0, Qt.Horizontal, "Points") model_Game_Competitors.setHeaderData(1, Qt.Horizontal, "Name") self.tableView_Game_Competitors.setModel(model_Game_Competitors) pass
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 model(self, prefix=None): """ recreate the model each call; opening a new window will not be needed to use the recent completions """ if prefix is None: query_nav = QSqlQuery( "select host || path from navigation " + "order by count desc", self.litedb) else: # CFG02 query_nav = QSqlQuery( "select host || path from navigation " + "where prefix = '{}' ".format(prefix) + "order by count desc", self.litedb) ret_model = QSqlQueryModel() ret_model.setQuery(query_nav) # AB01 return ret_model
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())
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 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 MainWindow(QMainWindow): def __init__(self): super(QMainWindow, self).__init__() self.ui = Ui_MainWindow() self.ui.setupUi(self) self.ui.txtNumber.setValidator(QIntValidator()) self.ui.txtName.textChanged.connect(self.onFilterChanged) self.ui.txtNumber.textChanged.connect(self.onFilterChanged) self.ui.btnNewMarble.clicked.connect(self.onNewMarble) self.ui.btnDeleteMarble.clicked.connect(self.onDeleteMarble) self.ui.btnSell.clicked.connect(self.onSell) self.ui.lstMarble.doubleClicked.connect(self.onMarbleDoubleClicked) self.ui.btnViewSales.clicked.connect(self.showSales) self.queryModel = QSqlQueryModel() self.ui.lstMarble.setModel(self.queryModel) self.db = QSqlDatabase.addDatabase('QSQLITE') self.db.setDatabaseName('inventory.db') self.db.open() self.execSql('''PRAGMA foreign_keys = ON;''') self.execSql('''CREATE TABLE IF NOT EXISTS `slabs` ( `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `name` TEXT NOT NULL, `num` INTEGER NOT NULL, `amount` INTEGER NOT NULL, `dimL` INTEGER NOT NULL, `dimW` INTEGER NOT NULL, `dimH` INTEGER NOT NULL, `notes` TEXT NOT NULL );''') self.execSql('''CREATE TABLE IF NOT EXISTS `sales` ( `id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, `slabid` INTEGER NOT NULL, `date` INTEGER NOT NULL, `amount` INTEGER NOT NULL, `leftover` INTEGER NOT NULL, `buyer` TEXT NOT NULL, `notes` TEXT NOT NULL, FOREIGN KEY(slabid) REFERENCES slabs(id) );''') self.onFilterChanged() self.ui.lstMarble.setColumnHidden(0, True) def onFilterChanged(self): conditions = [] if self.ui.txtNumber.text() != '': conditions.append('`num` = %d' % (int(self.ui.txtNumber.text()), )) if self.ui.txtName.text() != '': conditions.append("`name` LIKE '%%%s%%'" % (self.ui.txtName.text(), )) conditionsStr = '' if len(conditions) != 0: conditionsStr = ' WHERE ' + ' AND '.join(conditions) query = "SELECT id, name, num, amount, (dimH||'x'||dimL||'x'||dimW) as dimensions, notes FROM `slabs`" + conditionsStr + ' ORDER BY `name`, `num` ASC;' print(query) self.queryModel.setQuery(query, self.db) def onNewMarble(self): editMarble = EditMarble() while True: result = editMarble.exec() if result == 0: return try: name, number, amount, dimL, dimH, dimW, notes = editMarble.getValues() break except Exception as e: QMessageBox.critical(self, "Error", "Invalid values entered: " + str(e)) query = '''SELECT `id`, `amount`, `notes` FROM `slabs` WHERE `name`='%s' AND `num`=%d AND `dimL`=%d AND `dimW`=%d AND `dimH`=%d''' % (sqlEscape(name), number, dimL, dimW, dimH) result = self.execSql(query) if result == None: return #TOOD: error dialog? or maybe we should just try to insert? if result.next() and result.value(2) == notes: slabId = result.value(0) previousAmount = result.value(1) result.finish() del result result = None query = '''UPDATE `slabs` SET `amount`=%d WHERE `id`=%d''' % (previousAmount + amount, slabId) self.execSql(query) return del result query = '''INSERT INTO `slabs` (`name`, `num`, `amount`, `dimL`, `dimW`, `dimH`, `notes`) VALUES ('%s', %d, %d, %d, %d, %d, '%s');''' % (sqlEscape(name), number, amount, dimL, dimW, dimH, sqlEscape(notes)) self.execSql(query) def onDeleteMarble(self): if len(self.ui.lstMarble.selectedIndexes()) == 0: #nothing selected QMessageBox.critical(self, "Error", "No slab selected.\nPlease select a slab of marble first") return rowIndex = self.ui.lstMarble.selectedIndexes()[0].row() primaryKey = self.ui.lstMarble.model().data(self.ui.lstMarble.model().index(rowIndex,0)); query = '''DELETE FROM `slabs` WHERE `id` == %d''' % (primaryKey, ) self.execSql(query) def getSlabData(self, primaryKey): query = '''SELECT `name`, `num`, `amount`, `dimL`, `dimW`, `dimH`, `notes` FROM `slabs` WHERE id=%d''' % (primaryKey, ) sqlQuery = QSqlQuery(query, self.db); if not sqlQuery.next(): # user supplied an invalid primary key QMessageBox.critical(self, "Error", 'slab with id %d was not found' % (primaryKey,)) raise Exception('slab with id %d was not found' % (primaryKey,)) name = sqlQuery.value(0) number = sqlQuery.value(1) amount = sqlQuery.value(2) dimL = sqlQuery.value(3) dimW = sqlQuery.value(4) dimH = sqlQuery.value(5) notes = sqlQuery.value(6) return name, number, amount, dimL, dimW, dimH, notes def onMarbleDoubleClicked(self, modelIndex): rowIndex = modelIndex.row() primaryKey = self.ui.lstMarble.model().data(self.ui.lstMarble.model().index(rowIndex,0)); name, number, amount, dimL, dimW, dimH, notes = self.getSlabData(primaryKey) editMarble = EditMarble() editMarble.setValues(name, number, amount, dimL, dimW, dimH, notes) result = editMarble.exec() if result == 0: return name, number, amount, dimL, dimH, dimW, notes = editMarble.getValues() self.execSql('''UPDATE `slabs` SET `name`='%s', `num`=%d, `amount`=%d, `dimL`=%d, `dimW`=%d, `dimH`=%d, `notes`='%s' WHERE id = %d;''' % (sqlEscape(name), number, amount, dimL, dimW, dimH, sqlEscape(notes), primaryKey)) def execSql(self, query): print(query) sqlquery = QSqlQuery(query, self.db) #self.db.exec(query) if sqlquery.lastError().number() != -1: #TODO: print these in a dialog box QMessageBox.critical(self, "Error", 'ERROR OCCURRED WHILE EXECUTING STATEMENT: ' + query + "\n" + \ 'Database Text:' + sqlquery.lastError().databaseText() + \ 'Databse Driver:' + sqlquery.lastError().driverText()) return None self.onFilterChanged() return sqlquery def onSell(self): if len(self.ui.lstMarble.selectedIndexes()) == 0: #nothing selected QMessageBox.critical(self, "Error", "No slab selected.\nPlease select a slab of marble first") return rowIndex = self.ui.lstMarble.selectedIndexes()[0].row() slabPrimaryKey = self.ui.lstMarble.model().data(self.ui.lstMarble.model().index(rowIndex,0)); name, number, amount, dimL, dimW, dimH, notes = self.getSlabData(slabPrimaryKey) sell = SellDialog(name, number, amount) result = sell.exec() if result == 0: return amountSold, amountOfLeftovers, buyer, datetime, notes = sell.getValues() leftoverQueries = [] while amountOfLeftovers > 0: newMarble = EditMarble() newMarble.setValues(name, number, amountSold, dimL, dimW, dimH, notes) newMarble.setMaxAmount(amountOfLeftovers) result = newMarble.exec() if result == 0: return newName, newNumber, newAmount, newDimL, newDimW, newDimH, newNotes = newMarble.getValues() leftoverQueries.append('''INSERT INTO `slabs` (`name`, `num`, `amount`, `dimL`, `dimW`, `dimH`, `notes`) VALUES ('%s', %d, %d, %d, %d, %d, '%s');''' % (sqlEscape(newName), newNumber, newAmount, newDimL, newDimW, newDimH, sqlEscape(newNotes))) amountOfLeftovers = amountOfLeftovers - newAmount queryUpdate = '''UPDATE `slabs` SET `amount`=`amount`-%d WHERE id = %d;''' % (amountSold, slabPrimaryKey) querySale = '''INSERT INTO `sales` (`slabid`, `date`, `amount`, `leftover`, `buyer`, `notes`) VALUES(%d, %d, %d, 0, '%s', '%s');''' % (slabPrimaryKey, datetime, amountSold, buyer, notes) self.execSql('BEGIN TRANSACTION;') self.execSql(queryUpdate) self.execSql(querySale) for leftoverQuery in leftoverQueries: self.execSql(leftoverQuery) self.execSql('COMMIT;') self.onFilterChanged() def showSales(self): sales = SalesForm(self.db) sales.exec()
class DataGrid(QWidget): def __init__(self): super().__init__() self.setWindowTitle("分页查询例子") self.resize(750,300) # 查询模型 self.queryModel = None # 数据表 self.tableView = None # 总数页文本 self.totalPageLabel = None # 当前页文本 self.currentPageLabel = None # 转到页输入框 self.switchPageLineEdit = None # 前一页按钮 self.prevButton = None # 后一页按钮 self.nextButton = None # 转到页按钮 self.switchPageButton = None # 当前页 self.currentPage = 0 # 总页数 self.totalPage = 0 # 总记录数 self.totalRecrodCount = 0 # 每页显示记录数 self.PageRecordCount = 5 self.initUI() def initUI(self): # 创建窗口 self.createWindow() # 设置表格 self.setTableView() # 信号槽连接 self.prevButton.clicked.connect(self.onPrevButtonClick ) self.nextButton.clicked.connect(self.onNextButtonClick ) self.switchPageButton.clicked.connect(self.onSwitchPageButtonClick ) # 创建数据库 # 创建窗口 def createWindow(self): # 操作布局 operatorLayout = QHBoxLayout() self.prevButton = QPushButton("前一页") self.nextButton = QPushButton("后一页") self.switchPageButton = QPushButton("Go") self.switchPageLineEdit = QLineEdit() self.switchPageLineEdit.setFixedWidth(40) switchPage = QLabel("转到第") page = QLabel("页") operatorLayout.addWidget(self.prevButton) operatorLayout.addWidget(self.nextButton) operatorLayout.addWidget(switchPage) operatorLayout.addWidget(self.switchPageLineEdit) operatorLayout.addWidget(page) operatorLayout.addWidget(self.switchPageButton) operatorLayout.addWidget( QSplitter()) # 状态布局 statusLayout = QHBoxLayout() self.totalPageLabel = QLabel() self.totalPageLabel.setFixedWidth(70) self.currentPageLabel = QLabel() self.currentPageLabel.setFixedWidth(70) self.totalRecordLabel = QLabel() self.totalRecordLabel.setFixedWidth(70) statusLayout.addWidget(self.totalPageLabel) statusLayout.addWidget(self.currentPageLabel) statusLayout.addWidget( QSplitter() ) statusLayout.addWidget(self.totalRecordLabel) # 设置表格属性 self.tableView = QTableView() # 表格宽度的自适应调整 self.tableView.horizontalHeader().setStretchLastSection(True) self.tableView.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch) # 创建界面 mainLayout = QVBoxLayout(self); mainLayout.addLayout(operatorLayout); mainLayout.addWidget(self.tableView); mainLayout.addLayout(statusLayout); self.setLayout(mainLayout) # 设置表格 def setTableView(self): print('*** step2 SetTableView' ) # 声明查询模型 self.queryModel = QSqlQueryModel(self) # 设置当前页 self.currentPage = 1; # 得到总记录数 self.totalRecrodCount = self.getTotalRecordCount() # 得到总页数 self.totalPage = self.getPageCount() # 刷新状态 self.updateStatus() # 设置总页数文本 self.setTotalPageLabel() # 设置总记录数 self.setTotalRecordLabel() # 记录查询 self.recordQuery(0) # 设置模型 self.tableView.setModel(self.queryModel) print('totalRecrodCount=' + str(self.totalRecrodCount) ) print('totalPage=' + str(self.totalPage) ) # 设置表格表头 self.queryModel.setHeaderData(0,Qt.Horizontal,"编号") self.queryModel.setHeaderData(1,Qt.Horizontal,"姓名") self.queryModel.setHeaderData(2,Qt.Horizontal,"性别") self.queryModel.setHeaderData(3,Qt.Horizontal,"年龄") self.queryModel.setHeaderData(4,Qt.Horizontal,"院系") # 得到记录数 def getTotalRecordCount(self): self.queryModel.setQuery("select * from student") rowCount = self.queryModel.rowCount() print('rowCount=' + str(rowCount) ) return rowCount # 得到页数 def getPageCount(self): if self.totalRecrodCount % self.PageRecordCount == 0 : return (self.totalRecrodCount / self.PageRecordCount ) else : return (self.totalRecrodCount / self.PageRecordCount + 1) # 记录查询 def recordQuery(self, limitIndex ): szQuery = ("select * from student limit %d,%d" % ( limitIndex , self.PageRecordCount ) ) print('query sql=' + szQuery ) self.queryModel.setQuery(szQuery) # 刷新状态 def updateStatus(self): szCurrentText = ("当前第%d页" % self.currentPage ) self.currentPageLabel.setText( szCurrentText ) #设置按钮是否可用 if self.currentPage == 1 : self.prevButton.setEnabled( False ) self.nextButton.setEnabled( True ) elif self.currentPage == self.totalPage : self.prevButton.setEnabled( True ) self.nextButton.setEnabled( False ) else : self.prevButton.setEnabled( True ) self.nextButton.setEnabled( True ) # 设置总数页文本 def setTotalPageLabel(self): szPageCountText = ("总共%d页" % self.totalPage ) self.totalPageLabel.setText(szPageCountText) # 设置总总记录数 def setTotalRecordLabel(self): szTotalRecordText = ("共%d条" % self.totalRecrodCount ) print('*** setTotalRecordLabel szTotalRecordText=' + szTotalRecordText ) self.totalRecordLabel.setText(szTotalRecordText) # 前一页按钮按下 def onPrevButtonClick(self): print('*** onPrevButtonClick '); limitIndex = (self.currentPage - 2) * self.PageRecordCount self.recordQuery( limitIndex) self.currentPage -= 1 self.updateStatus() # 后一页按钮按下 def onNextButtonClick(self): print('*** onNextButtonClick '); limitIndex = self.currentPage * self.PageRecordCount self.recordQuery( limitIndex) self.currentPage += 1 self.updateStatus() # 转到页按钮按下 def onSwitchPageButtonClick(self): # 得到输入字符串 szText = self.switchPageLineEdit.text() #数字正则表达式 pattern = re.compile(r'^[-+]?[0-9]+\.[0-9]+$') match = pattern.match(szText) # 判断是否为数字 if not match : QMessageBox.information(self, "提示", "请输入数字" ) return # 是否为空 if szText == '' : QMessageBox.information(self, "提示" , "请输入跳转页面" ) return #得到页数 pageIndex = int(szText) #判断是否有指定页 if pageIndex > self.totalPage or pageIndex < 1 : QMessageBox.information(self, "提示", "没有指定的页面,请重新输入" ) return #得到查询起始行号 limitIndex = (pageIndex-1) * self.PageRecordCount #记录查询 self.recordQuery(limitIndex); #设置当前页 self.currentPage = pageIndex #刷新状态 self.updateStatus();
model.setHeaderData(0, Qt.Horizontal, "Серія") model.setHeaderData(1, Qt.Horizontal, "Проміжна") model.setHeaderData(2, Qt.Horizontal, "Готова") def createView(title, model): view = QTableView() view.setModel(model) view.setWindowTitle(title) return view if __name__ == '__main__': import sys app = QApplication(sys.argv) if not createConnection(): sys.exit(1) # model = QSqlTableModel() model = QSqlQueryModel() model.setQuery("SELECT Id, PrepData, ReadyData FROM PData") initializeModel(model) view1 = createView("Table Model (View 1)", model) view1.show() sys.exit(app.exec_())
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)
from PyQt5.QtSql import QSqlQueryModel,QSqlDatabase,QSqlQuery from PyQt5.QtWidgets import QTableView,QApplication import sys app = QApplication(sys.argv) db = QSqlDatabase.addDatabase("QSQLITE") db.setDatabaseName("patientData.db") db.open() projectModel = QSqlQueryModel() projectModel.setQuery("select * from patient",db) projectView = QTableView() projectView.setModel(projectModel) projectView.show() app.exec_()