コード例 #1
0
    def test_foreign_table_server(self):
        """Test foreign table with server"""

        md = QgsProviderRegistry.instance().providerMetadata(self.providerKey)
        conn = md.createConnection(self.uri, {})

        uri = QgsDataSourceUri(conn.uri())
        host = uri.host()
        port = uri.port()
        user = uri.username()
        dbname = uri.database()
        password = uri.password()
        service = uri.service()

        foreign_table_definition = """
        CREATE EXTENSION IF NOT EXISTS postgres_fdw;
        CREATE SERVER IF NOT EXISTS postgres_fdw_test_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (service '{service}', dbname '{dbname}', host '{host}', port '{port}');
        DROP SCHEMA  IF EXISTS foreign_schema CASCADE;
        CREATE SCHEMA IF NOT EXISTS foreign_schema;
        CREATE USER MAPPING IF NOT EXISTS FOR CURRENT_USER SERVER postgres_fdw_test_server OPTIONS (user '{user}', password '{password}');
        IMPORT FOREIGN SCHEMA qgis_test LIMIT TO ( "someData" )
        FROM SERVER postgres_fdw_test_server
        INTO foreign_schema;
        """.format(host=host,
                   user=user,
                   port=port,
                   dbname=dbname,
                   password=password,
                   service=service)
        conn.executeSql(foreign_table_definition)
        self.assertEquals(
            conn.tables(
                'foreign_schema',
                QgsAbstractDatabaseProviderConnection.Foreign)[0].tableName(),
            'someData')
コード例 #2
0
 def getParametersFromLyr(self, dbName):
     for lyr in self.iface.mapCanvas().layers():
         if isinstance(lyr, QgsVectorLayer):
             candidateUri = QgsDataSourceUri(
                 lyr.dataProvider().dataSourceUri())
             if candidateUri.database() == dbName or \
                     os.path.splitext(os.path.basename(candidateUri.uri().split('|')[0]))[0] == dbName:
                 currLyr = lyr
                 break
     dbParameters = dict()
     if currLyr.providerType() == 'postgres':
         dbParameters['host'] = candidateUri.host()
         dbParameters['port'] = candidateUri.port()
         dbParameters['user'] = candidateUri.username()
         dbParameters['password'] = candidateUri.password()
         return dbParameters, DsgEnums.DriverPostGIS
     elif currLyr.providerType() == 'spatialite':
         dbParameters['dbPath'] = candidateUri.database()
         return dbParameters, DsgEnums.DriverSpatiaLite
     elif currLyr.providerType() == 'ogr':
         # geopackage provider type is ogr
         dbParameters['dbPath'] = candidateUri.database()
         return dbParameters, DsgEnums.DriverGeopackage
     else:
         raise Exception(
             self.tr('Feature only implemented for PostGIS and Spatialite'))
コード例 #3
0
    def setUpClass(cls):
        # Make sure there are no other loaders
        del os.environ['QGIS_SERVER_PROJECTS_DIRECTORIES']
        cls.pg_conn = os.environ.get('QGIS_SERVER_LANDING_PAGE_PG_TEST', False)
        if not cls.pg_conn:  # default
            cls.pg_conn = "host=localhost port=5432 schema=public"

        # Use QGIS API to create the test data
        md = QgsProviderRegistry.instance().providerMetadata('postgres')
        conn = md.createConnection(cls.pg_conn, {})
        conn.executeSql('DROP DATABASE landing_page_test')
        conn.executeSql('CREATE DATABASE landing_page_test')

        # Add DB to conn string
        cls.pg_conn = "dbname=landing_page_test " + cls.pg_conn
        conn = md.createConnection(cls.pg_conn, {})
        conn.executeSql(
            open(
                os.path.join(os.path.dirname(__file__),
                             'landing_page_test.sql'), 'rt').read())

        uri = QgsDataSourceUri(cls.pg_conn)
        cls.pg_storage_conn = "postgresql://{host}:{port}?sslmode=disable&dbname=landing_page_test&schema=public".format(
            host=uri.host(), port=uri.port())
        os.environ['QGIS_SERVER_PROJECTS_PG_CONNECTIONS'] = cls.pg_storage_conn
        cls.server = QgsServer()
        cls.api = LandingPageApiLoader(cls.server.serverInterface())
コード例 #4
0
    def publishLayer(self, layer, fields=None):
        if layer.type() == layer.VectorLayer:
            if layer.featureCount() == 0:
                self.logError(
                    "Layer contains zero features and cannot be published")
                return

            if layer.dataProvider().name(
            ) == "postgres" and self.useOriginalDataSource:
                from .postgis import PostgisServer
                uri = QgsDataSourceUri(layer.source())
                db = PostgisServer("temp", uri.authConfigId(), uri.host(),
                                   uri.port(), uri.schema(), uri.database())
                self._publishVectorLayerFromPostgis(layer, db)
            elif self.storage in [
                    self.FILE_BASED, self.POSTGIS_MANAGED_BY_GEOSERVER
            ]:
                if layer.source() not in self._exportedLayers:
                    if self.storage == self.POSTGIS_MANAGED_BY_GEOSERVER:
                        path = exportLayer(layer,
                                           fields,
                                           toShapefile=True,
                                           force=True,
                                           log=self)
                        basename = os.path.splitext(path)[0]
                        zipfilename = basename + ".zip"
                        with ZipFile(zipfilename, 'w') as z:
                            for ext in [".shp", ".shx", ".prj", ".dbf"]:
                                filetozip = basename + ext
                                z.write(filetozip,
                                        arcname=os.path.basename(filetozip))
                        self._exportedLayers[layer.source()] = zipfilename
                    else:
                        path = exportLayer(layer, fields, log=self)
                        self._exportedLayers[layer.source()] = path
                filename = self._exportedLayers[layer.source()]
                if self.storage == self.FILE_BASED:
                    self._publishVectorLayerFromFile(layer, filename)
                else:
                    self._publishVectorLayerFromFileToPostgis(layer, filename)
            elif self.storage == self.POSTGIS_MANAGED_BY_BRIDGE:
                try:
                    from .servers import allServers
                    db = allServers()[self.postgisdb]
                except KeyError:
                    raise Exception(
                        QCoreApplication.translate(
                            "GeocatBridge",
                            "Cannot find the selected PostGIS database"))
                db.importLayer(layer, fields)
                self._publishVectorLayerFromPostgis(layer, db)
        elif layer.type() == layer.RasterLayer:
            if layer.source() not in self._exportedLayers:
                path = exportLayer(layer, fields, log=self)
                self._exportedLayers[layer.source()] = path
            filename = self._exportedLayers[layer.source()]
            self._publishRasterLayer(filename, layer.name())
        self._clearCache()
コード例 #5
0
    def processAlgorithm(self, parameters, context, feedback):
        # Retrieving parameters
        if qgs_version < 31400:
            connection_name = self.parameterAsString(parameters, self.DATABASE,
                                                     context)
            db = postgis.GeoDB.from_name(connection_name)
            uri = db.uri

            schema = self.parameterAsString(parameters, self.SCHEMA, context)
        else:
            connection_name = self.parameterAsConnectionName(
                parameters, self.DATABASE, context)
            md = QgsProviderRegistry.instance().providerMetadata('postgres')
            conn = md.createConnection(connection_name)
            uri = QgsDataSourceUri(conn.uri())

            schema = self.parameterAsSchema(parameters, self.SCHEMA, context)

        shapec = self.parameterAsFile(parameters, self.SHAPEFILE_CLIP, context)
        shapef = parameters[self.SHAPEFILE_FOLDER]

        # Execute ogr2ogr only in case the shape is Valid or no shape was provided
        shapec_qgs = QgsVectorLayer(shapec, 'test_valid', 'ogr')

        if shapec_qgs.isValid() or shapec == '':
            feedback.pushInfo("Valid shape")
        else:
            raise QgsProcessingException("Invalid shape")

        # Text for ogr2ogr
        if shapec == '':
            target_ogr = (
                'ogr2ogr -fieldTypeToString IntegerList,Integer64List,RealList,StringList '
                '-lco ENCODING=UTF-8 -f "ESRI Shapefile" {} -overwrite '
            ).format(shapef)
        else:
            target_ogr = (
                'ogr2ogr -fieldTypeToString IntegerList,Integer64List,RealList,StringList '
                '-lco ENCODING=UTF-8 -f "ESRI Shapefile" {} -clipsrc {} -overwrite '
            ).format(shapef, shapec)

        source_ogr = 'PG:"host={} dbname={} schemas={} port={} user={} password={}" '.format(
            uri.host(), uri.database(), schema, uri.port(), uri.username(),
            uri.password())
        string_ogr = target_ogr + source_ogr
        feedback.pushInfo('Text for ogr2ogr = ' + string_ogr)

        # Export schema to shapefile
        try:
            process = subprocess.run(string_ogr,
                                     shell=True,
                                     check=True,
                                     stdout=subprocess.PIPE,
                                     stderr=subprocess.PIPE)
        except subprocess.CalledProcessError as e:
            raise QgsProcessingException(str(e.stderr.decode('utf-8')))

        return {'Result': 'Exported'}
コード例 #6
0
ファイル: histoevent.py プロジェクト: sitn/Historisation
    def fromOtherLayerUri(uri: QgsDataSourceUri):
        histoEventUri = QgsDataSourceUri()
        histoEventUri.setConnection(uri.host(), uri.port(), uri.database(),
                                    uri.username(), uri.password(),
                                    uri.sslMode(), uri.authConfigId())
        histoEventUri.setDataSource(uri.schema(), "histo_event", None, None,
                                    "id")

        return HistoEvent(histoEventUri)
コード例 #7
0
    def processAlgorithm(self, parameters, context, feedback):
        # Retrieving parameters
        if qgs_version < 31400:
            connection_name = self.parameterAsString(parameters, self.DATABASE,
                                                     context)
            db = postgis.GeoDB.from_name(connection_name)
            uri = db.uri

            schema = self.parameterAsString(parameters, self.SCHEMA, context)
        else:
            connection_name = self.parameterAsConnectionName(
                parameters, self.DATABASE, context)
            md = QgsProviderRegistry.instance().providerMetadata('postgres')
            conn = md.createConnection(connection_name)
            uri = QgsDataSourceUri(conn.uri())

            schema = self.parameterAsSchema(parameters, self.SCHEMA, context)

        shape = self.parameterAsFile(parameters, self.SHAPEFILE, context)
        geopackage = parameters[self.GEOPACKAGE]

        # Execute ogr2ogr only in case the shape is Valid or no shape was provided
        shape_qgs = QgsVectorLayer(shape, 'test_valid', 'ogr')

        # Test is shape is valid
        if shape_qgs.isValid() or shape == '':
            feedback.pushInfo("Valid shape")
        else:
            raise QgsProcessingException("Invalid shape")

        # Text for ogr2ogr
        if shape == '':
            target_ogr = "ogr2ogr -f GPKG {} -overwrite {} ".format(
                geopackage, shape)
        else:
            target_ogr = "ogr2ogr -f GPKG {} -overwrite -clipsrc {} ".format(
                geopackage, shape)

        source_ogr = 'PG:"host={} dbname={} schemas={} port={} user={} password={}" '.format(
            uri.host(), uri.database(), schema, uri.port(), uri.username(),
            uri.password())
        string_ogr = target_ogr + source_ogr
        feedback.pushInfo('Text for ogr2ogr = ' + string_ogr)

        # Export schema to geopackage
        try:
            process = subprocess.run(string_ogr,
                                     shell=True,
                                     check=True,
                                     stdout=subprocess.PIPE,
                                     stderr=subprocess.PIPE)
        except subprocess.CalledProcessError as e:
            raise QgsProcessingException(str(e.stderr.decode('utf-8')))

        return {'Result': 'Exported'}
