def checkParameterValues(self, parameters, context): # Check that it corresponds to an existing connection connection_name = parameters[self.CONNECTION_NAME] dbpluginclass = createDbPlugin('postgis') connections = [c.connectionName() for c in dbpluginclass.connections()] if connection_name not in connections: return False, tr( 'The configured connection name does not exists in QGIS') # Check connection ok, uri, msg = getUriFromConnectionName(connection_name, True) if not ok: return False, msg # Check audit schema ok, msg = self.checkSchema('audit', parameters, context) if not ok: return False, msg # Check audit schema ok, msg = self.checkSchema('lizsync', parameters, context) if not ok: return False, msg return super(CreateDatabaseStructure, self).checkParameterValues(parameters, context)
def checkParameterValues(self, parameters, context): # Check that the connection name has been configured connection_name = QgsExpressionContextUtils.globalScope().variable( 'raepa_connection_name') if not connection_name: msg = 'Vous devez utiliser le l\'algorithme de configuration du plugin pour paramétrer le nom de connexion.' return False, msg # Check that it corresponds to an existing connection dbpluginclass = createDbPlugin('postgis') connections = [c.connectionName() for c in dbpluginclass.connections()] if connection_name not in connections: msg = 'La connexion "{}" n\'existe pas dans QGIS : {}'.format( connection_name, ', '.join(connections)) return False, msg # Check database content ok, msg = self.checkSchema(parameters, context) if not ok: return False, msg # Check inputs if len(parameters[self.CODE]) != 3: return False, 'Le nom abbrégé doit faire 3 exactement caractères.' if len(parameters[self.SIREN]) != 9: return False, "Le SIREN doit faire exactement 9 caractères." return super(CreateDatabaseStructure, self).checkParameterValues(parameters, context)
def checkParameterValues(self, parameters, context): # Check if runit is checked runit = self.parameterAsBool(parameters, self.RUNIT, context) if not runit: msg = tr('You must check the box to run the upgrade !') ok = False return ok, msg # Check that it corresponds to an existing connection connection_name = parameters[self.CONNECTION_NAME] dbpluginclass = createDbPlugin('postgis') connections = [c.connectionName() for c in dbpluginclass.connections()] if connection_name not in connections: return False, tr( 'The configured connection name does not exists in QGIS') # Check connection ok, uri, msg = getUriFromConnectionName(connection_name, True) if not ok: return False, msg # Check database content ok, msg = self.checkSchema(parameters, context) if not ok: return False, msg return super(UpgradeDatabaseStructure, self).checkParameterValues(parameters, context)
def checkParameterValues(self, parameters, context): # Check if runit is checked run_migration = self.parameterAsBool(parameters, self.RUN_MIGRATIONS, context) if not run_migration: msg = 'Vous devez cocher cette case à cocher pour faire la mise à jour !' ok = False return ok, msg # Check that the connection name has been configured connection_name = QgsExpressionContextUtils.globalScope().variable( 'raepa_connection_name') if not connection_name: msg = ( 'Vous devez utiliser l\'algorithme "Configuration du plugin RAEPA" pour paramètrer le nom de la ' 'connexion.') return False, msg # Check that it corresponds to an existing connection dbpluginclass = createDbPlugin('postgis') connections = [c.connectionName() for c in dbpluginclass.connections()] if connection_name not in connections: msg = ('La connexion configurée "{}" n\'existe pas dans QGIS : {}'. format(connection_name, ', '.join(connections))) return False, msg # Check database content ok, msg = self.checkSchema(parameters, context) if not ok: return False, msg return super(UpgradeDatabaseStructure, self).checkParameterValues(parameters, context)
def checkParameterValues(self, parameters, context): # Check if runit is checked run_delete = self.parameterAsBool(parameters, self.RUN_DELETE, context) if not run_delete: msg = tr( 'You must check the box to delete the spatial layer data !') ok = False return ok, msg # Check that the connection name has been configured connection_name = QgsExpressionContextUtils.globalScope().variable( 'gobs_connection_name') if not connection_name: return False, tr( 'You must use the "Configure G-obs plugin" alg to set the database connection name' ) # Check that it corresponds to an existing connection dbpluginclass = createDbPlugin('postgis') connections = [c.connectionName() for c in dbpluginclass.connections()] if connection_name not in connections: return False, tr( 'The configured connection name does not exists in QGIS') # Check layyer id is in the list of existing spatial layers spatial_layer_id = self.parameterAsInt(parameters, self.SPATIALLAYER_ID, context) if spatial_layer_id and spatial_layer_id > 0: if spatial_layer_id not in self.SPATIALLAYERS_DICT: return False, tr( 'Spatial layer ID does not exists in the database') return super(RemoveSpatialLayerData, self).checkParameterValues(parameters, context)
def checkParameterValues(self, parameters, context): # Check that the connection name has been configured connection_name = QgsExpressionContextUtils.globalScope().variable('raepa_connection_name') if not connection_name: msg = 'Vous devez utiliser le l\'algorithme de configuration du plugin pour paramétrer le nom de connexion.' return False, msg # Check that it corresponds to an existing connection dbpluginclass = createDbPlugin('postgis') connections = [c.connectionName() for c in dbpluginclass.connections()] if connection_name not in connections: msg = 'La connexion "{}" n\'existe pas dans QGIS : {}'.format( connection_name, ', '.join(connections)) return False, msg return super(ExecuteSql, self).checkParameterValues(parameters, context)
def initAlgorithm(self, config): # INPUTS connection_name = QgsExpressionContextUtils.globalScope().variable( 'gobs_connection_name') get_data = QgsExpressionContextUtils.globalScope().variable( 'gobs_get_database_data') # List of spatial_layer sql = ''' SELECT id, sl_label FROM gobs.spatial_layer ORDER BY sl_label ''' dbpluginclass = createDbPlugin('postgis') connections = [c.connectionName() for c in dbpluginclass.connections()] data = [] if get_data == 'yes' and connection_name in connections: [header, data, rowCount, ok, error_message] = fetchDataFromSqlQuery(connection_name, sql) self.SPATIALLAYERS = ['%s - %s' % (a[1], a[0]) for a in data] self.addParameter( QgsProcessingParameterEnum(self.SPATIALLAYER, tr('Target spatial layer'), options=self.SPATIALLAYERS, optional=False)) self.addParameter( QgsProcessingParameterVectorLayer(self.SOURCELAYER, tr('Source data layer'), optional=False)) self.addParameter( QgsProcessingParameterField( self.UNIQUEID, tr('Unique identifier'), parentLayerParameterName=self.SOURCELAYER)) self.addParameter( QgsProcessingParameterField( self.UNIQUELABEL, tr('Unique label'), parentLayerParameterName=self.SOURCELAYER, type=QgsProcessingParameterField.String)) # OUTPUTS # Add output for message self.addOutput( QgsProcessingOutputString(self.OUTPUT_STRING, tr('Output message')))
def initAlgorithm(self, config): # use parent class to get other parameters super(self.__class__, self).initAlgorithm(config) connection_name = QgsExpressionContextUtils.globalScope().variable('gobs_connection_name') get_data = QgsExpressionContextUtils.globalScope().variable('gobs_get_database_data') # Add spatial layer choice # List of spatial_layer sql = ''' SELECT id, sl_label FROM gobs.spatial_layer ORDER BY sl_label ''' dbpluginclass = createDbPlugin('postgis') connections = [c.connectionName() for c in dbpluginclass.connections()] data = [] if get_data == 'yes' and connection_name in connections: [header, data, rowCount, ok, error_message] = fetchDataFromSqlQuery( connection_name, sql ) self.SPATIALLAYERS = ['%s - %s' % (a[1], a[0]) for a in data] self.SPATIALLAYERS_DICT = {a[0]: a[1] for a in data} self.addParameter( QgsProcessingParameterEnum( self.SPATIALLAYER, tr('Spatial layer'), options=self.SPATIALLAYERS, optional=False ) ) # Id of spatial layer, to get the layer directly # mainly used from other processing algs p = QgsProcessingParameterNumber( self.SPATIALLAYER_ID, tr('Spatial layer ID. If given, it overrides previous choice'), optional=True, defaultValue=-1 ) p.setFlags(QgsProcessingParameterDefinition.FlagHidden) self.addParameter(p)
def checkParameterValues(self, parameters, context): # Check that the connection name has been configured connection_name = QgsExpressionContextUtils.globalScope().variable( 'gobs_connection_name') if not connection_name: return False, tr( 'You must use the "Configure G-obs plugin" alg to set the database connection name' ) # Check that it corresponds to an existing connection dbpluginclass = createDbPlugin('postgis') connections = [c.connectionName() for c in dbpluginclass.connections()] if connection_name not in connections: return False, tr( 'The configured connection name does not exists in QGIS') return super(GetDataAsLayer, self).checkParameterValues(parameters, context)
def setDbFromLayer(self, inLayer): # db helpers self.setDbConnName(inLayer) dbType = "postgis" # get connection to db if self.dbConnName: self.dbPluginClass = createDbPlugin(dbType, self.dbConnName) if self.dbPluginClass: try: self.dbConnection = self.dbPluginClass.connect() self.db = self.dbPluginClass.database() except BaseError, e: raise GeoAlgorithmExecutionException( "Error connecting to \ database %s. Exception: %s" % (self.dbConnName, str(e)) ) else: raise GeoAlgorithmExecutionException( "Error connecting to \ database %s" % self.dbConnName )
def run(self): # add connections to combobox self.dlg.comboBox_2.clear() dbpluginclass = createDbPlugin('postgis') connection_list = [] self.dbconnections = dbpluginclass.connections() for c in self.dbconnections: connection_list.append(unicode(c.connectionName())) c.connect() self.dlg.comboBox_2.addItems(connection_list) dbpluginclass.typeName() # show the dialog self.dlg.show() # Run the dialog event loop result = self.dlg.exec_() # See if OK was pressed if result: # Do something useful here - delete the line containing pass and # substitute with your code. pass
def checkParameterValues(self, parameters, context): # Check that the connection name has been configured connection_name_central = parameters[self.CONNECTION_NAME_CENTRAL] # Check that it corresponds to an existing connection dbpluginclass = createDbPlugin('postgis') connections = [c.connectionName() for c in dbpluginclass.connections()] if connection_name_central not in connections: return False, tr( 'The configured connection name does not exists in QGIS') # Check connection ok, uri, msg = getUriFromConnectionName(connection_name_central, True) if not ok: return False, msg # Check database content ok, msg = self.checkSchema(parameters, context) if not ok: return False, msg return super(InitializeCentralDatabase, self).checkParameterValues(parameters, context)
from qgis.core import * from db_manager.db_plugins.plugin import DBPlugin, Schema, Table, BaseError from db_manager.db_plugins import createDbPlugin from db_manager.dlg_db_error import DlgDbError from processing.core.VectorWriter import VectorWriter connectionName = unicode(Connection_name) dbTypeMap = { 0: 'postgis', 1: 'spatialite' } dbType = dbTypeMap[Database_type] progress.setText('%s' % dbType) # Get database connection via DbManager classes connection = None if connectionName: dbpluginclass = createDbPlugin( dbType, connectionName ) if dbpluginclass: try: connection = dbpluginclass.connect() except BaseError as e: progress.setText(e.msg) else: progress.setText('<b>## Please give a database connection name.</b>') # Run the Query and create vector layer layer = None if connection: db = dbpluginclass.database() if db: # get a new layer name
def initAlgorithm(self, config): # INPUTS connection_name = QgsExpressionContextUtils.globalScope().variable( 'gobs_connection_name') get_data = QgsExpressionContextUtils.globalScope().variable( 'gobs_get_database_data') # Add spatial layer choice # List of spatial_layer sql = ''' SELECT id, sl_label FROM gobs.spatial_layer ORDER BY sl_label ''' dbpluginclass = createDbPlugin('postgis') connections = [c.connectionName() for c in dbpluginclass.connections()] data = [] if get_data == 'yes' and connection_name in connections: [header, data, rowCount, ok, error_message] = fetchDataFromSqlQuery(connection_name, sql) self.SPATIALLAYERS = ['%s - %s' % (a[1], a[0]) for a in data] self.SPATIALLAYERS_DICT = {a[0]: a[1] for a in data} self.addParameter( QgsProcessingParameterEnum(self.SPATIALLAYER, tr('Spatial layer'), options=self.SPATIALLAYERS, optional=False)) # Id of spatial layer, to get the layer directly # mainly used from other processing algs p = QgsProcessingParameterNumber( self.SPATIALLAYER_ID, tr('Spatial layer ID. If given, it overrides previous choice'), optional=True, defaultValue=-1) p.setFlags(QgsProcessingParameterDefinition.FlagHidden) self.addParameter(p) # Confirmation self.addParameter( QgsProcessingParameterBoolean( self.RUN_DELETE, tr('Check this box to delete. No action will be done otherwise' ), defaultValue=False, )) # Delete the series self.addParameter( QgsProcessingParameterBoolean( self.DELETE_SPATIAL_LAYER, tr('Also delete the spatial layer item'), defaultValue=False, )) # OUTPUTS # Add output for status (integer) self.addOutput( QgsProcessingOutputNumber(self.OUTPUT_STATUS, tr('Output status'))) # Add output for message self.addOutput( QgsProcessingOutputString(self.OUTPUT_STRING, tr('Output message')))
def initAlgorithm(self, config): # use parent class to get other parameters super(self.__class__, self).initAlgorithm(config) connection_name = QgsExpressionContextUtils.globalScope().variable('gobs_connection_name') get_data = QgsExpressionContextUtils.globalScope().variable('gobs_get_database_data') # List of series sql = ''' SELECT s.id, concat( id_label, ' (', p.pr_label, ')', ' / Source: ', a_label, ' / Layer: ', sl_label ) AS label FROM gobs.series s INNER JOIN gobs.protocol p ON p.id = s.fk_id_protocol INNER JOIN gobs.actor a ON a.id = s.fk_id_actor INNER JOIN gobs.indicator i ON i.id = s.fk_id_indicator INNER JOIN gobs.spatial_layer sl ON sl.id = s.fk_id_spatial_layer ORDER BY label ''' dbpluginclass = createDbPlugin('postgis') connections = [c.connectionName() for c in dbpluginclass.connections()] data = [] if get_data == 'yes' and connection_name in connections: [header, data, rowCount, ok, error_message] = fetchDataFromSqlQuery( connection_name, sql ) self.SERIES = ['%s' % a[1] for a in data] self.SERIES_DICT = {a[1]: a[0] for a in data} self.addParameter( QgsProcessingParameterEnum( self.SERIE, tr('Series of observations'), options=self.SERIES, optional=False ) ) # Id of series, to get the series directly # mainly used from other processing algs p = QgsProcessingParameterNumber( self.SERIE_ID, tr('Series ID. If given, it overrides previous choice'), optional=True, defaultValue=-1 ) p.setFlags(QgsProcessingParameterDefinition.FlagHidden) self.addParameter(p) # Add spatial object data ? self.addParameter( QgsProcessingParameterBoolean( self.ADD_SPATIAL_OBJECT_DATA, tr('Add spatial object ID and label ?'), defaultValue=True, optional=False ) ) # Add spatial object geometry ? self.addParameter( QgsProcessingParameterBoolean( self.ADD_SPATIAL_OBJECT_GEOM, tr('Add spatial object geometry ?'), defaultValue=False, optional=False ) ) # Aggregate with a temporal resolution, such as hour, day, etc. ? self.TEMPORAL_RESOLUTIONS = [ 'original', 'second', 'minute', 'hour', 'day', 'week', 'month', 'year' ] self.addParameter( QgsProcessingParameterEnum( self.TEMPORAL_RESOLUTION, tr('Timestamp extraction resolution'), options=self.TEMPORAL_RESOLUTIONS, optional=False ) ) # Aggregate functions self.addParameter( QgsProcessingParameterEnum( self.AGGREGATE_FUNCTIONS, tr('Choose aggregate functions to use'), options=self.AGGREGATE_FUNCTIONS_LIST, optional=False, allowMultiple=True, defaultValue=[a for a in range(len(self.AGGREGATE_FUNCTIONS_LIST))] ) ) # Aggregate with a value # self.addParameter( # QgsProcessingParameterBoolean( # self.GROUP_BY_DISTINCT_VALUES, # tr('Group by the distinct values'), # defaultValue=False, # optional=False # ) # ) # Min timestamp self.addParameter( QgsProcessingParameterString( self.MIN_TIMESTAMP, tr('Minimum observation timestamp, Ex: 2019-01-01 or 2019-01-06 00:00:00'), defaultValue='', optional=True ) ) # Max timestamp self.addParameter( QgsProcessingParameterString( self.MAX_TIMESTAMP, tr('Maximum observation timestamp, Ex:2019-12-31 or 2019-12-31 23:59:53'), defaultValue='', optional=True ) )
##layer_name=string Choose_name import psycopg2 from PyQt4.QtSql import * from qgis.PyQt.QtCore import * from qgis.core import * from qgis.utils import * from db_manager.db_plugins.plugin import DBPlugin, Schema, Table, BaseError from db_manager.db_plugins import createDbPlugin from db_manager.dlg_db_error import DlgDbError dbTypeMap = {0: 'postgis', 1: 'spatialite'} #Connection and creation of the cursor dbpluginclass = createDbPlugin(dbTypeMap[Database_type], Database_name) cnx = dbpluginclass.connect() db = dbpluginclass.database() connector = db.connector cursorVector = connector._get_cursor() #Request to get the key of the d_vector field and indicator type wo will be used in the function sqlQueryVector = ''' SELECT jsonb_object_keys(d.d_vector), i.i_type FROM geoobs.indicator i, geoobs.data d WHERE i.id = %i AND i.id = d.fk_id_indicator LIMIT 1 ''' % (id_indicator) cursorVector.execute(sqlQueryVector)
def fetchDataFromSqlQuery(connection_name, sql): from db_manager.db_plugins.plugin import BaseError from db_manager.db_plugins import createDbPlugin from db_manager.db_plugins.postgis.connector import PostGisDBConnector header = None data = [] header = [] rowCount = 0 error_message = None connection = None # Create plugin class and try to connect ok = True try: dbpluginclass = createDbPlugin('postgis', connection_name) connection = dbpluginclass.connect() except BaseError as e: # DlgDbError.showError(e, self.dialog) ok = False error_message = e.msg except Exception: ok = False error_message = 'Connexion à la base de données impossible' if not connection: return [header, data, rowCount, ok, error_message] db = dbpluginclass.database() if not db: ok = False error_message = 'Impossible de récupérer la base de données depuis la connexion' return [header, data, rowCount, ok, error_message] # Get URI uri = db.uri() try: connector = PostGisDBConnector(uri) except Exception: error_message = 'Impossible de se connecer à la base de données' ok = False return [header, data, rowCount, ok, error_message] c = None ok = True # print "run query" try: c = connector._execute(None, str(sql)) data = [] header = connector._get_cursor_columns(c) if header is None: header = [] if len(header) > 0: data = connector._fetchall(c) rowCount = c.rowcount if rowCount == -1: rowCount = len(data) except BaseError as e: ok = False error_message = e.msg return [header, data, rowCount, ok, error_message] finally: if c: c.close() del c # Log errors if not ok: error_message = 'Une erreur inconnue lors de la récupération des données' return [header, data, rowCount, ok, error_message] print(error_message) print(sql) return [header, data, rowCount, ok, error_message]
def initAlgorithm(self, config): # INPUTS connection_name = QgsExpressionContextUtils.globalScope().variable('gobs_connection_name') get_data = QgsExpressionContextUtils.globalScope().variable('gobs_get_database_data') # List of series sql = ''' SELECT s.id, concat( id_label, ' (', p.pr_label, ')', ' / Source: ', a_label, ' / Layer: ', sl_label ) AS label FROM gobs.series s INNER JOIN gobs.protocol p ON p.id = s.fk_id_protocol INNER JOIN gobs.actor a ON a.id = s.fk_id_actor INNER JOIN gobs.indicator i ON i.id = s.fk_id_indicator INNER JOIN gobs.spatial_layer sl ON sl.id = s.fk_id_spatial_layer ORDER BY label ''' dbpluginclass = createDbPlugin('postgis') connections = [c.connectionName() for c in dbpluginclass.connections()] data = [] if get_data == 'yes' and connection_name in connections: [header, data, rowCount, ok, error_message] = fetchDataFromSqlQuery( connection_name, sql ) self.SERIES = ['%s' % a[1] for a in data] self.SERIES_DICT = {a[1]: a[0] for a in data} self.addParameter( QgsProcessingParameterEnum( self.SERIE, tr('Series of observations'), options=self.SERIES, optional=False ) ) # Id of series, to get the series directly # mainly used from other processing algs p = QgsProcessingParameterNumber( self.SERIE_ID, tr('Series ID. If given, it overrides previous choice'), optional=True, defaultValue=-1 ) p.setFlags(QgsProcessingParameterDefinition.FlagHidden) self.addParameter(p) # Confirmation self.addParameter( QgsProcessingParameterBoolean( self.RUN_DELETE, tr('Check this box to delete. No action will be done otherwise'), defaultValue=False, ) ) # Delete the series self.addParameter( QgsProcessingParameterBoolean( self.DELETE_SERIES, tr('Also delete the series item'), defaultValue=False, ) ) # OUTPUTS # Add output for status (integer) self.addOutput( QgsProcessingOutputNumber( self.OUTPUT_STATUS, tr('Output status') ) ) # Add output for message self.addOutput( QgsProcessingOutputString( self.OUTPUT_STRING, tr('Output message') ) )
def initAlgorithm(self, config): # use parent class to get other parameters super(self.__class__, self).initAlgorithm(config) connection_name = QgsExpressionContextUtils.globalScope().variable('gobs_connection_name') get_data = QgsExpressionContextUtils.globalScope().variable('gobs_get_database_data') # List of series sql = ''' SELECT s.id, concat( id_label, ' (', p.pr_label, ')', ' / Source: ', a_label, ' / Layer: ', sl_label ) AS label FROM gobs.series s INNER JOIN gobs.protocol p ON p.id = s.fk_id_protocol INNER JOIN gobs.actor a ON a.id = s.fk_id_actor INNER JOIN gobs.indicator i ON i.id = s.fk_id_indicator INNER JOIN gobs.spatial_layer sl ON sl.id = s.fk_id_spatial_layer ORDER BY label ''' dbpluginclass = createDbPlugin('postgis') connections = [c.connectionName() for c in dbpluginclass.connections()] data = [] if get_data == 'yes' and connection_name in connections: [header, data, rowCount, ok, error_message] = fetchDataFromSqlQuery( connection_name, sql ) self.SERIES = ['%s' % a[1] for a in data] self.SERIES_DICT = {a[1]: a[0] for a in data} self.addParameter( QgsProcessingParameterEnum( self.SERIE, tr('Series of observations'), options=self.SERIES, optional=False ) ) # Id of series, to get the series directly # mainly used from other processing algs p = QgsProcessingParameterNumber( self.SERIE_ID, tr('Series ID. If given, it overrides previous choice'), optional=True, defaultValue=-1 ) p.setFlags(QgsProcessingParameterDefinition.FlagHidden) self.addParameter(p) # Add spatial object geometry ? self.addParameter( QgsProcessingParameterBoolean( self.ADD_SPATIAL_OBJECT_GEOM, tr('Add spatial object geometry ?'), defaultValue=False, optional=False ) )