def processAlgorithm(self, progress): connection = self.getParameterValue(self.DATABASE) settings = QSettings() mySettings = '/PostgreSQL/connections/' + connection try: database = settings.value(mySettings + '/database') username = settings.value(mySettings + '/username') host = settings.value(mySettings + '/host') port = settings.value(mySettings + '/port', type=int) password = settings.value(mySettings + '/password') except Exception as e: raise GeoAlgorithmExecutionException( self.tr('Wrong database connection name: %s' % connection)) try: self.db = postgis.GeoDB(host=host, port=port, dbname=database, user=username, passwd=password) except postgis.DbError as e: raise GeoAlgorithmExecutionException( self.tr("Couldn't connect to database:\n%s") % unicode(e)) sql = self.getParameterValue(self.SQL).replace('\n', ' ') try: self.db._exec_sql_and_commit(unicode(sql)) except postgis.DbError as e: raise GeoAlgorithmExecutionException( self.tr('Error executing SQL:\n%s') % unicode(e))
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 processAlgorithm(self, progress): connection = self.DB_CONNECTIONS[self.getParameterValue(self.DATABASE)] schema = self.getParameterValue(self.SCHEMA) overwrite = self.getParameterValue(self.OVERWRITE) createIndex = self.getParameterValue(self.CREATEINDEX) convertLowerCase = self.getParameterValue(self.LOWERCASE_NAMES) dropStringLength = self.getParameterValue(self.DROP_STRING_LENGTH) forceSinglePart = self.getParameterValue(self.FORCE_SINGLEPART) primaryKeyField = self.getParameterValue(self.PRIMARY_KEY) encoding = self.getParameterValue(self.ENCODING) settings = QSettings() mySettings = '/PostgreSQL/connections/' + connection try: database = settings.value(mySettings + '/database') username = settings.value(mySettings + '/username') host = settings.value(mySettings + '/host') port = settings.value(mySettings + '/port', type=int) password = settings.value(mySettings + '/password') except Exception as e: raise GeoAlgorithmExecutionException( self.tr('Wrong database connection name: %s' % connection)) layerUri = self.getParameterValue(self.INPUT) layer = dataobjects.getObjectFromUri(layerUri) table = self.getParameterValue(self.TABLENAME).strip() if table == '': table = layer.name() table = table.replace(' ', '').lower()[0:62] providerName = 'postgres' try: db = postgis.GeoDB(host=host, port=port, dbname=database, user=username, passwd=password) except postgis.DbError as e: raise GeoAlgorithmExecutionException( self.tr("Couldn't connect to database:\n%s") % unicode(e)) geomColumn = self.getParameterValue(self.GEOMETRY_COLUMN) if not geomColumn: geomColumn = 'the_geom' options = {} if overwrite: options['overwrite'] = True if convertLowerCase: options['lowercaseFieldNames'] = True geomColumn = geomColumn.lower() if dropStringLength: options['dropStringConstraints'] = True if forceSinglePart: options['forceSinglePartGeometryType'] = True # Clear geometry column for non-geometry tables if not layer.hasGeometryType(): geomColumn = None uri = QgsDataSourceURI() uri.setConnection(host, unicode(port), database, username, password) if primaryKeyField: uri.setDataSource(schema, table, geomColumn, '', primaryKeyField) else: uri.setDataSource(schema, table, geomColumn, '') if encoding: layer.setProviderEncoding(encoding) (ret, errMsg) = QgsVectorLayerImport.importLayer( layer, uri.uri(), providerName, self.crs, False, False, options, ) if ret != 0: raise GeoAlgorithmExecutionException( self.tr('Error importing to PostGIS\n%s' % errMsg)) if geomColumn and createIndex: db.create_spatial_index(table, schema, geomColumn) db.vacuum_analyze(table, schema)