def insert_record(self):
"""Insert record in the database
The method reads the informations in the active insert form
(Books, Authors, Publishers or Series) and uses them to insert
a new record in the database.
"""
sql_query = QSqlQuery()
if self.layout_insert.currentIndex() == 0:
# Get text from insert form
isbn = QVariant(self.book_insert.isbn.text())
title = QVariant(self.book_insert.title.text())
author = QVariant(self.book_insert.author.text())
otherauthors = QVariant(self.book_insert.otherauthors.text())
publisher = QVariant(self.book_insert.publisher.text())
series = QVariant(self.book_insert.series.text())
subseries = QVariant(self.book_insert.subseries.text())
category = QVariant(self.book_insert.category.text())
language = QVariant(self.book_insert.language.text())
year = QVariant(self.book_insert.year.text())
pages = QVariant(self.book_insert.pages.text())
owner = QVariant(self.book_insert.owner.currentText())
booktype = QVariant(self.book_insert.booktype.currentText())
# Set values to None where strings are empty
if isbn.value() == '':
isbn = QVariant()
else:
isbn = isbn.value()
# Check is the ISBN is valid
if ISBN_CHECK:
if '-' in isbn:
isbn = canonical(isbn)
if len(isbn) == 10:
if not is_isbn10(isbn):
# Show an error if the ISBN is invalid
error = ErrorDialog(
'The ISBN inserted is invalid. Operation failed.'
)
error.show()
return
elif len(isbn) == 13:
if not is_isbn13(isbn):
# Show an error if the ISBN is invalid
error = ErrorDialog(
'The ISBN inserted is invalid. Operation failed.'
)
error.show()
return
else:
# Show an error if the ISBN is invalid
error = ErrorDialog(
'The ISBN inserted is invalid. Operation failed.')
error.show()
return
isbn = QVariant(isbn)
if title.value() == '':
title = QVariant()
if author.value() == '':
author = QVariant()
if otherauthors.value() == '':
otherauthors = QVariant()
if publisher.value() == '':
publisher = QVariant()
if series.value() == '':
series = QVariant()
if subseries.value() == '':
subseries = QVariant()
if category.value() == '':
category = QVariant()
if language.value() == '':
language = QVariant()
if year.value() == '':
year = QVariant()
if pages.value() == '':
pages = QVariant()
if owner.value() == '':
owner = QVariant()
if booktype.value() == '':
booktype = QVariant()
# Get Author Id from Name
if not author.isNull():
value = author.value().replace("'", "\\'")
sql_query.prepare(
f'SELECT Id FROM Authors WHERE Name LIKE \'%{value}%\'')
if sql_query.exec_():
if sql_query.size() == 0:
# Author cannot be NULL, show error
error = ErrorDialog(
'Author not found. Operation failed')
error.show()
return
if sql_query.size() == 1:
sql_query.next()
author = sql_query.value(0)
else:
# Show warning if string matches multiple authors
warning = WarningDialog('String matches multiple authors. ' +\
'Using exact match')
warning.show()
# Get Author Id from Name using exact match
sql_query.prepare(
f'SELECT Id FROM Authors WHERE Name=\'{author.value()}\''
)
if sql_query.exec_():
if sql_query.size() == 0:
# Author cannot be NULL, show error
error = ErrorDialog(
'Author not found. Operation failed')
error.show()
return
if sql_query.size() == 1:
sql_query.next()
author = sql_query.value(0)
else:
#Show error if query failed
error = ErrorDialog(
str(sql_query.lastError().databaseText()))
error.show()
return
else:
#Show error if query failed
error = ErrorDialog(
str(sql_query.lastError().databaseText()))
error.show()
return
# Get Publisher Id from Name
if not publisher.isNull():
value = publisher.value().replace("'", "\\'")
sql_query.prepare('SELECT Id FROM Publishers WHERE ' +\
f'Name LIKE \'%{value}%\'')
if sql_query.exec_():
if sql_query.size() == 0:
publisher = QVariant()
# Show warning if string doesn't match any Publisher
warning = WarningDialog(
'Publisher not found, set to \'NULL\'')
warning.show()
elif sql_query.size() == 1:
sql_query.next()
publisher = sql_query.value(0)
else:
# Show warning if string matches multiple publishers
warning = WarningDialog('String matches multiple publishers.' +\
'Using exact match')
warning.show()
# Get Publisher Id from Name using exact match
sql_query.prepare('SELECT Id FROM Publishers WHERE ' +\
f'Name=\'{publisher.value()}\'')
if sql_query.exec_():
if sql_query.size() == 0:
publisher = QVariant()
# Show warning if exact match is not found
warning = WarningDialog(
'Publisher not found, set to \'NULL\'')
warning.show()
elif sql_query.size() == 1:
sql_query.next()
publisher = sql_query.value(0)
else:
#Show error if query failed
error = ErrorDialog(
str(sql_query.lastError().databaseText()))
error.show()
return
else:
#Show error if query failed
error = ErrorDialog(
str(sql_query.lastError().databaseText()))
error.show()
return
# Get Series Id from Name
if not series.isNull():
value = series.value().replace("'", "\\'")
sql_query.prepare(
f'SELECT Id FROM Series WHERE Name LIKE \'%{value}%\'')
if sql_query.exec_():
if sql_query.size() == 0:
series = QVariant()
# Show warning if string doesn't match any Series
warning = WarningDialog(
'Series not found, set to \'NULL\'')
warning.show()
elif sql_query.size() == 1:
sql_query.next()
series = sql_query.value(0)
else:
# Show warning is string matches multiple Series
warning = WarningDialog(
'String matches multiple series. Using exact match'
)
warning.show()
# Get Series Id from Name using exact match
sql_query.prepare(
f'SELECT Id FROM Series WHERE Name=\'{series.value()}\''
)
if sql_query.exec_():
if sql_query.size() == 0:
series = QVariant()
# Show warning if exact match is not found
warning = WarningDialog(
'Series not found, set to \'NULL\'')
warning.show()
elif sql_query.size() == 1:
sql_query.next()
series = sql_query.value(0)
else:
#Show error if query failed
error = ErrorDialog(
str(sql_query.lastError().databaseText()))
error.show()
return
else:
#Show error if query failed
error = ErrorDialog(
str(sql_query.lastError().databaseText()))
error.show()
return
sql_query.prepare('INSERT INTO Books(ISBN, Title, Author, OtherAuthors, Publisher, ' +\
'Series, Subseries, Category, Language, Year, Pages, Owner, Type) ' +\
'Values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)')
sql_query.bindValue(0, isbn)
sql_query.bindValue(1, title)
sql_query.bindValue(2, author)
sql_query.bindValue(3, otherauthors)
sql_query.bindValue(4, publisher)
sql_query.bindValue(5, series)
sql_query.bindValue(6, subseries)
sql_query.bindValue(7, category)
sql_query.bindValue(8, language)
sql_query.bindValue(9, year)
sql_query.bindValue(10, pages)
sql_query.bindValue(11, owner)
sql_query.bindValue(12, booktype)
# Execute the query
if sql_query.exec_():
# Show message if insertion succeeded
info = InfoDialog('Record inserted successfully into Books')
info.show()
else:
# Create error message box
error = ErrorDialog(str(sql_query.lastError().databaseText()))
error.show()
elif self.layout_insert.currentIndex() == 1:
# Get text from insert form
name = QVariant(self.author_insert.name.text())
gender = QVariant(self.author_insert.gender.currentText())
nationality = QVariant(self.author_insert.nationality.text())
birthyear = QVariant(self.author_insert.birthyear.text())
deathyear = QVariant(self.author_insert.deathyear.text())
# Set values to None where strings are empty
if name.value() == '':
name = QVariant()
if gender.value() == '':
gender = QVariant()
if nationality.value() == '':
nationality = QVariant()
if birthyear.value() == '':
birthyear = QVariant()
if deathyear.value() == '':
deathyear = QVariant()
sql_query.prepare('INSERT INTO Authors(' +\
'Name, Gender, Nationality, BirthYear, DeathYear) ' +\
'Values(?, ?, ?, ?, ?)')
sql_query.bindValue(0, name)
sql_query.bindValue(1, gender)
sql_query.bindValue(2, nationality)
sql_query.bindValue(3, birthyear)
sql_query.bindValue(4, deathyear)
# Execute the query
if sql_query.exec_():
# Show message if insertion succeeded
info = InfoDialog('Record inserted successfully into Authors')
info.show()
else:
# Create error message box
error = ErrorDialog(str(sql_query.lastError().databaseText()))
error.show()
elif self.layout_insert.currentIndex() == 2:
# Get text from insert form
name = QVariant(self.publisher_insert.name.text())
# Set value to None if string is empty
if name.value() == '':
name = QVariant()
sql_query.prepare('INSERT INTO Publishers(Name) Values(?)')
sql_query.bindValue(0, name)
# Execute the query
if sql_query.exec_():
# Show message if insertion succeeded
info = InfoDialog(
'Record inserted successfully into Publishers')
info.show()
else:
# Create error message box
error = ErrorDialog(str(sql_query.lastError().databaseText()))
error.show()
elif self.layout_insert.currentIndex() == 3:
# Get text from insert form
name = QVariant(self.series_insert.name.text())
author = QVariant(self.series_insert.author.text())
# Set values to None where strings are empty
if name.value() == '':
name = QVariant()
if author.value() == '':
author = QVariant()
if not author.isNull():
# Get Author Id from Name
value = author.value().replace("'", "\\'")
sql_query.prepare(
f'SELECT Id FROM Authors WHERE Name LIKE \'%{value}%\'')
if sql_query.exec_():
if sql_query.size() == 0:
# Author cannot be NULL, show error
error = ErrorDialog(
'Author not found. Operation failed')
error.show()
return
if sql_query.size() == 1:
sql_query.next()
author = sql_query.value(0)
else:
# Show warning if string matches multiple authors
warning = WarningDialog('String matches multiple authors. ' +\
'Using exact match')
warning.show()
# Get Author Id from Name using exact match
sql_query.prepare(f'SELECT Id FROM Authors WHERE ' +\
'Name = \'{author.value()}\'')
if sql_query.exec_():
if sql_query.size() == 0:
# Author cannot be NULL, show error
error = ErrorDialog(
'Author not found. Operation failed')
error.show()
return
if sql_query.size() == 1:
sql_query.next()
author = sql_query.value(0)
else:
#Show error if query failed
error = ErrorDialog(
str(sql_query.lastError().databaseText()))
error.show()
else:
#Show error if query failed
error = ErrorDialog(
str(sql_query.lastError().databaseText()))
error.show()
sql_query.prepare('INSERT INTO Series(Name, Author) Values(?, ?)')
sql_query.bindValue(0, name)
sql_query.bindValue(1, author)
# Execute the query
if sql_query.exec_():
# Show message if insertion succeeded
info = InfoDialog('Record inserted successfully into Series')
info.show()
else:
# Create error message box
error = ErrorDialog(str(sql_query.lastError().databaseText()))
error.show()
def processAlgorithm(self, parameters, context, feedback):
"""
"""
self.points_input_layer = self.parameterAsVectorLayer(
parameters, QScoutValueGrabberAlgorithm.POINTS_INPUT, context)
grid_w = self.parameterAsDouble(parameters, self.GRID_CELL_W_INPUT,
context)
grid_h = self.parameterAsDouble(parameters, self.GRID_CELL_H_INPUT,
context)
fields_to_use = self.parameterAsFields(parameters,
self.FIELDS_TO_USE_INPUT,
context)
ag_idx = self.parameterAsEnum(parameters,
self.AGGREGATION_FUNCTION_INPUT, context)
bounds = self.parameterAsExtent(parameters, self.GRID_EXTENT_INPUT,
context)
file_out = self.parameterAsFileOutput(parameters, self.FILE_OUTPUT,
context)
if bounds is None or bounds.area() == 0:
# if the user didn't provide an extent, use the extent of the points layer
bounds = self.points_input_layer.extent()
else:
# if the user provided an extent, it will be in the project CRS, which isn't nessecarily the same as the
# points layer, so we gotta run a conversion
bounds_crs = self.parameterAsExtentCrs(parameters,
self.GRID_EXTENT_INPUT,
context)
bounds_crs_convert = QgsCoordinateTransform(
bounds_crs, self.points_input_layer.crs(),
QgsProject.instance().transformContext())
bounds = bounds_crs_convert.transformBoundingBox(bounds)
aggregation_class = AGGREGATION_FUNCTIONS[list(
AGGREGATION_FUNCTIONS.keys())[ag_idx]]
if aggregation_class is not None:
aggregator = aggregation_class(self)
else:
# load custom aggregation function
ag_func_file = self.parameterAsFile(
parameters, self.CUSTOM_AGGREGATION_FUNCTION_INPUT, context)
spec = spec_from_file_location(
ag_func_file[ag_func_file.find(sep):ag_func_file.find(".")],
ag_func_file)
module = module_from_spec(spec)
spec.loader.exec_module(module)
aggregator = module.Aggregator(self)
assert grid_w > 0, "Grid width must be greater than zero."
assert grid_h > 0, "Grid height must be greater than zero.s"
input_fields = []
self.output_fields = QgsFields()
if aggregator.manual_field_ag():
for field_name, field_dtype in aggregator.return_vals():
self.feature_output_fields().add(field_name, field_dtype)
input_fields = [f.name() for f in self.points_input_layer.fields()]
for field in self.points_input_layer.fields():
if field.name() in fields_to_use:
assert field.type() in ALLOWED_TYPES or aggregation_class is None, \
"Wrong dtype %s. Only int or double field types are supported." % field.typeName()
# allow aggregators for non-numeric data types if using a custom aggregation class
for return_val_name, return_val_dtype in aggregator.return_vals(
):
return_val_dtype = field.type(
) if return_val_dtype is None else return_val_dtype
self.feature_output_fields().append(
QgsField(return_val_name + field.name(),
return_val_dtype))
input_fields.append(field.name())
assert len(input_fields) == self.output_fields.size()
grid_cells = self.setup_grid(bounds, grid_w, grid_h)
xstart = floor(bounds.xMinimum())
ystart = floor(bounds.yMinimum())
df_w = ceil(bounds.width() / grid_w) + 1
df_h = ceil(bounds.height() / grid_h) + 1
fprogress = 0
ftotal = self.points_input_layer.featureCount() + (df_w * df_h)
for feature in self.feature_input():
if feedback.isCanceled():
return {
self.AGGREGATE_GRID_OUTPUT: None,
self.FILE_OUTPUT: None
}
if feature.hasGeometry() and feature.geometry() is not None:
point = feature.geometry().asPoint()
x = int((point.x() - xstart) / grid_w)
y = int((point.y() - ystart) / grid_h)
if (x, y) in grid_cells:
vals_dict = {
f: (feature[f].value()
if not QVariant.isNull(feature[f]) else np.NAN)
if isinstance(feature[f], QVariant) else feature[f]
for f in input_fields
}
grid_cells[(x, y)].add_point(point, vals_dict)
else:
feedback.pushInfo("(%s, %s) outside bounds." %
(point.x(), point.y()))
else:
feedback.pushInfo("Feature %s has no geometry. Skipping." %
feature.id())
fprogress += 1
feedback.setProgress(100 * int(fprogress / ftotal))
dest_id = self.create_sink(parameters, self.AGGREGATE_GRID_OUTPUT,
context, QgsWkbTypes.Polygon)
count = 0
if file_out:
grid_frame = pd.DataFrame(index=range(len(grid_cells)),
columns=["x", "y"] +
[f.name() for f in self.output_fields] +
["Point Count"],
dtype=np.float32)
i = 0
for x, y in grid_cells:
if feedback.isCanceled():
return {
self.AGGREGATE_GRID_OUTPUT: None,
self.FILE_OUTPUT: None
}
cell_coords = (x, y)
cell = grid_cells[cell_coords]
feature = QgsFeature(count)
feature.setGeometry(QgsGeometry.fromRect(cell.rect))
cell_values = aggregator.aggregate(cell)
if file_out:
centroid = cell.rect.center()
grid_frame.iloc[i]["x"] = centroid.x()
grid_frame.iloc[i]["y"] = centroid.y()
grid_frame.iloc[i]["Point Count"] = cell.point_count()
grid_frame.iloc[i][[f.name()
for f in self.output_fields]] = cell_values
i += 1
assert len(cell_values) == self.feature_output_fields().size()
feature.setAttributes(cell_values)
count = self.append_to_feature_output(feature, count)
fprogress += 1
feedback.setProgress(100 * int(fprogress / ftotal))
if file_out:
if file_out.endswith(".xlsx"):
grid_frame.to_excel(file_out, index=False)
else:
grid_frame.to_csv(file_out, index=False)
with open(file_out) as fout:
pass # make and close file io object
else:
fout = None
feedback.pushWarning("File path '%s' invalid." % file_out)
return {self.AGGREGATE_GRID_OUTPUT: dest_id, self.FILE_OUTPUT: fout}
