def test_query(self): if not self.layer: return layer = self.layer db = Database.fromLayer(layer) try: if not self.mapKeyEdit.text(): feature = next(layer.getFeatures()) self.mapKeyEdit.setText(str(feature.id())) else: try: mapkey = int(self.mapKeyEdit.text()) rq = QgsFeatureRequest().setFilterFid(mapkey) feature = next(layer.getFeatures(rq)) except ValueError: self.attributesLabel.setText("") self.previewGrid.setModel(None) self.resultsLabel.setText( "Error in mapkey. Map key is invalid. Should be a valid number" ) return except StopIteration: self.attributesLabel.setText("") self.previewGrid.setModel(None) self.resultsLabel.setText( "No features found. No results or map key not found in layer") return self.resultsLabel.setText("") dbkey = self.dbKeyEdit.text() attributes = utils.values_from_feature(feature, safe_names=True, ordered=True) attributes['mapkey'] = feature.id() attributes['dbkey'] = dbkey # Run the SQL text though the QGIS expression engine first. sql = self.Editor.text() sql = roam.api.utils.replace_expression_placeholders(sql, feature) results = db.querymodel(sql, **attributes) self.previewGrid.setModel(results) labelText = "" for key, value in attributes.items(): labelText += "<br> {}: {}".format(key, value) print(labelText) self.attributesLabel.setText("Feature Attributes Used:<br>" + labelText)
def _get_sqlite_col_length(layer, fieldname): """ Get the length of a sqlite based column using the metadata NOTE: SQLITE doesn't support this. And this is a bit of a hack. NOTE NOTE: Looks for VARCHAR(...) as the datatype which isn't really a sqlite data type. :returns: True, length if column is found in table metadata """ source = layer.source() if ".sqlite" not in source: return False, 0 database = Database.fromLayer(layer) try: index = source.index("|") + 1 args = source[index:].split("=") args = dict(zip(args[0::2], args[1::2])) try: layer = args['layername'] except KeyError: return False, 0 except ValueError: layer = layer.name() try: tabledata = list(database.query("pragma table_info({})".format(layer))) for row in tabledata: if not row['name'] == fieldname: continue import re # Look for varchar(...) so we can grab the length. match = re.search("VARCHAR\((\d.*)\)", row['type'], re.IGNORECASE) if match: length = match.group(1) return True, int(length) except DatabaseException: pass finally: database.close() return False, 0
def _get_sqlite_col_length(layer, fieldname): """ Get the length of a sqlite based column using the metadata NOTE: SQLITE doesn't support this. And this is a bit of a hack. NOTE NOTE: Looks for VARCHAR(...) as the datatype which isn't really a sqlite data type. :returns: True, length if column is found in table metadata """ source = layer.source() if ".sqlite" not in source: return False, 0 database = Database.fromLayer(layer) uri = QgsDataSourceUri(layer.dataProvider().dataSourceUri()) layer = uri.quotedTablename() try: tabledata = list(database.query("pragma table_info({})".format(layer))) for row in tabledata: if not row['name'] == fieldname: continue import re # Look for varchar(...) so we can grab the length. match = re.search("VARCHAR\((\d.*)\)", row['type'], re.IGNORECASE) if match: length = match.group(1) return True, int(length) except DatabaseException: pass finally: database.close() return False, 0
def basedatadb(self): path = os.path.join(self.folder, "_data", "basedata.sqlite") return Database.connect(type="QSQLITE", database=path)