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)
