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)
def basedatadb(self):
path = os.path.join(self.folder, "_data", "basedata.sqlite")
return Database.connect(type="QSQLITE", database=path)