コード例 #8
0
 def createConnection(uri):
     ds_uri = QgsDataSourceUri(uri)
     conn = dbapi.connect(address=ds_uri.host(),
                          port=ds_uri.port(),
                          user=ds_uri.username(),
                          password=ds_uri.password(),
                          ENCRYPT=True,
                          sslValidateCertificate=False,
                          CHAR_AS_UTF8=1)
     conn.setautocommit(False)
     return conn
コード例 #9
0
    def uri(self, value):
        data_source_uri = QgsDataSourceUri(value)

        self._dict_conn_params = {
            'host': data_source_uri.host(),
            'port': data_source_uri.port(),
            'username': data_source_uri.username(),
            'password': data_source_uri.password(),
            'database': data_source_uri.database(),
            'schema': self.schema
        }

        self._uri = value
コード例 #10
0
    def is_ladm_layer(self, layer):
        result = False
        if layer.dataProvider().name() == PGConnector._PROVIDER_NAME:
            layer_uri = layer.dataProvider().uri()
            db_uri = QgsDataSourceUri(self._uri)

            result = (layer_uri.schema() == self.schema
                      and layer_uri.database() == db_uri.database()
                      and layer_uri.host() == db_uri.host()
                      and layer_uri.port() == db_uri.port()
                      and layer_uri.username() == db_uri.username()
                      and layer_uri.password() == db_uri.password())

        return result
コード例 #11
0
 def checkLoaded(self, name):
     """
     Checks if the layers is already loaded in the QGIS' TOC
     :param name:
     :param loadedLayers:
     :return:
     """
     loaded = None
     for ll in self.iface.mapCanvas().layers():
         if ll.name() == name:
             candidateUri = QgsDataSourceUri(ll.dataProvider().dataSourceUri())
             if self.host == candidateUri.host() and self.database == candidateUri.database() and self.port == int(candidateUri.port()):
                 return ll
     return loaded
コード例 #12
0
    def publishLayer(self, layer, fields=None):
        lyr_title, safe_name = lyr_utils.getLayerTitleAndName(layer)
        if layer.type() == layer.VectorLayer:
            if layer.featureCount() == 0:
                self.logError(f"Layer '{lyr_title}' contains zero features and cannot be published")
                return

            if layer.dataProvider().name() == "postgres" and self.useOriginalDataSource:
                try:
                    from geocatbridge.servers.models.postgis import PostgisServer
                except (ImportError, ModuleNotFoundError):
                    raise Exception(self.translate(getAppName(), "Cannot find or import PostgisServer class"))
                else:
                    uri = QgsDataSourceUri(layer.source())
                    db = PostgisServer(
                        "temp", uri.authConfigId(), uri.host(), uri.port(), uri.schema(), uri.database())
                    self._publishVectorLayerFromPostgis(layer, db)
            elif self.storage != GeoserverStorage.POSTGIS_BRIDGE:
                src_path, src_name, src_ext = lyr_utils.getLayerSourceInfo(layer)
                filename = self._exported_layers.get(src_path)
                if not filename:
                    if self.storage == GeoserverStorage.POSTGIS_GEOSERVER:
                        shp_name = exportLayer(layer, fields, to_shapefile=True, force=True, logger=self)
                        basename = os.path.splitext(shp_name)[0]
                        filename = basename + ".zip"
                        with ZipFile(filename, 'w') as z:
                            for ext in (".shp", ".shx", ".prj", ".dbf"):
                                filetozip = basename + ext
                                z.write(filetozip, arcname=os.path.basename(filetozip))
                    else:
                        filename = exportLayer(layer, fields, logger=self)
                self._exported_layers[src_path] = filename
                if self.storage == GeoserverStorage.FILE_BASED:
                    self._publishVectorLayerFromFile(layer, filename)
                else:
                    self._publishVectorLayerFromFileToPostgis(layer, filename)
            elif self.storage == GeoserverStorage.POSTGIS_BRIDGE:
                db = manager.getServer(self.postgisdb)
                if not db:
                    raise Exception(self.translate(getAppName(), "Cannot find the selected PostGIS database"))
                db.importLayer(layer, fields)
                self._publishVectorLayerFromPostgis(layer, db)
        elif layer.type() == layer.RasterLayer:
            if layer.source() not in self._exported_layers:
                path = exportLayer(layer, fields, logger=self)
                self._exported_layers[layer.source()] = path
            filename = self._exported_layers[layer.source()]
            self._publishRasterLayer(filename, safe_name)
        self._clearCache()
コード例 #13
0
    def update_yaml_config(self, db, config_path):
        text = ''
        qgs_uri = QgsDataSourceUri(db.uri)

        with open(os.path.join(config_path, 'config_template.yaml')) as f:
            text = f.read()
            text = text.format('{}',
                               DB_USER=qgs_uri.username(),
                               DB_PASSWORD=qgs_uri.password(),
                               DB_HOST=qgs_uri.host(),
                               DB_PORT=qgs_uri.port(),
                               DB_NAME=qgs_uri.database())
        new_file_path = os.path.join(
            config_path, self.get_tmp_filename('yaml_config', 'yaml'))

        with open(new_file_path, 'w') as new_yaml:
            new_yaml.write(text)

        return new_file_path
コード例 #14
0
ファイル: database.py プロジェクト: terry-longmacch/Roam
    def fromLayer(cls, layer):
        source = layer.source()
        uri = QgsDataSourceUri(layer.dataProvider().dataSourceUri())
        if ".sqlite" in source:
            if uri.database():
                source = uri.database()

            connectioninfo = {"type": "QSQLITE", "database": source}
        else:
            uri = QgsDataSourceUri(layer.dataProvider().dataSourceUri())
            connectioninfo = {
                "host": uri.host(),
                "database": uri.database(),
                "user": uri.username(),
                "password": uri.password()
            }
        connectioninfo["connectionname"] = layer.id()
        database = Database.connect(**connectioninfo)
        return database
コード例 #15
0
    def addConnectionConfig(cls, conn_name, uri):
        """Necessary to allow db_manager to have the list of connections get from settings."""
        uri = QgsDataSourceUri(uri)

        settings = QgsSettings()
        baseKey = "/PostgreSQL/connections/"
        baseKey += conn_name
        settings.setValue(baseKey + "/service", uri.service())
        settings.setValue(baseKey + "/host", uri.host())
        settings.setValue(baseKey + "/port", uri.port())
        settings.setValue(baseKey + "/database", uri.database())
        if uri.username():
            settings.setValue(baseKey + "/username", uri.username())
        if uri.password():
            settings.setValue(baseKey + "/password", uri.password())
        if uri.authConfigId():
            settings.setValue(baseKey + "/authcfg", uri.authConfigId())
        if uri.sslMode():
            settings.setValue(baseKey + "/sslmode", uri.sslMode())
コード例 #16
0
    def processAlgorithm(self, parameters, context, feedback):
        if Qgis.QGIS_VERSION_INT >= 31400:
            connection = self.parameterAsConnectionName(parameters, self.DATABASE, context)
            schema = self.parameterAsSchema(parameters, self.SCHEMA, context)
        else:
            connection = self.parameterAsString(parameters, self.DATABASE, context)
            schema = self.parameterAsString(parameters, self.SCHEMA, context)

        # Get connection info
        feedback.pushInfo("## CONNEXION A LA BASE DE DONNEES ##")
        metadata = QgsProviderRegistry.instance().providerMetadata('postgres')
        connection = metadata.findConnection(connection)
        uri = QgsDataSourceUri(connection.uri())
        connection_info = uri.connectionInfo()

        is_host = uri.host() != ""
        if is_host:
            feedback.pushInfo("Connexion établie via l'hôte")
        else:
            feedback.pushInfo("Connexion établie via le service")

        tables_name = [
            "repere", "poi_tourisme", "poi_service", "portion", "itineraire",
            "liaison", "segment", "v_portion", "v_itineraire", "etape", "element",
            "statut_segment_val", "amenagement_segment_val", "amenagement_type_segment_val"
        ]

        # Get available layers
        self.available_layers = {}
        feedback.pushInfo("")
        feedback.pushInfo("## LISTE DES COUCHES A METTRE A JOUR ##")
        for layer in context.project().mapLayers().values():
            if layer.type() == QgsMapLayerType.VectorLayer and \
               layer.dataProvider().name() == 'postgres':
                l_uri = layer.dataProvider().uri()
                table_name = l_uri.table()
                if l_uri.connectionInfo() == connection_info and \
                   l_uri.schema() == schema and \
                   table_name in tables_name:
                    self.available_layers[table_name] = layer.id()
                    feedback.pushInfo("// {}".format(layer.name()))

        return {self.OUTPUT_MSG: '', self.OUTPUT: list(self.available_layers.values())}
コード例 #17
0
    def addConnectionConfig(cls, conn_name, uri):
        """Necessary to allow db_manager to have the list of connections get from settings."""
        uri = QgsDataSourceUri(uri)

        settings = QgsSettings()
        baseKey = "/PostgreSQL/connections/"
        baseKey += conn_name
        settings.setValue(baseKey + "/service", uri.service())
        settings.setValue(baseKey + "/host", uri.host())
        settings.setValue(baseKey + "/port", uri.port())
        settings.setValue(baseKey + "/database", uri.database())
        if uri.username():
            settings.setValue(baseKey + "/username", uri.username())
        if uri.password():
            settings.setValue(baseKey + "/password", uri.password())
        if uri.authConfigId():
            settings.setValue(baseKey + "/authcfg", uri.authConfigId())
        if uri.sslMode():
            settings.setValue(baseKey + "/sslmode", uri.sslMode())
コード例 #18
0
def getDBEncoding(layerProvider):
    dbConnection = QgsDataSourceUri(layerProvider.dataSourceUri())
    db = QSqlDatabase.addDatabase("QPSQL", "WPSClient")
    db.setHostName(dbConnection.host())
    db.setDatabaseName(dbConnection.database())
    db.setUserName(dbConnection.username())
    db.setPassword(dbConnection.password())
    db.setPort(int(dbConnection.port()))
    db.open()

    query = "select pg_encoding_to_char(encoding) as encoding "
    query += "from pg_catalog.pg_database "
    query += "where datname = '" + dbConnection.database() + "' "

    result = QSqlQuery(query, db)
    result.first()
    encoding = pystring(result.value(0))
    db.close()

    return encoding
コード例 #19
0
ファイル: complexWindow.py プロジェクト: sgtrafael/DsgTools
    def getUserCredentials(self, lyr):
        """
        Gets user credentials to acess the database
        """
        dataSourceUri = QgsDataSourceUri(lyr.dataProvider().dataSourceUri())
        if dataSourceUri.host() == '':
            return (None, None)

        if dataSourceUri.password() != '':
            return (dataSourceUri.username(), dataSourceUri.password())

        connInfo = dataSourceUri.connectionInfo()
        (success, user, passwd ) = QgsCredentials.instance().get(connInfo, dataSourceUri.username(), None)
        # Put the credentials back (for yourself and the provider), as QGIS removes it when you "get" it
        if success:
            QgsCredentials.instance().put(connInfo, user, passwd)
        else:
            return (None, None)

        return (user, passwd)
