def querproduct(self): self.ui.label_21.setText('') self.ui.label_22.setText('') self.ui.label_24.setText('') self.ui.label_26.setText('') self.ui.label_28.setText('') productno = self.ui.comboBox_2.currentText() prodtype = 0 if self.ui.radioButton_5.isChecked(): prodtype = u'良' if self.ui.radioButton_6.isChecked(): prodtype = u'不良' #rs=dbconn.quer(productno,prodtype) #print rs model = QtSql.QSqlQueryModel() sql = u"select prodctuno 编号,time as 时间,prodtype as 良否,qty as 数量,mark as 备注,\ recordman as 记录人,place as 库位 from record \ where prodctuno='%s' and prodtype='%s'" % (productno, prodtype) model.setQuery(sql) self.ui.tableView_3.setModel(model) model2 = QtSql.QSqlQueryModel() sql = "select sum(qty) from record where prodctuno='%s' and prodtype='%s'" % ( productno, prodtype) sql1 = "select sum(qty) from record where prodctuno='%s'" % (productno) sql2 = "select sum(qty) from outrecord where prodctuno='%s' and prodtype='%s'" % ( productno, prodtype) sql3 = "select sum(qty) from outrecord where prodctuno='%s'" % ( productno) model2.setQuery(sql) record = model2.record(0) self.ui.label_21.setText(record.value('sum(qty)').toString()) model2.setQuery(sql1) record = model2.record(0) self.ui.label_24.setText(record.value('sum(qty)').toString()) model2.setQuery(sql3) record = model2.record(0) self.ui.label_22.setText(record.value('sum(qty)').toString()) model2.setQuery(sql2) record = model2.record(0) self.ui.label_26.setText(record.value('sum(qty)').toString()) model1 = QtSql.QSqlQueryModel() sql = u"select prodctuno as 编号,time as 时间,prodtype as 良否,-qty as 数量,mark as 备注,\ recordman as 记录人,lingyongman as 领用人,place as 库位 \ from outrecord where prodctuno='%s' and prodtype='%s'" % (productno, prodtype) model1.setQuery(sql) self.ui.tableView_4.setModel(model1) if self.ui.label_21.text() != '' and self.ui.label_26.text() != '': self.ui.label_28.setText( str( int(self.ui.label_21.text()) - int(self.ui.label_26.text()))) elif self.ui.label_21.text() != '' and self.ui.label_26.text() == '': self.ui.label_28.setText(str(int(self.ui.label_21.text())))
def search(self): self.model = QtSql.QSqlQueryModel(self) a = self.ui.cxb.isChecked() if a == True: a = "y" else: a = "A" print(a) dir = self.ui.lne_dir.text() main = self.ui.lne_main.text() format = self.ui.cb_format.currentText() format = format[0] format = str(format) genre = self.ui.cb_genre.currentText() genre = genre[0] genre = str(genre) lang = self.ui.cb_lang.currentText() lang = lang[0] lang = str(lang) query = "select * from movie where \ (lang = '" + lang + "' or '" + lang + "' = 'A')\ and (genre = '" + genre + "' or '" + genre + "' = 'A')\ and(format = '" + format + "' or '" + format + "' = 'A')\ and(available = '" + a + "' or '" + a + "' = 'A')\ and(director = '" + dir + "' or '" + dir + "' = '')\ and(main_lead = '" + main + "' or '" + main + "' = '')" self.model.setQuery(query) self.ui.tableView.setModel(self.model)
def __init__(self, parent=None): QtGui.QMainWindow.__init__(self) self.ui = MainWindow.Ui_MainWindow() self.ui.setupUi(self) self.db = QtSql.QSqlDatabase.addDatabase("QMYSQL") self.db.setHostName("localhost") self.db.setDatabaseName("dancebook") self.db.setUserName("root") self.db.setPassword("") self.db.open() self.model = QtSql.QSqlQueryModel() self.model.setQuery("SELECT name FROM dance", self.db) self.ui.dance_form_table.setModel(self.model) self.ui.dance_form_table.clicked.connect(self.selcon) self.ui.dance_form_table.setMaximumWidth(120) self.ui.plainTextEdit.setStyleSheet(""" border: 2px solid black; background-color: #773467; color : #56DD35; """) self.o = My_Thread(self) #objest of My_t=Thread class self.o.start() # for starting the thread
def InitUserPage(self): self.tmUser = QtSql.QSqlQueryModel(self) self.tmUser.setQuery('SELECT username FROM user WHERE(ID>0)ORDER BY id DESC') self.ui.tableUser.setModel(self.tmUser) self.ui.tableUser.horizontalHeader().setResizeMode(0,QHeaderView.Stretch) self.ui.tableUser.verticalHeader().hide() self.ui.tableUser.setSelectionMode(QAbstractItemView.SingleSelection)
def __init__(self): QtGui.QMainWindow.__init__(self) self.ventana = Ui_MainWindow() self.ventana.setupUi(self) self.model = QtSql.QSqlQueryModel(self) #Create Objects self.adding = adding() self.searching = searching() self.modifying = modifying() self.deleting = deleting() #TableView Initial self.model.setQuery( "SELECT nombre, ap_paterno, ap_materno, edad, fecha_registro, grado, grupo FROM alumno JOIN escolares USING(id_alumno);" ) self.view = self.ventana.tableView self.view.setModel(self.model) #Toolbar SIGNAL's self.connect(self.ventana.actionAgregar, QtCore.SIGNAL('activated()'), self.agregar) self.connect(self.ventana.actionBuscar, QtCore.SIGNAL('activated()'), self.buscar) self.connect(self.ventana.actionEditar, QtCore.SIGNAL('activated()'), self.editar) self.connect(self.ventana.actionBorrar, QtCore.SIGNAL('activated()'), self.borrar) #Query SIGNAL's self.connect(self.adding, QtCore.SIGNAL('agregado'), self.insertar) self.connect(self.searching, QtCore.SIGNAL('buscado'), self.consultar) self.connect(self.modifying, QtCore.SIGNAL('editado'), self.actualizar) self.connect(self.deleting, QtCore.SIGNAL('borrado'), self.eliminar)
def add_gridpending(self): gridpending = QtGui.QGridLayout() self.db_table = QtGui.QTableView(self) self.model = QtSql.QSqlQueryModel() self.model.setQuery("SELECT * FROM sale") self.db_table.setModel(self.model) #self.db_table.hideColumn(0) # hide column 'id' self.db_table.hideColumn(6) # self.db_table.hideColumn(7) # self.db_table.hideColumn(9) self.db_table.hideColumn(10) # hide column 'status' #self.db_table. self.db_table.setSelectionBehavior( QtGui.QAbstractItemView.SelectRows) # select Row self.db_table.setSelectionMode( QtGui.QAbstractItemView.SingleSelection) # disable multiselect self.db_table.horizontalHeader().setResizeMode( QtGui.QHeaderView.Stretch) gridpending.addWidget(self.db_table, 0, 0, 1, 5) done_btn = QtGui.QPushButton("Done") done_btn.resize(done_btn.sizeHint()) gridpending.addWidget(done_btn, 1, 4) #done_btn.clicked.connect(self.submit_from_pending) cancel_btn = QtGui.QPushButton("Cancel") cancel_btn.resize(cancel_btn.sizeHint()) gridpending.addWidget(cancel_btn, 1, 3) cancel_btn.clicked.connect(self.cancel_from_pending) self.grid.addLayout(gridpending, 4, 0, 8, 2) ''' self.view = QtGui.QTableView()
def get_dates_from_event_room_model(self, event, room): """Gets the Dates Model. Useful for Qt GUI based Frontends to load the Model into Views.""" self.datesModel = QtSql.QSqlQueryModel() self.datesModel.setQuery( "SELECT DISTINCT date FROM presentations WHERE Event='%s' and Room='%s' ORDER BY Date ASC" % (event, room)) return self.datesModel
def __init__(self, header, tablename, datatypes, asOfDate, datamodel=None, menu=False, parent=None): # data types: s: string, d: date, t: datetime, f: float, i: int super(ViewBase, self).__init__(parent) self.header = header self.tablename = tablename self.datatypes = datatypes self.asOfDate = asOfDate self.build_query() self.dataModel = datamodel or QtSql.QSqlQueryModel() self.dataModel.setQuery(self.query) for i, h in enumerate(self.header): self.dataModel.setHeaderData(i, QtCore.Qt.Horizontal, h) self.proxyModel = QtGui.QSortFilterProxyModel() self.proxyModel.setDynamicSortFilter(True) self.proxyModel.setSourceModel(self.dataModel) self.setModel(self.proxyModel) self.verticalHeader().hide() self.resizeColumnsToContents() #self.resizeRowsToContents() self.setSortingEnabled(True) self.setAlternatingRowColors(True) self.setSelectionBehavior(QtGui.QTableView.SelectRows) if menu: self.setContextMenuPolicy(QtCore.Qt.CustomContextMenu) self.customContextMenuRequested.connect(self.showRightClickMenu) self.buildMenu()
def listar_cerrados(self): self.cursor = self.con.cursor() self.model = QtSql.QSqlQueryModel() self.model.setQuery( 'SELECT ST.N_TICKET AS "N° TICKET",ST.N_S_FK AS "NUMERO DE SERIE",ST.MOTIVO,ST.ESTADO,ST.FECHA_CONIG FROM ST WHERE ST.ESTADO = "Caso Cerrado"' ) self.ui.comboBox.clear() self.ui.comboBox.addItems([ self.model.headerData(x, QtCore.Qt.Horizontal) for x in range(self.model.columnCount()) ]) ##Llena el combobox con el nombre de las columnas self.filter_proxy_model.setSourceModel(self.model) self.ui.Edit_Filtro.textChanged.connect(self.on_lineEdit_textChanged) self.ui.comboBox.currentIndexChanged.connect( self.on_comboBox_currentIndexChanged) self.model_sele = QtGui.QItemSelectionModel(self.filter_proxy_model) self.ui.tableWidget.setModel(self.filter_proxy_model) self.ui.tableWidget.setSelectionModel(self.model_sele) self.con.commit() self.ticket = str( self.filter_proxy_model.data(self.filter_proxy_model.index(0, 0))) self.model_sele.selectionChanged.connect( self.fila_diferente_seleccionada) #Fila Seleccionada self.ui.btn_Lista_Cerrados.setText("Listar Casos no resueltos") self.ui.btn_Alta.setEnabled(False) #self.ui.btn_Modificar.setEnabled(False) self.ui.btn_BorrarRegistro.setEnabled(False) self.ui.btn_Cambiar_Estado.setEnabled(False) QtCore.QObject.connect(self.ui.btn_Lista_Cerrados, QtCore.SIGNAL('clicked()'), self.Completar_lista_alumnos)
def _slotSearchClicked(self): search_coords = [ float(d.strip()) for d in self.ui.coords.text().split(",") ] qm = QtSql.QSqlQueryModel() # DB type, host, user, password... db = QtSql.QSqlDatabase.addDatabase("QPSQL") db.setHostName("lsnpgt01") db.setDatabaseName("voiriegeo") db.setUserName("voiriegeo_adm") db.setPassword("Ls3Qt6msjmok") ok = db.open() # True if connected if ok: print 'Connected to Postgresql' else: print 'Postgresql' # do a query "on" a DB connection query = self.query % (self.ui.table.currentText(), search_coords[0], search_coords[1], self.ui.distance.value()) print query qm.setQuery(query) self.ui.results.setModel(qm) db.close()
def get_rooms_model(self, event): """Gets the Rooms Model. Useful for Qt GUI based Frontends to load the Model into Views""" self.roomsModel = QtSql.QSqlQueryModel() self.roomsModel.setQuery( "SELECT DISTINCT Room FROM presentations WHERE Event='%s' ORDER BY Room ASC" % event) return self.roomsModel
def student(self): db=QtSql.QSqlDatabase.addDatabase("QSQLITE") db.setDatabaseName("feedback.db") db.open() self.model = QtSql.QSqlQueryModel(self) self.model.setQuery("select b.batch_id as ID, c.course_name as 'Name', b.batch_start_date as 'Start Date', b.batch_day as 'Day', b.batch_time as 'Time' from batch b, courses c where b.course_id = c.course_id") self.ui.tableView.setModel(self.model)
def courses_in_metacourse(self, metacourse): self.courses_in_metacourse_model = QtSql.QSqlQueryModel() self.courses_in_metacourse_model.setQuery( QtCore.QString(""" SELECT course.fullname FROM course_meta JOIN course ON course_meta.parent_course = course.id WHERE course_meta.child_course = %1; """).arg(metacourse)) self.ui.listView_3.setModel(self.courses_in_metacourse_model)
def get_address_matchmodel(self, search_values): ''' get's a list of addresses who's criteria match a user search ''' query = '''SELECT ix, addr1, addr2, addr3, city, county, country, postal_cd from addresses WHERE ''' conds, values = '', [] address_id = search_values.get("address_id") if address_id: conds += "ix = ?" values.append(address_id) else: addr = search_values.get("addr1") if addr: values.append(u"%%%s%%"% addr) values.append(u"%%%s%%"% addr) values.append(u"%%%s%%"% addr) conds += "(addr1 like ? or addr2 like ? or addr3 like ?) and " addr = search_values.get("addr2") if addr: values.append(u"%%%s%%"% addr) values.append(u"%%%s%%"% addr) values.append(u"%%%s%%"% addr) conds += "(addr1 like ? or addr2 like ? or addr3 like ?) and " city = search_values.get("city") if city: conds += 'city like ? ' values.append("%"+city+"%") country = search_values.get("country") if country: conds += 'country like ? ' values.append("%"+country+"%") pcde = search_values.get("postal_cd") if pcde: conds += 'postal_cd like ? ' values.append("%"+pcde+"%") query = query + conds.rstrip("and ") q_query = QtSql.QSqlQuery(self) q_query.prepare(query) for value in values: q_query.addBindValue(value) if not q_query.exec_(): print "error with query", query self.emit_caught_error(q_query.lastError()) model = QtSql.QSqlQueryModel() model.setQuery(q_query) return model
def get_events_model(self): """ Gets the Events Model. Useful for Qt GUI based Frontends to load the Model into Views. """ self.eventsModel = QtSql.QSqlQueryModel() self.eventsModel.setQuery("SELECT DISTINCT Event FROM presentations ORDER BY Event ASC") return self.eventsModel
def curall(self): model = QtSql.QSqlQueryModel(self) sql = u''' select total_all.prodctuno as 编号,productlist.productname as 名称,\ total_all.total as 总数,total_all.ok as 良品,total_all.bad as 不良 from total_all,productlist where total_all.prodctuno=productlist.prodcutno; ''' model.setQuery(sql) self.ui.tableView_8.setModel(model)
def meta_courses_list(self): self.meta_course_model = QtSql.QSqlQueryModel() self.meta_course_model.setQuery( "SELECT id, fullname FROM course WHERE metacourse=0 AND id != 1;", self.db) self.ui.listView_5.setModel(self.meta_course_model) self.ui.listView_5.setModelColumn(1) self.ui.comboBox_2.setModel(self.meta_course_model) self.ui.comboBox_2.setModelColumn(1)
def student(self): db = QtSql.QSqlDatabase.addDatabase("QSQLITE") db.setDatabaseName("feedback.db") db.open() self.model = QtSql.QSqlQueryModel(self) self.model.setQuery( 'select st_id as "ID", st_full_name as "Name", st_parent_name as "Parent' + "'s Name" + '", st_address_1 as "Address" from student;') self.ui.tableView.setModel(self.model)
def __init__(self, application): QtGui.QMainWindow.__init__(self) self._filename = None self.application = application self.database_menu = database.DatabaseMenu(application.settings) self.query_executer = QueryExecuter(self) self.query_model = QtSql.QSqlQueryModel(self) self.statusbar_updater = QtCore.QTimer() self.setupUi() self.setupConnections()
def load_in_process(self): qry = query("in_process_orders") if qry: in_process_mod = QtSql.QSqlQueryModel() in_process_mod.setQuery(qry) self.in_process_table.setModel(in_process_mod) self.in_process_table.resizeColumnsToContents() return True else: return False
def load_available_orders(self): qry = query("active_orders") if qry: active_mod = QtSql.QSqlQueryModel() active_mod.setQuery(qry) self.active_table.setModel(active_mod) self.active_table.resizeColumnsToContents() return True else: return False
def load_setup_orders(self): qry = query("setup_orders") if qry: setup_mod = QtSql.QSqlQueryModel() setup_mod.setQuery(qry) self.setup_table.setModel(setup_mod) self.setup_table.resizeColumnsToContents() return True else: return False
def load_material_orders(self): qry = query("material_orders") if qry: material_mod = QtSql.QSqlQueryModel() material_mod.setQuery(qry) self.waiting_table.setModel(material_mod) self.waiting_table.resizeColumnsToContents() return True else: return False
def outdata(self): import time prodtype = 0 qty = 0 if self.ui.radioButton_4.isChecked(): prodtype = u'良' if self.ui.radioButton_3.isChecked(): prodtype = u'不良' if prodtype == 0: QMessageBox.information(self, 'Waring', u'请选择良品或者不良品!!!') return bh = str(self.ui.comboBox.currentText()) if self.ui.lineEdit_6.text() == '': QMessageBox.information(self, 'Waring', u'出库数量不能为空请重新输入!!!') return else: qty = int(self.ui.lineEdit_6.text()) recordman = str(self.ui.lineEdit_5.text().toUtf8()) lingyongman = str(self.ui.lineEdit_7.text().toUtf8()) mark = str(self.ui.lineEdit_21.text().toUtf8()) #print bh,qty,recordman,mark,prodtype kw = str(self.ui.lineEdit_13.text().toUtf8()) sqlstr = [ bh, time.strftime('%Y-%m-%d'), prodtype, qty, mark, recordman, lingyongman, kw ] # print sqlstr self.ui.textEdit.append(u"编号:" + bh + u"...时间:" + time.strftime('%Y-%m-%d') + u"...类型:" + prodtype + u"....数量:" + str(qty) + u"...备注:" + mark + u"...记录人:" + recordman + u"....领用人:" + lingyongman + u"....库位:" + kw + u"...........数据更新成功!!") #dbconn.outdata(sqlstr) print sqlstr ss = tuple(sqlstr) sql1 = ( "insert into outrecord(prodctuno,time,prodtype,qty,mark,recordman,lingyongman,place) \ values('%s','%s','%s',%d,'%s','%s','%s','%s')" % ss) model = QtSql.QSqlQueryModel() model.setQuery(sql1) print model.submit() sql = u''' select prodctuno as 编号,time as 时间,prodtype as 良否,qty as 数量,\ lingyongman as 领用人, place as 库位,recordman as 记录人, mark as 备注 from outrecord ''' model.setQuery(sql) self.ui.tableView_5.setModel(model) self.ui.lineEdit_6.setText('') self.ui.lineEdit_7.setText('') self.ui.lineEdit_5.setText('') self.ui.lineEdit_21.setText('') self.ui.lineEdit_13.setText('')
def ret(self): """conn = sqlite3.connect("data.db") cursor = conn.cursor() cursor.execute("select * from student") a = cursor.fetchall() print(a) conn.commit()""" self.model = QtSql.QSqlQueryModel(self) sql = "select s.Name,f.Q1,f.Q2,f.Q3,f.Q4 from feedback f, student s where f.ID = s.ID;" self.model.setQuery(sql) self.ui.tableView.setModel(self.model)
def ret(self): """conn = sqlite3.connect("data.db") cursor = conn.cursor() cursor.execute("select * from student") a = cursor.fetchall() print(a) conn.commit()""" self.model = QtSql.QSqlQueryModel(self) sql = "select ID, Name, School from student;" self.model.setQuery(sql) self.tableView.setModel(self.model)
def get_combo_box(self, parent=None): db = Database.get('postgres') self.query_model = QtSql.QSqlQueryModel() self.query_model.setQuery(LIST_DATABASES, db) self.combo_box = QtGui.QComboBox(parent) self.combo_box.setModel(self.query_model) self.connect(self.combo_box, QtCore.SIGNAL('activated(QString)'), self.change_database) self.combo_box.setCurrentIndex(-1) return self.combo_box
def __init__(self, parent=None): QtCore.QAbstractItemModel.__init__(self, parent) self.model = QtSql.QSqlQueryModel(parent) self.root_item = _TreeItem(None) self._past_items = None self.patient_id = None QtGui.QApplication.instance().db_signaller.connect( self.receive_db_notification)
def btn_retrieveall(self): self.model = QtSql.QSqlQueryModel(self) sql = " select st_full_name as 'Full Name', f1.fb_text as 'FB Text 1', c.fb_rating1 as 'Rating 1',\ f2.fb_text 'FB Text 2', c.fb_rating2 as 'Rating 2', f3.fb_text as 'FB Text 3', c.fb_rating3 as 'Rating 3', f4.fb_text as 'FB Text 4', c.fb_rating4 as 'Rating 4',\ f5.fb_text as 'FB Text 5', c.fb_rating5 as 'Rating 5',f6.fb_text as 'FB Text 6', c.fb_rating6 as 'Rating 6', c.course_id as cid, c.st_id as stid\ from st_feedback c, student s, feedback f1, feedback f2, feedback f3, feedback f4, \ feedback f5 left join feedback f6 on c.fb_id6 = f6.fb_id where c.fb_id1 = f1.fb_id and c.fb_id2 =f2.fb_id \ and c.fb_id3 = f3.fb_id and c.fb_id4 = f4.fb_id \ and c.fb_id5 = f5.fb_id\ and s.st_id = c.st_id" self.model.setQuery(sql) self.tableView.setModel(self.model)
def outall(self): self.model = QtSql.QSqlQueryModel(self) sql = u''' select outrecord.prodctuno as 编号,productlist.productname as 物品名称,\ -sum(outrecord.qty) as 总数 from outrecord,productlist where outrecord.prodctuno=productlist.prodcutno group by outrecord.prodctuno ''' self.model.setQuery(sql) self.ui.tableView_7.setModel(self.model)