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
Example #2
0
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 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"])))
Example #4
0
    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
        connector = SpatiaLiteDBConnector(uri)

    return connector
Example #6
0
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
Example #7
0
    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)
Example #8
0
    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)
Example #9
0
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]