コード例 #20
0
def getConnectionParameterFromDbLayer(layer: QgsMapLayer) -> Dict[str,str]:
    '''
    Get connection parameters
    from the layer datasource
    '''
    connectionParams = None

    if layer.providerType() == 'postgres':
        dbType = 'postgis'
    else:
        dbType = 'spatialite'

    src = layer.source()
    try:
        uri = QgsDataSourceUri(src)
    except:
        uri = QgsDataSourceURI(src)

    # TODO Use immutable namedtuple
    connectionParams = {
        'service' : uri.service(),
        'dbname' : uri.database(),
        'host' : uri.host(),
        'port': uri.port(),
        'user' : uri.username(),
        'password': uri.password(),
        'sslmode' : uri.sslMode(),
        'key': uri.keyColumn(),
        'estimatedmetadata' : str(uri.useEstimatedMetadata()),
        'checkPrimaryKeyUnicity' : '',
        'srid' : uri.srid(),
        'type': uri.wkbType(),
        'schema': uri.schema(),
        'table' : uri.table(),
        'geocol' : uri.geometryColumn(),
        'sql' : uri.sql(),
        'dbType': dbType
    }

    return connectionParams
コード例 #21
0
ファイル: tools.py プロジェクト: 3liz/qgis-lizsync-plugin
def getUriFromConnectionName(connection_name, must_connect=True):

    metadata = QgsProviderRegistry.instance().providerMetadata('postgres')
    connection = metadata.findConnection(connection_name)
    if not connection:
        return False, None, tr('The given connection name does not exists in QGIS')

    # Get uri
    uri = QgsDataSourceUri(connection.uri())

    # Try to connect if asked
    if must_connect:
        ok, msg = check_postgresql_connection(uri)
        if uri:
            connection_info = tr('Connection') + ': {} ({}{})'.format(
                connection_name,
                uri.host(),
                uri.service()
            )
            msg = connection_info + ' \n' + msg
        return ok, uri, msg
    else:
        return True, uri, ''
コード例 #22
0
    def __init__(self, destination, encoding, fields, geometryType,
                 crs, options=None):
        self.destination = destination
        self.isNotFileBased = False
        self.layer = None
        self.writer = None

        if encoding is None:
            settings = QSettings()
            encoding = settings.value('/Processing/encoding', 'System', str)

        if self.destination.startswith(self.MEMORY_LAYER_PREFIX):
            self.isNotFileBased = True

            uri = QgsWkbTypes.displayString(geometryType) + "?uuid=" + str(uuid.uuid4())
            if crs.isValid():
                uri += '&crs=' + crs.authid()
            fieldsdesc = []
            for f in fields:
                qgsfield = _toQgsField(f)
                fieldsdesc.append('field=%s:%s' % (qgsfield.name(),
                                                   TYPE_MAP_MEMORY_LAYER.get(qgsfield.type(), "string")))
            if fieldsdesc:
                uri += '&' + '&'.join(fieldsdesc)

            self.layer = QgsVectorLayer(uri, self.destination, 'memory')
            self.writer = self.layer.dataProvider()
        elif self.destination.startswith(self.POSTGIS_LAYER_PREFIX):
            self.isNotFileBased = True
            uri = QgsDataSourceUri(self.destination[len(self.POSTGIS_LAYER_PREFIX):])
            connInfo = uri.connectionInfo()
            (success, user, passwd) = QgsCredentials.instance().get(connInfo, None, None)
            if success:
                QgsCredentials.instance().put(connInfo, user, passwd)
            else:
                raise GeoAlgorithmExecutionException("Couldn't connect to database")
            try:
                db = postgis.GeoDB(host=uri.host(), port=int(uri.port()),
                                   dbname=uri.database(), user=user, passwd=passwd)
            except postgis.DbError as e:
                raise GeoAlgorithmExecutionException(
                    "Couldn't connect to database:\n%s" % e.message)

            def _runSQL(sql):
                try:
                    db._exec_sql_and_commit(str(sql))
                except postgis.DbError as e:
                    raise GeoAlgorithmExecutionException(
                        'Error creating output PostGIS table:\n%s' % e.message)

            fields = [_toQgsField(f) for f in fields]
            fieldsdesc = ",".join('%s %s' % (f.name(),
                                             TYPE_MAP_POSTGIS_LAYER.get(f.type(), "VARCHAR"))
                                  for f in fields)

            _runSQL("CREATE TABLE %s.%s (%s)" % (uri.schema(), uri.table().lower(), fieldsdesc))
            if geometryType != QgsWkbTypes.NullGeometry:
                _runSQL("SELECT AddGeometryColumn('{schema}', '{table}', 'the_geom', {srid}, '{typmod}', 2)".format(
                    table=uri.table().lower(), schema=uri.schema(), srid=crs.authid().split(":")[-1],
                    typmod=QgsWkbTypes.displayString(geometryType).upper()))

            self.layer = QgsVectorLayer(uri.uri(), uri.table(), "postgres")
            self.writer = self.layer.dataProvider()
        elif self.destination.startswith(self.SPATIALITE_LAYER_PREFIX):
            self.isNotFileBased = True
            uri = QgsDataSourceUri(self.destination[len(self.SPATIALITE_LAYER_PREFIX):])
            try:
                db = spatialite.GeoDB(uri=uri)
            except spatialite.DbError as e:
                raise GeoAlgorithmExecutionException(
                    "Couldn't connect to database:\n%s" % e.message)

            def _runSQL(sql):
                try:
                    db._exec_sql_and_commit(str(sql))
                except spatialite.DbError as e:
                    raise GeoAlgorithmExecutionException(
                        'Error creating output Spatialite table:\n%s' % str(e))

            fields = [_toQgsField(f) for f in fields]
            fieldsdesc = ",".join('%s %s' % (f.name(),
                                             TYPE_MAP_SPATIALITE_LAYER.get(f.type(), "VARCHAR"))
                                  for f in fields)

            _runSQL("DROP TABLE IF EXISTS %s" % uri.table().lower())
            _runSQL("CREATE TABLE %s (%s)" % (uri.table().lower(), fieldsdesc))
            if geometryType != QgsWkbTypes.NullGeometry:
                _runSQL("SELECT AddGeometryColumn('{table}', 'the_geom', {srid}, '{typmod}', 2)".format(
                    table=uri.table().lower(), srid=crs.authid().split(":")[-1],
                    typmod=QgsWkbTypes.displayString(geometryType).upper()))

            self.layer = QgsVectorLayer(uri.uri(), uri.table(), "spatialite")
            self.writer = self.layer.dataProvider()
        else:
            formats = QgsVectorFileWriter.supportedFiltersAndFormats()
            OGRCodes = {}
            for (key, value) in list(formats.items()):
                extension = str(key)
                extension = extension[extension.find('*.') + 2:]
                extension = extension[:extension.find(' ')]
                OGRCodes[extension] = value
            OGRCodes['dbf'] = "DBF file"

            extension = self.destination[self.destination.rfind('.') + 1:]

            if extension not in OGRCodes:
                extension = 'shp'
                self.destination = self.destination + '.shp'

            if geometryType == QgsWkbTypes.NoGeometry:
                if extension == 'shp':
                    extension = 'dbf'
                    self.destination = self.destination[:self.destination.rfind('.')] + '.dbf'
                if extension not in self.nogeometry_extensions:
                    raise GeoAlgorithmExecutionException(
                        "Unsupported format for tables with no geometry")

            qgsfields = QgsFields()
            for field in fields:
                qgsfields.append(_toQgsField(field))

            # use default dataset/layer options
            dataset_options = QgsVectorFileWriter.defaultDatasetOptions(OGRCodes[extension])
            layer_options = QgsVectorFileWriter.defaultLayerOptions(OGRCodes[extension])

            self.writer = QgsVectorFileWriter(self.destination, encoding,
                                              qgsfields, geometryType, crs, OGRCodes[extension],
                                              dataset_options, layer_options)
コード例 #23
0
    def ogrConnectionStringAndFormat(uri, context):
        """Generates OGR connection string and format string from layer source
        Returned values are a tuple of the connection string and format string
        """
        ogrstr = None
        format = None

        layer = QgsProcessingUtils.mapLayerFromString(uri, context, False)
        if layer is None:
            path, ext = os.path.splitext(uri)
            format = QgsVectorFileWriter.driverForExtension(ext)
            return uri, '"' + format + '"'

        provider = layer.dataProvider().name()
        if provider == 'spatialite':
            # dbname='/geodata/osm_ch.sqlite' table="places" (Geometry) sql=
            regex = re.compile("dbname='(.+)'")
            r = regex.search(str(layer.source()))
            ogrstr = r.groups()[0]
            format = 'SQLite'
        elif provider == 'postgres':
            # dbname='ktryjh_iuuqef' host=spacialdb.com port=9999
            # user='******' password='******' sslmode=disable
            # key='gid' estimatedmetadata=true srid=4326 type=MULTIPOLYGON
            # table="t4" (geom) sql=
            dsUri = QgsDataSourceUri(layer.dataProvider().dataSourceUri())
            conninfo = dsUri.connectionInfo()
            conn = None
            ok = False
            while not conn:
                try:
                    conn = psycopg2.connect(dsUri.connectionInfo())
                except psycopg2.OperationalError:
                    (ok, user, passwd) = QgsCredentials.instance().get(conninfo, dsUri.username(), dsUri.password())
                    if not ok:
                        break

                    dsUri.setUsername(user)
                    dsUri.setPassword(passwd)

            if not conn:
                raise RuntimeError('Could not connect to PostgreSQL database - check connection info')

            if ok:
                QgsCredentials.instance().put(conninfo, user, passwd)

            ogrstr = "PG:%s" % dsUri.connectionInfo()
            format = 'PostgreSQL'
        elif provider == "oracle":
            # OCI:user/password@host:port/service:table
            dsUri = QgsDataSourceUri(layer.dataProvider().dataSourceUri())
            ogrstr = "OCI:"
            if dsUri.username() != "":
                ogrstr += dsUri.username()
                if dsUri.password() != "":
                    ogrstr += "/" + dsUri.password()
                delim = "@"

            if dsUri.host() != "":
                ogrstr += delim + dsUri.host()
                delim = ""
                if dsUri.port() != "" and dsUri.port() != '1521':
                    ogrstr += ":" + dsUri.port()
                ogrstr += "/"
                if dsUri.database() != "":
                    ogrstr += dsUri.database()
            elif dsUri.database() != "":
                ogrstr += delim + dsUri.database()

            if ogrstr == "OCI:":
                raise RuntimeError('Invalid oracle data source - check connection info')

            ogrstr += ":"
            if dsUri.schema() != "":
                ogrstr += dsUri.schema() + "."

            ogrstr += dsUri.table()
            format = 'OCI'
        else:
            ogrstr = str(layer.source()).split("|")[0]
            path, ext = os.path.splitext(ogrstr)
            format = QgsVectorFileWriter.driverForExtension(ext)

        return ogrstr, '"' + format + '"'
