def connectDatabaseWithQSettings(self, name):
(host, port, database, user, password) = self.getConnectionFromQSettings(name)
self.db.setHostName(host)
if type(port) != 'int':
self.db.setPort(int(port))
else:
self.db.setPort(port)
self.db.setDatabaseName(database)
self.db.setUserName(user)
if not password or password == '':
conInfo = 'host='+host+' port='+port+' dbname='+database
check = False
while not check:
try:
(success, user, password) = QgsCredentials.instance().get(conInfo, user, None)
if not success:
return
self.db.setPassword(password)
check = True
self.checkAndOpenDb()
QgsCredentials.instance().put(conInfo, user, password)
except:
pass
else:
self.db.setPassword(password)
def __init__(self, uri):
DBConnector.__init__(self, uri)
self.host = uri.host() or os.environ.get("PGHOST")
self.port = uri.port() or os.environ.get("PGPORT")
username = uri.username() or os.environ.get("PGUSER") or os.environ.get("USER")
password = uri.password() or os.environ.get("PGPASSWORD")
try:
self.connection = psycopg2.connect(self._connectionInfo().encode("utf-8"))
except self.connection_error_types(), e:
err = str(e)
uri = self.uri()
conninfo = uri.connectionInfo()
for i in range(3):
(ok, username, password) = QgsCredentials.instance().get(conninfo, username, password, err)
if not ok:
raise ConnectionError(e)
if username:
uri.setUsername(username)
if password:
uri.setPassword(password)
try:
self.connection = psycopg2.connect(uri.connectionInfo().encode("utf-8"))
QgsCredentials.instance().put(conninfo, username, password)
except self.connection_error_types(), e:
if i == 2:
raise ConnectionError(e)
err = str(e)
def pg_conn_info(self):
"""returns current postgis versionned DB connection info
request credentials if needed"""
if not self._pg_conn_info:
# In the simple case: all pg layers share the same conn info
# we set the conn info, if not, we ask for a connection
# We then request credentials if necessary
conn_info = ""
for layer in self.iface.legendInterface().layers():
if layer.providerType() == "postgres":
cni = QgsDataSourceURI(layer.source()).connectionInfo()
if not conn_info:
conn_info = cni
elif conn_info != cni:
conn_info = "heterogeneous"
if conn_info == "heterogeneous":
assert False # TODO request connection
uri = QgsDataSourceURI(conn_info)
conn = None
try:
conn = psycopg2.connect(conn_info)
except:
conn = None
if not conn:
# print "Case when the pass/user are not saved in the project"
(success, user, passwd) = QgsCredentials.instance().get(conn_info, None, None)
if success:
QgsCredentials.instance().put(conn_info, user, passwd)
uri.setPassword(passwd)
uri.setUsername(user)
self._pg_conn_info = uri.connectionInfo()
return self._pg_conn_info
def saveToPostGIS(self):
dlg = PostgisTableSelector(self, self.parameter.name().lower())
dlg.exec_()
if dlg.connection:
self.use_temporary = False
settings = QgsSettings()
mySettings = '/PostgreSQL/connections/' + dlg.connection
dbname = settings.value(mySettings + '/database')
user = settings.value(mySettings + '/username')
host = settings.value(mySettings + '/host')
port = settings.value(mySettings + '/port')
password = settings.value(mySettings + '/password')
uri = QgsDataSourceUri()
uri.setConnection(host, str(port), dbname, user, password)
uri.setDataSource(dlg.schema, dlg.table,
"the_geom" if isinstance(self.parameter, QgsProcessingParameterFeatureSink) and self.parameter.hasGeometry() else None)
connInfo = uri.connectionInfo()
(success, user, passwd) = QgsCredentials.instance().get(connInfo, None, None)
if success:
QgsCredentials.instance().put(connInfo, user, passwd)
self.leText.setText("postgis:" + uri.uri())
self.skipOutputChanged.emit(False)
self.destinationChanged.emit()
def ogrConnectionString(self, uri):
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()
(success, user, passwd ) = QgsCredentials.instance().get(connInfo, None, None)
if success:
QgsCredentials.instance().put(connInfo, user, passwd)
ogrstr = ("PG:dbname='%s' host='%s' port='%s' user='******' password='******'"
% (dsUri.database(), dsUri.host(), dsUri.port(), user, passwd))
else:
ogrstr = unicode(layer.source()).split("|")[0]
return '"' + ogrstr + '"'
def setCredentials(self, db, conInfo, user):
(success, user, password) = QgsCredentials.instance().get(conInfo, user, None)
if not success:
return
else:
db.setPassword(password)
if not db.open():
self.setCredentials(db, conInfo, user)
else:
QgsCredentials.instance().put(conInfo, user, password)
def connect_to_uri(self, uri):
self.close_connection()
self.host = uri.host() or os.environ.get('PGHOST')
self.port = uri.port() or os.environ.get('PGPORT')
username = uri.username() or os.environ.get('PGUSER') or os.environ.get('USER')
password = uri.password() or os.environ.get('PGPASSWORD')
try:
self.connection = psycopg2.connect(uri.connectionInfo())
except self.pg_error_types() as e:
err = str(e)
conninfo = uri.connectionInfo()
ok, username, password = QgsCredentials.instance().get(
conninfo, username, password, err)
if not ok:
raise Exception(e)
if username:
uri.setUsername(username)
if password:
uri.setPassword(password)
self.connection = psycopg2.connect(uri.connectionInfo())
self.pgencoding = self.connection.encoding
return True
def setCredentials(self, db, conInfo, user):
"""
Sets connection credentials
db: QSqlDatabase used
conInfo: connection information
user: user name
"""
(success, user, password) = QgsCredentials.instance().get(conInfo, user, None)
if not success:
return
else:
db.setPassword(password)
db.setUserName(user)
if not db.open():
self.setCredentials(db, conInfo, user)
else:
QgsCredentials.instance().put(conInfo, user, password)
def checkPasswordSupply(self, db, host, port, user, password):
if not password or password == '':
conInfo = 'host='+host+' port='+port+' dbname='+db
(success, user, password) = QgsCredentials.instance().get(conInfo, user, None)
if not success:
QMessageBox.warning(self, self.tr('Warning!'), self.tr('Password not supplied. Nothing can be done!'))
return False, None
return True, password
def __init__(self, uri):
DBConnector.__init__(self, uri)
self.host = uri.host() or os.environ.get('PGHOST')
self.port = uri.port() or os.environ.get('PGPORT')
username = uri.username() or os.environ.get('PGUSER') or os.environ.get('USER')
password = uri.password() or os.environ.get('PGPASSWORD')
try:
self.connection = psycopg2.connect(self._connectionInfo().encode('utf-8'))
except self.connection_error_types() as e:
err = unicode(e)
uri = self.uri()
conninfo = uri.connectionInfo()
for i in range(3):
(ok, username, password) = QgsCredentials.instance().get(conninfo, username, password, err)
if not ok:
raise ConnectionError(e)
if username:
uri.setUsername(username)
if password:
uri.setPassword(password)
try:
self.connection = psycopg2.connect(uri.connectionInfo().encode('utf-8'))
QgsCredentials.instance().put(conninfo, username, password)
except self.connection_error_types() as e:
if i == 2:
raise ConnectionError(e)
err = unicode(e)
self.connection.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
c = self._execute(None, u"SELECT current_user")
self.user = self._fetchone(c)
self._close_cursor(c)
self._checkSpatial()
self._checkRaster()
self._checkGeometryColumnsTable()
self._checkRasterColumnsTable()
def saveToPostGIS(self):
dlg = PostgisTableSelector(self, self.output.name.lower())
dlg.exec_()
if dlg.connection:
settings = QSettings()
mySettings = '/PostgreSQL/connections/' + dlg.connection
dbname = settings.value(mySettings + '/database')
user = settings.value(mySettings + '/username')
host = settings.value(mySettings + '/host')
port = settings.value(mySettings + '/port')
password = settings.value(mySettings + '/password')
uri = QgsDataSourceURI()
uri.setConnection(host, str(port), dbname, user, password)
uri.setDataSource(dlg.schema, dlg.table, "the_geom")
connInfo = uri.connectionInfo()
(success, user, passwd) = QgsCredentials.instance().get(connInfo, None, None)
if success:
QgsCredentials.instance().put(connInfo, user, passwd)
self.leText.setText("postgis:" + uri.uri())
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 ogrConnectionString(self, uri):
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, e:
(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()
def populateSchemas(self):
if self.childCount() != 0:
return
settings = QSettings()
connSettings = '/PostgreSQL/connections/' + self.connection
database = settings.value(connSettings + '/database')
user = settings.value(connSettings + '/username')
host = settings.value(connSettings + '/host')
port = settings.value(connSettings + '/port')
passwd = settings.value(connSettings + '/password')
uri = QgsDataSourceURI()
uri.setConnection(host, str(port), database, user, passwd)
connInfo = uri.connectionInfo()
(success, user, passwd) = QgsCredentials.instance().get(connInfo, None, None)
if success:
QgsCredentials.instance().put(connInfo, user, passwd)
geodb = GeoDB(host, int(port), database, user, passwd)
schemas = geodb.list_schemas()
for oid, name, owner, perms in schemas:
item = QTreeWidgetItem()
item.setText(0, name)
item.setIcon(0, self.schemaIcon)
self.addChild(item)
def diviLogin(self):
QgsMessageLog.logMessage('Fetching token', 'DIVI')
settings = QSettings()
(success, email, password) = QgsCredentials.instance().get(
'Logowanie DIVI', settings.value('%s/email' % CONFIG_NAME, None), None )
if not success:
return
content = self.sendPostRequest('/authenticate', {
'email': email,
'password' : password,
'remember': True
})
try:
data = json.loads(content)
except TypeError:
return
self.token = data['token']
settings.setValue('%s/email' % CONFIG_NAME, email)
settings.setValue('%s/token' % CONFIG_NAME, self.token)
settings.setValue('%s/status' % CONFIG_NAME, data['status'])
settings.setValue('%s/id' % CONFIG_NAME, data['id'])
self.diviLogged.emit(email, self.token)
return self.token
def toMapLayer(self):
from qgis.core import QgsRasterLayer, QgsContrastEnhancement, QgsDataSourceURI, QgsCredentials
rl = QgsRasterLayer(self.gdalUri(), self.name)
if not rl.isValid():
err = rl.error().summary()
uri = QgsDataSourceURI(self.database().uri())
conninfo = uri.connectionInfo(False)
username = uri.username()
password = uri.password()
for i in range(3):
(ok, username, password) = QgsCredentials.instance().get(conninfo, username, password, err)
if ok:
uri.setUsername(username)
uri.setPassword(password)
rl = QgsRasterLayer(self.gdalUri(uri), self.name)
if rl.isValid():
break
if rl.isValid():
rl.setContrastEnhancement(QgsContrastEnhancement.StretchToMinimumMaximum)
return rl
def authenticationRequired(self, reply, authenticator ):
# check if reached mas retry
gw.instance().authenticationRetryCounter += 1
if (gw.instance().authenticationRetryCounter % gw.instance().maxAuthenticationError) == 0:
gw.instance().authenticationRetryCounter = 0 # reset counter
message = self.tr("Autenticazione fallita piu' di %d volte" % gw.instance().maxAuthenticationError)
self.message.emit(message, QgsMessageLog.CRITICAL)
QMessageBox.critical(self, gw.MESSAGELOG_CLASS, message)
# abort continuing request
reply.abort()
self.done.emit(False)
return
# if not authenticated ask credentials
if not gw.instance().autenthicated:
(ok, gw.instance().user, gw.instance().pwd) = QgsCredentials.instance().get("", gw.instance().user, gw.instance().pwd, self.tr("Inserisci User e PWD della tua utenza Geosisma"))
if not ok: # MEANS PRESSED CANCEL
gw.instance().authenticationRetryCounter = 0
reply.abort()
self.done.emit(False)
return
# do authentication
authenticator.setUser(gw.instance().user)
authenticator.setPassword(gw.instance().pwd)
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 open_connection(name):
# look for connection data in QGIS configration
# get connection data
qgisSettings = QSettings()
root = "/PostgreSQL/connections/"+name+"/"
DATABASE_HOST = qgisSettings.value(root+"host", '')
DATABASE_NAME = qgisSettings.value(root+"database", '')
DATABASE_PORT = qgisSettings.value(root+"port", '')
DATABASE_USER = qgisSettings.value(root+"username", '')
DATABASE_PWD = qgisSettings.value(root+"password", '')
SSL_MODE = qgisSettings.value(root+"sslmode", QgsDataSourceURI.SSLdisable)
# get realm of the connection (realm don't have use ry pwd)
# realm is the connectioInfo from QgsDataSourceURI
uri = QgsDataSourceURI()
uri.setConnection(DATABASE_HOST, DATABASE_PORT, DATABASE_NAME, '', '', int(SSL_MODE))
connInfo = uri.connectionInfo()
# get credentials if at least there's no PWD
if not DATABASE_PWD:
# get credentials and mutate cache => need lock
QgsCredentials.instance().lock()
(ok, DATABASE_USER, DATABASE_PWD) = QgsCredentials.instance().get(connInfo, DATABASE_USER, DATABASE_PWD)
if not ok:
QgsCredentials.instance().unlock()
message = 'Refused or Can not get credentials for realm: {} '.format(connInfo)
QMessageBox.warning(None, "Connection error", message)
return False
# unlock credentials... but not add to cache
# wait to verify that connection is ok to add into the cache
QgsCredentials.instance().unlock()
# add user and password if not set in the previous setConnection
uri.setConnection(DATABASE_HOST, DATABASE_PORT, DATABASE_NAME, DATABASE_USER, DATABASE_PWD, int(SSL_MODE))
# Try to connect
global conn, cursor
try:
conn = psycopg2.connect(uri.connectionInfo().encode('utf-8'))
cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
except Exception as ex:
message = 'Can not connect to connection named: {} for reason: {} '.format(name, str(ex))
QMessageBox.warning(None, "Connection error", message)
return False
else:
# last credential were ok, so record them in the cache
QgsCredentials.instance().lock()
QgsCredentials.instance().put(connInfo, DATABASE_USER, DATABASE_PWD)
QgsCredentials.instance().unlock()
return True
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 get_layer_source_from_credentials(sslmode_default,
layer_name='v_edit_node'):
""" Get database parameters from layer @layer_name or database connection settings
sslmode_default should be (disable, allow, prefer, require, verify-ca, verify-full)"""
# Get layer @layer_name
layer = tools_qgis.get_layer_by_tablename(layer_name)
# Get database connection settings
settings = QSettings()
settings.beginGroup("PostgreSQL/connections")
if layer is None and settings is None:
not_version = False
tools_log.log_warning(
f"Layer '{layer_name}' is None and settings is None")
global_vars.session_vars[
'last_error'] = f"Layer not found: '{layer_name}'"
return None, not_version
credentials = None
not_version = True
if layer:
not_version = False
credentials = tools_qgis.get_layer_source(layer)
# If sslmode is not defined
sslmode = sslmode_default
if not credentials['sslmode']:
# If service is defined: get sslmode from .pg_service file
if credentials['service']:
tools_log.log_info(f"Getting sslmode from .pg_service file")
credentials_service = tools_os.manage_pg_service(
credentials['service'])
sslmode = credentials_service['sslmode'] if credentials_service[
'sslmode'] else sslmode_default
credentials['sslmode'] = sslmode
global_vars.schema_name = credentials['schema']
conn_info = QgsDataSourceUri(
layer.dataProvider().dataSourceUri()).connectionInfo()
status, credentials = connect_to_database_credentials(
credentials, conn_info)
if not status:
tools_log.log_warning("Error connecting to database (layer)")
global_vars.session_vars['last_error'] = tools_qt.tr(
"Error connecting to database", None, 'ui_message')
return None, not_version
# Put the credentials back (for yourself and the provider), as QGIS removes it when you "get" it
QgsCredentials.instance().put(conn_info, credentials['user'],
credentials['password'])
elif settings:
not_version = True
default_connection = settings.value('selected')
settings.endGroup()
credentials = {
'db': None,
'schema': None,
'table': None,
'service': None,
'host': None,
'port': None,
'user': None,
'password': None,
'sslmode': None
}
if default_connection:
settings.beginGroup(f"PostgreSQL/connections/{default_connection}")
credentials['host'] = settings.value('host')
if settings.value('host') in (None, ""):
credentials['host'] = 'localhost'
credentials['port'] = settings.value('port')
credentials['db'] = settings.value('database')
credentials['user'] = settings.value('username')
credentials['password'] = settings.value('password')
credentials['service'] = settings.value('service')
sslmode_settings = settings.value('sslmode')
try:
sslmode_dict = {0: 'prefer', 1: 'disable', 3: 'require'}
sslmode = sslmode_dict.get(sslmode_settings, sslmode_default)
except ValueError:
sslmode = sslmode_settings
credentials['sslmode'] = sslmode
settings.endGroup()
status, credentials = connect_to_database_credentials(
credentials, max_attempts=0)
if not status:
tools_log.log_warning(
"Error connecting to database (settings)")
global_vars.session_vars['last_error'] = tools_qt.tr(
"Error connecting to database", None, 'ui_message')
return None, not_version
else:
tools_log.log_warning(
"Error getting default connection (settings)")
global_vars.session_vars['last_error'] = tools_qt.tr(
"Error getting default connection", None, 'ui_message')
return None, not_version
global_vars.dao_db_credentials = credentials
return credentials, not_version
def set_database_connection(self, connection=None, crs=None):
""" Create a database connection
"""
# fetch settings
settings_plugin = QSettings()
settings_postgis = QSettings()
settings_plugin.beginGroup('CoGo Plugin')
settings_postgis.beginGroup('PostgreSQL/connections')
self.connection = connection
if not bool(self.connection):
# fetch pre-chosen database connection
self.connection = settings_plugin.value("DatabaseConnection", None)
# check if still exists
if bool(self.connection):
if self.connection not in settings_postgis.childGroups():
settings_plugin.setValue("DatabaseConnection", "")
self.connection = None
# fetch from user if necessary
if not bool(self.connection):
dialog = DatabaseConnectionDialog()
dialog.show()
if bool(dialog.exec_()):
self.connection = dialog.get_database_connection()
if dialog.get_crs():
self.crs = QgsCoordinateReferenceSystem(
dialog.get_crs().get('auth_id'))
settings_plugin.setValue("DatabaseConnection", self.connection)
# validate database connection
if bool(self.connection):
db_service = settings_postgis.value(self.connection + '/service')
db_host = settings_postgis.value(self.connection + '/host')
db_port = settings_postgis.value(self.connection + '/port')
db_name = settings_postgis.value(self.connection + '/database')
db_username = settings_postgis.value(self.connection + '/username')
db_password = settings_postgis.value(self.connection + '/password')
max_attempts = 3
self.uri = QgsDataSourceUri()
self.uri.setConnection(db_host, db_port, db_name, db_username,
db_password)
if db_username and db_password:
for i in range(max_attempts):
error_message = self.connect_to_db(db_service, db_host,
db_port, db_name,
db_username,
db_password)
if error_message:
ok, db_username, db_password = (
QgsCredentials.instance().get(
self.uri.connectionInfo(), db_username,
db_password, error_message))
if not ok:
break
else:
break
else:
msg = "Please enter the username and password."
for i in range(max_attempts):
ok, db_username, db_password = (
QgsCredentials.instance().get(
self.uri.connectionInfo(), db_username,
db_password, msg))
if not ok:
break
error_message = self.connect_to_db(db_service, db_host,
db_port, db_name,
db_username,
db_password)
if not error_message:
break
settings_plugin.endGroup()
settings_postgis.endGroup()
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 + '"'
class OracleDBPlugin(DBPlugin):
@classmethod
def icon(self):
return QIcon(":/db_manager/oracle/icon")
@classmethod
def typeName(self):
return 'oracle'
@classmethod
def typeNameString(self):
return 'Oracle Spatial'
@classmethod
def providerName(self):
return 'oracle'
@classmethod
def connectionSettingsKey(self):
return '/Oracle/connections'
def connectToUri(self, uri):
self.db = self.databasesFactory(self, uri)
if self.db:
return True
return False
def databasesFactory(self, connection, uri):
return ORDatabase(connection, uri)
def connect(self, parent=None):
conn_name = self.connectionName()
settings = QSettings()
settings.beginGroup(u"/{0}/{1}".format(self.connectionSettingsKey(),
conn_name))
if not settings.contains("database"): # non-existent entry?
raise InvalidDataException(
self.tr('There is no defined database connection "{}".'.format(
conn_name)))
from qgis.core import QgsDataSourceURI
uri = QgsDataSourceURI()
settingsList = ["host", "port", "database", "username", "password"]
host, port, database, username, password = map(
lambda x: settings.value(x, "", type=str), settingsList)
# qgis1.5 use 'savePassword' instead of 'save' setting
savedPassword = settings.value("save", False, type=bool) or \
settings.value("savePassword", False, type=bool)
# get all of the connexion options
useEstimatedMetadata = settings.value("estimatedMetadata",
False,
type=bool)
uri.setParam(
'userTablesOnly',
unicode(settings.value("userTablesOnly", False, type=bool)))
uri.setParam(
'geometryColumnsOnly',
unicode(settings.value("geometryColumnsOnly", False, type=bool)))
uri.setParam(
'allowGeometrylessTables',
unicode(settings.value("allowGeometrylessTables", False,
type=bool)))
uri.setParam(
'onlyExistingTypes',
unicode(settings.value("onlyExistingTypes", False, type=bool)))
settings.endGroup()
uri.setConnection(host, port, database, username, password)
uri.setUseEstimatedMetadata(useEstimatedMetadata)
err = u""
try:
return self.connectToUri(uri)
except ConnectionError, e:
err = unicode(e)
# ask for valid credentials
max_attempts = 3
for i in range(max_attempts):
(ok, username,
password) = QgsCredentials.instance().get(uri.connectionInfo(),
username, password, err)
if not ok:
return False
uri.setConnection(host, port, database, username, password)
try:
self.connectToUri(uri)
except ConnectionError, e:
if i == max_attempts - 1: # failed the last attempt
raise e
err = unicode(e)
continue
QgsCredentials.instance().put(uri.connectionInfo(), username,
password)
return True
def get_credentials(self,
host,
port,
database,
username=None,
password=None,
message=None):
"""
Runs a QgsCredentials instance to ask for database credentials.
The credentials are tested in a QgisBackend instance using
test_connection(). Returns \'true\' or \'false\' depending on if the connection
works. cx_Oracle errors will be caught and the error message returned.
If the QgsCredentials dialog is canceled/escaped return \'exit\'.
Parameters
----------
host : str
Oracle database host name
port : str, int
Oracle database port number
database : str
Oracle database service name
username : str or None, optional
Oracle database user (defaults to None)
password : str or None, optional
Oracle database password (defaults to None)
message : str or None, optional
Message to be shown in the credentials dialog, usually the errormessage
from a previous connection attempt
Returns
----------
str
\'true\' if connection valid
\'false\' if connection invalid
\'exit\' if dialog is canceled
qb : QgisBackend object
QgisBackend with the database properties initialized
errorMessage : str
Oracle Database connection error message
"""
uri = QgsDataSourceUri()
# assign this information before you query the QgsCredentials data store
uri.setConnection(host, port, database, username, password)
connInfo = uri.connectionInfo()
(success, user,
passwd) = QgsCredentials.instance().get(connInfo, username, password,
message)
qb = None
errorMessage = None
if success:
try:
qb = qgis_backend.QgisBackend(host=host,
port=port,
database=database,
username=user,
password=passwd)
qb.check_connection()
return 'true', qb, errorMessage
except cx_Oracle.DatabaseError as e:
errorObj, = e.args
errorMessage = errorObj.message
return 'false', qb, errorMessage
else:
return 'exit', qb, errorMessage
def __init__(self, uri):
DBConnector.__init__(self, uri)
self.host = uri.host() or os.environ.get('PGHOST')
self.port = uri.port() or os.environ.get('PGPORT')
username = uri.username() or os.environ.get('PGUSER')
password = uri.password() or os.environ.get('PGPASSWORD')
# Do not get db and user names from the env if service is used
if not uri.service():
if username is None:
username = os.environ.get('USER')
self.dbname = uri.database() or os.environ.get('PGDATABASE') or username
uri.setDatabase(self.dbname)
expandedConnInfo = self._connectionInfo()
try:
self.connection = psycopg2.connect(expandedConnInfo)
except self.connection_error_types() as e:
# get credentials if cached or asking to the user no more than 3 times
err = str(e)
uri = self.uri()
conninfo = uri.connectionInfo(False)
for i in range(3):
(ok, username, password) = QgsCredentials.instance().get(conninfo, username, password, err)
if not ok:
raise ConnectionError(e)
if username:
uri.setUsername(username)
if password:
uri.setPassword(password)
newExpandedConnInfo = uri.connectionInfo(True)
try:
self.connection = psycopg2.connect(newExpandedConnInfo)
QgsCredentials.instance().put(conninfo, username, password)
except self.connection_error_types() as e:
if i == 2:
raise ConnectionError(e)
err = str(e)
finally:
# clear certs for each time trying to connect
self._clearSslTempCertsIfAny(newExpandedConnInfo)
finally:
# clear certs of the first connection try
self._clearSslTempCertsIfAny(expandedConnInfo)
self.connection.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
c = self._execute(None, u"SELECT current_user,current_database()")
self.user, self.dbname = self._fetchone(c)
self._close_cursor(c)
self._checkSpatial()
self._checkRaster()
self._checkGeometryColumnsTable()
self._checkRasterColumnsTable()
def load_work_layers(self):
def check_conn(host, port, database, user, password):
try:
conn = psycopg2.connect(host=host,port=port, database=database, user=user, password=password, connect_timeout=1)
conn.close()
#self.iface.messageBar().pushMessage(self.tr('Povezava uspešna'))
return True
except:
#self.iface.messageBar().pushMessage(self.tr('Povezava neuspešna, napačen uporabnik ali geslo!'))
return False
host = parameters(self)[0]
database = parameters(self)[1]
port = parameters(self)[4]
table = get_work_layers(self)
uri = QgsDataSourceUri()
root = QgsProject.instance().layerTreeRoot()
self.host = parameters(self)[0]
self.database = parameters(self)[1]
self.port = parameters(self)[4]
#Input authentication
authcfg = self.dlg.mAuthConfigSelect.configId()
auth_mgr = QgsApplication.authManager()
auth_cfg = QgsAuthMethodConfig()
auth_mgr.loadAuthenticationConfig(authcfg, auth_cfg, True)
auth = auth_cfg.configMap()
# Input Username, password
user = self.dlg.user_input.text()
password = self.dlg.pass_input.text()
def auth_text(user, password):
authMgr = QgsApplication.authManager()
cfg = QgsAuthMethodConfig()
cfg.setName(user)
cfg.setMethod('Basic')
cfg.setConfig('username', user)
cfg.setConfig('password', password)
authMgr.storeAuthenticationConfig(cfg)
return(cfg)
aut_meth = 0
if user:
check_conn(host, port, database, user, password)
authentication = auth_text(user, password)
aut_meth = 1
elif auth_mgr:
try:
check_conn(host, port, database, auth["username"], auth["password"])
authentication = auth_cfg
except:
pass
else:
self.iface.messageBar().pushMessage(self.tr("Napačen uporabnik ali geslo."), self.tr("Potrdi za javni dostop."), level=Qgis.Critical)
text = self.tr('Uporabljam javni dostop:')
uri.setConnection(host, port, database, None, None)
(success, user, passwd) = QgsCredentials.instance().get(text, parameters(self)[3], parameters(self)[2])
if success:
if check_conn(host, port, database, user, passwd):
user = user
password = passwd
else:
check_conn(host, port, database, user, passwd)
self.iface.messageBar().pushMessage(self.tr('Povezava neuspešna, napačen uporabnik ali geslo!'))
#List of groups and layers
w_layers = [r[1] for r in table.getFeatures()]
groups = [self.tr('Delovni sloji')]
def load_wl(shema, table, geom, sql, fid):
if geom == '':
geom = None
uri.setConnection(self.host, self.port, self.database, "", "", QgsDataSourceUri.SslDisable,"")
uri.setAuthConfigId(authentication.id())
uri.setDataSource(shema, table, geom, sql, fid)
layer=QgsVectorLayer (uri .uri(False), table, "postgres")
return layer
#Attribute form config for layer ZLS Int
def field_to_value_relation(layer):
fields = layer.fields()
pattern = re.compile(r'Vrsta')
fields_vrsta = [field for field in fields if pattern.match(field.name())]
if len(fields_vrsta) > 0:
config = {'AllowMulti': False,
'AllowNull': False,
'FilterExpression': 'Sloj = current_value(\'Sloj\') and \"Opombe\" =\'kategorije\'',
'Key': 'Vrsta',
'Layer': layer.id(),
'NofColumns': 1,
'OrderByValue': False,
'UseCompleter': False,
'Value': 'Vrsta'}
for field in fields_vrsta:
field_idx = fields.indexOf(field.name())
if field_idx >= 0:
try:
widget_setup = QgsEditorWidgetSetup('ValueRelation',config)
layer.setEditorWidgetSetup(field_idx, widget_setup)
except:
pass
else:
return False
else:
return False
return True
#Join fields function
def field_join(t_layer, s_layer, t_field, s_field):
joinObject = QgsVectorLayerJoinInfo()
joinObject.setJoinFieldName(s_field)
joinObject.setTargetFieldName(t_field)
joinObject.setJoinLayerId(s_layer.id())
joinObject.setUsingMemoryCache(True)
joinObject.setJoinLayer(s_layer)
t_layer.addJoin(joinObject)
#Populate list of accessible layers
layers_list = []
for f in table.getFeatures():
if f[3] != 'admin':
print(f[2])
try:
layer = load_wl(f[2], f[1], f[4], "", f[5])
if layer.isValid():
layers_list.append(layer)
except:
continue
if not root.findGroup(self.tr('Delovni sloji')) and len(layers_list) != 0:
w_group = root.addGroup(self.tr('Delovni sloji'))
else:
w_group = root.findGroup(self.tr('Delovni sloji'))
# load layers
for current, layer in enumerate(layers_list):
QgsProject.instance().addMapLayer(layer, False)
w_group.insertChildNode(current, QgsLayerTreeLayer(layer))
myLayerNode = root.findLayer(layer.id())
myLayerNode.setExpanded(False)
if layer.name() == 'ZLS Interpretacija_delovno':
field_to_value_relation(layer)
if aut_meth == 1:
authMgr = QgsApplication.authManager()
authMgr.removeAuthenticationConfig(authentication.id())
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, uri):
DBConnector.__init__(self, uri)
self.host = uri.host() or os.environ.get('PGHOST')
self.port = uri.port() or os.environ.get('PGPORT')
username = uri.username() or os.environ.get('PGUSER')
password = uri.password() or os.environ.get('PGPASSWORD')
# Do not get db and user names from the env if service is used
if uri.service() is None:
if username is None:
username = os.environ.get('USER')
self.dbname = uri.database() or os.environ.get('PGDATABASE') or username
uri.setDatabase(self.dbname)
expandedConnInfo = self._connectionInfo()
try:
self.connection = psycopg2.connect(expandedConnInfo.encode('utf-8'))
except self.connection_error_types() as e:
err = unicode(e)
uri = self.uri()
conninfo = uri.connectionInfo(False)
for i in range(3):
(ok, username, password) = QgsCredentials.instance().get(conninfo, username, password, err)
if not ok:
raise ConnectionError(e)
if username:
uri.setUsername(username)
if password:
uri.setPassword(password)
newExpandedConnInfo = uri.connectionInfo(True)
try:
self.connection = psycopg2.connect(newExpandedConnInfo.encode('utf-8'))
QgsCredentials.instance().put(conninfo, username, password)
except self.connection_error_types() as e:
if i == 2:
raise ConnectionError(e)
err = unicode(e)
finally:
# remove certs (if any) of the expanded connectionInfo
expandedUri = QgsDataSourceUri(newExpandedConnInfo)
sslCertFile = expandedUri.param("sslcert")
if sslCertFile:
sslCertFile = sslCertFile.replace("'", "")
os.remove(sslCertFile)
sslKeyFile = expandedUri.param("sslkey")
if sslKeyFile:
sslKeyFile = sslKeyFile.replace("'", "")
os.remove(sslKeyFile)
sslCAFile = expandedUri.param("sslrootcert")
if sslCAFile:
sslCAFile = sslCAFile.replace("'", "")
os.remove(sslCAFile)
finally:
# remove certs (if any) of the expanded connectionInfo
expandedUri = QgsDataSourceUri(expandedConnInfo)
sslCertFile = expandedUri.param("sslcert")
if sslCertFile:
sslCertFile = sslCertFile.replace("'", "")
os.remove(sslCertFile)
sslKeyFile = expandedUri.param("sslkey")
if sslKeyFile:
sslKeyFile = sslKeyFile.replace("'", "")
os.remove(sslKeyFile)
sslCAFile = expandedUri.param("sslrootcert")
if sslCAFile:
sslCAFile = sslCAFile.replace("'", "")
os.remove(sslCAFile)
self.connection.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
c = self._execute(None, u"SELECT current_user,current_database()")
self.user, self.dbname = self._fetchone(c)
self._close_cursor(c)
self._checkSpatial()
self._checkRaster()
self._checkGeometryColumnsTable()
self._checkRasterColumnsTable()
def __init__(self, uri):
DBConnector.__init__(self, uri)
self.host = uri.host() or os.environ.get('PGHOST')
self.port = uri.port() or os.environ.get('PGPORT')
username = uri.username() or os.environ.get('PGUSER')
password = uri.password() or os.environ.get('PGPASSWORD')
# Do not get db and user names from the env if service is used
if not uri.service():
if username is None:
username = os.environ.get('USER')
self.dbname = uri.database() or os.environ.get(
'PGDATABASE') or username
uri.setDatabase(self.dbname)
expandedConnInfo = self._connectionInfo()
try:
self.connection = psycopg2.connect(expandedConnInfo)
except self.connection_error_types() as e:
# get credentials if cached or asking to the user no more than 3 times
err = str(e)
uri = self.uri()
conninfo = uri.connectionInfo(False)
for i in range(3):
(ok, username, password) = QgsCredentials.instance().get(
conninfo, username, password, err)
if not ok:
raise ConnectionError(e)
if username:
uri.setUsername(username)
if password:
uri.setPassword(password)
newExpandedConnInfo = uri.connectionInfo(True)
try:
self.connection = psycopg2.connect(newExpandedConnInfo)
QgsCredentials.instance().put(conninfo, username, password)
except self.connection_error_types() as e:
if i == 2:
raise ConnectionError(e)
err = str(e)
finally:
# clear certs for each time trying to connect
self._clearSslTempCertsIfAny(newExpandedConnInfo)
finally:
# clear certs of the first connection try
self._clearSslTempCertsIfAny(expandedConnInfo)
self.connection.set_isolation_level(
psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
c = self._execute(None, u"SELECT current_user,current_database()")
self.user, self.dbname = self._fetchone(c)
self._close_cursor(c)
self._checkSpatial()
self._checkRaster()
self._checkGeometryColumnsTable()
self._checkRasterColumnsTable()
class PostGisDBPlugin(DBPlugin):
@classmethod
def icon(self):
return QIcon(":/db_manager/postgis/icon")
@classmethod
def typeName(self):
return 'postgis'
@classmethod
def typeNameString(self):
return 'PostGIS'
@classmethod
def providerName(self):
return 'postgres'
@classmethod
def connectionSettingsKey(self):
return '/PostgreSQL/connections'
def databasesFactory(self, connection, uri):
return PGDatabase(connection, uri)
def connect(self, parent=None):
conn_name = self.connectionName()
settings = QSettings()
settings.beginGroup( u"/%s/%s" % (self.connectionSettingsKey(), conn_name) )
if not settings.contains( "database" ): # non-existent entry?
raise InvalidDataException( self.tr('There is no defined database connection "%s".') % conn_name )
from qgis.core import QgsDataSourceURI
uri = QgsDataSourceURI()
settingsList = ["service", "host", "port", "database", "username", "password"]
service, host, port, database, username, password = map(lambda x: settings.value(x, "", type=str), settingsList)
# qgis1.5 use 'savePassword' instead of 'save' setting
savedPassword = settings.value("save", False, type=bool) or settings.value("savePassword", False, type=bool)
useEstimatedMetadata = settings.value("estimatedMetadata", False, type=bool)
sslmode = settings.value("sslmode", QgsDataSourceURI.SSLprefer, type=int)
settings.endGroup()
if service:
uri.setConnection(service, database, username, password, sslmode)
else:
uri.setConnection(host, port, database, username, password, sslmode)
uri.setUseEstimatedMetadata(useEstimatedMetadata)
err = u""
try:
return self.connectToUri(uri)
except ConnectionError, e:
err = str(e)
# ask for valid credentials
max_attempts = 3
for i in range(max_attempts):
(ok, username, password) = QgsCredentials.instance().get(uri.connectionInfo(), username, password, err)
if not ok:
return False
if service != "":
uri.setConnection(service, database, username, password, sslmode)
else:
uri.setConnection(host, port, database, username, password, sslmode)
try:
self.connectToUri(uri)
except ConnectionError, e:
if i == max_attempts-1: # failed the last attempt
raise e
err = str(e)
continue
QgsCredentials.instance().put(uri.connectionInfo(), username, password)
return True
def connect(self, parent=None):
conn_name = self.connectionName()
settings = QgsSettings()
settings.beginGroup(u"/{0}/{1}".format(self.connectionSettingsKey(),
conn_name))
if not settings.contains("database"): # non-existent entry?
raise InvalidDataException(
self.tr(
'There is no defined database connection "{0}".'.format(
conn_name)))
from qgis.core import QgsDataSourceUri
uri = QgsDataSourceUri()
settingsList = ["host", "port", "database", "username", "password"]
host, port, database, username, password = [
settings.value(x, "", type=str) for x in settingsList
]
# get all of the connexion options
useEstimatedMetadata = settings.value("estimatedMetadata",
False,
type=bool)
uri.setParam('userTablesOnly',
str(settings.value("userTablesOnly", False, type=bool)))
uri.setParam(
'geometryColumnsOnly',
str(settings.value("geometryColumnsOnly", False, type=bool)))
uri.setParam(
'allowGeometrylessTables',
str(settings.value("allowGeometrylessTables", False, type=bool)))
uri.setParam(
'onlyExistingTypes',
str(settings.value("onlyExistingTypes", False, type=bool)))
uri.setParam(
'includeGeoAttributes',
str(settings.value("includeGeoAttributes", False, type=bool)))
settings.endGroup()
uri.setConnection(host, port, database, username, password)
uri.setUseEstimatedMetadata(useEstimatedMetadata)
err = u""
try:
return self.connectToUri(uri)
except ConnectionError as e:
err = str(e)
# ask for valid credentials
max_attempts = 3
for i in range(max_attempts):
(ok, username, password) = QgsCredentials.instance().get(
uri.connectionInfo(False), username, password, err)
if not ok:
return False
uri.setConnection(host, port, database, username, password)
try:
self.connectToUri(uri)
except ConnectionError as e:
if i == max_attempts - 1: # failed the last attempt
raise e
err = str(e)
continue
QgsCredentials.instance().put(uri.connectionInfo(False), username,
password)
return True
return False
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 connect(self, parent=None):
conn_name = self.connectionName()
settings = QSettings()
settings.beginGroup(u"/{0}/{1}".format(
self.connectionSettingsKey(), conn_name))
if not settings.contains("database"): # non-existent entry?
raise InvalidDataException(
self.tr('There is no defined database connection "{}".'.format(
conn_name)))
from qgis.core import QgsDataSourceURI
uri = QgsDataSourceURI()
settingsList = ["host", "port", "database", "username", "password"]
host, port, database, username, password = [settings.value(x, "", type=str) for x in settingsList]
# get all of the connexion options
useEstimatedMetadata = settings.value(
"estimatedMetadata", False, type=bool)
uri.setParam('userTablesOnly', unicode(
settings.value("userTablesOnly", False, type=bool)))
uri.setParam('geometryColumnsOnly', unicode(
settings.value("geometryColumnsOnly", False, type=bool)))
uri.setParam('allowGeometrylessTables', unicode(
settings.value("allowGeometrylessTables", False, type=bool)))
uri.setParam('onlyExistingTypes', unicode(
settings.value("onlyExistingTypes", False, type=bool)))
settings.endGroup()
uri.setConnection(host, port, database, username, password)
uri.setUseEstimatedMetadata(useEstimatedMetadata)
err = u""
try:
return self.connectToUri(uri)
except ConnectionError as e:
err = unicode(e)
# ask for valid credentials
max_attempts = 3
for i in range(max_attempts):
(ok, username, password) = QgsCredentials.instance().get(
uri.connectionInfo(False), username, password, err)
if not ok:
return False
uri.setConnection(host, port, database, username, password)
try:
self.connectToUri(uri)
except ConnectionError as e:
if i == max_attempts - 1: # failed the last attempt
raise e
err = unicode(e)
continue
QgsCredentials.instance().put(
uri.connectionInfo(False), username, password)
return True
return False
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 = GEOM_TYPE_MAP[geometryType] + "?uuid=" + unicode(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")
print uri.uri()
try:
db = postgis_utils.GeoDB(host=uri.host(), port=int(uri.port()),
dbname=uri.database(), user=user, passwd=passwd)
except postgis_utils.DbError as e:
raise GeoAlgorithmExecutionException(
"Couldn't connect to database:\n%s" % e.message)
def _runSQL(sql):
try:
db._exec_sql_and_commit(unicode(sql))
except postgis_utils.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 != QGis.WKBNoGeometry:
_runSQL("SELECT AddGeometryColumn('{schema}', '{table}', 'the_geom', {srid}, '{typmod}', 2)".format(
table=uri.table().lower(), schema=uri.schema(), srid=crs.authid().split(":")[-1],
typmod=GEOM_TYPE_MAP[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):])
print uri.uri()
try:
db = spatialite_utils.GeoDB(uri=uri)
except spatialite_utils.DbError as e:
raise GeoAlgorithmExecutionException(
"Couldn't connect to database:\n%s" % e.message)
def _runSQL(sql):
try:
db._exec_sql_and_commit(unicode(sql))
except spatialite_utils.DbError as e:
raise GeoAlgorithmExecutionException(
'Error creating output Spatialite table:\n%s' % unicode(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 != QGis.WKBNoGeometry:
_runSQL("SELECT AddGeometryColumn('{table}', 'the_geom', {srid}, '{typmod}', 2)".format(
table=uri.table().lower(), srid=crs.authid().split(":")[-1],
typmod=GEOM_TYPE_MAP[geometryType].upper()))
self.layer = QgsVectorLayer(uri.uri(), uri.table(), "spatialite")
self.writer = self.layer.dataProvider()
else:
formats = QgsVectorFileWriter.supportedFiltersAndFormats()
OGRCodes = {}
for (key, value) in formats.items():
extension = unicode(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 == QGis.WKBNoGeometry:
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))
self.writer = QgsVectorFileWriter(self.destination, encoding,
qgsfields, geometryType, crs, OGRCodes[extension])
def __init__(self,
host=None,
port=None,
dbname=None,
user=None,
passwd=None,
service=None,
uri=None):
# Regular expression for identifiers without need to quote them
self.re_ident_ok = re.compile(r"^\w+$")
port = str(port)
if uri:
self.uri = uri
else:
self.uri = QgsDataSourceUri()
if service:
self.uri.setConnection(service, dbname, user, passwd)
else:
self.uri.setConnection(host, port, dbname, user, passwd)
conninfo = self.uri.connectionInfo(False)
err = None
for i in range(4):
expandedConnInfo = self.uri.connectionInfo(True)
try:
self.con = psycopg2.connect(expandedConnInfo)
if err is not None:
QgsCredentials.instance().put(conninfo,
self.uri.username(),
self.uri.password())
break
except psycopg2.OperationalError as e:
if i == 3:
raise QgsProcessingException(str(e))
err = str(e)
user = self.uri.username()
password = self.uri.password()
(ok, user, password) = QgsCredentials.instance().get(
conninfo, user, password, err)
if not ok:
raise QgsProcessingException(
QCoreApplication.translate("PostGIS",
'Action canceled by user'))
if user:
self.uri.setUsername(user)
if password:
self.uri.setPassword(password)
finally:
# remove certs (if any) of the expanded connectionInfo
expandedUri = QgsDataSourceUri(expandedConnInfo)
sslCertFile = expandedUri.param("sslcert")
if sslCertFile:
sslCertFile = sslCertFile.replace("'", "")
os.remove(sslCertFile)
sslKeyFile = expandedUri.param("sslkey")
if sslKeyFile:
sslKeyFile = sslKeyFile.replace("'", "")
os.remove(sslKeyFile)
sslCAFile = expandedUri.param("sslrootcert")
if sslCAFile:
sslCAFile = sslCAFile.replace("'", "")
os.remove(sslCAFile)
self.has_postgis = self.check_postgis()
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):
"""
Constructor of SelvanGeo. References to the
"""
self.conf = yaml.load(
open(
os.path.dirname(os.path.abspath(__file__)) +
"\\selvansgeo.yaml", 'r'))['vars']
# Get reference to the QGIS interface
self.iface = iface
# A reference to our map canvas
self.canvas = self.iface.mapCanvas()
# Get reference to legend interface
self.legendInterface = None #self.iface.legendInterface()
# Get reference to the legend interface
self.layerRegistry = QgsProject.instance()
# Create the GUI Dialog
self.dlg = SelvansGeoDialog()
# Initialize plugin directory
self.plugin_dir = os.path.dirname(__file__)
# Get the QGIS message bar
self.messageBar = self.iface.messageBar()
# Initialize locale
locale = QSettings().value("locale/userLocale")[0:2]
localePath = os.path.join(self.plugin_dir, 'i18n',
'selvansgeo_{}.qm'.format(locale))
# Globals
self.currentRole = "init"
self.credentialInstance = QgsCredentials.instance()
self.readerPwd = self.conf['pg']['password']
# Project paths
if qversion == 3:
self.defaultProjectPath = currentPath + "/qgisprj/" + \
self.conf['default_project_qgis3']
else:
self.defaultProjectPath = currentPath + "/qgisprj/" + \
self.conf['default_project_qgis2']
print(self.defaultProjectPath)
s = QSettings()
self.customProjectPath = s.value("SelvansGeo/customProject",
self.defaultProjectPath)
if self.customProjectPath == "":
self.customProjectPath = self.defaultProjectPath
if os.path.exists(localePath):
self.translator = QTranslator()
self.translator.load(localePath)
if qVersion() > '4.3.3':
QCoreApplication.installTranslator(self.translator)
def __init__(self, uri):
DBConnector.__init__(self, uri)
self.host = uri.host() or os.environ.get('PGHOST')
self.port = uri.port() or os.environ.get('PGPORT')
username = uri.username() or os.environ.get('PGUSER')
password = uri.password() or os.environ.get('PGPASSWORD')
# Do not get db and user names from the env if service is used
if uri.service() is None:
if username is None:
username = os.environ.get('USER')
self.dbname = uri.database() or os.environ.get(
'PGDATABASE') or username
uri.setDatabase(self.dbname)
expandedConnInfo = self._connectionInfo()
try:
self.connection = psycopg2.connect(
expandedConnInfo.encode('utf-8'))
except self.connection_error_types() as e:
err = unicode(e)
uri = self.uri()
conninfo = uri.connectionInfo(False)
for i in range(3):
(ok, username, password) = QgsCredentials.instance().get(
conninfo, username, password, err)
if not ok:
raise ConnectionError(e)
if username:
uri.setUsername(username)
if password:
uri.setPassword(password)
newExpandedConnInfo = uri.connectionInfo(True)
try:
self.connection = psycopg2.connect(
newExpandedConnInfo.encode('utf-8'))
QgsCredentials.instance().put(conninfo, username, password)
except self.connection_error_types() as e:
if i == 2:
raise ConnectionError(e)
err = unicode(e)
finally:
# remove certs (if any) of the expanded connectionInfo
expandedUri = QgsDataSourceUri(newExpandedConnInfo)
sslCertFile = expandedUri.param("sslcert")
if sslCertFile:
sslCertFile = sslCertFile.replace("'", "")
os.remove(sslCertFile)
sslKeyFile = expandedUri.param("sslkey")
if sslKeyFile:
sslKeyFile = sslKeyFile.replace("'", "")
os.remove(sslKeyFile)
sslCAFile = expandedUri.param("sslrootcert")
if sslCAFile:
sslCAFile = sslCAFile.replace("'", "")
os.remove(sslCAFile)
finally:
# remove certs (if any) of the expanded connectionInfo
expandedUri = QgsDataSourceUri(expandedConnInfo)
sslCertFile = expandedUri.param("sslcert")
if sslCertFile:
sslCertFile = sslCertFile.replace("'", "")
os.remove(sslCertFile)
sslKeyFile = expandedUri.param("sslkey")
if sslKeyFile:
sslKeyFile = sslKeyFile.replace("'", "")
os.remove(sslKeyFile)
sslCAFile = expandedUri.param("sslrootcert")
if sslCAFile:
sslCAFile = sslCAFile.replace("'", "")
os.remove(sslCAFile)
self.connection.set_isolation_level(
psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
c = self._execute(None, u"SELECT current_user,current_database()")
self.user, self.dbname = self._fetchone(c)
self._close_cursor(c)
self._checkSpatial()
self._checkRaster()
self._checkGeometryColumnsTable()
self._checkRasterColumnsTable()
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 + '"'
def __init__(self, host=None, port=None, dbname=None, user=None,
passwd=None, service=None, uri=None):
# Regular expression for identifiers without need to quote them
self.re_ident_ok = re.compile(r"^\w+$")
port = str(port)
if uri:
self.uri = uri
else:
self.uri = QgsDataSourceUri()
if service:
self.uri.setConnection(service, dbname, user, passwd)
else:
self.uri.setConnection(host, port, dbname, user, passwd)
conninfo = self.uri.connectionInfo(False)
err = None
for i in range(4):
expandedConnInfo = self.uri.connectionInfo(True)
try:
self.con = psycopg2.connect(expandedConnInfo)
if err is not None:
QgsCredentials.instance().put(conninfo,
self.uri.username(),
self.uri.password())
break
except psycopg2.OperationalError as e:
if i == 3:
raise QgsProcessingException(str(e))
err = str(e)
user = self.uri.username()
password = self.uri.password()
(ok, user, password) = QgsCredentials.instance().get(conninfo,
user,
password,
err)
if not ok:
raise QgsProcessingException(QCoreApplication.translate("PostGIS", 'Action canceled by user'))
if user:
self.uri.setUsername(user)
if password:
self.uri.setPassword(password)
finally:
# remove certs (if any) of the expanded connectionInfo
expandedUri = QgsDataSourceUri(expandedConnInfo)
sslCertFile = expandedUri.param("sslcert")
if sslCertFile:
sslCertFile = sslCertFile.replace("'", "")
os.remove(sslCertFile)
sslKeyFile = expandedUri.param("sslkey")
if sslKeyFile:
sslKeyFile = sslKeyFile.replace("'", "")
os.remove(sslKeyFile)
sslCAFile = expandedUri.param("sslrootcert")
if sslCAFile:
sslCAFile = sslCAFile.replace("'", "")
os.remove(sslCAFile)
self.has_postgis = self.check_postgis()
def connection_from_uri(uri):
username = uri.username() or os.environ.get('PGUSER')
password = uri.password() or os.environ.get('PGPASSWORD')
# Do not get db and user names from the env if service is used
if uri.service() is None:
if username is None:
username = os.environ.get('USER')
dbname = uri.database() or os.environ.get('PGDATABASE') or username
uri.setDatabase(dbname)
expandedConnInfo = QgsDataSourceURI(uri.uri(False)).connectionInfo(True)
try:
connection = psycopg2.connect(expandedConnInfo.encode('utf-8'))
except Exception as e:
err = unicode(e)
uri = QgsDataSourceURI(uri.uri(False))
conninfo = uri.connectionInfo(False)
for i in range(3):
(ok, username, password) = QgsCredentials.instance().get(conninfo, username, password, err)
if not ok:
raise Exception("???") # why would this happen?
if username:
uri.setUsername(username)
if password:
uri.setPassword(password)
newExpandedConnInfo = uri.connectionInfo(True)
try:
connection = psycopg2.connect(newExpandedConnInfo.encode('utf-8'))
QgsCredentials.instance().put(conninfo, username, password)
except Exception as e:
if i == 2:
raise
err = unicode(e)
finally:
# remove certs (if any) of the expanded connectionInfo
expandedUri = QgsDataSourceURI(newExpandedConnInfo)
sslCertFile = expandedUri.param("sslcert")
if sslCertFile:
sslCertFile = sslCertFile.replace("'", "")
os.remove(sslCertFile)
sslKeyFile = expandedUri.param("sslkey")
if sslKeyFile:
sslKeyFile = sslKeyFile.replace("'", "")
os.remove(sslKeyFile)
sslCAFile = expandedUri.param("sslrootcert")
if sslCAFile:
sslCAFile = sslCAFile.replace("'", "")
os.remove(sslCAFile)
finally:
# remove certs (if any) of the expanded connectionInfo
expandedUri = QgsDataSourceURI(expandedConnInfo)
sslCertFile = expandedUri.param("sslcert")
if sslCertFile:
sslCertFile = sslCertFile.replace("'", "")
os.remove(sslCertFile)
sslKeyFile = expandedUri.param("sslkey")
if sslKeyFile:
sslKeyFile = sslKeyFile.replace("'", "")
os.remove(sslKeyFile)
sslCAFile = expandedUri.param("sslrootcert")
if sslCAFile:
sslCAFile = sslCAFile.replace("'", "")
os.remove(sslCAFile)
return connection
