[Qgis-developer] QGIS DB Manager - Execute SQL

Giuseppe Sucameli brush.tyler at gmail.com
Mon Oct 26 10:23:07 PDT 2015

Hi Spencer,
I'm moving the thread to  qgis-dev ML.

Instead of using the postgis_utils.py module,
just import db_manager/db_plugins/postgis/connector.py
and create an instance of PostGisDBConnector.
That class allows you to do everything you need, even run a sql query.

Here's a code snippet acting on the active PostGIS layer:

from db_manager.db_plugins.postgis.connector import PostGisDBConnector

vl = iface.activeLayer()
uri = QgsDataSourceURI( vl.source() )
conn = PostGisDBConnector( uri )

sql = u"SELECT * FROM mytable"
cursor = conn._execute( None, sql )
for row in conn._fetchall( cursor ):
    print row
conn._close_cursor( cursor )

sql = u"UPDATE mytable SET col1='5' "
conn._execute_and_commit( sql )

For more usage examples, have a look at connector.py code,
or just ask ;-)


On Thu, Oct 22, 2015 at 2:09 PM Spencer Gardner <spencergardner at gmail.com>

> I have created a QGIS plugin that adds several tools to the QGIS
> Processing framework. Many of the tools need to communicate with a PostGIS
> database. Up to now, I have accomplished this using a helper utility
> included in the Processing framework. (See this
> <https://lists.osgeo.org/pipermail/qgis-developer/2015-October/039609.html>
> mailing list discussion for more details.)
> I'm now investigating the possibility to switch to using the db_manager
> plugin for all of the database work. I'm testing on my own tools right now,
> although this could eventually replace the utility that is included within
> Processing for all algorithms.
> I'm having some trouble figuring out how to execute a simple SQL query on
> my database and I'm hoping that you can provide an example for me to refer
> to. I can successfully create a layer from a sql query using
> the toSqlLayer() function, but I don't see an easy way to just run a query.
> For example, some of my tools involve running an UPDATE operation on a
> table. How could I accomplish that?

> Thanks for your help,
> Spencer
Giuseppe Sucameli
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20151026/186ca1bf/attachment.html>

More information about the Qgis-developer mailing list