コード例 #24
0
ファイル: GdalUtils.py プロジェクト: byron-locstat/QGIS
    def ogrConnectionStringAndFormatFromLayer(layer):
        provider = layer.dataProvider().name()
        if provider == 'spatialite':
            # dbname='/geodata/osm_ch.sqlite' table="places" (Geometry) sql=
            regex = re.compile("dbname='(.+)'")
            r = regex.search(str(layer.source()))
            ogrstr = r.groups()[0]
            format = 'SQLite'
        elif provider == 'postgres':
            # dbname='ktryjh_iuuqef' host=spacialdb.com port=9999
            # user='******' password='******' sslmode=disable
            # key='gid' estimatedmetadata=true srid=4326 type=MULTIPOLYGON
            # table="t4" (geom) sql=
            dsUri = QgsDataSourceUri(layer.dataProvider().dataSourceUri())
            conninfo = dsUri.connectionInfo()
            conn = None
            ok = False
            while not conn:
                try:
                    conn = psycopg2.connect(dsUri.connectionInfo())
                except psycopg2.OperationalError:
                    (ok, user, passwd) = QgsCredentials.instance().get(
                        conninfo, dsUri.username(), dsUri.password())
                    if not ok:
                        break

                    dsUri.setUsername(user)
                    dsUri.setPassword(passwd)

            if not conn:
                raise RuntimeError(
                    'Could not connect to PostgreSQL database - check connection info'
                )

            if ok:
                QgsCredentials.instance().put(conninfo, user, passwd)

            ogrstr = "PG:%s" % dsUri.connectionInfo()
            format = 'PostgreSQL'
        elif provider == 'mssql':
            #'dbname=\'db_name\' host=myHost estimatedmetadata=true
            # srid=27700 type=MultiPolygon table="dbo"."my_table"
            # #(Shape) sql='
            dsUri = layer.dataProvider().uri()
            ogrstr = 'MSSQL:'
            ogrstr += 'database={0};'.format(dsUri.database())
            ogrstr += 'server={0};'.format(dsUri.host())
            if dsUri.username() != "":
                ogrstr += 'uid={0};'.format(dsUri.username())
            else:
                ogrstr += 'trusted_connection=yes;'
            if dsUri.password() != '':
                ogrstr += 'pwd={0};'.format(dsUri.password())
            ogrstr += 'tables={0}'.format(dsUri.table())
            format = 'MSSQL'
        elif provider == "oracle":
            # OCI:user/password@host:port/service:table
            dsUri = QgsDataSourceUri(layer.dataProvider().dataSourceUri())
            ogrstr = "OCI:"
            if dsUri.username() != "":
                ogrstr += dsUri.username()
                if dsUri.password() != "":
                    ogrstr += "/" + dsUri.password()
                delim = "@"

            if dsUri.host() != "":
                ogrstr += delim + dsUri.host()
                delim = ""
                if dsUri.port() != "" and dsUri.port() != '1521':
                    ogrstr += ":" + dsUri.port()
                ogrstr += "/"
                if dsUri.database() != "":
                    ogrstr += dsUri.database()
            elif dsUri.database() != "":
                ogrstr += delim + dsUri.database()

            if ogrstr == "OCI:":
                raise RuntimeError(
                    'Invalid oracle data source - check connection info')

            ogrstr += ":"
            if dsUri.schema() != "":
                ogrstr += dsUri.schema() + "."

            ogrstr += dsUri.table()
            format = 'OCI'
        else:
            ogrstr = str(layer.source()).split("|")[0]
            path, ext = os.path.splitext(ogrstr)
            format = QgsVectorFileWriter.driverForExtension(ext)

        return ogrstr, '"' + format + '"'
コード例 #25
0
    def run(self):
        """Roda método que faz todo o trabalho real"""
        """Executa tarefas antes de apertar o OK da janela do Complemento"""

        # Pega camada ativa na legenda
        camadaAtiva = self.iface.activeLayer()

        # Se não tiver nenhuma camada ativa selecionada (se não tiver nenhum dataProvider) acusa erro
        try:
            provedor = camadaAtiva.dataProvider()
        except:
            QMessageBox.information(self.iface.mainWindow(), 'Aviso',
                                    'Selecione alguma camada')
            return

        # Se o dataProvider da camada não for Postgres também acusa erro
        if provedor.name() != 'postgres':
            QMessageBox.information(
                self.iface.mainWindow(), 'Aviso',
                'Selecione uma camada com conexao PostGIS')
            return

        # Cria mesma URI da camada ativa
        uri_camadaAtiva = QgsDataSourceUri(
            camadaAtiva.dataProvider().dataSourceUri())
        print(uri_camadaAtiva)

        # Pega tabela selecionada para ser usada como dado da consulta
        tabela = uri_camadaAtiva.table()
        esquema = uri_camadaAtiva.schema()
        print('Tabela = ', uri_camadaAtiva.table())
        print('Esquema = ', uri_camadaAtiva.schema())

        # Estabelece nomes a serem usados na cláusula WHERE da consulta
        ultima_letra_geom = tabela[-1]
        dic_geom = {'a': 'poligono', 'p': 'ponto', 'l': 'linha'}
        sigla_classe = tabela[0:-2]
        print('sigla_classe = ', sigla_classe)

        # Lista de camadas presente na legenda
        camadasAbertas = QgsProject.instance().layerTreeRoot().layerOrder()
        print(camadasAbertas)

        # Percorre camadas na legenda para determinar se existe a mesma classe da camada selecionada em outra escala.
        # Caso ela exista a URI dela será adicionada
        # para InfoUriCamadasOutraEscala e o objeto da camada será adicionado para CamadasOutraEscala

        InfoUriCamadasOutraEscala = []
        CamadasOutraEscala = []

        for camadaLegenda in camadasAbertas:
            try:
                uri_camadaLegenda = QgsDataSourceUri(
                    camadaLegenda.dataProvider().dataSourceUri())

                # Testa para ver se é mesma camada só que em outra escala - Não está funcionando ainda

                # Condição antiga
                #if uri_camadaAtiva.table()[0:-2:] == uri_camadaLegenda.table()[0:-2:] and uri_camadaAtiva.schema() != uri_camadaLegenda.schema():
                # Condição nova
                if uri_camadaAtiva.table()[0:3] == uri_camadaLegenda.table(
                )[0:3] and uri_camadaAtiva.schema(
                ) != uri_camadaLegenda.schema():
                    # print 'Tabela_selecao: ', uri.table(), ' Esquema_selecao: ', uri.schema(), ' diferente de Tabela_legenda: ', uri_camadaLegenda.table(), ' Esquema_legenda: ', uri_camadaLegenda.schema()
                    #QMessageBox.information(self.iface.mainWindow(), 'Aviso', 'Tem mesma(s) camada(s) em escala diferente')
                    if uri_camadaLegenda not in InfoUriCamadasOutraEscala:
                        # print 'Vou adicionar camada'
                        InfoUriCamadasOutraEscala.append(uri_camadaLegenda)
                        CamadasOutraEscala.append(camadaLegenda)
                    else:
                        # print 'Não vou adicionar camada'
                        pass
            except:
                pass

        print('Info outras escalas = ',
              [uri.uri() for uri in InfoUriCamadasOutraEscala])
        print('Camadas outras escalas = ', CamadasOutraEscala)

        mensagem_uri = ''
        for uri in InfoUriCamadasOutraEscala:
            #print uri
            mensagem_uri = mensagem_uri + 'tabela: ' + uri.table(
            ) + '; esquema: ' + uri.schema() + '\n'

        QMessageBox.information(
            self.iface.mainWindow(), 'Aviso',
            'Tem mesma(s) camada(s) em escala diferente: \n' + mensagem_uri)

        # Cria uma conexão ao Postgres através de QSqlDatabase - QPSQL se refere ao Postgres
        db = QSqlDatabase.addDatabase("QPSQL")

        # Dados da conexão são os mesmos da camada ativa (mesmo servidor, mesmo banco e mesma porta)
        db.setHostName(uri_camadaAtiva.host())
        db.setDatabaseName(uri_camadaAtiva.database())
        db.setPort(int(uri_camadaAtiva.port()))

        # Pega nome e senha de usuários nas configurações do plugin
        usuario = QSettings().value(SETTINGS_NAME + "/usuario",
                                    DEFAULT_USUARIO)
        senha = QSettings().value(SETTINGS_NAME + "/senha", DEFAULT_SENHA)
        print('usuario = ', usuario)

        # Estabelece usuário e senha para conexão com o banco
        db.setUserName(usuario)
        db.setPassword(senha)

        # Feições selecionadas
        selecao = camadaAtiva.selectedFeatures()
        selecao_outras_camadas = [
            camada.selectedFeatures() for camada in CamadasOutraEscala
        ]

        # cria lista do id_objeto das feições selecionadas de todas as classes iguais de escalas diferentes presentes na legenda
        lista_id_objeto = [feicao['id'] for feicao in selecao]
        lista_id_objeto_outras_camadas = [[feicao['id'] for feicao in camada]
                                          for camada in selecao_outras_camadas]

        # converte elementos da lista de id_objeto em string para usá-los na consulta
        lista_id_objeto = [str(el) for el in lista_id_objeto]
        lista_id_objeto_string = ','.join(lista_id_objeto)
        lista_id_objeto_outras_camadas = [[
            str(subel) for subel in el
        ] for el in lista_id_objeto_outras_camadas]

        # Abre conexão ao banco de dados para adicionar os esquemas que têm tabelas espaciais contidos lá
        if db.open():
            # Tenho que rever essa consulta
            string_consulta = "SELECT id_objeto_producao, id_nomebngb, nm_geografico_producao, cd_validacao_operador, tx_apontamento_ng, dsc_solucao FROM bngb_interface.t_interface WHERE id_objeto_producao IN (" + lista_id_objeto_string + ") " + \
            "AND nm_esquema_base = '" + esquema + "' AND concat_ws('_', nm_sigla_categoria, nm_classe) ILIKE '" + sigla_classe + "' AND tp_geom ILIKE '" + dic_geom[ultima_letra_geom] +"' "
            #print "string consulta exibir comboBox = ", string_consulta
            msg_add = ""
            for indice, uri in enumerate(InfoUriCamadasOutraEscala):
                # Verifica se a lista de id_objeto contém algum valor (alguma seleção)
                if lista_id_objeto_outras_camadas[indice]:
                    msg_add = msg_add + "UNION SELECT id_objeto_producao, id_nomebngb, nm_geografico_producao, cd_validacao_operador, tx_apontamento_ng, dsc_solucao FROM bngb_interface.t_interface WHERE id_objeto_producao IN (" + ','.join(lista_id_objeto_outras_camadas[indice]) + ") " + \
                    "AND nm_esquema_base = '" + uri.schema() + "' AND concat_ws('_', nm_sigla_categoria, nm_classe) ILIKE '" + sigla_classe + "' AND tp_geom ILIKE '" + dic_geom[ultima_letra_geom] +"' "

            string_consulta = string_consulta + msg_add
            #print string_consulta

            #print string_consulta
            consulta = db.exec_(
                string_consulta)  #IN (" + lista_id_objeto_string + ")")

            resultado_consulta = []

            # Adiciona resultados da consulta (id_objeto_producao, id_nomebngb, nm_geografico_producao)
            # na lista Python para posterior adição à comboBox
            while consulta.next():
                record = consulta.record()

                #print 'Comecando'
                # Pega o id_objeto_producao (na tabela t_interface) correspondente ao id_objeto das feições selecionadas
                id_objeto_producao = str(
                    record.field('id_objeto_producao').value())
                # id_objeto_producao = record.field('id_objeto_producao').value() # retorna int
                print("id_objeto_producao =", id_objeto_producao,
                      type(id_objeto_producao))
                #print id_objeto_producao, ' - tipo ', type(id_objeto_producao)

                # Pega id_nomebngb
                id_nomebngb = str(record.field('id_nomebngb').value())
                print("id_nomebngb =", id_nomebngb, type(id_nomebngb))
                #print id_nomebngb, ' - tipo ', type(id_nomebngb)

                # Pega nm_geografico_producao
                nome_bngb = str(record.field('nm_geografico_producao').value())
                print("nm_geografico_producao =", nome_bngb, type(nome_bngb))

                # Pega cd_validacao_operador
                cd_validacao_operador = str(
                    record.field('cd_validacao_operador').value())
                print("cd_validacao_operador =", cd_validacao_operador,
                      type(cd_validacao_operador))

                # Pega tx_apontamento_crng
                tx_apontamento_crng = str(
                    record.field('tx_apontamento_ng').value())
                print("tx_apontamento_crng =", tx_apontamento_crng,
                      type(tx_apontamento_crng))

                # Pega dsc_solucao
                dsc_solucao = str(record.field('dsc_solucao').value())
                print("dsc_solucao =", dsc_solucao, type(dsc_solucao))

                # Para PyQt4
                '''                
                # Pega nm_geografico_producao
                if isinstance(record.field('nm_geografico_producao').value(), QPyNullVariant):
                    nome_bngb = str(record.field('nm_geografico_producao').value())
                    #print nome_bngb, ' - tipo1 ', type(nome_bngb)
                else:
                    nome_bngb = record.field('nm_geografico_producao').value()
                    #print nome_bngb, ' - tipo1 ', type(nome_bngb)
                    
                cd_validacao_operador = str(record.field('cd_validacao_operador').value());
                
                if isinstance(record.field('tx_apontamento_crng').value(), QPyNullVariant):
                    tx_apontamento_crng = str(record.field('tx_apontamento_crng').value())
                    #print nome_bngb, ' - tipo1 ', type(nome_bngb)
                else:
                    tx_apontamento_crng = record.field('tx_apontamento_crng').value()
                    #print nome_bngb, ' - tipo1 ', type(nome_bngb)
                
                if isinstance(record.field('dsc_solucao').value(), QPyNullVariant):
                    dsc_solucao = str(record.field('dsc_solucao').value())
                else:
                    dsc_solucao = record.field('dsc_solucao').value()
                '''

                # Tupla a ser adicionada para a lista que será posteriormente adicionada à ComboBox
                tupla_add = (id_objeto_producao, id_nomebngb, nome_bngb,
                             cd_validacao_operador, tx_apontamento_crng,
                             dsc_solucao)
                resultado_consulta.append(tupla_add)

            #print 'Resultado da consulta = ', resultado_consulta

            # Parei aqui. Até aqui tudo bem, resultado_consulta guarda a tupla (id_objeto_producao, id_nomebngb, nome_bngb) da t_interface referente a todos os registros selecionados nas diversas camadas da legenda que se referem a uma classe em escalas distintas
            # Cria lista de strings a partir de resultado_consulta para adição à ComboBox
            resultado_consulta_string = []
            for el in resultado_consulta:
                item_comboBox = el[0] + '\ ' + el[1] + '\ ' + el[
                    2] + '\ ' + el[3] + '\ ' + el[4] + '\ ' + el[5]
                resultado_consulta_string.append(item_comboBox)

            #print resultado_consulta_string

            # Limpa e adiciona elementos na Combo Box (caixa de opção)
            self.dlg.comboBox.clear()
            self.dlg.comboBox.addItems(resultado_consulta_string)

            # Indica na caixa de diálogo uma linha para mostrar a camada que está sendo editada na t_interface
            self.dlg.lineEdit.clear()
            self.dlg.lineEdit.setText(uri_camadaAtiva.schema() + '.' +
                                      uri_camadaAtiva.table())
        else:
            QMessageBox.information(self.iface.mainWindow(), 'Aviso',
                                    'Conexao rejeitada')
            return

        # show the dialog
        self.dlg.show()
        # Run the dialog event loop
        result = self.dlg.exec_()
        # See if OK was pressed
        if result:
            # Pega índice e tupla (id_objeto_producao, id_nomebngb, nm_geografico_producao) escolhidos no ComboBox
            indice = self.dlg.comboBox.currentIndex()
            stringSelecionada = resultado_consulta_string[indice]

            #print 'String Selecionada = ', stringSelecionada

            # Transforma opção selecionada na ComboBox em lista
            listastringSelecionada = stringSelecionada.split("\ ")
            #print 'Lista selecionada =', listastringSelecionada
            #print 'Lista selecionada[2] =', listastringSelecionada[2], 'tipo = ', type(listastringSelecionada[2])
            listastringSelecionada[2] = listastringSelecionada[2].replace(
                "'", "''")
            listastringSelecionada[4] = listastringSelecionada[4].replace(
                "'", "''")
            listastringSelecionada[5] = listastringSelecionada[5].replace(
                "'", "''")
            #print listastringSelecionada[4]

            #print 'Lista selecionada[2] depois =', listastringSelecionada[2], 'tipo = ', type(listastringSelecionada[2])
            #print
            #print
            #print

            # Estabelece string para consulta na Camada Ativa (nm_geografico_producao e tx_apontamento_crng podem ser nulos)
            if listastringSelecionada[2] == 'NULL':
                string_consulta = "UPDATE bngb_interface.t_interface SET cd_validacao_operador = '" + listastringSelecionada[
                    3] + "', id_nomebngb = " + listastringSelecionada[
                        1] + ", nm_geografico_producao = " + listastringSelecionada[
                            2]
                #print "UPDATE referente à camada ativa =", string_consulta
            else:
                string_consulta = "UPDATE bngb_interface.t_interface SET cd_validacao_operador = '" + listastringSelecionada[
                    3] + "', id_nomebngb = " + listastringSelecionada[
                        1] + ", nm_geografico_producao = '" + listastringSelecionada[
                            2] + "'"
                #print "UPDATE referente à camada ativa =", string_consulta

            if listastringSelecionada[5] == 'NULL':
                adiciona_dsc = ", dsc_solucao = " + listastringSelecionada[5]
            else:
                adiciona_dsc = ", dsc_solucao = '" + listastringSelecionada[
                    5] + "'"

            if listastringSelecionada[4] == 'NULL':
                string_consulta = string_consulta + adiciona_dsc + ", tx_apontamento_ng = " + listastringSelecionada[4] + \
                " WHERE id_objeto_producao IN (" + lista_id_objeto_string + ") " + \
                "AND nm_esquema_base = '" + esquema  + "' AND concat_ws('_', nm_sigla_categoria, nm_classe) ILIKE '" + sigla_classe + "' AND tp_geom ILIKE '" + dic_geom[ultima_letra_geom] +"'"
                #print u"UPDATE referente à camada ativa =", string_consulta
            else:
                string_consulta = string_consulta + adiciona_dsc + ", tx_apontamento_ng = '" + listastringSelecionada[4] + \
                "' WHERE id_objeto_producao IN (" + lista_id_objeto_string + ") " + \
                "AND nm_esquema_base = '" + esquema  + "' AND concat_ws('_', nm_sigla_categoria, nm_classe) ILIKE '" + sigla_classe + "' AND tp_geom ILIKE '" + dic_geom[ultima_letra_geom] +"'"
                #print u"UPDATE referente à camada ativa =", string_consulta

            # Executa atualização (update)
            db.exec_(string_consulta)

            # Percorre a mesma classe nas outras escalas
            for indice, uri in enumerate(InfoUriCamadasOutraEscala):
                # Verifica se a lista de id_objeto contém algum valor (alguma seleção)
                if lista_id_objeto_outras_camadas[indice]:
                    if listastringSelecionada[2] == 'NULL':
                        string_consulta = "UPDATE bngb_interface.t_interface SET cd_validacao_operador = '" + listastringSelecionada[
                            3] + "', id_nomebngb = " + listastringSelecionada[
                                1] + ", nm_geografico_producao = " + listastringSelecionada[
                                    2]
                        #print "UPDATE referente à camada auxiliar = ", string_consulta
                    else:
                        string_consulta = "UPDATE bngb_interface.t_interface SET cd_validacao_operador = '" + listastringSelecionada[
                            3] + "', id_nomebngb = " + listastringSelecionada[
                                1] + ", nm_geografico_producao = '" + listastringSelecionada[
                                    2] + "'"
                        #print "UPDATE referente à camada auxiliar = ", string_consulta

                    if listastringSelecionada[4] == 'NULL':
                        string_consulta = string_consulta + adiciona_dsc + ", tx_apontamento_ng = " + listastringSelecionada[4] + \
                        " WHERE id_objeto_producao IN (" + ','.join(lista_id_objeto_outras_camadas[indice]) + ") " + \
                        "AND nm_esquema_base = '" + uri.schema()  + "' AND concat_ws('_', nm_sigla_categoria, nm_classe) ILIKE '" + sigla_classe + "' AND tp_geom ILIKE '" + dic_geom[ultima_letra_geom] +"'"
                        #print u"UPDATE referente à camada auxiliar =", string_consulta
                    else:
                        string_consulta = string_consulta + adiciona_dsc + ", tx_apontamento_ng = '" + listastringSelecionada[4] + \
                        "' WHERE id_objeto_producao IN (" + ','.join(lista_id_objeto_outras_camadas[indice]) + ") " + \
                        "AND nm_esquema_base = '" + uri.schema()  + "' AND concat_ws('_', nm_sigla_categoria, nm_classe) ILIKE '" + sigla_classe + "' AND tp_geom ILIKE '" + dic_geom[ultima_letra_geom] +"'"
                        #print u"UPDATE referente à camada auxiliar =", string_consulta

                    # Executa atualização (update)
                    db.exec_(string_consulta)
