def prepare(self, query_string): query = QSqlQuery(self.db) query.setForwardOnly(True) # weird; QSqlQuery instances don't appear to be GC'ed by the time the DB is closed - so I capture them in a strong-ref'd # set and finish them up myself manually. self.__refQuery(query) if query.prepare(query_string): return query raise SyntaxError("failed to prepare query, sql was: '{}', db error: {}".format(query_string, query.lastError().text()))
def get_info(self, image_date): query = QSqlQuery(self.db) query.setForwardOnly(True) query.prepare('SELECT copyright_info FROM copyright WHERE image_date = ?;') query.addBindValue(image_date) if not query.exec_(): self.error.emit('Error getting copyright info', query.lastError().text()) return '' query.first() copyright_info = query.value(0).toString() return copyright_info
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 export_esu_line(self): """ Export ESUs :return: """ canvas = self.iface.mapCanvas() clayer = canvas.currentLayer() # will return 0 if none selected count = clayer.selectedFeatureCount() feature_count = clayer.featureCount() # Get list of selected features selected_esu_ids = list() if count > 0: selectedfeats = clayer.selectedFeatures() for feat in selectedfeats: selected_esu_ids.append(int(feat.attribute('esu_id'))) feature_count = clayer.selectedFeatureCount() self.warn_about_selected_features(feature_count) # Prepare sql query if self.unassigned: nsgexportsql = self.sql_queries['export_all'] else: nsgexportsql = self.sql_queries['export_assigned_only'] # SQL to filter out selected records if count > 0: nsgexportsql += " WHERE esu.esu_id IN ({})".format(', '.join( map(str, selected_esu_ids))) # Setup database temporary tables for table in ['qryType12', 'qryType3', 'qryType4']: # Drop tables if left behind from last export args = {'table': table} query = self.run_sql('drop_table', args) query = self.run_sql('create_qryType12') query = self.run_sql('create_qryType3') query = self.run_sql('create_qryType4') # Run the main query if config.DEBUG_MODE: print(nsgexportsql) query = QSqlQuery(self.db) query.setForwardOnly(True) query.exec_(nsgexportsql) if query.isActive() is False: raise StandardError('Database query problem: {}'.format( query.lastError().text())) # create layer vlayer = QgsVectorLayer("multilinestring?crs=EPSG:27700", "temp", "memory") vlayer.setCrs( QgsCoordinateReferenceSystem( 27700, QgsCoordinateReferenceSystem.EpsgCrsId)) provider = vlayer.dataProvider() # add fields self.fields = [ QgsField("esu_id", QVariant.String), QgsField("USRN", QVariant.LongLong), QgsField("Rec_type", QVariant.Int), QgsField("DescTxt", QVariant.String), QgsField("Locality", QVariant.String), QgsField("Town", QVariant.String), QgsField("Entry_date", QVariant.Date), QgsField("Type_3_USRN", QVariant.LongLong), QgsField("Type_3_Desc", QVariant.String), QgsField("Type_4_USRN", QVariant.LongLong), QgsField("Type_4_Desc", QVariant.String) ] provider.addAttributes(self.fields) vlayer.updateFields() # Exit if output file path is invalid if len(str(self.export_path)) < 0: return False if self.check_if_export_file_in_use(): return False # Run through SQL results creating features from rows self.progresswin.show() i = 0 while query.next(): if self.progresswin.wasCanceled(): self.kill_export() break record = query.record() new_feature = self.create_feature_from_record(record) provider.addFeatures([new_feature]) # Update progress bar i += 1 diff = feature_count + ( i - feature_count) if i > feature_count else feature_count percent_complete = (i / float(diff)) * 100 self.progresswin.setValue(percent_complete) if self.killed: # Show message and exit if killed export_error_msg_box = QMessageBox( QMessageBox.Warning, " ", "An error occurred while exporting shapefile", QMessageBox.Ok, None) export_error_msg_box.setWindowFlags(Qt.CustomizeWindowHint | Qt.WindowTitleHint) export_error_msg_box.exec_() return False vlayer.updateExtents() result = QgsVectorFileWriter.writeAsVectorFormat( vlayer, self.export_path, "utf-8", None, "ESRI Shapefile") # checks for completed export if result == 0: self.progresswin.close() if config.DEBUG_MODE: print('DEBUG_MODE: {} features exported'.format( vlayer.featureCount())) return True
def get_esu_feature(self): # Run Query and get the matching features shape_tbl = 'SELECT AsBinary(E.geometry) AS geom, E.esu_id AS esuid, E.PK_UID AS FID, T.* ' \ 'FROM esu E, tblESU T ' \ 'WHERE E.esu_id = T.esu_id and T.currency_flag = 0' query = QSqlQuery(self.db) query.setForwardOnly(True) query.exec_(shape_tbl) rec = query.record() feat_list = list() while query.next(): esu_id = query.value(rec.indexOf("esuid")) geom = query.value(rec.indexOf("geom")) # write a temp geom to extract the geom to be written g = QgsGeometry() g.fromWkb(geom) feat = QgsFeature() feat.setGeometry(g) geom = feat.geometry() count = len(geom.asMultiPolyline()[0]) start = geom.vertexAt(0) end = geom.vertexAt(count - 1) # only write in a mid point if the line has a midpoint vert mids = None if count >= 2: geom.deleteVertex(count - 1) geom.deleteVertex(0) mids = geom.asMultiPolyline() esuy = query.value(rec.indexOf("yref")) esux = query.value(rec.indexOf("xref")) if esuy <= 999999: esuy = "0" + str(query.value(rec.indexOf("yref"))) if esux <= 999999: esux = "0" + str(query.value(rec.indexOf("xref"))) afields = dict(esu_id=query.value(rec.indexOf("esu_id")), xref=query.value(rec.indexOf("xref")), yref=query.value(rec.indexOf("yref")), ESUXYID=str(esux) + str(esuy), version_no=query.value(rec.indexOf("version_no")), entry_date=query.value(rec.indexOf("entry_date")), closure_date=query.value(rec.indexOf("closure_date")), start_date=query.value(rec.indexOf("start_date")), Update_Date=query.value(rec.indexOf("Update_Date")), start_xref=query.value(rec.indexOf("start_xref")), start_yref=query.value(rec.indexOf("start_yref")), end_xref=query.value(rec.indexOf("end_xref")), end_yref=query.value(rec.indexOf("end_yref")), tolerance=query.value(rec.indexOf("tolerance")), ) if geom is not None: self.xref_esu.append(esu_id) item = dict(result=0, esu_id=esu_id, start={ 'x': start[0], 'y': start[1] }, end={ 'x': end[0], 'y': end[1] }, length=count, attributes=afields, midpoints=mids) else: item = dict(result=1, esu_id=esu_id, ESUXYID=int(str(esux) + str(esuy))) feat_list.append(item) return feat_list