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
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 + '"'
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)
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 + '"'
def __init__(self, iface, layer, parent=None): QWidget.__init__(self, parent) self.iface = iface self.layer = layer uri = QgsDataSourceUri(layer.source()) dbplugin = None db = None if layer.dataProvider().name() == 'postgres': dbplugin = createDbPlugin('postgis', 'postgres') elif layer.dataProvider().name() == 'spatialite': dbplugin = createDbPlugin('spatialite', 'spatialite') elif layer.dataProvider().name() == 'oracle': dbplugin = createDbPlugin('oracle', 'oracle') elif layer.dataProvider().name() == 'virtual': dbplugin = createDbPlugin('vlayers', 'virtual') elif layer.dataProvider().name() == 'ogr': dbplugin = createDbPlugin('gpkg', 'gpkg') if dbplugin: dbplugin.connectToUri(uri) db = dbplugin.db self.dbplugin = dbplugin self.db = db self.filter = "" self.allowMultiColumnPk = isinstance(db, PGDatabase) # at the moment only PostgreSQL allows a primary key to span multiple columns, SpatiaLite doesn't self.aliasSubQuery = isinstance(db, PGDatabase) # only PostgreSQL requires subqueries to be aliases self.setupUi(self) self.setWindowTitle( u"%s - %s [%s]" % (self.windowTitle(), db.connection().connectionName(), db.connection().typeNameString())) self.defaultLayerName = 'QueryLayer' if self.allowMultiColumnPk: self.uniqueColumnCheck.setText(self.tr("Column(s) with unique values")) else: self.uniqueColumnCheck.setText(self.tr("Column with unique values")) self.editSql.setFocus() self.editSql.setVerticalScrollBarPolicy(Qt.ScrollBarAsNeeded) self.editSql.setMarginVisible(True) self.initCompleter() # allow copying results copyAction = QAction("copy", self) self.viewResult.addAction(copyAction) copyAction.setShortcuts(QKeySequence.Copy) copyAction.triggered.connect(self.copySelectedResults) self.btnExecute.clicked.connect(self.executeSql) self.btnSetFilter.clicked.connect(self.setFilter) self.btnClear.clicked.connect(self.clearSql) self.presetStore.clicked.connect(self.storePreset) self.presetDelete.clicked.connect(self.deletePreset) self.presetCombo.activated[str].connect(self.loadPreset) self.presetCombo.activated[str].connect(self.presetName.setText) self.editSql.textChanged.connect(self.updatePresetButtonsState) self.presetName.textChanged.connect(self.updatePresetButtonsState) self.presetCombo.currentIndexChanged.connect(self.updatePresetButtonsState) self.updatePresetsCombobox() self.geomCombo.setEditable(True) self.geomCombo.lineEdit().setReadOnly(True) self.uniqueCombo.setEditable(True) self.uniqueCombo.lineEdit().setReadOnly(True) self.uniqueModel = QStandardItemModel(self.uniqueCombo) self.uniqueCombo.setModel(self.uniqueModel) if self.allowMultiColumnPk: self.uniqueCombo.setItemDelegate(QStyledItemDelegate()) self.uniqueModel.itemChanged.connect(self.uniqueChanged) # react to the (un)checking of an item self.uniqueCombo.lineEdit().textChanged.connect(self.uniqueTextChanged) # there are other events that change the displayed text and some of them can not be caught directly self.layerTypeWidget.hide() # show if load as raster is supported # self.loadLayerBtn.clicked.connect(self.loadSqlLayer) self.updateLayerBtn.clicked.connect(self.updateSqlLayer) self.getColumnsBtn.clicked.connect(self.fillColumnCombos) self.queryBuilderFirst = True self.queryBuilderBtn.setIcon(QIcon(":/db_manager/icons/sql.gif")) self.queryBuilderBtn.clicked.connect(self.displayQueryBuilder) self.presetName.textChanged.connect(self.nameChanged) # Update from layer # First the SQL from QgsDataSourceUri table sql = uri.table() if uri.keyColumn() == '_uid_': match = re.search(r'^\(SELECT .+ AS _uid_,\* FROM \((.*)\) AS _subq_.+_\s*\)$', sql, re.S | re.X) if match: sql = match.group(1) else: match = re.search(r'^\((SELECT .+ FROM .+)\)$', sql, re.S | re.X) if match: sql = match.group(1) # Need to check on table() since the parentheses were removed by the regexp if not uri.table().startswith('(') and not uri.table().endswith(')'): schema = uri.schema() if schema and schema.upper() != 'PUBLIC': sql = 'SELECT * FROM {0}.{1}'.format(self.db.connector.quoteId(schema), self.db.connector.quoteId(sql)) else: sql = 'SELECT * FROM {0}'.format(self.db.connector.quoteId(sql)) self.editSql.setText(sql) self.executeSql() # Then the columns self.geomCombo.setCurrentIndex(self.geomCombo.findText(uri.geometryColumn(), Qt.MatchExactly)) if uri.keyColumn() != '_uid_': self.uniqueColumnCheck.setCheckState(Qt.Checked) if self.allowMultiColumnPk: itemsData = uri.keyColumn().split(',') for item in self.uniqueModel.findItems("*", Qt.MatchWildcard): if item.data() in itemsData: item.setCheckState(Qt.Checked) else: keyColumn = uri.keyColumn() if self.uniqueModel.findItems(keyColumn): self.uniqueCombo.setEditText(keyColumn) # Finally layer name, filter and selectAtId self.layerNameEdit.setText(layer.name()) self.filter = uri.sql() if uri.selectAtIdDisabled(): self.avoidSelectById.setCheckState(Qt.Checked)
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 + '"'
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' elif provider.lower() == "wfs": uri = QgsDataSourceUri(layer.source()) baseUrl = uri.param('url').split('?')[0] ogrstr = "WFS:{}".format(baseUrl) format = 'WFS' else: ogrstr = str(layer.source()).split("|")[0] path, ext = os.path.splitext(ogrstr) format = QgsVectorFileWriter.driverForExtension(ext) return ogrstr, '"' + format + '"'
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 + '"'
def __init__(self, iface, layer, parent=None): QWidget.__init__(self, parent) self.iface = iface self.layer = layer uri = QgsDataSourceUri(layer.source()) dbplugin = None db = None if layer.dataProvider().name() == 'postgres': dbplugin = createDbPlugin('postgis', 'postgres') elif layer.dataProvider().name() == 'spatialite': dbplugin = createDbPlugin('spatialite', 'spatialite') elif layer.dataProvider().name() == 'oracle': dbplugin = createDbPlugin('oracle', 'oracle') elif layer.dataProvider().name() == 'virtual': dbplugin = createDbPlugin('vlayers', 'virtual') elif layer.dataProvider().name() == 'ogr': dbplugin = createDbPlugin('gpkg', 'gpkg') if dbplugin: dbplugin.connectToUri(uri) db = dbplugin.db self.dbplugin = dbplugin self.db = db self.filter = "" self.allowMultiColumnPk = isinstance( db, PGDatabase ) # at the moment only PostgreSQL allows a primary key to span multiple columns, SpatiaLite doesn't self.aliasSubQuery = isinstance( db, PGDatabase) # only PostgreSQL requires subqueries to be aliases self.setupUi(self) self.setWindowTitle( u"%s - %s [%s]" % (self.windowTitle(), db.connection().connectionName(), db.connection().typeNameString())) self.defaultLayerName = 'QueryLayer' if self.allowMultiColumnPk: self.uniqueColumnCheck.setText( self.tr("Column(s) with unique values")) else: self.uniqueColumnCheck.setText( self.tr("Column with unique values")) self.editSql.setFocus() self.editSql.setVerticalScrollBarPolicy(Qt.ScrollBarAsNeeded) self.editSql.setMarginVisible(True) self.initCompleter() # allow copying results copyAction = QAction("copy", self) self.viewResult.addAction(copyAction) copyAction.setShortcuts(QKeySequence.Copy) copyAction.triggered.connect(self.copySelectedResults) self.btnExecute.clicked.connect(self.executeSql) self.btnSetFilter.clicked.connect(self.setFilter) self.btnClear.clicked.connect(self.clearSql) self.presetStore.clicked.connect(self.storePreset) self.presetDelete.clicked.connect(self.deletePreset) self.presetCombo.activated[str].connect(self.loadPreset) self.presetCombo.activated[str].connect(self.presetName.setText) self.editSql.textChanged.connect(self.updatePresetButtonsState) self.presetName.textChanged.connect(self.updatePresetButtonsState) self.presetCombo.currentIndexChanged.connect( self.updatePresetButtonsState) self.updatePresetsCombobox() self.geomCombo.setEditable(True) self.geomCombo.lineEdit().setReadOnly(True) self.uniqueCombo.setEditable(True) self.uniqueCombo.lineEdit().setReadOnly(True) self.uniqueModel = QStandardItemModel(self.uniqueCombo) self.uniqueCombo.setModel(self.uniqueModel) if self.allowMultiColumnPk: self.uniqueCombo.setItemDelegate(QStyledItemDelegate()) self.uniqueModel.itemChanged.connect( self.uniqueChanged) # react to the (un)checking of an item self.uniqueCombo.lineEdit().textChanged.connect( self.uniqueTextChanged ) # there are other events that change the displayed text and some of them can not be caught directly self.layerTypeWidget.hide() # show if load as raster is supported # self.loadLayerBtn.clicked.connect(self.loadSqlLayer) self.updateLayerBtn.clicked.connect(self.updateSqlLayer) self.getColumnsBtn.clicked.connect(self.fillColumnCombos) self.queryBuilderFirst = True self.queryBuilderBtn.setIcon(QIcon(":/db_manager/icons/sql.gif")) self.queryBuilderBtn.clicked.connect(self.displayQueryBuilder) self.presetName.textChanged.connect(self.nameChanged) # Update from layer # First the SQL from QgsDataSourceUri table sql = uri.table() if uri.keyColumn() == '_uid_': match = re.search( r'^\(SELECT .+ AS _uid_,\* FROM \((.*)\) AS _subq_.+_\s*\)$', sql, re.S | re.X) if match: sql = match.group(1) else: match = re.search(r'^\((SELECT .+ FROM .+)\)$', sql, re.S | re.X) if match: sql = match.group(1) # Need to check on table() since the parentheses were removed by the regexp if not uri.table().startswith('(') and not uri.table().endswith(')'): schema = uri.schema() if schema and schema.upper() != 'PUBLIC': sql = 'SELECT * FROM {0}.{1}'.format( self.db.connector.quoteId(schema), self.db.connector.quoteId(sql)) else: sql = 'SELECT * FROM {0}'.format( self.db.connector.quoteId(sql)) self.editSql.setText(sql) self.executeSql() # Then the columns self.geomCombo.setCurrentIndex( self.geomCombo.findText(uri.geometryColumn(), Qt.MatchExactly)) if uri.keyColumn() != '_uid_': self.uniqueColumnCheck.setCheckState(Qt.Checked) if self.allowMultiColumnPk: itemsData = uri.keyColumn().split(',') for item in self.uniqueModel.findItems("*", Qt.MatchWildcard): if item.data() in itemsData: item.setCheckState(Qt.Checked) else: keyColumn = uri.keyColumn() for item in self.uniqueModel.findItems("*", Qt.MatchWildcard): if item.data() == keyColumn: self.uniqueCombo.setCurrentIndex( self.uniqueModel.indexFromItem(item).row()) # Finally layer name, filter and selectAtId self.layerNameEdit.setText(layer.name()) self.filter = uri.sql() if uri.selectAtIdDisabled(): self.avoidSelectById.setCheckState(Qt.Checked)
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 = QgsSettings() 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)
def publishLayer(self, layer, fields=None): lyr_title, safe_name = layerUtils.getLayerTitleAndName(layer) if layer.type() == layer.VectorLayer: if layer.featureCount() == 0: self.logError( "Layer '%s' contains zero features and cannot be published" % lyr_title) 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 ]: src_path, src_name, src_ext = layerUtils.getLayerSourceInfo( layer) filename = self._exportedLayers.get(src_path) if not filename: if self.storage == self.POSTGIS_MANAGED_BY_GEOSERVER: shp_name = exportLayer(layer, fields, toShapefile=True, force=True, log=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, log=self) self._exportedLayers[src_path] = filename 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( "GeoCat Bridge", "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, safe_name) self._clearCache()
def _onPointClicked(self, ps_layer, point): # get the id of the point feature under the mouse click from .MapTools import FeatureFinder fid = FeatureFinder.findAtPoint(ps_layer, point, canvas=self.iface.mapCanvas(), onlyTheClosestOne=True, onlyIds=True) if fid is None: return # get the attribute map of the selected feature feat = QgsFeature() feats = ps_layer.getFeatures( QgsFeatureRequest(fid) ) feats.nextFeature(feat) attrs = feat.attributes() x, y = [], [] # lists containg x,y values infoFields = {} # hold the index->name of the fields containing info to be displayed ps_source = ps_layer.source() ps_fields = ps_layer.dataProvider().fields() providerType = ps_layer.providerType() uri = ps_source subset = "" if providerType == 'ogr' and ps_source.lower().endswith( ".shp" ): # Shapefile for idx, fld in enumerate(ps_fields): if QRegExp( "D\\d{8}", Qt.CaseInsensitive ).indexIn( fld.name() ) < 0: # info fields are all except those containing dates infoFields[ idx ] = fld else: x.append( QDate.fromString( fld.name()[1:], "yyyyMMdd" ).toPyDate() ) y.append( float(attrs[ idx ]) ) elif providerType == 'ogr' and (ps_source.upper().startswith("OCI:") or ps_source.lower().endswith(".vrt")): # Oracle Spatial # fields containing values dateField = "data_misura" valueField = "spost_rel_mm" infoFields = dict(enumerate(ps_fields)) # search for the id_dataset and code_target fields needed to join # PS and TS tables idDataset = codeTarget = None for idx, fld in enumerate(ps_fields): if fld.name().lower() == "id_dataset": idDataset = attrs[ idx ] if fld.name().lower() == "code_target": codeTarget = attrs[ idx ] if idDataset is None or codeTarget is None: QgsMessageLog.logMessage( "idDataset is %s, codeTarget is %s. Exiting" % (idDataset, codeTarget), "PSTimeSeriesViewer" ) return subset = "id_dataset='%s' AND code_target='%s'" % (idDataset, codeTarget) # create the uri if ps_source.upper().startswith( "OCI:" ): default_tbl_name = "RISKNAT.RNAT_TARGET_SSTO" elif ps_source.lower().endswith(".vrt"): default_tbl_name = "rnat_target_sso.vrt" else: default_tbl_name = "" if not self._askTStablename( ps_layer, default_tbl_name ): return if ps_source.upper().startswith( "OCI:" ): # uri is like OCI:userid/password@database:table pos = uri.indexOf(':', 4) if pos >= 0: uri = uri[0:pos] uri = "%s:%s" % (uri, self.ts_tablename) else: # it's a VRT file uri = "%s/%s" % (QFileInfo(ps_source).path(), self.ts_tablename) uri = QDir.toNativeSeparators( uri ) # load the layer containing time series ts_layer = self._createTSlayer( uri, providerType, subset ) if ts_layer is None: return # get time series X and Y values try: x, y = self._getXYvalues( ts_layer, dateField, valueField ) finally: ts_layer.deleteLater() del ts_layer elif providerType in ['postgres', 'spatialite']:# either PostGIS or SpatiaLite # fields containing values dateField = "dataripresa" valueField = "valore" infoFields = dict(enumerate(ps_fields)) # search for the id_dataset and code_target fields needed to join # PS and TS tables code = None for idx, fld in enumerate( ps_fields ): if fld.name().lower() == "code": code = attrs[ idx ] if code is None: QgsMessageLog.logMessage( "code is None. Exiting" % code, "PSTimeSeriesViewer" ) return subset = "code='%s'" % code # create the uri dsuri = QgsDataSourceUri( ps_layer.source() ) default_tbl_name = "ts_%s" % dsuri.table() if not self._askTStablename( ps_layer, default_tbl_name ): return dsuri.setDataSource( dsuri.schema(), self.ts_tablename, None ) # None or "" ? check during tests dsuri.setWkbType(QgsWkbTypes.Unknown) dsuri.setSrid(None) uri = dsuri.uri() # load the layer containing time series ts_layer = self._createTSlayer( uri, providerType, subset ) if ts_layer is None: return # get time series X and Y values try: x, y = self._getXYvalues( ts_layer, dateField, valueField ) finally: ts_layer.deleteLater() del ts_layer if len(x) * len(y) <= 0: QMessageBox.warning( self.iface.mainWindow(), "PS Time Series Viewer", "No time series values found for the selected point." ) QgsMessageLog.logMessage( "provider: %s - uri: %s\nsubset: %s" % (providerType, uri, subset), "PSTimeSeriesViewer" ) return # display the plot dialog from .pstimeseries_dlg import PSTimeSeries_Dlg dlg = PSTimeSeries_Dlg( ps_layer, infoFields ) dlg.setFeatureId( fid ) dlg.setData( x, y ) return dlg
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(unicode(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 = unicode(layer.source()).split("|")[0] return '"' + ogrstr + '"'