コード例 #26
0
ファイル: GdalUtils.py プロジェクト: ufolr/QGIS
    def ogrConnectionStringAndFormat(uri, context):
        """Generates OGR connection string and format string from layer source
        Returned values are a tuple of the connection string and format string
        """
        ogrstr = None
        format = None

        layer = QgsProcessingUtils.mapLayerFromString(uri, context, False)
        if layer is None:
            path, ext = os.path.splitext(uri)
            format = QgsVectorFileWriter.driverForExtension(ext)
            return '"' + uri + '"', '"' + format + '"'

        provider = layer.dataProvider().name()
        if provider == 'spatialite':
            # dbname='/geodata/osm_ch.sqlite' table="places" (Geometry) sql=
            regex = re.compile("dbname='(.+)'")
            r = regex.search(str(layer.source()))
            ogrstr = r.groups()[0]
            format = 'SQLite'
        elif provider == 'postgres':
            # dbname='ktryjh_iuuqef' host=spacialdb.com port=9999
            # user='******' password='******' sslmode=disable
            # key='gid' estimatedmetadata=true srid=4326 type=MULTIPOLYGON
            # table="t4" (geom) sql=
            dsUri = QgsDataSourceUri(layer.dataProvider().dataSourceUri())
            conninfo = dsUri.connectionInfo()
            conn = None
            ok = False
            while not conn:
                try:
                    conn = psycopg2.connect(dsUri.connectionInfo())
                except psycopg2.OperationalError:
                    (ok, user, passwd) = QgsCredentials.instance().get(conninfo, dsUri.username(), dsUri.password())
                    if not ok:
                        break

                    dsUri.setUsername(user)
                    dsUri.setPassword(passwd)

            if not conn:
                raise RuntimeError('Could not connect to PostgreSQL database - check connection info')

            if ok:
                QgsCredentials.instance().put(conninfo, user, passwd)

            ogrstr = "PG:%s" % dsUri.connectionInfo()
            format = 'PostgreSQL'
        elif provider == "oracle":
            # OCI:user/password@host:port/service:table
            dsUri = QgsDataSourceUri(layer.dataProvider().dataSourceUri())
            ogrstr = "OCI:"
            if dsUri.username() != "":
                ogrstr += dsUri.username()
                if dsUri.password() != "":
                    ogrstr += "/" + dsUri.password()
                delim = "@"

            if dsUri.host() != "":
                ogrstr += delim + dsUri.host()
                delim = ""
                if dsUri.port() != "" and dsUri.port() != '1521':
                    ogrstr += ":" + dsUri.port()
                ogrstr += "/"
                if dsUri.database() != "":
                    ogrstr += dsUri.database()
            elif dsUri.database() != "":
                ogrstr += delim + dsUri.database()

            if ogrstr == "OCI:":
                raise RuntimeError('Invalid oracle data source - check connection info')

            ogrstr += ":"
            if dsUri.schema() != "":
                ogrstr += dsUri.schema() + "."

            ogrstr += dsUri.table()
            format = 'OCI'
        else:
            ogrstr = str(layer.source()).split("|")[0]
            path, ext = os.path.splitext(ogrstr)
            format = QgsVectorFileWriter.driverForExtension(ext)

        return '"' + ogrstr + '"', '"' + format + '"'
