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}