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
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()
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 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 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!")
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 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 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 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 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 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 __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 carregarTableByEditora(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 editoras.editora 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()
def setData(self, index, value, role): if index.column() != 2: return False primaryKeyIndex = QSqlQueryModel.index(self, index.row(), 1) id = self.data(primaryKeyIndex) self.clear() ok = self.setState(id, str(value)) self.refresh() return ok
def nozzleInit(parent): parent.nozzleMapper = QDataWidgetMapper(parent) parent.nozzleModel = QSqlQueryModel(parent) thickness = parent.thicknessLbl.text() parent.nozzleModel.setQuery("SELECT DISTINCT nozzle FROM cut_chart \ WHERE thickness = '{}'".format(thickness)) parent.nozzleMapper.setModel(parent.nozzleModel) parent.nozzleMapper.addMapping(parent.nozzleLbl, 0, b'text') parent.nozzleMapper.toLast() parent.nozzleLast = parent.nozzleMapper.currentIndex() parent.nozzleMapper.toFirst()
def Edit(self): edtxt, edok = QInputDialog.getText( self, 'Редактирование', 'Введите ID препарата, который вы хотите изменить:') if edok: text1, ok1 = QInputDialog.getText(self, 'Редактирование', 'Введите название препарата. ') if ok1: text2, ok2 = QInputDialog.getText( self, 'Редактирование', 'Введите описание препарата. ') if ok2: text3, ok3 = QInputDialog.getText( self, 'Редактирование', 'Введите цену препарата. ') if ok3: db = QtSql.QSqlDatabase.addDatabase('QSQLITE') db.setDatabaseName("db.db") db.open() queryAdd = QSqlQuery() queryAdd.prepare( "UPDATE medications SET mName=( ? ), mDescription=( ? ), mPrice=( ? ) WHERE mId=(?) " ) queryAdd.bindValue(0, text1) queryAdd.bindValue(1, text2) queryAdd.bindValue(2, text3) queryAdd.bindValue(3, edtxt) queryAdd.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 queryCache(self, SPath, manageLocalStorage): projectModel = QSqlQueryModel() if self.db.open(): query = QSqlQuery(self.db) # print("in queryCache") # print(SPath) query.prepare( "select SPathRec, Title, Artist from cache WHERE SPath=:SPath " ) query.bindValue(":SPath", SPath) query.exec_() projectModel.setQuery(query) # print("num of row returned queryCache:") # print(query.numRowsAffected()) # print("query row count") if projectModel.rowCount() == 0: print("get recommendation, nothing is in Cache for this song") recommendedSongsPathList = [] getRecom = GetRecommendation(manageLocalStorage) relevantSongDict = getRecom.fetchRelevantSongOffline(SPath) if SPath: recommendedSongsPathList = getRecom.predict( SPath, relevantSongDict) else: print("problem with SongPath so can't call predict") # if playing song is also recommended then remove it if SPath in recommendedSongsPathList: recommendedSongsPathList.remove(SPath) # for item in recommendedSongsPathList: # print(item) # build cache if self.writeCache(recommendedSongsPathList, SPath): print("re Querying ") projectModel.clear() requery = QSqlQuery(self.db) requery.prepare( "select SPathRec, Title, Artist from cache WHERE SPath=:SPath " ) requery.bindValue(":SPath", SPath) requery.exec_() projectModel.setQuery(requery) # print("row Count After re query ") # print(projectModel.rowCount()) else: print("Query failed") return projectModel
class sdb: def __init__(self): self.db = QSqlDatabase.addDatabase('QMYSQL') self.model = QSqlQueryModel() # 1 self.connect() def connect(self): # print(QSqlDatabase.drivers()) gl._init() self.db.setHostName(gl.get_value('HOSTNAME')) self.db.setPort(gl.get_value('PORT')) self.db.setDatabaseName(gl.get_value('DBNAME')) self.db.setUserName(gl.get_value('USRNAME')) self.db.setPassword(gl.get_value('PWD')) # self.db.setHostName('111.231.82.68') # self.db.setPort(8000) # self.db.setDatabaseName('suggestion') # self.db.setUserName('pentairSuggest') # self.db.setPassword('Zhao.jiayun_0217') if not self.db.open(): print(self.db.lastError().text()) def close(self): self.db.close() print("db closed") def exec(self, sql): self.model.setQuery(sql) return self.model def query(self, sql): query = QSqlQuery(self.db) query.exec(sql) return query def querycheck(self, sql): query = QSqlQuery(self.db) return query.exec(sql)
def setupUi(self, MainWindow): MainWindow.setObjectName("MainWindow") MainWindow.resize(800, 600) self.centralwidget = QtWidgets.QWidget(MainWindow) self.centralwidget.setObjectName("centralwidget") self.verticalLayout = QtWidgets.QVBoxLayout(self.centralwidget) self.verticalLayout.setObjectName("verticalLayout") self.tv = tableView = QtWidgets.QTableView(self.centralwidget) self.tv.setObjectName("tableView") self.verticalLayout.addWidget(self.tv) MainWindow.setCentralWidget(self.centralwidget) self.model = None db = QSqlDatabase.addDatabase("QSQLITE") db.setDatabaseName("test.db") if db.open(): print("open DB success.") query = QSqlQuery() query.prepare("insert into user ('nom','prenom' )values ('Bauer', 'Jack')") # query.exec_("create table person(id int primary key, name varchar(20), address varchar(30))") query.exec() # query.exec_("insert into user values('Jack', 'shanghai')") # query.exec_("insert into user values('Alex', 'chengdu')") # query.prepare("SELECT * FROM user") # query.exec() self.model = QSqlQueryModel() self.model.setQuery("SELECT * FROM user LIMIT 10,10") self.model.setHeaderData(0,QtCore.Qt.Horizontal,"Nom") self.model.setHeaderData(1,QtCore.Qt.Horizontal,"Prénom") self.model.setHeaderData(2,QtCore.Qt.Horizontal,"ID") # self.tv.setHorizontalHeaderLabels(QString("Nom;Prénom;ID").split(";")) # self.model.setHeaderData(1, Qt.Horizontal, "Prénom") self.tv.setModel(self.model) self.model.setQuery("SELECT * FROM user LIMIT 5") # self.tv.setModel(self.model) db.close() self.retranslateUi(MainWindow) QtCore.QMetaObject.connectSlotsByName(MainWindow)
def setTableView(self): print('*** step2 SetTableView') self.db = QSqlDatabase.addDatabase('QMYSQL') self.db.setHostName("67.209.xxx.xxx") self.db.setDatabaseName("db_sharedbike") self.db.setUserName("root") self.db.setPassword("xxxxxxxxx") # 打开数据库 self.db.open() # 声明查询模型 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 thicknessInit(parent): parent.thicknessMapper = QDataWidgetMapper(parent) parent.thicknessModel = QSqlQueryModel(parent) material = parent.materialLbl.text() parent.thicknessModel.setQuery("SELECT DISTINCT gauge FROM cut_chart \ WHERE material = '{}'".format(material)) parent.thicknessMapper.setModel(parent.thicknessModel) parent.thicknessMapper.addMapping(parent.thicknessLbl, 0, b'text') parent.thicknessMapper.toLast() parent.thicknessLast = parent.thicknessMapper.currentIndex() parent.thicknessMapper.toFirst() nozzleInit(parent)
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
def do_browse(self): if self.first_run: return model = QSqlQueryModel() filterStr = "" if not self.first_run and self.browsed_continent_code is not None: filterStr = f"WHERE countries.continent_code = '{self.browsed_continent_code}'" if self.browsed_country_code is not None: filterStr = f"{filterStr} AND runways.country_code = '{self.browsed_country_code}'" if self.browsed_region_code is not None: filterStr = f"{filterStr} AND runways.region_code = '{self.browsed_region_code}'" sql = f''' SELECT runways.airport_code as airport_code, runways.location as location, runways.airport_name as airport_name, all_airports.municipality as municipality, regions.name as region_name, countries.name as country_name FROM runways INNER JOIN all_airports ON all_airports.code = runways.airport_code INNER JOIN regions ON all_airports.region_code = regions.code INNER JOIN countries ON countries.code = regions.country_code INNER JOIN continents ON continents.code = countries.continent_code {filterStr} ORDER BY runways.airport_code, runways.location; ''' model.setQuery(sql) self.ui.tableView.setModel(model) self.set_table_col_sizes()
def updateAllPage(self, *args): if args == ([], [], []) or args == (): self.query.exec( "SELECT FILENAME FROM FileLibrary WHERE SUFFIX != ('{}')". format("")) #如果是目录,会被排除掉 model = QSqlQueryModel() model.setQuery(self.query) self.allFileTab.setModel(model) else: core.FileOperator.unionQuery(self.query, args[0], args[1], args[2]) model = QSqlQueryModel() model.setQuery(self.query) self.allFileTab.setModel(model)
def 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 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 _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
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 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 loaddatatv(self, text): try: connString = "DRIVER={ODBC Driver 17 for SQL Server};SERVER=" + server + ";DATABASE=" + database + ";UID=" + user + ";PWD=" + pwd db = QSqlDatabase.addDatabase('QODBC') db.setDatabaseName(connString) db.open() qry = QSqlQuery(db) qry.prepare("select * from LDA2 where Nom like '%" + text + "%' or Prénom like '%" + text + "%' or NuméroDemandeurEmploi like '%" + text + "%'") qry.exec() model = QSqlQueryModel() model.setQuery(qry) self.tvDatas.setModel(model) self.tvDatas.resizeColumnsToContents() self.tvDatas.horizontalHeader().setStyleSheet( "QHeaderView::section {background-color:#5B9BD5;color:white}") self.tvDatas.setShowGrid(True) self.tvDatas.verticalHeader().setVisible(False) self.tvDatas.setAlternatingRowColors(True) self.tvDatas.setStyleSheet( "alternate-background-color: #DDEBF7; background:white;border: 1px solid black;" ) self.tvDatas.show() except pyodbc.Error as err: msg = QMessageBox() msg.setWindowTitle("LDA") msg.setText(err.__str__()) msg.setStandardButtons(QMessageBox.Ok | QMessageBox.Cancel) r = msg.exec_() if r == QMessageBox.Ok: msg.close()
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.ui.statusBar.showMessage("记录条数:%d" % self.qryModel.rowCount()) 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.mapper = QDataWidgetMapper() self.mapper.setModel(self.qryModel) self.mapper.addMapping(self.ui.dbSpinEmpNo, 0) self.mapper.addMapping(self.ui.dbEditName, 1) self.mapper.addMapping(self.ui.dbComboSex, 2) self.mapper.addMapping(self.ui.dbEditBirth, 3) self.mapper.addMapping(self.ui.dbComboProvince, 4) self.mapper.addMapping(self.ui.dbComboDep, 5) self.mapper.addMapping(self.ui.dbSpinSalary, 6) self.mapper.toFirst() 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)
def threadClassInit(parent): parent.classMapper = QDataWidgetMapper(parent) parent.classModel = QSqlQueryModel(parent) form = parent.threadFormLbl.text() classSelect = "SELECT DISTINCT class FROM internal_threads \ WHERE form = '{}'".format(form) parent.classModel.setQuery(classSelect) parent.classMapper.setModel(parent.classModel) parent.classMapper.addMapping(parent.threadClassLbl, 0, b'text') parent.classMapper.toLast() parent.classLast = parent.classMapper.currentIndex() parent.classMapper.toFirst() threadSizeInit(parent)
def rtSizeInit(parent): parent.rtSizeMapper = QDataWidgetMapper(parent) parent.rtSizeModel = QSqlQueryModel(parent) form = parent.rtFormLbl.text() classSelect = "SELECT DISTINCT size FROM tap \ WHERE form = '{}'".format(form) parent.rtSizeModel.setQuery(classSelect) parent.rtSizeMapper.setModel(parent.rtSizeModel) parent.rtSizeMapper.addMapping(parent.rtSizeLbl, 0, b'text') parent.rtSizeMapper.toLast() parent.rtSizeLast = parent.rtSizeMapper.currentIndex() parent.rtSizeMapper.toFirst() rtInfoInit(parent)
def setTableView(self): print('*** step2 SetTableView') self.db = QSqlDatabase.addDatabase('QSQLITE') # 设置数据库名称 self.db.setDatabaseName('data/all.db') # 打开数据库 self.db.open() # 声明查询模型 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, "员工工号") self.queryModel.setHeaderData(5, Qt.Horizontal, "消费商品金额") self.tableView.setWordWrap(True)
def setMatplotView(self): print('*** step2 setMatplotView') # self.db = QSqlDatabase.addDatabase('QSQLITE') # # 设置数据库名称 # self.db.setDatabaseName('./db/database.db') # # 打开数据库 # self.db.open() # 声明查询模型 # 加载所有曲线数据 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 setTableView(self): print('*** step2 SetTableView') self.db = QSqlDatabase.addDatabase('QMYSQL') # 设置数据库名称 self.db.setDatabaseName('pyqttest') self.db.setUserName("root") self.db.setPassword("123456") # 打开数据库 self.db.open() # 声明查询模型 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 open_db(self): db_file, filter = \ QFileDialog.getOpenFileName(self, "Open Database", ".", "All files (*);;SQLite databases (*.db)") conn = sqlite3.connect(db_file) curs = conn.cursor() curs.execute("select name from sqlite_master where type='table'") tabs = QTabWidget() db = QSqlDatabase.addDatabase('QSQLITE') db.setDatabaseName(db_file) db.open() for table_data in curs: table_name = table_data[0] 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) self.setCentralWidget(tabs)
def update_dsc(self, thing, dsc): query = QSqlQueryModel() query.setQuery("UPDATE things "\ "SET dsc = '{}' "\ "WHERE id={}" .format(dsc, thing.idx)) if query.lastError().isValid(): print(query.lastError().text(), file=sys.stderr)
def __init__(self, cesta_nazvu): super().__init__() self.setupUi(self) self.cesta_nazvu = cesta_nazvu # # nacte nazev aktualniho projektu ze souboru "nazev.txt" # with open(cesta_nazvu) as n: # nazev=n.readlines() # # nazev=nazev[0] # cesta_inv=cesta_nazvu[::-1] #invertuje cestu # pozice=cesta_inv.find('/') #najde poradi lomitka # cesta_konecna=cesta_nazvu[0:len(cesta_nazvu)-pozice] #udela cestu adresare bez nazvu souboru # # databaze=cesta_konecna+nazev #vytvori cestu+nazev databaze #otevreni databaze db1 = QSqlDatabase.addDatabase("QSQLITE", "db1") # db = QSqlDatabase.addDatabase("") db1.setDatabaseName(cesta_nazvu) db1.open() # vytvori model databaza a nacte data projectModel1 = QSqlQueryModel() # projectModel.setQuery('select Stanovisko,Orientace, Delka,Zenitka, Smer, Kod from gps_sour',db) projectModel1.setQuery( 'select Stanovisko,Orientace,Delka,Zenitka,Smer, Kod from mereni', db1) self.tableView.setModel(projectModel1) # self.tableView.setColumnWidth(1,5) db1.close() del projectModel1 del db1 QSqlDatabase.removeDatabase("db1") self.show() self.exec()
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
def setModel(self, model: QtSql.QSqlQueryModel): if len(self._list) == 0: raise Exception("显示列集合为空,请先使用append()方法添加列信息!") if not isinstance(model, QtSql.QSqlQueryModel): raise Exception("model参数必须为QSqlQueryModel或其子类的实例!") self.__model = model record = model.record() for vc in self._list: at = vc.fieldIndex if at != -1: field = record.field(at) vc.jpFieldType = getFieldType(self.db, field.typeID()) if vc.formatString is None: self._setViewColumnAlighAndFormatString(vc)
def __init__(self, parent=None): super().__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.model = QSqlQueryModel(self) self.model.setQuery('SELECT * FROM test') 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 sptmSizeInit(parent): parent.sptmMapper = QDataWidgetMapper(parent) parent.sptmModel = QSqlQueryModel(parent) parent.sptmModel.setQuery('SELECT * FROM sptm') parent.sptmMapper.setModel(parent.sptmModel) parent.sptmMapper.addMapping(parent.sptmSizeLbl, 0, b'text') parent.sptmMapper.addMapping(parent.sptmDiaLbl, 1, b'text') parent.sptmMapper.addMapping(parent.sptmCrestLbl, 2, b'text') parent.sptmMapper.addMapping(parent.sptmMaxDepthLbl, 3, b'text') parent.sptmMapper.addMapping(parent.sptmFlutesLbl, 4, b'text') parent.sptmMapper.addMapping(parent.sptmNeckDiaLbl, 5, b'text') parent.sptmMapper.toLast() parent.sptmLast = parent.sptmMapper.currentIndex() parent.sptmMapper.toFirst()
def Nexpired2(self): db = QtSql.QSqlDatabase.addDatabase('QSQLITE') db.setDatabaseName("db.db") db.open() qry = QSqlQuery() now = datetime.datetime.now() Date = now.strftime("%Y-%m-%d") DateNow = str(Date) qry.prepare( "SELECT bDateEx AS 'Срок годности', bId AS 'ID партии', mName AS 'Название' FROM batch, medications WHERE bDateEx > ( ? ) and bDateS is null and b_mId= mId ORDER BY bDateEx ASC" ) qry.bindValue(0, DateNow) qry.exec() tabmodel = QSqlQueryModel() self.ui.tableView.setModel(tabmodel) tabmodel.setQuery(qry) #self.ui.tableView.setColumnWidth( 2, 310 ) header = self.ui.tableView.horizontalHeader() Lheader = self.ui.tableView.verticalHeader() Lheader.setVisible(False) header.setStretchLastSection(True) self.ui.tableView.show db.close()
def thing_by_id(self, idx): query = QSqlQueryModel() query.setQuery("select * from things "\ "where id={}"\ .format(idx), db=self.db) if query.rowCount() == 0: return None return self.thing_by_sqlrecord(query.record(0))
def thing_by_name(self, name): query = QSqlQueryModel() query.setQuery("select * from things "\ "where name='{}'"\ .format(name), db=self.db) if query.rowCount() == 0: return None return self.thing_by_sqlrecord(query.record(0))
def populateFilmCombo(self, filmNumber=None): editor = self.uiFilmNumberCombo model = QSqlQueryModel(self) model.setQuery( "SELECT DISTINCT {0} FROM {1} ORDER BY {2}".format( 'filmnummer', 'film', 'filmnummer'), self.dbm.db) tv = QTableView() editor.setView(tv) tv.setHorizontalScrollBarPolicy(Qt.ScrollBarAsNeeded) tv.setSelectionMode(QAbstractItemView.SingleSelection) tv.setSelectionBehavior(QAbstractItemView.SelectRows) tv.setAutoScroll(False) editor.setModel(model) editor.setModelColumn(0) 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) if filmNumber: editor.setCurrentIndex(editor.findText(filmNumber)) self.populateAvailableImagesCombo() else: editor.setCurrentIndex(-1) editor.currentIndexChanged.connect(self.populateAvailableImagesCombo)
def setData(self, index, value, role=Qt.EditRole): if role == Qt.EditRole: mycolumn = index.column() if mycolumn in self.editables: (query, filter_cols) = self.editables[mycolumn] values = [value] for col in filter_cols: filter_value = self.index(index.row(), col).data() values.append(filter_value) q = QSqlQuery(query.format(*values)) result = q.exec_() if result: self.query().exec_() else: print(self.query().lastError().text()) return result return QSqlQueryModel.setData(self, index, value, role)
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 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 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 flags(self, index): flags = QSqlQueryModel.flags(self, index) if index.column() == 2: flags |= Qt.ItemIsEditable return flags
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 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 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 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_())