コード例 #27
0
ファイル: sitndb.py プロジェクト: maltaesousa/SelvansGeo
class SitnDB(object):
    def __init__(self, dbname, host, port, user, password, providerkey, iface):
        """
            Defines the db connexion parameters and the qgis provider key
        """

        self.uri = QgsDataSourceUri()
        self.uri.setConnection(host, port, dbname, user, password)
        self.providerkey = providerkey
        self.errorMessage = ''
        self.messageBar = iface.messageBar()

    def getLayer(self,
                 shema,
                 table,
                 geomfieldname,
                 whereclause,
                 layername,
                 uniqueidfield):
        """
            Returns a layer or a table. If no geometry is available,
            geomfieldname must be set to None.
        """

        self.uri.setDataSource(shema,
                               table,
                               geomfieldname,
                               whereclause,
                               uniqueidfield)

        layer = QgsVectorLayer(self.uri.uri(), layername, self.providerkey)

        if not layer.isValid():
            return None
        else:
            return layer

    def getStyleUri(self,
                    shema,
                    table,
                    geomfieldname,
                    whereclause,
                    layername,
                    uniqueidfield):
        """
            Returns a style URI
        """

        self.uri.setDataSource(shema,
                               table,
                               geomfieldname,
                               whereclause,
                               uniqueidfield)

        return self.uri.uri()

    def createQtMSDB(self):
        """
            Returns a db Connection to a MSSQL (SQL Server database) using
            QtSql. This is requiered in order to create views with SQL Server
        """

        db = QSqlDatabase.addDatabase("QODBC")

        if db.isValid():
            db.setDatabaseName("DRIVER={SQL Server};SERVER=" + self.uri.host()
                               + ";DATABASE=" + self.uri.database())
            db.setUserName(self.uri.username())
            db.setPassword(self.uri.password())

            if db.open():
                return db, True

            else:
                self.messageBar.pushMessage("Connection SQl Server",
                                            db.lastError().text(),
                                            level=QgsMessageBar.CRITICAL)
                db.close()
                db.removeDatabase(db.databaseName())
                db = None
                return db, False
        else:
            self.messageBar.pushMessage("Connection SQL Server",
                                        'QODBC db is NOT valid',
                                        level=QgsMessageBar.CRITICAL)
コード例 #28
0
    def processAlgorithm(self, parameters, context, feedback):
        
        # Dummy function to enable running an alg inside an alg
        def no_post_process(alg, context, feedback):
            pass
        

        # Retrieving parameters
        if qgs_version < 31400:
            connection_name = self.parameterAsString(parameters, self.DATABASE, context)
            db = postgis.GeoDB.from_name(connection_name)
            uri = db.uri
        
            schema = self.parameterAsString(parameters, self.SCHEMA, context)
        else:
            connection_name = self.parameterAsConnectionName(parameters, self.DATABASE, context)
            md = QgsProviderRegistry.instance().providerMetadata('postgres')
            conn = md.createConnection(connection_name)
            uri = QgsDataSourceUri(conn.uri())

            schema = self.parameterAsSchema(parameters, self.SCHEMA, context)

        geopackage = parameters[self.GEOPACKAGE]

        



        # Debugging info
        '''
        feedback.pushInfo('Input parameters:')
        feedback.pushInfo('connection = ' + connection)
        feedback.pushInfo('db = ' + str(db))
        feedback.pushInfo('schema = ' + schema)
        feedback.pushInfo('geopackage = ' + geopackage)
        feedback.pushInfo('')
        '''
        
        # Raise error if reamb isn't in schema name
        if not 'reamb' in schema:
            raise QgsProcessingException('A palavra reamb precisa fazer parte do nome do esquema')        

        # Connect with Geopackage 
        feedback.pushInfo('Listing non-empty layers from geopackage')
        with lite.connect(geopackage) as con:
            feedback.pushInfo('Con = ' + str(con))
            
            layers_import = [] # will store the non-empty tables
            
            # Create cursor
            cur = con.cursor()
            
            # Fetch layer names
            cur.execute("SELECT table_name FROM gpkg_geometry_columns")
            rows = cur.fetchall()
            layer_names = [camada[0] for camada in rows]
            feedback.pushInfo('Layers = ' + str(layer_names)) 
            

            
            # Append non-empty geometry layers to list 
            for layer in layer_names:
                # Count rows
                cur.execute("SELECT COUNT(1) FROM {}".format(layer))
                
                rows = cur.fetchall()
                rows_count = rows[0][0]                
                #feedback.pushInfo('Rows = ' + str(rows_count))
                
                # Append to list
                if rows_count > 0:
                    #feedback.pushInfo('Table non-empty = ' + str(rows_count))
                    layers_import.append(layer)
        
                    
        feedback.pushInfo('Non-empty tables = ' + str(layers_import))
        feedback.pushInfo('')

        
        # Connect with PostGIS database
        con = psycopg2.connect(user = uri.username(), password = uri.password(), 
                                       host = uri.host(), port = uri.port(), database = uri.database())

        feedback.pushInfo('Uri = ' + str(uri))
        feedback.pushInfo('Uri text = ' + uri.uri())         
        feedback.pushInfo('Connection = ' + str(con))
        
        
        # Clean PostGIS schema if marked
        #cleanSchema = self.parameterAsBool(parameters, self.CLEAN_SCHEMA, context)
        cleanSchema = False
         
        if cleanSchema:
            with con:
                select_schema_tables = "SELECT table_name FROM information_schema.tables " \
                                        "WHERE table_type = '{}' AND table_schema = '{}'".format('BASE TABLE', schema)
                                        
                cur = con.cursor()
                 
                cur.execute(select_schema_tables)
                 
                rows = cur.fetchall()
     
                schema_tables = [table[0] for table in rows]
                 
                for table in schema_tables:
                    feedback.pushInfo("Deleting from {}.{}".format(schema, table))
                    cur.execute("DELETE FROM {}.{}".format(schema, table))
                    con.commit()
                        
        cur.close()
        con.close()
         
        feedback.pushInfo('')




       
# =============================================================================
#         # Testing
#         nome = 'cbge_trecho_arruamento_l'
#         # QGIS Vector Layer from geopackage layer 
#         uri_geopackage = geopackage + '|layername=' + nome
#         vlayer = QgsVectorLayer(uri_geopackage, 'geopackage_layer', 'ogr')
# 
#         # Use database table as QGIS Vector Layer
#         uri_tabela = uri
#         uri_tabela.setDataSource(schema, nome, 'geom')
#         uri_tabela.setWkbType(vlayer.wkbType())
#         uri_tabela.setSrid(str(vlayer.sourceCrs().postgisSrid()))
#         target = QgsVectorLayer(uri_tabela.uri(), 'teste', 'postgres')
#         feedback.pushInfo(uri_tabela.uri())
#         feedback.pushInfo('Validade = ' + str(target.isValid()))
#          
# 
#         processing.run("script:appendfeaturestolayer", {'SOURCE_LAYER':vlayer, 'TARGET_LAYER':target, 'ACTION_ON_DUPLICATE':0}, context=context, feedback=feedback, onFinish=no_post_process)
# =============================================================================

        
        
        
        # Import layers
        for layer in layers_import:
            feedback.pushInfo("Importing {}.{}".format(schema, layer))
            

             
            # QGIS Vector Layer from source 
            uri_geopackage = geopackage + '|layername=' + layer
            source = QgsVectorLayer(uri_geopackage, 'geopackage_layer', 'ogr')
            if not source.isValid():
                raise QgsProcessingException('Source layer not valid') 
                
            # QGIS Vector Layer from target
            uri_table = uri
            uri_table.setDataSource(schema, layer, 'geom')
            uri_table.setWkbType(source.wkbType())
            uri_table.setSrid(str(source.sourceCrs().postgisSrid()))
            target = QgsVectorLayer(uri_table.uri(), 'schema_table', 'postgres')
            if not target.isValid():
                raise QgsProcessingException('Target layer not valid') 
            
            # Run QGIS script for importing
            processing.run("publibase:appendfeaturestolayer", {'SOURCE_LAYER':source, 'TARGET_LAYER':target, 'ACTION_ON_DUPLICATE':0}, context=context, feedback=feedback, onFinish=no_post_process)
            feedback.pushInfo('')    
 
       



        return {'Result':'Layers imported'}
コード例 #29
0
ファイル: GdalUtils.py プロジェクト: jonnyforestGIS/QGIS
    def ogrConnectionStringAndFormatFromLayer(layer):
        provider = layer.dataProvider().name()
        if provider == 'spatialite':
            # dbname='/geodata/osm_ch.sqlite' table="places" (Geometry) sql=
            regex = re.compile("dbname='(.+)'")
            r = regex.search(str(layer.source()))
            ogrstr = r.groups()[0]
            format = 'SQLite'
        elif provider == 'postgres':
            # dbname='ktryjh_iuuqef' host=spacialdb.com port=9999
            # user='******' password='******' sslmode=disable
            # key='gid' estimatedmetadata=true srid=4326 type=MULTIPOLYGON
            # table="t4" (geom) sql=
            dsUri = QgsDataSourceUri(layer.dataProvider().dataSourceUri())
            conninfo = dsUri.connectionInfo()
            conn = None
            ok = False
            while not conn:
                try:
                    conn = psycopg2.connect(dsUri.connectionInfo())
                except psycopg2.OperationalError:
                    (ok, user, passwd) = QgsCredentials.instance().get(conninfo, dsUri.username(), dsUri.password())
                    if not ok:
                        break

                    dsUri.setUsername(user)
                    dsUri.setPassword(passwd)

            if not conn:
                raise RuntimeError('Could not connect to PostgreSQL database - check connection info')

            if ok:
                QgsCredentials.instance().put(conninfo, user, passwd)

            ogrstr = "PG:%s" % dsUri.connectionInfo()
            format = 'PostgreSQL'
        elif provider == 'mssql':
            #'dbname=\'db_name\' host=myHost estimatedmetadata=true
            # srid=27700 type=MultiPolygon table="dbo"."my_table"
            # #(Shape) sql='
            dsUri = layer.dataProvider().uri()
            ogrstr = 'MSSQL:'
            ogrstr += 'database={0};'.format(dsUri.database())
            ogrstr += 'server={0};'.format(dsUri.host())
            if dsUri.username() != "":
                ogrstr += 'uid={0};'.format(dsUri.username())
            else:
                ogrstr += 'trusted_connection=yes;'
            if dsUri.password() != '':
                ogrstr += 'pwd={0};'.format(dsUri.password())
            ogrstr += 'tables={0}'.format(dsUri.table())
            format = 'MSSQL'
        elif provider == "oracle":
            # OCI:user/password@host:port/service:table
            dsUri = QgsDataSourceUri(layer.dataProvider().dataSourceUri())
            ogrstr = "OCI:"
            if dsUri.username() != "":
                ogrstr += dsUri.username()
                if dsUri.password() != "":
                    ogrstr += "/" + dsUri.password()
                delim = "@"

            if dsUri.host() != "":
                ogrstr += delim + dsUri.host()
                delim = ""
                if dsUri.port() != "" and dsUri.port() != '1521':
                    ogrstr += ":" + dsUri.port()
                ogrstr += "/"
                if dsUri.database() != "":
                    ogrstr += dsUri.database()
            elif dsUri.database() != "":
                ogrstr += delim + dsUri.database()

            if ogrstr == "OCI:":
                raise RuntimeError('Invalid oracle data source - check connection info')

            ogrstr += ":"
            if dsUri.schema() != "":
                ogrstr += dsUri.schema() + "."

            ogrstr += dsUri.table()
            format = 'OCI'
        else:
            ogrstr = str(layer.source()).split("|")[0]
            path, ext = os.path.splitext(ogrstr)
            format = QgsVectorFileWriter.driverForExtension(ext)

        return ogrstr, '"' + format + '"'
