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')
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'))
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())
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()
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'}
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)
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'}
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
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
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
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
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()
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
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
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())
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())}
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())
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
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)
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 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, ''
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 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 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)
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 + '"'
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)
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'}
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',
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)
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 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()
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)
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 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}