Execute SQL on PostGIS from PyQGIS

Sooner or later a PostGIS workflow needs something the layer API does not offer: create an index, refresh a materialised view, run an UPDATE across ten million rows, or ask a question whose answer is a number rather than a layer. QGIS's provider connection API gives you a direct SQL channel using the credentials the project already holds — no second connection library, no duplicated password.

This recipe belongs to PostGIS & Database Workflows in PyQGIS. It covers getting a connection object, executing statements and reading results, parameterising safely, wrapping work in a transaction, and turning a query into a map layer.

Three ways into the same databaseA vector layer through the postgres provider is for viewing and editing a table. A query layer wraps a SELECT and needs a unique key column declared. The provider connection is a direct SQL channel for statements that return no geometry, for schema changes and for maintenance.Pick the channel that matches the questionvector layerQgsVectorLayerview and edit a tablespatial index usedthe default choicequery layera SELECT as a layerneeds a unique keyread-onlyjoins and aggregatesprovider connectionarbitrary SQLDDL, UPDATE, VACUUMreturns rows, not featuresthis pageall three reuse the stored connectionone set of credentials, managed in one place

Prerequisites

  • QGIS 3.34 LTR (bundled Python 3.12) or newer with the PostgreSQL provider available.
  • A saved PostGIS connection in QGIS, or the connection parameters to build one. See connecting to a PostGIS database.
  • Database privileges appropriate to what you intend to run. A script that creates indexes needs more than one that selects.

Get a connection object

The provider registry hands out connection objects for any saved connection.

from qgis.core import QgsProviderRegistry

metadata = QgsProviderRegistry.instance().providerMetadata("postgres")
print(sorted(metadata.connections().keys()))

connection = metadata.findConnection("survey_db")
if connection is None:
    raise LookupError("no saved connection named 'survey_db'")

Breakdown: connections() returns the saved connections by name — the same list the browser panel shows — so a script can discover what is available rather than hard-coding a URI. findConnection() returns None for an unknown name, and the guard turns that into a clear message rather than an AttributeError three lines later. The connection object carries the stored credentials, including any authentication configuration, so no password appears in the script.

Where no saved connection exists — a headless run on a fresh machine — build one from a URI instead:

from qgis.core import QgsDataSourceUri

uri = QgsDataSourceUri()
uri.setConnection("db.example.org", "5432", "survey", "reader", "")
uri.setAuthConfigId("abc1234")
connection = metadata.createConnection(uri.uri(False), {})

Breakdown: setAuthConfigId() points at an entry in the authentication database rather than embedding a password, which is what keeps credentials out of source control. uri(False) omits the password from the string. createConnection() builds a connection object without saving it into the user's settings, which is the right behaviour for a script that should not modify the user's environment.

Execute a statement

Two methods cover everything: one that returns rows and one that does not.

rows = connection.executeSql(
    "SELECT district, count(*) AS n, round(sum(area_m2)/10000.0, 1) AS ha "
    "FROM survey.parcels GROUP BY district ORDER BY n DESC"
)
for district, count, hectares in rows:
    print(f"{district:<20} {count:>6}  {hectares:>10} ha")

Breakdown: executeSql() returns a list of lists — plain Python values, not features — so aggregate queries, counts and existence checks come back as ordinary data. There is no cursor and no streaming: the whole result is materialised, which is fine for a summary and a problem for a million rows. Column names are not returned, so the unpacking above depends on the SELECT order, and adding a column to the query silently shifts everything.

For statements that return nothing, the same call works and returns an empty list:

connection.executeSql(
    "CREATE INDEX IF NOT EXISTS parcels_geom_idx "
    "ON survey.parcels USING GIST (geom)"
)
connection.executeSql("ANALYZE survey.parcels")

Breakdown: IF NOT EXISTS makes the statement idempotent, which matters for a script that may run repeatedly. ANALYZE after a bulk change is the step people forget: PostgreSQL's planner uses statistics that go stale after a large insert, and a query that was fast yesterday can choose a sequential scan today. Both of these are exactly the sort of maintenance the layer API cannot express.

Parameterise, do not interpolate

String-formatting a value into SQL is an injection risk and, more mundanely, breaks the first time a value contains an apostrophe.

district = "St Mary's"
rows = connection.executeSql(
    "SELECT count(*) FROM survey.parcels WHERE district = %s",
    [district],
)
print(rows[0][0])

Breakdown: The second argument to executeSql() is a list of parameters bound by the driver, using the provider's placeholder syntax — %s for PostgreSQL. The value never becomes part of the SQL text, so quoting, escaping and type conversion are the driver's problem. This is not merely a security nicety in a desktop script: a district named St Mary's breaks a naive f-string immediately, and the resulting error is a syntax error that points at the wrong thing.

Identifiers — table and column names — cannot be parameterised. Where they must be dynamic, validate against a list you control rather than against a pattern:

ALLOWED = {"parcels", "buildings", "roads"}
if table not in ALLOWED:
    raise ValueError(f"refusing to query unknown table {table!r}")

Breakdown: An allowlist is the only reliable defence for identifiers. Escaping them is possible with connection.tableUri() and quoting helpers, but a fixed set is simpler to reason about and covers the realistic cases.

Interpolation versus bindingAn f-string places the value directly into the SQL text, so an apostrophe ends the literal early and the rest of the value is parsed as SQL. A bound parameter is sent separately from the statement, so the driver quotes it correctly and the value can never change the meaning of the query.The apostrophe finds this bug before an attacker doesinterpolatedWHERE district = 'St Mary's'literal ends at the apostrophesyntax error at bestarbitrary SQL at worstthe value became codeboundWHERE district = %svalue sent separatelydriver quotes it correctlytypes converted for youthe value stays a value

Transactions

Several statements that must succeed or fail together belong in a transaction, and the connection object supports one directly.

connection.executeSql("BEGIN")
try:
    connection.executeSql(
        "UPDATE survey.parcels SET status = %s WHERE district = %s",
        ["reviewed", district],
    )
    connection.executeSql(
        "INSERT INTO survey.audit (action, district, at) VALUES (%s, %s, now())",
        ["review", district],
    )
    connection.executeSql("COMMIT")
except Exception:
    connection.executeSql("ROLLBACK")
    raise

Breakdown: Issuing BEGIN, COMMIT and ROLLBACK as statements is the portable route and works on every provider that supports transactions. Re-raising after the rollback keeps the original traceback, which is what tells you which statement failed. The one thing to watch is that the connection object may pool underlying connections, so a long-lived transaction can behave unexpectedly if other code uses the same connection concurrently — keeping transactions short and self-contained avoids the question entirely.

Turn a query into a layer

When the result does have geometry and should appear on the map, a query layer is the route rather than executeSql.

from qgis.core import QgsDataSourceUri, QgsVectorLayer, QgsProject

uri = QgsDataSourceUri(connection.uri())
uri.setDataSource(
    "",
    "(SELECT p.gid, p.geom, d.name AS district_name "
    " FROM survey.parcels p JOIN survey.districts d ON d.id = p.district_id)",
    "geom",
    "",
    "gid",
)
layer = QgsVectorLayer(uri.uri(False), "parcels with district", "postgres")
if not layer.isValid():
    raise RuntimeError("query layer did not load — check the key column and geometry column")
QgsProject.instance().addMapLayer(layer)

Breakdown: The subquery must be wrapped in parentheses and the key column named as the last argument — without a unique integer key the provider cannot identify features and the layer loads empty or misbehaves on selection. Including the geometry column explicitly avoids ambiguity when the join brings in a second one. The layer is read-only, and every pan re-runs the query with a bounding-box filter appended, so a query that is slow without a spatial index will be slow on every redraw; more on this in loading a PostGIS query layer.

QGIS version compatibility

The examples target QGIS 3.34 LTR (Python 3.12).

QGIS versionPythonNotes
3.16 LTR3.7Provider connection API with executeSql available for postgres.
3.22 LTR3.9Bound parameters supported in executeSql for the postgres provider.
3.28 LTR3.9createConnection and findConnection stable on provider metadata.
3.34 LTR3.12Baseline for this page.
3.40+3.12Connection API extended with schema and table introspection helpers.

Troubleshooting

  • findConnection returns None. The saved connection name differs. List metadata.connections().keys().
  • A query fails on some values. The value was interpolated and contained a quote. Bind it as a parameter.
  • The query layer is empty. No usable key column was declared, or the geometry column name is wrong.
  • Permission denied. The connection's role lacks the privilege. DDL needs more than SELECT.
  • A big query exhausts memory. executeSql materialises everything. Aggregate in SQL, or page with LIMIT and OFFSET.
  • A query got slower overnight. Statistics are stale after a bulk change. Run ANALYZE.

Conclusion

Fetch the connection from the provider registry so credentials stay in one place, bind every value instead of interpolating it, allowlist any dynamic identifiers, and wrap multi-statement work in an explicit transaction with a rollback. Use executeSql for numbers and maintenance, and a query layer when the answer belongs on the map.

Frequently Asked Questions

Should I use psycopg2 instead? Only if you need cursors, server-side streaming or COPY. The provider connection reuses QGIS's stored credentials and needs no extra dependency, which for scripts running inside QGIS is a real advantage.

Does this work with GeoPackage? Yes — the same API with providerMetadata("ogr") gives a connection to a GeoPackage, where executeSql runs SQLite SQL including the spatial functions. Placeholder syntax differs by provider.

Can I create a table and then load it as a layer? Yes, and it is a common pattern: run the DDL and the insert through the connection, then build a QgsVectorLayer on the new table. Nothing caches the schema, so the new table is visible immediately.

How do I append features efficiently? For bulk loading, append features to a PostGIS table through the provider rather than generating INSERT statements — the provider batches, and it handles geometry encoding.