コード例 #30
0
ファイル: vector.py プロジェクト: rui88/QGIS
    def __init__(self,
                 destination,
                 encoding,
                 fields,
                 geometryType,
                 crs,
                 options=None):
        self.destination = destination
        self.isNotFileBased = False
        self.layer = None
        self.writer = None

        if encoding is None:
            settings = QSettings()
            encoding = settings.value('/Processing/encoding',
                                      'System',
                                      type=str)

        if self.destination.startswith(self.MEMORY_LAYER_PREFIX):
            self.isNotFileBased = True

            uri = QgsWkbTypes.displayString(geometryType) + "?uuid=" + str(
                uuid.uuid4())
            if crs.isValid():
                uri += '&crs=' + crs.authid()
            fieldsdesc = []
            for f in fields:
                qgsfield = _toQgsField(f)
                fieldsdesc.append(
                    'field=%s:%s' %
                    (qgsfield.name(),
                     TYPE_MAP_MEMORY_LAYER.get(qgsfield.type(), "string")))
            if fieldsdesc:
                uri += '&' + '&'.join(fieldsdesc)

            self.layer = QgsVectorLayer(uri, self.destination, 'memory')
            self.writer = self.layer.dataProvider()
        elif self.destination.startswith(self.POSTGIS_LAYER_PREFIX):
            self.isNotFileBased = True
            uri = QgsDataSourceUri(
                self.destination[len(self.POSTGIS_LAYER_PREFIX):])
            connInfo = uri.connectionInfo()
            (success, user,
             passwd) = QgsCredentials.instance().get(connInfo, None, None)
            if success:
                QgsCredentials.instance().put(connInfo, user, passwd)
            else:
                raise GeoAlgorithmExecutionException(
                    "Couldn't connect to database")
            # fix_print_with_import
            print(uri.uri())
            try:
                db = postgis.GeoDB(host=uri.host(),
                                   port=int(uri.port()),
                                   dbname=uri.database(),
                                   user=user,
                                   passwd=passwd)
            except postgis.DbError as e:
                raise GeoAlgorithmExecutionException(
                    "Couldn't connect to database:\n%s" % e.message)

            def _runSQL(sql):
                try:
                    db._exec_sql_and_commit(str(sql))
                except postgis.DbError as e:
                    raise GeoAlgorithmExecutionException(
                        'Error creating output PostGIS table:\n%s' % e.message)

            fields = [_toQgsField(f) for f in fields]
            fieldsdesc = ",".join(
                '%s %s' %
                (f.name(), TYPE_MAP_POSTGIS_LAYER.get(f.type(), "VARCHAR"))
                for f in fields)

            _runSQL("CREATE TABLE %s.%s (%s)" %
                    (uri.schema(), uri.table().lower(), fieldsdesc))
            if geometryType != QgsWkbTypes.NullGeometry:
                _runSQL(
                    "SELECT AddGeometryColumn('{schema}', '{table}', 'the_geom', {srid}, '{typmod}', 2)"
                    .format(table=uri.table().lower(),
                            schema=uri.schema(),
                            srid=crs.authid().split(":")[-1],
                            typmod=QgsWkbTypes.displayString(
                                geometryType).upper()))

            self.layer = QgsVectorLayer(uri.uri(), uri.table(), "postgres")
            self.writer = self.layer.dataProvider()
        elif self.destination.startswith(self.SPATIALITE_LAYER_PREFIX):
            self.isNotFileBased = True
            uri = QgsDataSourceUri(
                self.destination[len(self.SPATIALITE_LAYER_PREFIX):])
            # fix_print_with_import
            print(uri.uri())
            try:
                db = spatialite.GeoDB(uri=uri)
            except spatialite.DbError as e:
                raise GeoAlgorithmExecutionException(
                    "Couldn't connect to database:\n%s" % e.message)

            def _runSQL(sql):
                try:
                    db._exec_sql_and_commit(str(sql))
                except spatialite.DbError as e:
                    raise GeoAlgorithmExecutionException(
                        'Error creating output Spatialite table:\n%s' % str(e))

            fields = [_toQgsField(f) for f in fields]
            fieldsdesc = ",".join(
                '%s %s' %
                (f.name(), TYPE_MAP_SPATIALITE_LAYER.get(f.type(), "VARCHAR"))
                for f in fields)

            _runSQL("DROP TABLE IF EXISTS %s" % uri.table().lower())
            _runSQL("CREATE TABLE %s (%s)" % (uri.table().lower(), fieldsdesc))
            if geometryType != QgsWkbTypes.NullGeometry:
                _runSQL(
                    "SELECT AddGeometryColumn('{table}', 'the_geom', {srid}, '{typmod}', 2)"
                    .format(table=uri.table().lower(),
                            srid=crs.authid().split(":")[-1],
                            typmod=QgsWkbTypes.displayString(
                                geometryType).upper()))

            self.layer = QgsVectorLayer(uri.uri(), uri.table(), "spatialite")
            self.writer = self.layer.dataProvider()
        else:
            formats = QgsVectorFileWriter.supportedFiltersAndFormats()
            OGRCodes = {}
            for (key, value) in list(formats.items()):
                extension = str(key)
                extension = extension[extension.find('*.') + 2:]
                extension = extension[:extension.find(' ')]
                OGRCodes[extension] = value
            OGRCodes['dbf'] = "DBF file"

            extension = self.destination[self.destination.rfind('.') + 1:]

            if extension not in OGRCodes:
                extension = 'shp'
                self.destination = self.destination + '.shp'

            if geometryType == QgsWkbTypes.NoGeometry:
                if extension == 'shp':
                    extension = 'dbf'
                    self.destination = self.destination[:self.destination.
                                                        rfind('.')] + '.dbf'
                if extension not in self.nogeometry_extensions:
                    raise GeoAlgorithmExecutionException(
                        "Unsupported format for tables with no geometry")

            qgsfields = QgsFields()
            for field in fields:
                qgsfields.append(_toQgsField(field))

            # use default dataset/layer options
            dataset_options = QgsVectorFileWriter.defaultDatasetOptions(
                OGRCodes[extension])
            layer_options = QgsVectorFileWriter.defaultLayerOptions(
                OGRCodes[extension])

            self.writer = QgsVectorFileWriter(self.destination, encoding,
                                              qgsfields, geometryType, crs,
                                              OGRCodes[extension],
                                              dataset_options, layer_options)
コード例 #31
0
ファイル: vector.py プロジェクト: rui88/QGIS
def ogrConnectionString(uri):
    """Generates OGR connection sting from layer source
    """
    ogrstr = None

    layer = dataobjects.getObjectFromUri(uri, False)
    if layer is None:
        return '"' + uri + '"'
    provider = layer.dataProvider().name()
    if provider == 'spatialite':
        # dbname='/geodata/osm_ch.sqlite' table="places" (Geometry) sql=
        regex = re.compile("dbname='(.+)'")
        r = regex.search(str(layer.source()))
        ogrstr = r.groups()[0]
    elif provider == 'postgres':
        # dbname='ktryjh_iuuqef' host=spacialdb.com port=9999
        # user='******' password='******' sslmode=disable
        # key='gid' estimatedmetadata=true srid=4326 type=MULTIPOLYGON
        # table="t4" (geom) sql=
        dsUri = QgsDataSourceUri(layer.dataProvider().dataSourceUri())
        conninfo = dsUri.connectionInfo()
        conn = None
        ok = False
        while not conn:
            try:
                conn = psycopg2.connect(dsUri.connectionInfo())
            except psycopg2.OperationalError:
                (ok, user, passwd) = QgsCredentials.instance().get(
                    conninfo, dsUri.username(), dsUri.password())
                if not ok:
                    break

                dsUri.setUsername(user)
                dsUri.setPassword(passwd)

        if not conn:
            raise RuntimeError(
                'Could not connect to PostgreSQL database - check connection info'
            )

        if ok:
            QgsCredentials.instance().put(conninfo, user, passwd)

        ogrstr = "PG:%s" % dsUri.connectionInfo()
    elif provider == "oracle":
        # OCI:user/password@host:port/service:table
        dsUri = QgsDataSourceUri(layer.dataProvider().dataSourceUri())
        ogrstr = "OCI:"
        if dsUri.username() != "":
            ogrstr += dsUri.username()
            if dsUri.password() != "":
                ogrstr += "/" + dsUri.password()
            delim = "@"

        if dsUri.host() != "":
            ogrstr += delim + dsUri.host()
            delim = ""
            if dsUri.port() != "" and dsUri.port() != '1521':
                ogrstr += ":" + dsUri.port()
            ogrstr += "/"
            if dsUri.database() != "":
                ogrstr += dsUri.database()
        elif dsUri.database() != "":
            ogrstr += delim + dsUri.database()

        if ogrstr == "OCI:":
            raise RuntimeError(
                'Invalid oracle data source - check connection info')

        ogrstr += ":"
        if dsUri.schema() != "":
            ogrstr += dsUri.schema() + "."

        ogrstr += dsUri.table()
    else:
        ogrstr = str(layer.source()).split("|")[0]

    return '"' + ogrstr + '"'
コード例 #32
0
    def on_accept(self):
        """Accept and run the Command.run_it method."""

        db_key = self.database_combo.currentText()

        settings = self.databases[db_key]
        db_set = settings["db_settings"]

        if settings["db_type"] == "spatialite":
            pass
        else:  # postgres

            successful_connection = False

            uname = db_set["username"]
            passwd = db_set["password"]
            msg = "Log in"

            while not successful_connection:

                uri = QgsDataSourceUri()
                uri.setConnection(
                    db_set["host"],
                    db_set["port"],
                    db_set["database"],
                    db_set["username"],
                    db_set["password"],
                )

                # try to connect
                # create a PostgreSQL connection using QSqlDatabase
                db = QSqlDatabase.addDatabase("QPSQL")
                # check to see if it is valid

                db.setHostName(uri.host())
                db.setDatabaseName(uri.database())
                try:
                    # port can be an empty string, e.g. for spatialite db's
                    db.setPort(int(uri.port()))
                except ValueError:
                    # TODO: I've seen this uri.port() handling before in some
                    # other file, this can probably be refactored.
                    pass
                db.setUserName(uri.username())
                db.setPassword(uri.password())

                # open (create) the connection
                if db.open():
                    successful_connection = True
                    break
                else:
                    # todo - provide feedback what is wrong
                    pass

                connInfo = uri.connectionInfo()
                (success, uname, passwd) = QgsCredentialDialog.instance().get(
                    connInfo, uname, passwd, msg)

                if success:
                    db_set["username"] = uname
                    db_set["password"] = passwd
                else:
                    return

        checks = []

        if self.check_manhole_indicator.isChecked():
            checks.append("manhole_indicator")

        if self.check_pipe_friction.isChecked():
            checks.append("pipe_friction")

        if self.check_manhole_area.isChecked():
            checks.append("manhole_area")

        self.command.run_it(
            checks,
            self.check_only_empty_fields.isChecked(),
            db_set,
            settings["db_type"],
        )

        self.accept()
