def makeValueRelationDict(self, valueDict): ret = dict() codes = valueDict['FilterExpression'].replace('code in (', '').replace(')','').split(',') keyColumn = valueDict['Key'] valueColumn = valueDict['Value'] table = valueDict['Layer'][:-17]#removing the date-time characters uri = QgsDataSourceURI(self.currLayer.dataProvider().dataSourceUri()) if uri.host() == '': db = QSqlDatabase('QSQLITE') db.setDatabaseName(uri.database()) else: db = QSqlDatabase('QPSQL') db.setHostName(uri.host()) db.setPort(int(uri.port())) db.setDatabaseName(uri.database()) db.setUserName(uri.username()) db.setPassword(uri.password()) if not db.open(): db.close() return ret in_clause = ','.join(map(str, codes)) query = QSqlQuery('select code, code_name from dominios.%s where code in (%s)' % (table, in_clause), db) while query.next(): code = str(query.value(0)) code_name = query.value(1) ret[code_name] = code db.close() return ret
def getStructureDict(self): ''' Gets database structure according to the edgv version ''' self.checkAndOpenDb() classDict = dict() sql = self.gen.getStructure(self.getDatabaseVersion()) query = QSqlQuery(sql, self.db) if not query.isActive(): self.db.close() raise Exception( self.tr("Problem getting database structure: ") + query.lastError().text()) while query.next(): className = str(query.value(0)) classSql = str(query.value(1)) if className.split('_')[0] == 'complexos' or className.split( '_')[-1] in ['p', 'l', 'a']: if className not in classDict.keys(): classDict[className] = dict() classSql = classSql.split(className)[1] sqlList = classSql.replace('(', '').replace(')', '').replace( '\"', '').replace('\'', '').split(',') for s in sqlList: fieldName = str(s.strip().split(' ')[0]) classDict[className][fieldName] = fieldName if 'GEOMETRY' in classDict[className].keys(): classDict[className]['GEOMETRY'] = 'geom' if 'geometry' in classDict[className].keys(): classDict[className]['geometry'] = 'geom' if 'OGC_FID' in classDict[className].keys(): classDict[className]['OGC_FID'] = 'id' return classDict
def get_song_listmodel(self, key = None, parent = None): """Create and return a QStringListModel of songs in database By default all songs are included @param key: Include only songs in given key @param key: String @return: List model to be passed to QListView @type return: QStringListModel""" if key == None: query = QSqlQuery() query.prepare("SELECT song FROM Patterns") success = query.exec_() if not success: pass # TODO songs = [], while query.next(): songs.append(query.value(0)) else: query = QSqlQuery() query.prepare("SELECT song FROM Patterns WHERE key == :key") query.bindValue(":key", key) success = query.exec_() if not success: pass # TODO songs = [], while query.next(): songs.append(query.value(0)) # Ajatusvirhe return QStringListModel(songs, parent)
def getEDGVDbsFromServer(self): #Can only be used in postgres database. try: self.checkAndOpenDb() except: return [] query = QSqlQuery(self.gen.getDatabasesFromServer(),self.db) dbList = [] while query.next(): dbList.append(query.value(0)) edvgDbList = [] for database in dbList: db = None db = QSqlDatabase("QPSQL") db.setDatabaseName(database) db.setHostName(self.db.hostName()) db.setPort(self.db.port()) db.setUserName(self.db.userName()) db.setPassword(self.db.password()) if not db.open(): QgsMessageLog.logMessage('DB :'+database+'| msg: '+db.lastError().databaseText(), "DSG Tools Plugin", QgsMessageLog.CRITICAL) query2 = QSqlQuery(db) if query2.exec_(self.gen.getEDGVVersion()): while query2.next(): version = query2.value(0) if version: edvgDbList.append((database,version)) else: QgsMessageLog.logMessage(self.tr('Problem accessing database: ') +database+'\n'+query2.lastError().text(), "DSG Tools Plugin", QgsMessageLog.CRITICAL) return edvgDbList
def get_last_lsg_date(self): """ Finds the most recent record insert/update date from either ESU's or street records. :return: date formatted YYYYMMDD """ last_date = 0 last_street = "Select max(Update_Date) as LastChange FROM tblStreet" query = QSqlQuery(self.db) query.exec_(last_street) query.seek(0) rec = query.record() last_street_date = query.value(rec.indexOf('LastChange')) last_esu = "SELECT Max([closure_date]) AS LastClose, Max([entry_date]) AS lastEntry FROM tblESU" query_esu = QSqlQuery(self.db) query_esu.exec_(last_esu) query_esu.seek(0) rec_esu = query_esu.record() last_esu_closure = query_esu.value(rec_esu.indexOf('LastClose')) last_esu_entry = query_esu.value(rec_esu.indexOf('lastEntry')) if last_street_date > last_date: last_date = last_street_date if last_esu_closure > last_date: last_date = last_esu_closure if last_esu_entry > last_date: last_date = last_esu_entry return last_date
def getStructureDict(self): ''' Gets database structure according to the edgv version ''' self.checkAndOpenDb() classDict = dict() sql = self.gen.getStructure(self.getDatabaseVersion()) query = QSqlQuery(sql, self.db) if not query.isActive(): self.db.close() raise Exception(self.tr("Problem getting database structure: ")+query.lastError().text()) while query.next(): className = str(query.value(0)) classSql = str(query.value(1)) if className.split('_')[0] == 'complexos' or className.split('_')[-1] in ['p','l','a']: if className not in classDict.keys(): classDict[className]=dict() classSql = classSql.split(className)[1] sqlList = classSql.replace('(','').replace(')','').replace('\"','').replace('\'','').split(',') for s in sqlList: fieldName = str(s.strip().split(' ')[0]) classDict[className][fieldName]=fieldName if 'GEOMETRY' in classDict[className].keys(): classDict[className]['GEOMETRY'] = 'geom' if 'geometry' in classDict[className].keys(): classDict[className]['geometry'] = 'geom' if 'OGC_FID' in classDict[className].keys(): classDict[className]['OGC_FID'] = 'id' return classDict
def getGeomDict(self, getCentroids = False): ''' returns a dict like this: {'tablePerspective' : { 'layerName' : ''' self.checkAndOpenDb() edgvVersion = self.getDatabaseVersion() sql = self.gen.getGeomTablesFromGeometryColumns(edgvVersion) query = QSqlQuery(sql, self.db) if not query.isActive(): raise Exception(self.tr("Problem getting geom tables from db: ")+query.lastError().text()) geomDict = dict() geomDict['primitivePerspective'] = self.getGeomTypeDict() geomDict['tablePerspective'] = dict() while query.next(): isCentroid = False srid = query.value(0) if edgvVersion in ('2.1.3','FTer_2a_Ed'): geometryType = query.value(2) else: geometryType = self.getResolvedGeomType(query.value(2)) tableName = query.value(3) tableSchema = tableName.split('_')[0] geometryColumn = query.value(1) layerName = '_'.join(tableName.split('_')[1::]) if layerName not in geomDict['tablePerspective'].keys(): geomDict['tablePerspective'][layerName] = dict() geomDict['tablePerspective'][layerName]['schema'] = tableSchema geomDict['tablePerspective'][layerName]['srid'] = str(srid) geomDict['tablePerspective'][layerName]['geometryColumn'] = geometryColumn geomDict['tablePerspective'][layerName]['geometryType'] = geometryType geomDict['tablePerspective'][layerName]['tableName'] = tableName return geomDict
def getAllEntriesInDiscipline(self, discipline): """Returns all the Entries for the given discipline""" entryList = [] try: query = QSqlQuery(self.conn) query.prepare("SELECT participant_id, teacher_id, discipline, level, class_number, \ class_name, instrument, years_of_instruction, scheduling_requirements, id FROM entries \ WHERE discipline=:discipline") query.bindValue(":discipline", discipline) query.exec_() if query.isActive() == False: print query.lastError().text() return query.lastError().text() while query.next() == True: participantID = str(query.value(0).toString()) teacherID = str(query.value(1).toString()) discipline = str(query.value(2).toString()) level = str(query.value(3).toString()) classNumber = str(query.value(4).toString()) className = str(query.value(5).toString()) instrument = str(query.value(6).toString()) yearsOfInstruction = str(query.value(7).toString()) schedulingRequirements = str(query.value(8).toString()) entryId = str(query.value(9).toString()) # get associated selections selections = self.getSelectionsFromEntryId(entryId) ee = Entry(participantID, teacherID, discipline, level, yearsOfInstruction, classNumber, className, instrument, selections, schedulingRequirements) entryList.append(ee) return entryList except Exception, e: # TODO: log this instead of printing to console print "getAllEntriesInDiscipline FAILED\n\tquery: {0}\n\terror: {1}".format(query.lastQuery(), e) return e
def get_song_listmodel(self, key=None, parent=None): """Create and return a QStringListModel of songs in database By default all songs are included @param key: Include only songs in given key @param key: String @return: List model to be passed to QListView @type return: QStringListModel""" if key == None: query = QSqlQuery() query.prepare("SELECT song FROM Patterns") success = query.exec_() if not success: pass # TODO songs = [], while query.next(): songs.append(query.value(0)) else: query = QSqlQuery() query.prepare("SELECT song FROM Patterns WHERE key == :key") query.bindValue(":key", key) success = query.exec_() if not success: pass # TODO songs = [], while query.next(): songs.append(query.value(0)) # Ajatusvirhe return QStringListModel(songs, parent)
def __init__( self, parentId, parent = None ): super( AccountsModel, self ).__init__( parent ) query = """ SELECT cc.codigo, cc.descripcion, cc.esdebe, COUNT(ch.idcuenta) nhijos, SUM(IFNULL(monto,0)) monto, cc.padre, cc.idcuenta FROM cuentascontables cc LEFT JOIN cuentascontables ch ON cc.idcuenta = ch.padre LEFT JOIN cuentasxdocumento cxd ON cc.idcuenta = cxd.idcuenta WHERE cc.padre = %d GROUP BY cc.idcuenta """ % parentId query = QSqlQuery( query ) query.exec_() query.first() self.rootItem = Account( QModelIndex(), parentId, query.value( CODIGO ).toString(), query.value( DESCRIPCION ).toString(), Decimal( query.value( MONTO ).toString() ), query.value( ESDEBE ).toInt()[0], query.value( HIJOS ).toInt()[0] )
def getStructureDict(self): try: self.checkAndOpenDb() except: return dict() classDict = dict() sql = self.gen.getStructure(self.getDatabaseVersion()) query = QSqlQuery(sql, self.db) while query.next(): className = str(query.value(0)) classSql = str(query.value(1)) if className.split('_')[0] == 'complexos' or className.split( '_')[-1] in ['p', 'l', 'a']: if className not in classDict.keys(): classDict[className] = dict() classSql = classSql.split(className)[1] sqlList = classSql.replace('(', '').replace(')', '').replace( '\"', '').replace('\'', '').split(',') for s in sqlList: fieldName = str(s.strip().split(' ')[0]) classDict[className][fieldName] = fieldName if 'GEOMETRY' in classDict[className].keys(): classDict[className]['GEOMETRY'] = 'geom' if 'OGC_FID' in classDict[className].keys(): classDict[className]['OGC_FID'] = 'id' return classDict
def lsg_last_date(self): """ get the last update date from db to show in LSG label :return: string """ qry_lsg = QSqlQuery(self.get_last_street, self.db) # THIS while qry_lsg.next(): try: date_lsg_obj = datetime.strptime(str(qry_lsg.value(0)), "%Y%m%d") except ValueError: date_lsg_obj = '000000' qry_esu = QSqlQuery(self.get_last_esu, self.db) while qry_esu.next(): try: date_esu_closure_obj = datetime.strptime( str(qry_esu.value(0)), "%Y%m%d") except ValueError: date_esu_closure_obj = '000000' try: date_esu_entry_obj = datetime.strptime(str(qry_esu.value(1)), "%Y%m%d") except ValueError: date_esu_entry_obj = '00000' last_lsg_change = max(date_lsg_obj, date_esu_closure_obj, date_esu_entry_obj).date() date_clean = str(last_lsg_change.strftime("%d/%m/%Y")) return date_clean
def getGeomDict(self, getCentroids=False): ''' returns a dict like this: {'tablePerspective' : { 'layerName' : ''' self.checkAndOpenDb() sql = self.gen.getGeomTablesFromGeometryColumns() query = QSqlQuery(sql, self.db) if not query.isActive(): raise Exception( self.tr("Problem getting geom tables from db: ") + query.lastError().text()) geomDict = dict() geomDict['primitivePerspective'] = self.getGeomTypeDict() geomDict['tablePerspective'] = dict() while query.next(): isCentroid = False srid = query.value(0) geometryType = query.value(2) tableName = query.value(3) tableSchema = tableName.split('_')[0] geometryColumn = query.value(1) layerName = '_'.join(tableName.split('_')[1::]) if layerName not in geomDict['tablePerspective'].keys(): geomDict['tablePerspective'][layerName] = dict() geomDict['tablePerspective'][layerName]['schema'] = tableSchema geomDict['tablePerspective'][layerName]['srid'] = str(srid) geomDict['tablePerspective'][layerName][ 'geometryColumn'] = geometryColumn geomDict['tablePerspective'][layerName][ 'geometryType'] = geometryType geomDict['tablePerspective'][layerName][ 'tableName'] = tableName return geomDict
def results_to_csv_row(self, vals, sql, mode): query = QSqlQuery(self.db) query.exec_(sql) rec = query.record() if mode == "streets": avals = [ rec.indexOf(vals[0]), rec.indexOf(vals[1]), rec.indexOf(vals[2]), rec.indexOf(vals[3]), rec.indexOf(vals[4]), rec.indexOf(vals[5]), rec.indexOf(vals[6]), rec.indexOf(vals[7]), rec.indexOf(vals[8]), rec.indexOf(vals[9]), rec.indexOf(vals[10]), rec.indexOf(vals[11]) ] while query.next(): line = [ query.value(avals[0]), query.value(avals[1]), query.value(avals[2]), query.value(avals[3]), query.value(avals[4]), query.value(avals[5]), self.format_dates(str(query.value(avals[6]))), self.format_dates(str(query.value(avals[7]))), self.format_dates(str(query.value(avals[8]))), self.format_dates(str(query.value(avals[9]))), query.value(avals[10]), query.value(avals[11]) ] self.csv.writerow(line) else: avals = [ rec.indexOf(vals[0]), rec.indexOf(vals[1]), rec.indexOf(vals[2]), rec.indexOf(vals[3]), rec.indexOf(vals[4]), rec.indexOf(vals[5]), rec.indexOf(vals[6]), rec.indexOf(vals[7]) ] while query.next(): line = [ query.value(avals[0]), query.value(avals[1]), query.value(avals[2]), query.value(avals[3]), query.value(avals[4]), query.value(avals[5]), query.value(avals[6]), query.value(avals[7]) ] self.csv.writerow(line)
def fill_fields(self): search_word = self.filterEdit.text() if search_word.strip() != '': search_str = stem_str(search_word) else: search_str = '' query = QSqlQuery(self.local_cn) LIMIT = 100 OFFSET = 0 query.prepare(show_termins_in_link_selector_query) query.bindValue(':search_str', search_str + '%') query.bindValue(':linked', 1) query.bindValue(':limit', LIMIT.__str__()) query.bindValue(':offset', OFFSET.__str__()) if query.exec_(): self.root.takeChildren() while query.next(): c = QTreeWidgetItem() c.setText(0, query.value(0)) # Заглавное слово c.setData(1, 0, query.value(1)) # uuid self.root.addChild(c) else: print(query.lastError().text()) print("not exec") self.treeWidget.scrollToTop()
def prepareLinkFromWordInDB(word, html, exclude_uuid): try: html = html.split("<!--StartFragment-->")[1].split( "<!--EndFragment-->")[0] except: html = word str = stem_str(word) remote_sql = QSqlQuery(get_local_connection()) remote_sql.prepare(count_word_in_db_query) # remote_sql.prepare(search_word_in_db_query) remote_sql.bindValue(":word", str) remote_sql.bindValue(":exclude_uuid", exclude_uuid) if remote_sql.exec_(): if remote_sql.next(): num = remote_sql.value(0) if num == 0: return None elif num == 1: return { 'link': "<a href='termin##" + remote_sql.value(1) + "##status##1##word##" + word + "##inithtml##" + html + "' style='color:green'>" + html + "</a>" } elif num > 1: return { 'link': "<a href='termin##" + str + "##status##2##word##" + word + "##inithtml##" + html + "' style='color:red'>" + html + "</a>" } else: return None else: return None
def getStructureDict(self): try: self.checkAndOpenDb() except: return dict() classDict = dict() sql = self.gen.getStructure(self.getDatabaseVersion()) query = QSqlQuery(sql, self.db) while query.next(): className = str(query.value(0)) classSql = str(query.value(1)) if className.split('_')[0] == 'complexos' or className.split('_')[-1] in ['p','l','a']: if className not in classDict.keys(): classDict[className]=dict() classSql = classSql.split(className)[1] sqlList = classSql.replace('(','').replace(')','').replace('\"','').replace('\'','').split(',') for s in sqlList: fieldName = str(s.strip().split(' ')[0]) classDict[className][fieldName]=fieldName if 'GEOMETRY' in classDict[className].keys(): classDict[className]['GEOMETRY'] = 'geom' if 'OGC_FID' in classDict[className].keys(): classDict[className]['OGC_FID'] = 'id' return classDict
def getParticipantsWithName(self, first, last): """Looks for participants with the given name""" pList = [] try: query = QSqlQuery(self.conn) query.prepare("SELECT first_name, last_name, address, town, postal_code, home_phone, \ cell_phone, email, date_of_birth, school_attending, parent \ FROM soloparticipants WHERE first_name=:first AND last_name=:last") query.bindValue(":first", first) query.bindValue(":last", last) query.exec_() if query.isActive() == False: print query.lastError().text() return query.lastError().text() while query.next() == True: first = str(query.value(0).toString()) last = str(query.value(1).toString()) address = str(query.value(2).toString()) town = str(query.value(3).toString()) postal = str(query.value(4).toString()) home = str(query.value(5).toString()) cell = str(query.value(6).toString()) email = str(query.value(7).toString()) dob = str(query.value(8).toString()) schoolAttending = str(query.value(9).toString()) parent = str(query.value(10).toString()) pList.append(Participant(first, last, address, town, postal, home, cell, email, dob, schoolAttending, parent)) return pList except Exception, e: # TODO: log this instead of printing to console print "getParticipantsWithName FAILED\n\tquery: {0}\ \n\terror: {1}".format(query.lastQuery(), e)
def setModelData(self,editor,model,index): curindx = self.editor.currentIndex() text = self.itemslist[curindx] model.setData(index, text) # update the student sn newClassname = index.sibling(index.row(),1).data() stusn = index.sibling(index.row(),2).data() stuname = index.sibling(index.row(),3).data() query = QSqlQuery(self.db) strsql = "select count(*), stusn from student where classname='" + newClassname + "' and stuname='" + stuname + "'" query.exec_(strsql) query.next() if query.value(0) == 0: ## the student is not in this class # print(query.value(0), "count**", strsql) ret = query.exec_("select max(stusn) from student where classname = '" + newClassname + "'") query.next() tmpsn = query.value(0) if type(tmpsn)== QPyNullVariant: newstusn = "01" else: newstusn = str(int(query.value(0)) + 1).zfill(2) # if newClassname != self.oldclassname: model.setData(index.sibling(index.row(), 2), newstusn) else: model.setData(index.sibling(index.row(), 2), query.value(1))
def populate_combos(self): """ Populate all comboboxes from db lookup tables. Combos sorted alphabetically with a 0 (default None) value at the top. """ for query_str, combo in self.query_lst.iteritems(): query = QSqlQuery(query_str) all_items = {} while query.next(): text = str(query.value(0)) value = str(query.value(1)) all_items[text] = value if '0' in all_items.values(): sorted_items = sorted(all_items.iteritems(), key=operator.itemgetter(0), reverse=True) else: # The only lookup without a 0 as 'None' item is the street ref type, also this is sorted on the type sorted_items = sorted(all_items.iteritems(), key=operator.itemgetter(1), reverse=True) default_none = None for item in sorted_items: text = item[0] data = item[1] if int(data) != 0: combo.insertItem(0, text, userData=data) else: default_none = item if default_none: combo.insertItem(0, default_none[0], userData=default_none[1]) combo.setCurrentIndex(0)
def exportNotice(self): query = QSqlQuery(self.db) query.exec_("select stusn, stuname, classname, rightquestions, wrongquestions from student" ) lstInfo = [["学号","姓名", "班级", "回答正确次数", "回答错误次数"]] while(query.next()): lstInfo.append([query.value(0),query.value(1),query.value(2),query.value(3),query.value(4)]) from xlwt import Workbook,easyxf book = Workbook(encoding='ascii') # 'pattern: pattern solid, fore_colour white;' style = easyxf( 'font: height 280, name 黑体;' 'align: vertical center, horizontal center;' ) style2 = easyxf('font: height 260, name 仿宋_GB2312, bold True; align: vertical center, horizontal left;') style3 = easyxf('font: height 260, name 仿宋_GB2312, bold True; align: vertical center, horizontal left, wrap True;') sheet1 = book.add_sheet('学生提问情况汇总',cell_overwrite_ok=True) # sheet1.write(0,7,flagtxt, easyxf('font: height 200, name 黑体;align: vertical center, horizontal right;')) sheet1.write_merge(0,0,0,4, '学生提问情况汇总表',style) sheet1.row(0).height_mismatch = 1 sheet1.row(0).height = 5*256 sheet1.col(0).width = 10*256 sheet1.col(1).width = 25*256 sheet1.col(2).width = 25*256 sheet1.col(3).width = 20*256 sheet1.col(4).width = 20*256 tmprows = 1 for item in lstInfo: stusn = item[0] stuname = item[1] classname = item[2] rightquestions = item[3] wrongquestions = item[4] sheet1.write(tmprows,0,stusn, style2) sheet1.write(tmprows,1,stuname, style2) sheet1.write(tmprows,2,classname, style2) sheet1.write(tmprows,3,rightquestions, style2) sheet1.write(tmprows,4,wrongquestions, style2) tmprows += 1 # print(tmprows) sheet1.header_str = "".encode() sheet1.footer_str = "".encode() # book.save('d:/simple.xls') # print(QDir.home().dirName() , QDir.homePath ()) filename = QDir.homePath () + "\学生提问情况汇总表.xls" try: book.save(filename) except Exception as e: QMessageBox.warning(self, "写入错误", "错误号:"+str(e.errno)+"\n错误描述:"+e.strerror+"\n请关闭已经打开的%s文档!" % filename) QMessageBox.about (self, "导出成功", "请查看文档:%s" % filename)
def getEntryFromId(self, entryId): """Retrieve Entry from specified id.""" try: query = QSqlQuery(self.conn) query.prepare("SELECT participant_id, teacher_id, discipline, level, class_number, \ class_name, instrument, years_of_instruction, scheduling_requirements FROM entries \ WHERE id=:id") query.bindValue(":id", entryId) query.exec_() if query.isActive() == False: print query.lastError().text() return query.lastError().text() query.next() participantID = str(query.value(0).toString()) teacherID = str(query.value(1).toString()) discipline = str(query.value(2).toString()) level = str(query.value(3).toString()) classNumber = str(query.value(4).toString()) className = str(query.value(5).toString()) instrument = str(query.value(6).toString()) yearsOfInstruction = str(query.value(7).toString()) schedulingRequirements = str(query.value(8).toString()) # get associated selections selections = self.getSelectionsFromEntryId(entryId) ee = Entry(participantID, teacherID, discipline, level, yearsOfInstruction, classNumber, className, instrument, selections, schedulingRequirements) return ee except Exception, e: # TODO: log this instead of printing to console print "getEntryFromId FAILED\n\tquery: {0}\n\terror: {1}".format(query.lastQuery(), e) return e
def load(self, error=True): s = QSettings("norBIT", "norGIS-ALKIS-Erweiterung") s.setValue("service", self.leSERVICE.text()) s.setValue("host", self.leHOST.text()) s.setValue("port", self.lePORT.text()) s.setValue("dbname", self.leDBNAME.text()) s.setValue("uid", self.leUID.text()) s.setValue("pwd", self.lePWD.text()) modelle = s.value("modellarten", ['DLKM', 'DKKM1000']) (db, conninfo) = self.plugin.opendb() if db: qry = QSqlQuery(db) if qry.exec_(""" SELECT modell,count(*) FROM ( SELECT unnest(modell) AS modell FROM po_points UNION ALL SELECT unnest(modell) AS modell FROM po_lines UNION ALL SELECT unnest(modell) AS modell FROM po_polygons UNION ALL SELECT unnest(modell) AS modell from po_lines UNION ALL SELECT unnest(modell) AS modell from po_labels ) AS foo GROUP BY modell ORDER BY count(*) DESC """): self.twModellarten.clearContents() res = {} while qry.next(): res[qry.value(0)] = qry.value(1) self.twModellarten.setRowCount(len(res)) i = 0 for k, n in sorted(res.iteritems(), key=operator.itemgetter(1), reverse=True): item = QTableWidgetItem(k) item.setCheckState(Qt.Checked if ( item.text() in modelle) else Qt.Unchecked) self.twModellarten.setItem(i, 0, item) item = QTableWidgetItem(str(n)) self.twModellarten.setItem(i, 1, item) i += 1 self.twModellarten.resizeColumnsToContents() elif error: modelle = [] self.twModellarten.clearContents() self.twModellarten.setDisabled(True) else: modelle = [] elif error: QMessageBox.critical(None, "ALKIS", u"Datenbankverbindung schlug fehl.")
def show_termins_in_list(self, page): self.currentPage = page # фильтрация списка терминов search_word = self.searchLineEdit.text() if search_word.strip() != '': search_str = search_word else: search_str = '' # показ уже обработанных терминов if self.showLinkedCheck.isChecked(): show_linked = 1 else: show_linked = 0 query = QSqlQuery(self.local_cn) LIMIT = int(self.terminsPerPageComboBos.currentText()) OFFSET = (page - 1) * LIMIT query.prepare(show_termins_in_list_query) query.bindValue(':search_str', search_str + '%') query.bindValue(':linked', show_linked) query.bindValue(':limit', LIMIT.__str__()) query.bindValue(':offset', OFFSET.__str__()) if query.exec_(): self.root.takeChildren() i = 1 f = QFont() f.setBold(True) while query.next(): c = QTreeWidgetItem() c.setText(0, query.value(0)) # Заглавное слово c.setData(1, 0, query.value(1)) # uuid c.setData(2, 0, i) # номерок if query.value(2) == 1: c.setFont(0, f) self.root.addChild(c) i += 1 pages = 1 query.prepare(show_termins_in_list_count_query) query.bindValue(':search_str', search_str + '%') query.bindValue(':linked', show_linked) if query.exec_() and query.next(): try: pages = math.ceil(query.value(0) / LIMIT) except: pages = 1 self.draw_paginator(pages, page) else: print(query.lastError().text()) print("not exec") self.terminsTreeWidget.scrollToTop()
def makeValueRelationDict(self, table, codes): ret = dict() in_clause = '(%s)' % ",".join(map(str, codes)) query = QSqlQuery('select code, code_name from dominios.%s where code in %s' % (table, in_clause), self.db) while query.next(): code = str(query.value(0)) code_name = query.value(1) ret[code_name] = code return ret
def getRecord(self, id_): query = QSqlQuery(self.handle) query.exec_(self.fetch_query % id_) result = None while query.next(): # 22: number of total fields, skip first id field result = [query.value(i).toString() for i in range(1,22)] result[12] = query.value(13).toByteArray() return result
def on_pbSearchStr_clicked(self): qry = QSqlQuery(self.db) self.cbxStrassen.clear() if qry.exec_( u"SELECT k.schluesselgesamt, k.bezeichnung || coalesce(', ' || g.bezeichnung,'') FROM ax_lagebezeichnungkatalogeintrag k LEFT OUTER JOIN ax_gemeinde g ON k.land=g.land AND k.regierungsbezirk=g.regierungsbezirk AND k.kreis=g.kreis AND k.gemeinde::int=g.gemeinde::int AND g.endet IS NULL WHERE k.bezeichnung LIKE {0} AND k.endet IS NULL ORDER BY k.bezeichnung || coalesce(', ' || g.bezeichnung,'')".format( quote(self.leSuchbegriff.text()+'%') ) ): while qry.next(): self.cbxStrassen.addItem( qry.value(1), qry.value(0) ) self.lblStrassen.setVisible( self.cbxStrassen.count() > 0 ) self.cbxStrassen.setVisible( self.cbxStrassen.count() > 0 ) self.on_cbxStrassen_currentIndexChanged(0)
def get_all_info(self): info = {} query = QSqlQuery(self.db) query.setForwardOnly(True) query.prepare('SELECT image_date, copyright_info FROM copyright;') if not query.exec_(): self.error.emit('Error getting all copyright info', query.lastError().text()) return info while query.next(): image_date = str(query.value(0).toString()) copyright_info = unicode(query.value(1).toString()) info[image_date] = copyright_info return info
def category_validation_sql(self): """ Populate a dict with validation rules for different record categories :return: dict of cateogry rules """ sql = "SELECT reinstatement_code, category FROM tlkpREINS_CAT;" rules = {} query = QSqlQuery(sql, self.db) while query.next(): code = query.value(0) category = query.value(1) rules[code] = category return rules
def makeValueRelationDict(self, table, codes): ''' Makes the value relation dictionary (multi valued attributes) ''' self.checkAndOpenDb() ret = dict() in_clause = '(%s)' % ",".join(map(str, codes)) sql = self.gen.makeRelationDict(table, in_clause) query = QSqlQuery(sql, self.db) while query.next(): code = str(query.value(0)) code_name = query.value(1) ret[code_name] = code return ret
def validation_sql(self): """ Builds a dict of the validation rules and what the date label should be, depending on the designation. :return: dict [designation code] = (mandatory field, date field name) """ sql = "SELECT designation_code, date_text, mandatory_fields FROM tlkpSPEC_DES;" rules = {} query = QSqlQuery(sql, self.db) while query.next(): code = int(query.value(0)) date_text = query.value(1) mand_fields = query.value(2) rules[code] = (mand_fields, date_text) return rules
def makeValueRelationDict(self, table, codes): ret = dict() in_clause = '(%s)' % ",".join(map(str, codes)) query = QSqlQuery( 'select code, code_name from dominios.%s where code in %s' % (table, in_clause), self.db) while query.next(): code = str(query.value(0)) code_name = query.value(1) ret[code_name] = code return ret
def setupModelData( self, parent ): try: if not QSqlDatabase.database().isOpen(): if not QSqlDatabase.database().open(): raise Exception( "No se pudo conectar con la base"\ + " de datos" ) parents = [parent] query = """ SELECT p.nombre, p.idcategoria, COUNT(h.idcategoria) as hijos FROM categorias p LEFT JOIN categorias h ON p.idcategoria = h.padre WHERE p.padre IS NULL GROUP BY p.idcategoria """ query = QSqlQuery( query ) if not query.exec_(): raise Exception( "No se pudieron recuperar las categorias" ) x = 0 while query.next(): parent = parents[-1] parent.insertChildren( x, 1, [query.value( 0 ).toString(), query.value( 1 ).toInt()[0]] ) if query.value( 2 ) > 0: y = 0 childquery = """ SELECT p.nombre , p.idcategoria FROM categorias p WHERE p.padre = %d """ % query.value( 1 ).toInt()[0] childquery = QSqlQuery( childquery ) childquery.exec_() while childquery.next(): parent.child( x ).insertChildren( y, 1, [ childquery.value( 0 ).toString(), childquery.value( 1 ).toInt()[0]] ) y += 1 x += 1 return True except Exception as inst: logging.error( unicode( inst ) ) return False
def getGeomColumnTupleList(self, showViews=False): """ list in the format [(table_schema, table_name, geometryColumn, geometryType, tableType)] centroids are hidden by default """ self.checkAndOpenDb() edgvVersion = self.getDatabaseVersion() sql = self.gen.getGeomColumnTupleList(edgvVersion) query = QSqlQuery(sql, self.db) if not query.isActive(): raise Exception( self.tr("Problem getting geom tuple list: ") + query.lastError().text()) geomList = [] while query.next(): if edgvVersion in ['2.1.3', 'FTer_2a_Ed']: geomList.append((query.value(0).split('_')[0], '_'.join(query.value(0).split('_')[1::]), query.value(1), query.value(2), 'BASE TABLE')) else: geomList.append( (query.value(0).split('_')[0], '_'.join(query.value(0).split('_')[1::]), query.value(1), self.getResolvedGeomType(int(query.value(2))), 'BASE TABLE')) return geomList
def getDefaults(self): query = QSqlQuery("SELECT control, value FROM DefaultValues") query.exec_() defaults = {} while query.next(): try: name = query.value(0).toString() control = self.getControl(name) except ControlNotFound: continue value = query.value(1) defaults[control] = value return defaults
def startChoice(self, usernum="", oldbtn=""): # print(oldbtn, 1) if oldbtn != "": flag = str(oldbtn) else: self.dict_choices = {} strwhere = " and classname like '" + self.g_curClassName + "'" allstudent = [] lstrecord = [] query = QSqlQuery(self.db) query.exec_("select stusn from tmprecord where 1=1 " + strwhere) while(query.next()): lstrecord.append(query.value(0)) # print(lstrecord, 'record', "select stusn from student where stusn not in " + str(tuple(lstrecord))) query.exec_("select stusn from student where stusn not in " + str(tuple(lstrecord)) + strwhere) while(query.next()): allstudent.append(query.value(0)) if usernum == "": nums = int(self.choicenum_text.currentText()) else: nums = usernum if nums >= len(allstudent): query.exec_("delete from tmprecord where 1=1 " + strwhere) #delete tmp date no today allstudent = [] query.exec_("select stusn from student where 1=1 " + strwhere) while(query.next()): allstudent.append(query.value(0)) if oldbtn == "": random.seed() lstchoices = random.sample(allstudent, nums) for ibtn in lstchoices: self.dict_choices[ibtn] = "111" self.group_animation.start() QTimer.singleShot(1200, self.stopmovie) else: random.seed() otherbtn = random.sample(allstudent, 1)[0] # self.btngroup.button(int(otherbtn)).setFocus() self.dict_choices.pop(oldbtn) self.dict_choices[otherbtn] = '111' self.stopmovie() self.btnSysMenu.setFocus()
def makeValueRelationDict(self, valueDict): """ Gets the value relation dictionary. This is necessary for multi valued attributes. """ ret = dict() codes = valueDict['FilterExpression'].replace('code in (', '').replace( ')', '').split(',') in_clause = ','.join(map(str, codes)) keyColumn = valueDict['Key'] valueColumn = valueDict['Value'] table = valueDict['Layer'][:-17] #removing the date-time characters uri = QgsDataSourceURI(self.currLayer.dataProvider().dataSourceUri()) if uri.host() == '': db = QSqlDatabase('QSQLITE') db.setDatabaseName(uri.database()) sql = 'select code, code_name from dominios_%s where code in (%s)' % ( table, in_clause) else: db = QSqlDatabase('QPSQL') db.setHostName(uri.host()) db.setPort(int(uri.port())) db.setDatabaseName(uri.database()) db.setUserName(uri.username()) db.setPassword(uri.password()) sql = 'select code, code_name from dominios.%s where code in (%s)' % ( table, in_clause) if not db.open(): db.close() return ret query = QSqlQuery(sql, db) if not query.isActive(): QMessageBox.critical( self.iface.mainWindow(), self.tr("Error!"), self.tr("Problem obtaining domain values: ") + query.lastError().text()) return ret while query.next(): code = str(query.value(0)) code_name = query.value(1) ret[code_name] = code db.close() return ret
def changeTab(self): # pass curtab = self.tabWidget.currentIndex() # print(curtab, "-") if curtab == 1: ## when click the second tab page ,then pass. return # cur = conn.cursor() query = QSqlQuery(self.db) ## if current classname is null, then set current tabpage display the first class of classtable if self.g_curClassName == "": ret = query.exec_("select classname from classtable") query.next() self.g_curClassName = query.value(0) self.tabWidget.setTabText(0, self.g_curClassName) # print(1) strwhere = " and classname like '" + self.g_curClassName + "' ORDER BY stusn" self.g_curbtn = "" self.dict_choices = {} self.studentSnlst = [] ## clearn the question data of temp record . ret= query.exec_("delete from tmprecord where 1=1") ret = query.exec_("select stusn, stuname from student where 1=1 " + strwhere) ## now update the global data "self.btngroup" for indx in range(0, 56): self.btngroup.button(indx+1).setText("") self.btngroup.button(indx+1).setMyarg(None) self.btngroup.button(indx+1).setStyleSheet(stylesheetstr_old) self.btngroup.button(indx+1).setIcon(QIcon()) self.btngroup.button(indx+1).setEnabled(False) self.studentSnlst.append([indx+1,]) inum = 0 while (query.next()): inum += 1 self.btngroup.button(inum).setText(query.value(1)) self.btngroup.button(inum).setMyarg(query.value(0)) self.btngroup.button(inum).setStyleSheet(stylesheetstr_old) self.btngroup.button(inum).setIcon(QIcon()) self.btngroup.button(inum).setEnabled(True) # print(inum, len(self.btngroup.buttons())) self.group_animation = groupAnimation(self.studentSnlst, self.btngroup)
def load(self, error=True): s = QSettings( "norBIT", "norGIS-ALKIS-Erweiterung" ) s.setValue( "service", self.leSERVICE.text() ) s.setValue( "host", self.leHOST.text() ) s.setValue( "port", self.lePORT.text() ) s.setValue( "dbname", self.leDBNAME.text() ) s.setValue( "uid", self.leUID.text() ) s.setValue( "pwd", self.lePWD.text() ) modelle = s.value( "modellarten", ['DLKM','DKKM1000'] ) (db,conninfo) = self.plugin.opendb() if db: qry = QSqlQuery(db) if qry.exec_( """ SELECT modell,count(*) FROM ( SELECT unnest(modell) AS modell FROM po_points UNION ALL SELECT unnest(modell) AS modell FROM po_lines UNION ALL SELECT unnest(modell) AS modell FROM po_polygons UNION ALL SELECT unnest(modell) AS modell from po_lines UNION ALL SELECT unnest(modell) AS modell from po_labels ) AS foo GROUP BY modell ORDER BY count(*) DESC """ ): self.twModellarten.clearContents() res = {} while qry.next(): res[ qry.value(0) ] = qry.value(1) self.twModellarten.setRowCount( len(res) ) i = 0 for k,n in sorted(res.iteritems(), key=operator.itemgetter(1), reverse=True): item = QTableWidgetItem( k ) item.setCheckState( Qt.Checked if (item.text() in modelle) else Qt.Unchecked ) self.twModellarten.setItem( i, 0, item ) item = QTableWidgetItem( str(n) ) self.twModellarten.setItem( i, 1, item ) i += 1 self.twModellarten.resizeColumnsToContents() elif error: modelle = [] self.twModellarten.clearContents() self.twModellarten.setDisabled( True ) else: modelle = [] elif error: QMessageBox.critical( None, "ALKIS", u"Datenbankverbindung schlug fehl." )
def deleteRecord(self): index = self.view.currentIndex() if not index.isValid(): return record = self.model.record(index.row()) id = record.value(ID).toInt()[0] table = self.model.tableName() query = QSqlQuery() if table == "deps": query.exec_(QString("SELECT COUNT(*) FROM employee " "WHERE deo_id = %1").arg(id)) elif table == "cities": query.exec_(QString("SELECT COUNT(*) FROM employee " "WHERE city_id = %1").arg(id)) count = 0 if query.next(): count = query.value(0).toInt()[0] if count: QMessageBox.information(self, QString("Delete %1").arg(table), (QString("Cannot delete %1<br>" "from the %2 table because it is used by " "%3 records") .arg(record.value(NAME).toString()) .arg(table).arg(count))) return self.model.removeRow(index.row()) self.model.submitAll()
def getDbsFromServer(self, name): """ Gets server databases name: server name """ gen = self.factory.createSqlGenerator(False) (host, port, user, password) = self.getServerConfiguration(name) database = 'postgres' db = self.getPostGISDatabaseWithParams(database, host, port, user, password) if not db.open(): QgsMessageLog.logMessage(db.lastError().text(), "DSG Tools Plugin", QgsMessageLog.CRITICAL) QMessageBox.critical( self.iface.mainWindow(), self.tr('Critical'), self.tr('A problem occurred! Check log for details.')) query = QSqlQuery(gen.getDatabasesFromServer(), db) if not query.isActive(): QMessageBox.critical( self.iface.mainWindow(), self.tr('Critical'), self.tr("Problem executing query: ") + query.lastError().text()) dbList = [] while query.next(): dbList.append(query.value(0)) return self.browseServer(dbList, host, port, user, password)
def list_data(self, name="", type="", limit="", filter=""): data_list = [] if name == "": return "" query = QSqlQuery(self.db) if limit != "": limit = " LIMIT " + limit if filter != "": try: filter_split = filter.split('=') filter = 'AND data LIKE "%{0}: {1}%"'.format(filter_split[0], filter_split[1]) except: data_list.append("filter: BAD FILTER") filter = "" if type != "": query.exec_("SELECT * FROM DDS WHERE name = \"{0}\"" "AND type LIKE \"{1}%\"".format(name, type) + filter + limit) else: query.exec_("SELECT * FROM DDS WHERE name = \"{0}\"".format(name) + filter + limit) print query.lastQuery() while query.next(): type = str(query.value(2).toString()) data = str(query.value(3).toString()) data_list.append("type: " + type + '\n' + data) return data_list
def commit_esu_link(self): """ Updates existing esu links on edit and deal with adding/remove links via editing """ usrn = str(self.new_usrn_no) if self.edit_esu: # get new set of esu links esus = self.edit_esu.get_final_selection() final = esus[0] else: # No esu edits made so query for existing esu links final = self.esu_version.query_esu(usrn) date = str(datetime.datetime.now().strftime("%Y%m%d")) try: for esu in final: query_str = "SELECT version_no FROM tblESU WHERE esu_id = %s AND currency_flag = 0;" % esu query = QSqlQuery(query_str, self.db) seek = query.seek(0) if seek: esu_ver = query.value(0) else: esu_ver = str(1) # Create new links insert_sql = "INSERT INTO lnkESU_STREET (esu_id, usrn, esu_version_no, usrn_version_no, currency_flag," \ " entry_date, update_date) VALUES (%s, %s, %s, 1, 0, %s, %s)" \ % (esu, usrn, esu_ver, date, date) new_lnk_query = QSqlQuery(insert_sql, self.db) except TypeError: # No esu's attached to record pass
def SitesHaveFindSpots(db, siteNumbers): sites = u", ".join(u"'{0}'".format(siteNumber) for siteNumber in siteNumbers) query = QSqlQuery(db) query.prepare(u"SELECT COUNT(*) FROM fundstelle WHERE fundortnummer IN ({0})".format(sites)) query.exec_() query.first() return query.value(0)
def __init__( self, parent = None ): super( ReciboDelegate, self ).__init__( parent ) query = QSqlQuery( """ SELECT idtipomovimiento, CONCAT(descripcion, ' ' , moneda) as tipopago, idtipomoneda, m.simbolo FROM tiposmoneda m JOIN tiposmovimientocaja p ; """ ) self.filtrados = [] query.exec_() while query.next(): self.filtrados.append( query.value( 1 ).toString() ) self.abonosmodel = QSqlQueryModel() self.abonosmodel.setQuery( query ) self.proxymodel = QSortFilterProxyModel() self.proxymodel.setSourceModel( self.abonosmodel ) self.proxymodel.setFilterKeyColumn( 1 ) self.completer = QCompleter() self.completer.setModel( self.proxymodel ) self.completer.setCompletionColumn( 1 ) self.completer.setCaseSensitivity( Qt.CaseInsensitive ) self.completer.setCompletionMode( QCompleter.UnfilteredPopupCompletion ) query = QSqlQuery( """ SELECT idbanco,descripcion FROM bancos; """ ) self.bancosmodel = QSqlQueryModel() self.bancosmodel.setQuery( query )
def btns_click(self, btnid): # curclassname = self.tabWidget.tabText(0) query = QSqlQuery(self.db) # cur = conn.cursor() today = datetime.date.today() self.g_curbtn = str(btnid).zfill(2) if self.g_curbtn not in self.dict_choices: self.btngroup.button(int(self.g_curbtn)).setStyleSheet(stylesheetstr_new) query.exec_("select count(*) from tmprecord where stusn='" + str(self.g_curbtn) + "'") query.next() if query.value(0) == 0: query.prepare("insert into tmprecord (classname, stusn, datequestion) values (:classname, :stusn, :datequestion)") query.bindValue(":classname", self.g_curClassName) query.bindValue(":stusn", self.g_curbtn) query.bindValue(":datequestion", today) query.exec_() self.dict_choices[self.g_curbtn] = "111" else: self.btngroup.button(int(self.g_curbtn)).setStyleSheet(stylesheetstr_old) self.btngroup.button(int(self.g_curbtn)).setIcon(QIcon()) query.exec_("delete from tmprecord where stusn='"+ str(self.g_curbtn) + "'") self.dict_choices.pop(self.g_curbtn) self.btnSysMenu.setFocus()
def validate(self, usrn, final_esu_selection=None): """ Validates that all ESU's are only attached to a single type-1 or type-2 usrn :param usrn: USRN :param final_esu_selection: list of esu's :return: true if esu links are valid """ record_type_combo = self.street_browser.ui.recordTypeComboBox if final_esu_selection: esu_list = final_esu_selection else: esu_list = self.get_esu_selection(usrn) # only need to check type 1 and 2 records record_type = int( record_type_combo.itemData(record_type_combo.currentIndex())) if record_type == 1 or record_type == 2: # Type 1 or Type 2 hardcoded! bad_esu = [] for esu_key in esu_list: sql = """SELECT tblSTREET.usrn, tblSTREET.street_ref_type FROM lnkESU_STREET INNER JOIN tblSTREET ON lnkESU_STREET.usrn_version_no = tblSTREET.version_no AND lnkESU_STREET.usrn = tblSTREET.usrn WHERE lnkESU_STREET.esu_id = %s AND lnkESU_STREET.currency_flag = 0 AND tblSTREET.currency_flag = 0 AND tblSTREET.usrn != %s""" \ % (esu_key, usrn) query = QSqlQuery(sql, self.db) while query.next(): typee = int(query.value(1)) if typee == 1 or typee == 2: bad_esu.append(esu_key) if bad_esu: # esu's found return False else: return True else: return True
def populate_comboboxes(self): """ Populate comboboxes from db and add blank 0 index """ ui_s = self.filter_dlg.ui query_lst = [("""SELECT name FROM tlkpTOWN ORDER BY name DESC""", ui_s.townComboBox), ("""SELECT state_desc FROM tlkpSTREET_STATE ORDER BY state_desc DESC""", ui_s.stateComboBox), ("""SELECT name FROM tlkpLOCALITY ORDER BY name DESC""", ui_s.localityComboBox), ("""SELECT description FROM tlkpSTREET_REF_TYPE ORDER BY description DESC""", ui_s.recordTypeComboBox) ] for query in query_lst: sql = query[0] combo = query[1] combo.clear() query = QSqlQuery(sql) while query.next(): value = str(query.value(0)) combo.insertItem(0, value) combo.insertItem(0, "") combo.setCurrentIndex(0) combo.setSizeAdjustPolicy(QComboBox.AdjustToContents)
def listGeomClassesFromDatabase(self): try: self.checkAndOpenDb() except: return [] classList = [] sql = self.gen.getTablesFromDatabase() query = QSqlQuery(sql, self.db) while query.next(): tableSchema = query.value(0) tableName = query.value(1) layerName = tableSchema+'.'+tableName if tableName.split("_")[-1] == "p" or tableName.split("_")[-1] == "l" \ or tableName.split("_")[-1] == "a": classList.append(layerName) return classList
def listComplexClassesFromDatabase(self): try: self.checkAndOpenDb() except: return [] classList = [] sql = self.gen.getTablesFromDatabase() query = QSqlQuery(sql, self.db) while query.next(): tableSchema = query.value(0) tableName = query.value(1) layerName = tableSchema+'.'+tableName if tableSchema == 'complexos': classList.append(layerName) classList.sort() return classList
def data( self ): data = {} fila = self.tblCuenta.selectionModel().currentIndex().row() fecha = self.dtPicker.date() data['banco'] = self.filtermodel.index( fila, 0 ).data().toString() data['id_cuenta_contable'] = self.filtermodel.index( fila, 4 ).data().toInt()[0] data['codigo_cuenta_contable'] = self.filtermodel.index( fila, 3 ).data().toString() data['cuenta_bancaria'] = self.filtermodel.index( fila, 5 ).data().toString() data['fecha'] = QDate( fecha.year(), fecha.month(), fecha.daysInMonth() ) data['moneda'] = self.filtermodel.index( fila, 2 ).data().toString() if not QSqlDatabase.database().isOpen() and not QSqlDatabase.open(): raise Exception( QSqlDatabase.lastError() ) query = QSqlQuery() if not query.exec_( "CALL spSaldoCuenta( %d, %s )" % ( data['id_cuenta_contable'], QDate( data['fecha'].year(), data['fecha'].month(), data['fecha'].daysInMonth() ).toString( "yyyyMMdd" ) ) ): raise Exception( query.lastError().text() ) query.first() data['saldo_inicial_libro'] = Decimal( query.value( 0 ).toString() ) return data
def DevolverVeiculo(codigoAlug, aluguel): conn = ConexaoSQL db = conn.getConexao() db.open() select = "SELECT Veiculo.CodigoVeic FROM Aluguel"\ " INNER JOIN Veiculo ON Aluguel.CodigoVeic = Veiculo.CodigoVeic"\ " WHERE Aluguel.CodigoAlug = "+codigoAlug query = QSqlQuery(select) while query.next(): codigoVeic = str(query.value(0)) sql = "UPDATE Veiculo SET Alugado = 'Não' WHERE CodigoVeic = "+codigoVeic query.prepare(sql) query.exec_() db.commit() sql = "UPDATE Aluguel SET DataDevolucao = '"+aluguel.DataDevolucao+"', ValorMulta = '"+aluguel.ValorMulta\ +"', KmSaida = '"+aluguel.KmSaida\ +"' WHERE CodigoAlug = "+codigoAlug print(sql) query.prepare(sql) print query.exec_() db.commit()
def zoom_to_record(self): """ Zoom map to extent of street (comprising of multiple esu's) """ tbl_idx = self.table_view.selectedIndexes() if tbl_idx: source_idx = self.proxy.mapToSource(tbl_idx[0]) usrn = source_idx.data() # Query db for ESU's which make up the street if usrn and usrn != 0: query_str = "SELECT esu_id FROM lnkESU_STREET WHERE usrn = %s AND currency_flag = 0;" % usrn query = QSqlQuery(query_str, self.db) # Build query string q_string = "" while query.next(): q_string += '"esu_id" = %s OR ' % str(query.value(0)) q_string = q_string[:-3] # Get ref to ESU layer esu_layer = QgsMapLayerRegistry.instance().mapLayersByName( 'ESU Graphic')[0] # Select ESU's + get extent feats = esu_layer.getFeatures( QgsFeatureRequest().setFilterExpression(q_string)) feat_ids = [] for feature in feats: f_id = feature.id() feat_ids.append(f_id) esu_layer.setSelectedFeatures(feat_ids) esu_bbox = esu_layer.boundingBoxOfSelected() # Set new extent esu_bbox.scale(1.1) # Zoom out slightly for context self.parent.iface.mapCanvas().setExtent(esu_bbox) self.parent.iface.mapCanvas().refresh() # Close connection to db and goto_record self.goto_record(close=False)
def saveComboValues(self, combobox, text): """ Save the value of the combo box into the form settings values. Only saves new values. """ comboitems = [combobox.itemText(i) for i in range(combobox.count())] name = combobox.objectName() query = QSqlQuery() query.prepare("SELECT value FROM ComboBoxItems WHERE control = :contorl") query.bindValue(":control", name) query.exec_() log("LAST ERROR") log(query.lastError().text()) items = [] while query.next(): value = query.value(0).toString() if not value.isEmpty(): items.append(str(value)) if not text in comboitems and not text in items: query = QSqlQuery() query.prepare("INSERT INTO ComboBoxItems (control, value)" "VALUES (:control,:value)") query.bindValue(":control", name) query.bindValue(":value", text) query.exec_() log("LAST ERROR FOR INSERT") log(query.lastError().text())