[Qgis-developer] QGIS DB Manager - Execute SQL

[Giuseppe Sucameli]

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 ;-)

Cheers!
Giuseppe

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

> 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>