コード例 #33
0
    def read_form(self):
        """Extracts all inputs from the dialog form and provides run_task when succesful.
        
        The selected_layer object is used to find the connection info to the Oracle
        database. If the database connection is there but either the username or
        password is missing get_credentials() will be called until valid credentials
        are entered or the task is canceled.
        """
        filter_on_height = self.dlg.cb_filterOnHeight.isChecked()
        filter_on_volumetric_weight = self.dlg.cb_filterOnVolumetricWeight.isChecked(
        )
        selected_layer = self.dlg.cmb_layers.currentLayer()
        trx_bool = self.dlg.cb_TriaxiaalProeven.isChecked()
        sdp_bool = self.dlg.cb_SamendrukkingProeven.isChecked()
        output_location = self.dlg.fileWidget.filePath()
        output_name = self.dlg.le_outputName.text()
        args = {
            'selected_layer': selected_layer,
            'output_location': output_location,
            'output_name': output_name,
            'trx_bool': trx_bool,
            'sdp_bool': sdp_bool
        }

        # General Asserts
        assert isinstance(
            selected_layer, QgsVectorLayer
        ), 'De geselecteerde laag \'{}\' is geen vector laag.'.format(
            selected_layer.name())
        assert output_name, 'Het veld \'uitvoernaam\' mag niet leeg zijn.'
        assert output_location, 'Het veld \'uitvoermap\' mag niet leeg zijn.'

        if trx_bool:
            # TRX Asserts
            assert any([
                self.dlg.cb_CU.isChecked(),
                self.dlg.cb_CD.isChecked(),
                self.dlg.cb_UU.isChecked()
            ]), 'Een van de drie Proeftypes moet aangekruisd worden.'
            proef_types = []
            if self.dlg.cb_CU.isChecked():
                proef_types.append('CU')
            if self.dlg.cb_CD.isChecked():
                proef_types.append('CD')
            if self.dlg.cb_UU.isChecked():
                proef_types.append('UU')
            args['proef_types'] = proef_types
            args['ea'] = self.dlg.sb_strain.value()
            args['save_plot'] = self.dlg.cb_savePlot.isChecked()

            if self.dlg.le_vg_trx.text():
                volG_trx = self.dlg.le_vg_trx.text().strip('[').strip(
                    ']').split(',')
                volG_trx = [float(x) for x in volG_trx]
                volG_trx.sort()
                if len(volG_trx) < 2:
                    self.iface.messageBar().pushMessage(
                        "Warning",
                        'Maar 1 volumegewicht interval voor triaxiaalproeven is gegeven, het interval wordt automatisch gegenereerd.',
                        level=1,
                        duration=5)
                    volG_trx = None
            else:
                volG_trx = None
            args['volG_trx'] = volG_trx

        if sdp_bool:
            if self.dlg.le_vg_sdp.text():
                volG_sdp = self.dlg.le_vg_sdp.text().strip('[').strip(
                    ']').split(',')
                volG_sdp = [float(x) for x in volG_sdp]
                volG_sdp.sort()
                if len(volG_sdp) < 2:
                    self.iface.messageBar().pushMessage(
                        "Warning",
                        'Maar 1 volumegewicht interval voor samendrukkingsproeven is gegeven, het interval wordt automatisch gegenereerd.',
                        level=1,
                        duration=5)
                    volG_sdp = None
            else:
                volG_sdp = None
            args['volG_sdp'] = volG_sdp

        if filter_on_height:
            args['maxH'] = self.dlg.sb_maxHeight.value()
            args['minH'] = self.dlg.sb_minHeight.value()
            assert args['maxH'] > args[
                'minH'], 'Maximum hoogte moet hoger zijn dan minimum hoogte.'
        if filter_on_volumetric_weight:
            args['maxVg'] = self.dlg.sb_maxVolumetricWeight.value()
            args['minVg'] = self.dlg.sb_minVolumetricWeight.value()
            assert args['maxVg'] > args[
                'minVg'], 'Maximum volumegewicht moet hoger zijn dan het minimum volumegewicht.'

        source = selected_layer.source()
        uri = QgsDataSourceUri(source)

        savedUsername = uri.hasParam('username')
        savedPassword = uri.hasParam('password')

        host = uri.host()
        port = uri.port()
        database = uri.database()
        username = uri.username()
        password = uri.password()

        errorMessage = None
        if savedUsername is True and savedPassword is True:
            try:
                qb = qgis_backend.QgisBackend(host=host,
                                              port=port,
                                              database=database,
                                              username=username,
                                              password=password)
                qb.check_connection()
                args['qb'] = qb
                self.run_task(args)
            except cx_Oracle.DatabaseError as e:
                errorObj, = e.args
                errorMessage = errorObj.message
                suc = 'false'
                while suc == 'false':
                    suc, qb, errorMessage = self.get_credentials(
                        host,
                        port,
                        database,
                        username=username,
                        password=password,
                        message=errorMessage)
                if suc == 'exit':
                    pass
                elif suc == 'true':
                    args['qb'] = qb
                    self.run_task(args)
        else:
            suc, qb, errorMessage = self.get_credentials(host,
                                                         port,
                                                         database,
                                                         username=username,
                                                         password=password)
            while suc == 'false':
                suc, qb, message = self.get_credentials(host,
                                                        port,
                                                        database,
                                                        message=errorMessage)
            if suc == 'exit':
                pass
            elif suc == 'true':
                args['qb'] = qb
                self.run_task(args)
コード例 #34
0
ファイル: vector.py プロジェクト: nyalldawson/QGIS
def ogrConnectionString(uri):
    """Generates OGR connection sting from layer source
    """
    ogrstr = None

    layer = dataobjects.getObjectFromUri(uri, False)
    if layer is None:
        return '"' + uri + '"'
    provider = layer.dataProvider().name()
    if provider == "spatialite":
        # dbname='/geodata/osm_ch.sqlite' table="places" (Geometry) sql=
        regex = re.compile("dbname='(.+)'")
        r = regex.search(str(layer.source()))
        ogrstr = r.groups()[0]
    elif provider == "postgres":
        # dbname='ktryjh_iuuqef' host=spacialdb.com port=9999
        # user='******' password='******' sslmode=disable
        # key='gid' estimatedmetadata=true srid=4326 type=MULTIPOLYGON
        # table="t4" (geom) sql=
        dsUri = QgsDataSourceUri(layer.dataProvider().dataSourceUri())
        conninfo = dsUri.connectionInfo()
        conn = None
        ok = False
        while not conn:
            try:
                conn = psycopg2.connect(dsUri.connectionInfo())
            except psycopg2.OperationalError:
                (ok, user, passwd) = QgsCredentials.instance().get(conninfo, dsUri.username(), dsUri.password())
                if not ok:
                    break

                dsUri.setUsername(user)
                dsUri.setPassword(passwd)

        if not conn:
            raise RuntimeError("Could not connect to PostgreSQL database - check connection info")

        if ok:
            QgsCredentials.instance().put(conninfo, user, passwd)

        ogrstr = "PG:%s" % dsUri.connectionInfo()
    elif provider == "oracle":
        # OCI:user/password@host:port/service:table
        dsUri = QgsDataSourceUri(layer.dataProvider().dataSourceUri())
        ogrstr = "OCI:"
        if dsUri.username() != "":
            ogrstr += dsUri.username()
            if dsUri.password() != "":
                ogrstr += "/" + dsUri.password()
            delim = "@"

        if dsUri.host() != "":
            ogrstr += delim + dsUri.host()
            delim = ""
            if dsUri.port() != "" and dsUri.port() != "1521":
                ogrstr += ":" + dsUri.port()
            ogrstr += "/"
            if dsUri.database() != "":
                ogrstr += dsUri.database()
        elif dsUri.database() != "":
            ogrstr += delim + dsUri.database()

        if ogrstr == "OCI:":
            raise RuntimeError("Invalid oracle data source - check connection info")

        ogrstr += ":"
        if dsUri.schema() != "":
            ogrstr += dsUri.schema() + "."

        ogrstr += dsUri.table()
    else:
        ogrstr = str(layer.source()).split("|")[0]

    return '"' + ogrstr + '"'
コード例 #35
0
    def processAlgorithm(self, parameters, context, feedback):
        msg = ""
        output_layers = []
        layers_name = [
            "repere", "poi_tourisme", "poi_service", "liaison", "segment"
        ]
        layers_v_name = ["v_portion", "v_itineraire"]
        tables_name = [
            "element", "etape", "portion", "itineraire", "statut_segment_val",
            "amenagement_segment_val", "amenagement_type_segment_val"
        ]
        layers_to_load = layers_name + layers_v_name + tables_name
        if Qgis.QGIS_VERSION_INT >= 31400:
            connection = self.parameterAsConnectionName(
                parameters, self.DATABASE, context)
            schema = self.parameterAsSchema(parameters, self.SCHEMA, context)
        else:
            connection = self.parameterAsString(parameters, self.DATABASE,
                                                context)
            schema = self.parameterAsString(parameters, self.SCHEMA, context)

        feedback.pushInfo("## CONNEXION A LA BASE DE DONNEES ##")
        metadata = QgsProviderRegistry.instance().providerMetadata('postgres')
        connection = metadata.findConnection(connection)
        uri = QgsDataSourceUri(connection.uri())
        connection_info = uri.connectionInfo()

        is_host = uri.host() != ""
        if is_host:
            feedback.pushInfo("Connexion établie via l'hote")
        else:
            feedback.pushInfo("Connexion établie via le service")

        feedback.pushInfo("")
        feedback.pushInfo("## LISTE DES COUCHES A CHARGER ##")
        for layer in context.project().mapLayers().values():
            if layer.type() == QgsMapLayerType.VectorLayer and \
               layer.dataProvider().name() == 'postgres':
                l_uri = layer.dataProvider().uri()
                table_name = l_uri.table()
                if l_uri.connectionInfo() == connection_info and \
                   l_uri.schema() == schema and \
                   table_name in layers_to_load:
                    layers_to_load.remove(table_name)

        feedback.pushInfo("")
        feedback.pushInfo("## CHARGEMENT DES COUCHES ##")

        # add vector
        for x in layers_to_load:
            if x in layers_name:
                result = self.initLayer(context, uri, schema, x, "geom", "")
            elif x in layers_v_name:
                if x == "v_portion":
                    pkey = "id_portion"
                if x == "v_itineraire":
                    pkey = "id_itineraire"
                result = self.initLayer(context, uri, schema, x, "geom", "",
                                        pkey)
            elif x in tables_name:
                result = self.initLayer(context, uri, schema, x, None, "")

            if not result:
                feedback.reportError("La couche '" + x +
                                     "' ne peut pas être chargée")
            else:
                output_layers.append(result.id())

        # add raster
        raster = self.parameterAsBool(parameters, self.RASTER, context)
        if raster:
            if not context.project().mapLayersByName("OpenStreetMap"):
                url_with_params = (
                    'type=xyz&url=http://tile.openstreetmap.org/%7Bz%7D/%7Bx%7D/%7By%7D.png&'
                    'zmax=19&zmin=0&crs=EPSG3857')
                result = self.XYZ(context, url_with_params, 'OpenStreetMap')
                output_layers.append(result.id())

        output_len = len(output_layers)
        if output_len == 1:
            msg = '{} couche chargée'.format(output_len)
        elif output_len:
            msg = '{} couches chargées'.format(output_len)
        else:
            msg = 'Aucunes couches chargées'

        return {self.OUTPUT_MSG: msg, self.OUTPUT: output_layers}