Qgis Impl Postgis

qgis-impl-postgis

Quick Reference

Core Classes

QgsDataSourceUri Key Methods

Provider Names

Critical Warnings

NEVER store passwords in plain-text URIs in production code. ALWAYS use QgsAuthManager with stored authentication configurations (authcfg). Plain-text passwords leak into logs, project files, and connection strings.

NEVER load PostGIS views without specifying a unique key column -- this causes undefined behavior, duplicate features, and severe performance degradation. ALWAYS pass the key column as the 5th argument to setDataSource().

ALWAYS use uri.uri(False) when passing URIs to QgsVectorLayer or QgsRasterLayer constructors. Passing True (or omitting the argument) expands the authcfg reference and exposes credentials.

ALWAYS use QgsDataSourceUri to construct connection strings. NEVER manually concatenate URI strings -- this causes escaping errors with special characters in passwords, table names, or schema names.

ALWAYS check layer.isValid() immediately after constructing a PostGIS layer. Invalid layers fail silently and produce empty results.

NEVER use estimatedmetadata=true for layers where row count accuracy matters (e.g., feature counting for reports). Estimated metadata skips expensive table scans but returns approximate counts.

Decision Tree

Need to work with PostGIS?
├── Loading a layer?
│   ├── From a table → setDataSource(schema, table, geomCol)
│   ├── From a view → setDataSource(schema, view, geomCol, "", keyCol)  # key required
│   ├── From SQL query → setDataSource("", "(SELECT ...)", geomCol, "", keyCol)
│   └── Raster data → Use "postgresraster" provider with encodeUri()
├── Executing SQL?
│   ├── Non-spatial query → conn.executeSql("SELECT ...")
│   └── Spatial result needed → Load as SQL query layer (above)
├── Discovering schema?
│   ├── List schemas → conn.schemas()
│   └── List tables → conn.tables(schemaName)
├── Exporting to PostGIS?
│   ├── Processing toolbox → processing.run("native:importintopostgis", {...})
│   └── Direct export → QgsVectorFileWriter.writeAsVectorFormatV3()
└── Authentication?
    ├── Development/testing → setConnection() with user/password (temporary only)
    └── Production → setAuthConfigId() with QgsAuthManager config

Essential Patterns

Pattern 1: Connect and Load a Table

from qgis.core import QgsDataSourceUri, QgsVectorLayer

uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "gisdb", "", "")
uri.setAuthConfigId("my_postgis_auth")  # stored auth config
uri.setDataSource("public", "roads", "geom")

layer = QgsVectorLayer(uri.uri(False), "Roads", "postgres")
assert layer.isValid(), f"Layer failed to load: {layer.error().message()}"

QgsProject.instance().addMapLayer(layer)

Pattern 2: Load a View (Key Column Required)

uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "gisdb", "", "")
uri.setAuthConfigId("my_postgis_auth")
# 5th argument = unique key column -- REQUIRED for views
uri.setDataSource("public", "roads_summary_view", "geom", "", "view_id")

layer = QgsVectorLayer(uri.uri(False), "Roads Summary", "postgres")
assert layer.isValid()

Pattern 3: Load a SQL Query as Layer

uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "gisdb", "", "")
uri.setAuthConfigId("my_postgis_auth")

sql = "(SELECT r.gid, r.name, r.geom FROM roads r WHERE r.type = 'highway')"
uri.setDataSource("", sql, "geom", "", "gid")

layer = QgsVectorLayer(uri.uri(False), "Highways", "postgres")
assert layer.isValid()

Pattern 4: Load with SQL Filter and Performance Options

uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "gisdb", "", "")
uri.setAuthConfigId("my_postgis_auth")

# SQL filter as 4th argument to setDataSource
uri.setDataSource("public", "parcels", "geom", "area_sqm > 1000", "gid")
uri.setUseEstimatedMetadata(True)  # faster for large tables
uri.setParam("checkPrimaryKeyUnicity", "0")  # skip uniqueness check

layer = QgsVectorLayer(uri.uri(False), "Large Parcels", "postgres")
assert layer.isValid()

Pattern 5: Execute SQL Queries

from qgis.core import QgsProviderRegistry, QgsDataSourceUri

# Option A: Using a stored connection name
md = QgsProviderRegistry.instance().providerMetadata("postgres")
conn = md.createConnection("My PostGIS Server")  # stored connection name

results = conn.executeSql("SELECT count(*) FROM public.roads")
print(f"Row count: {results[0][0]}")

# Option B: Using a URI
uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "gisdb", "", "")
uri.setAuthConfigId("my_postgis_auth")

conn = md.createConnection(uri.uri(False), {})
results = conn.executeSql("SELECT DISTINCT road_type FROM public.roads ORDER BY road_type")

Pattern 6: Schema and Table Discovery

from qgis.core import QgsProviderRegistry

md = QgsProviderRegistry.instance().providerMetadata("postgres")
conn = md.createConnection("My PostGIS Server")

# List all schemas
schemas = conn.schemas()
for schema in schemas:
    print(f"Schema: {schema}")

# List tables in a schema
tables = conn.tables("public")
for table in tables:
    print(f"Table: {table.tableName()}, "
          f"Geometry: {table.geometryColumn()}, "
          f"Type: {table.geometryColumnTypes()}")

Pattern 7: Export Layer to PostGIS

