def postgis_path_to_uri(path):
"""Convert layer path from QgsBrowserModel to full QgsDataSourceURI.
:param path: The layer path from QgsBrowserModel
:type path: string
:returns: layer uri.
:rtype: QgsDataSourceURI
"""
connection_name = path.split('/')[1]
schema = path.split('/')[2]
table_name = path.split('/')[3]
settings = QSettings()
key = "/PostgreSQL/connections/" + connection_name
service = settings.value(key + "/service")
host = settings.value(key + "/host")
port = settings.value(key + "/port")
if not port:
port = "5432"
db = settings.value(key + "/database")
use_estimated_metadata = settings.value(
key + "/estimatedMetadata", False, type=bool)
sslmode = settings.value(
key + "/sslmode", QgsDataSourceURI.SSLprefer, type=int)
username = ""
password = ""
if settings.value(key + "/saveUsername") == "true":
username = settings.value(key + "/username")
if settings.value(key + "/savePassword") == "true":
password = settings.value(key + "/password")
# Old save setting
if settings.contains(key + "/save"):
username = settings.value(key + "/username")
if settings.value(key + "/save") == "true":
password = settings.value(key + "/password")
uri = QgsDataSourceURI()
if service:
uri.setConnection(service, db, username, password, sslmode)
else:
uri.setConnection(host, port, db, username, password, sslmode)
uri.setUseEstimatedMetadata(use_estimated_metadata)
# Obtain the geometry column name
connector = PostGisDBConnector(uri)
tables = connector.getVectorTables(schema)
tables = [table for table in tables if table[1] == table_name]
if not tables:
return None
table = tables[0]
geom_col = table[8]
uri.setDataSource(schema, table_name, geom_col)
return uri
def postgis_path_to_uri(path):
"""Convert layer path from QgsBrowserModel to full QgsDataSourceURI.
:param path: The layer path from QgsBrowserModel
:type path: string
:returns: layer uri.
:rtype: QgsDataSourceURI
"""
connection_name = path.split('/')[1]
schema = path.split('/')[2]
table_name = path.split('/')[3]
settings = QSettings()
key = "/PostgreSQL/connections/" + connection_name
service = settings.value(key + "/service")
host = settings.value(key + "/host")
port = settings.value(key + "/port")
if not port:
port = "5432"
db = settings.value(key + "/database")
use_estimated_metadata = settings.value(
key + "/estimatedMetadata", False, type=bool)
sslmode = settings.value(
key + "/sslmode", QgsDataSourceURI.SSLprefer, type=int)
username = ""
password = ""
if settings.value(key + "/saveUsername") == "true":
username = settings.value(key + "/username")
if settings.value(key + "/savePassword") == "true":
password = settings.value(key + "/password")
# Old save setting
if settings.contains(key + "/save"):
username = settings.value(key + "/username")
if settings.value(key + "/save") == "true":
password = settings.value(key + "/password")
uri = QgsDataSourceURI()
if service:
uri.setConnection(service, db, username, password, sslmode)
else:
uri.setConnection(host, port, db, username, password, sslmode)
uri.setUseEstimatedMetadata(use_estimated_metadata)
# Obtain the geometry column name
connector = PostGisDBConnector(uri)
tables = connector.getVectorTables(schema)
tables = [table for table in tables if table[1] == table_name]
if not tables:
return None
table = tables[0]
geom_col = table[8]
uri.setDataSource(schema, table_name, geom_col)
return uri
def getConnectorFromUri(connectionParams: Dict[str, str]) -> 'DBConnector':
"""
Set connector property
for the given database type
and parameters
"""
connector = None
uri = QgsDataSourceUri()
if connectionParams['dbType'] == 'postgis':
if connectionParams['host']:
uri.setConnection(
connectionParams['host'],
connectionParams['port'],
connectionParams['dbname'],
connectionParams['user'],
connectionParams['password']
)
if connectionParams['service']:
uri.setConnection(
connectionParams['service'],
connectionParams['dbname'],
connectionParams['user'],
connectionParams['password']
)
if Qgis.QGIS_VERSION_INT >= 31200:
# we need a fake DBPlugin object
# with connectionName and providerName methods
obj = QObject()
obj.connectionName = lambda: 'fake'
obj.providerName = lambda: 'postgres'
connector = PostGisDBConnector(uri, obj)
else:
connector = PostGisDBConnector(uri)
if connectionParams['dbType'] == 'spatialite':
uri.setConnection('', '', connectionParams['dbname'], '', '')
if hasSpatialiteSupport():
from db_manager.db_plugins.spatialite.connector import (
SpatiaLiteDBConnector,
)
# Il y a bug évident ici si il n'y pas le support spatialite, quid de SpatiaLiteDBConnector ?
try:
connector = SpatiaLiteDBConnector(uri)
except ConnectionError as e:
QgsMessageLog.logMessage(
"Erreur lors de la récupération du fichier SQLite : {}".format(str(e)),
'cadastre',
Qgis.Critical)
return connector
def importPostgis(appdef, progress):
progress.setText("Importing into PostGIS")
progress.setProgress(0)
host = appdef["Deploy"]["PostGIS host"]
port = appdef["Deploy"]["PostGIS port"]
username = appdef["Deploy"]["PostGIS username"]
password = appdef["Deploy"]["PostGIS password"]
dbname = appdef["Deploy"]["PostGIS database"]
schema = appdef["Deploy"]["PostGIS schema"]
uri = QgsDataSourceURI()
uri.setConnection(host, port, dbname, username, password)
connector = PostGisDBConnector(uri)
schemas = connector.getSchemas()
schemaExists = schema in [s[1] for s in schemas]
for i, layer in enumerate(appdef["Layers"]):
if layer.method in [METHOD_WFS_POSTGIS, METHOD_WMS_POSTGIS]:
if not schemaExists:
connector.createSchema(schema)
schemaExists = True
tables = connector.getTables(schema=schema)
tablename = safeName(layer.layer.name())
tableExists = tablename in [t[1] for t in tables]
if tableExists:
connector.deleteTable([schema, tablename])
importLayerIntoPostgis(layer.layer, host, port, username, password,
dbname, schema, tablename, appdef["Settings"]["App view CRS"])
progress.setProgress(int(i*100.0/len(appdef["Layers"])))
def fetchDataFromSqlQuery(connection_name, sql):
data = []
header = []
rowCount = 0
# Get URI
status, uri, error_message = getUriFromConnectionName(
connection_name, True)
if not uri or not status:
ok = False
return header, data, rowCount, ok, error_message
try:
connector = PostGisDBConnector(uri)
except Exception:
error_message = tr('Cannot connect to database')
ok = False
return header, data, rowCount, ok, error_message
c = None
ok = True
# print "run query"
try:
c = connector._execute(None, str(sql))
data = []
header = connector._get_cursor_columns(c)
if header is None:
header = []
if len(header) > 0:
data = connector._fetchall(c)
rowCount = c.rowcount
if rowCount == -1:
rowCount = len(data)
except BaseError as e:
ok = False
error_message = e.msg
return header, data, rowCount, ok, error_message
finally:
if c:
c.close()
del c
# Log errors
if not ok:
error_message = tr('Unknown error occurred while fetching data')
return header, data, rowCount, ok, error_message
return header, data, rowCount, ok, error_message
def test_dbnameLessURI(self):
c = PostGisDBConnector(QgsDataSourceUri())
self.assertIsInstance(c, PostGisDBConnector)
uri = c.uri()
# No username was passed, so we expect it to be taken
# from PGUSER or USER environment variables
expected_user = os.environ.get('PGUSER') or os.environ.get('USER')
actual_user = self._getUser(c)
self.assertEqual(actual_user, expected_user)
# No database was passed, so we expect it to be taken
# from PGDATABASE or expected user
expected_db = os.environ.get('PGDATABASE') or expected_user
actual_db = self._getDatabase(c)
self.assertEqual(actual_db, expected_db)
def test_dbnameLessURI(self):
c = PostGisDBConnector(QgsDataSourceUri())
self.assertIsInstance(c, PostGisDBConnector)
uri = c.uri()
# No username was passed, so we expect it to be taken
# from PGUSER or USER environment variables
expected_user = os.environ.get('PGUSER') or os.environ.get('USER')
actual_user = self._getUser(c)
self.assertEqual(actual_user, expected_user)
# No database was passed, so we expect it to be taken
# from PGDATABASE or expected user
expected_db = os.environ.get('PGDATABASE') or expected_user
actual_db = self._getDatabase(c)
self.assertEqual(actual_db, expected_db)
def getConnectorFromUri(connectionParams: Dict[str,str]) -> 'db_manager.db_plugins.DBConnector':
'''
Set connector property
for the given database type
and parameters
'''
connector = None
uri = QgsDataSourceUri()
if connectionParams['dbType'] == 'postgis':
if connectionParams['host']:
uri.setConnection(
connectionParams['host'],
connectionParams['port'],
connectionParams['dbname'],
connectionParams['user'],
connectionParams['password']
)
if connectionParams['service']:
uri.setConnection(
connectionParams['service'],
connectionParams['dbname'],
connectionParams['user'],
connectionParams['password']
)
connector = PostGisDBConnector(uri)
if connectionParams['dbType'] == 'spatialite':
uri.setConnection('', '', connectionParams['dbname'], '', '')
if hasSpatialiteSupport():
from db_manager.db_plugins.spatialite.connector import SpatiaLiteDBConnector
connector = SpatiaLiteDBConnector(uri)
return connector
def getConnectorFromUri(connectionParams: Dict[str, str]) -> 'DBConnector':
"""
Set connector property
for the given database type
and parameters
"""
connector = None
uri = QgsDataSourceUri()
if connectionParams['dbType'] == 'postgis':
if connectionParams['host']:
uri.setConnection(
connectionParams['host'],
connectionParams['port'],
connectionParams['dbname'],
connectionParams['user'],
connectionParams['password']
)
if connectionParams['service']:
uri.setConnection(
connectionParams['service'],
connectionParams['dbname'],
connectionParams['user'],
connectionParams['password']
)
if Qgis.QGIS_VERSION_INT >= 31200:
# we need a fake DBPlugin object
# with connectionName and providerName methods
obj = QObject()
obj.connectionName = lambda: 'fake'
obj.providerName = lambda: 'postgres'
connector = PostGisDBConnector(uri, obj)
else:
connector = PostGisDBConnector(uri)
if connectionParams['dbType'] == 'spatialite':
uri.setConnection('', '', connectionParams['dbname'], '', '')
if hasSpatialiteSupport():
from db_manager.db_plugins.spatialite.connector import SpatiaLiteDBConnector
connector = SpatiaLiteDBConnector(uri)
return connector
def test_dbnameLessURI(self):
obj = QObject() # needs to be kept alive
obj.connectionName = lambda: 'fake'
obj.providerName = lambda: 'postgres'
c = PostGisDBConnector(QgsDataSourceUri(), obj)
self.assertIsInstance(c, PostGisDBConnector)
uri = c.uri()
# No username was passed, so we expect it to be taken
# from PGUSER or USER environment variables
expected_user = os.environ.get('PGUSER') or os.environ.get('USER')
actual_user = self._getUser(c)
self.assertEqual(actual_user, expected_user)
# No database was passed, so we expect it to be taken
# from PGDATABASE or expected user
expected_db = os.environ.get('PGDATABASE') or expected_user
actual_db = self._getDatabase(c)
self.assertEqual(actual_db, expected_db)
def sqlExec(self, sql):
# Execute a SQL query and, return [header, data, rowCount]
connector = PostGisDBConnector(self.uri)
#print "DEBUG dbrequest : sql = " + sql
try:
c = connector._execute(None, unicode(sql))
data = []
header = connector._get_cursor_columns(c)
except:
print "Erreur SQL : " + str(sql) # debug purpose
raise
if header is None:
header = []
if len(header) > 0:
data = connector._fetchall(c)
row_count = c.rowcount
if row_count == -1:
row_count = len(data)
if c:
c.close()
del c
connector.__del__()
return [header, data, row_count]
def fetchDataFromSqlQuery(connection_name, sql):
from db_manager.db_plugins.plugin import BaseError
from db_manager.db_plugins import createDbPlugin
from db_manager.db_plugins.postgis.connector import PostGisDBConnector
header = None
data = []
header = []
rowCount = 0
error_message = None
connection = None
# Create plugin class and try to connect
ok = True
try:
dbpluginclass = createDbPlugin('postgis', connection_name)
connection = dbpluginclass.connect()
except BaseError as e:
# DlgDbError.showError(e, self.dialog)
ok = False
error_message = e.msg
except Exception:
ok = False
error_message = 'Connexion à la base de données impossible'
if not connection:
return [header, data, rowCount, ok, error_message]
db = dbpluginclass.database()
if not db:
ok = False
error_message = 'Impossible de récupérer la base de données depuis la connexion'
return [header, data, rowCount, ok, error_message]
# Get URI
uri = db.uri()
try:
connector = PostGisDBConnector(uri)
except Exception:
error_message = 'Impossible de se connecer à la base de données'
ok = False
return [header, data, rowCount, ok, error_message]
c = None
ok = True
# print "run query"
try:
c = connector._execute(None, str(sql))
data = []
header = connector._get_cursor_columns(c)
if header is None:
header = []
if len(header) > 0:
data = connector._fetchall(c)
rowCount = c.rowcount
if rowCount == -1:
rowCount = len(data)
except BaseError as e:
ok = False
error_message = e.msg
return [header, data, rowCount, ok, error_message]
finally:
if c:
c.close()
del c
# Log errors
if not ok:
error_message = 'Une erreur inconnue lors de la récupération des données'
return [header, data, rowCount, ok, error_message]
print(error_message)
print(sql)
return [header, data, rowCount, ok, error_message]