import processing

# Using the Processing algorithm (recommended)
result = processing.run("native:importintopostgis", {
    'INPUT': source_layer,
    'DATABASE': 'My PostGIS Server',  # stored connection name
    'SCHEMA': 'public',
    'TABLENAME': 'exported_roads',
    'PRIMARY_KEY': 'id',
    'GEOMETRY_COLUMN': 'geom',
    'ENCODING': 'UTF-8',
    'OVERWRITE': True,
    'CREATEINDEX': True,
    'LOWERCASE_NAMES': True,
    'DROP_STRING_LENGTH': False,
    'FORCE_SINGLEPART': False
})

Pattern 8: Export with QgsVectorFileWriter

from qgis.core import QgsVectorFileWriter, QgsCoordinateTransformContext

save_options = QgsVectorFileWriter.SaveVectorOptions()
save_options.driverName = "PostgreSQL"
save_options.layerName = "new_table"

# ALWAYS use authcfg in production instead of plain password
uri_str = "PG:host=localhost port=5432 dbname=gisdb authcfg=my_postgis_auth"
error = QgsVectorFileWriter.writeAsVectorFormatV3(
    layer,
    uri_str,
    QgsCoordinateTransformContext(),
    save_options
)
if error[0] != QgsVectorFileWriter.NoError:
    raise RuntimeError(f"Export failed: {error[1]}")

PostGIS Raster Loading

from qgis.core import QgsProviderRegistry, QgsDataSourceUri, QgsRasterLayer

uri_config = {
    'dbname': 'gisdb',
    'host': 'localhost',
    'port': '5432',
    'sslmode': QgsDataSourceUri.SslDisable,
    'authcfg': 'my_postgis_auth',
    'schema': 'public',
    'table': 'elevation_tiles',
    'geometrycolumn': 'rast',
    'mode': '2'  # 0=one tile per row, 1=one layer per row, 2=union all tiles
}

md = QgsProviderRegistry.instance().providerMetadata('postgresraster')
uri = QgsDataSourceUri(md.encodeUri(uri_config))

rlayer = QgsRasterLayer(uri.uri(False), "Elevation", "postgresraster")
assert rlayer.isValid(), f"Raster layer failed: {rlayer.error().message()}"

QgsProject.instance().addMapLayer(rlayer)

Raster Mode Values

Authentication

Using QgsAuthManager (Production)

from qgis.core import QgsApplication, QgsAuthMethodConfig

# Create a new auth config
auth_mgr = QgsApplication.authManager()
config = QgsAuthMethodConfig()
config.setName("My PostGIS Server")
config.setMethod("Basic")
config.setConfig("username", "db_user")
config.setConfig("password", "db_password")

# Store it -- returns (success, config) with config.id() populated
success, config = auth_mgr.storeAuthenticationConfig(config)
assert success, "Failed to store auth config"
auth_config_id = config.id()  # e.g., "abc123"

# Use in URI
uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "gisdb", "", "")
uri.setAuthConfigId(auth_config_id)
uri.setDataSource("public", "roads", "geom")

layer = QgsVectorLayer(uri.uri(False), "Roads", "postgres")

Retrieving an Existing Auth Config

auth_mgr = QgsApplication.authManager()
config = QgsAuthMethodConfig()
auth_mgr.loadAuthenticationConfig("abc123", config, True)
# config now contains stored credentials

Connection Management

Stored Connections vs URI

Using Stored Connections

from qgis.core import QgsProviderRegistry

md = QgsProviderRegistry.instance().providerMetadata("postgres")

# List all stored connections
connections = md.connections()
for name, conn in connections.items():
    print(f"Connection: {name}")

# Use a stored connection
conn = md.createConnection("My PostGIS Server")
tables = conn.tables("public")

Common Operations

Check if PostGIS Extension is Installed

conn = md.createConnection("My PostGIS Server")
result = conn.executeSql("SELECT PostGIS_Version()")
print(f"PostGIS version: {result[0][0]}")

Create a Spatial Index

conn.executeSql("CREATE INDEX IF NOT EXISTS idx_roads_geom ON public.roads USING GIST (geom)")

Vacuum and Analyze

conn.executeSql("VACUUM ANALYZE public.roads")

Count Features with Filter

uri = QgsDataSourceUri()
uri.setConnection("localhost", "5432", "gisdb", "", "")
uri.setAuthConfigId("my_postgis_auth")
uri.setDataSource("public", "roads", "geom", "road_type = 'highway'")

layer = QgsVectorLayer(uri.uri(False), "Highways", "postgres")
assert layer.isValid()
print(f"Feature count: {layer.featureCount()}")

Reference Links

• references/methods.md -- Complete API signatures for QgsDataSourceUri and database connection classes
• references/examples.md -- Working code examples for all PostGIS operations
• references/anti-patterns.md -- What NOT to do with PostGIS connections

Official Sources

• https://docs.qgis.org/3.34/en/docs/pyqgis_developer_cookbook/loadlayer.html
• https://qgis.org/pyqgis/3.34/core/QgsDataSourceUri.html
• https://qgis.org/pyqgis/3.34/core/QgsAbstractDatabaseProviderConnection.html
• https://qgis.org/pyqgis/3.34/core/QgsAuthManager.html
• https://qgis.org/pyqgis/3.34/core/QgsProviderRegistry.html