9

Can I retrieve the available connections to PostGIS databases in PyQGIS? I would like to provide a list of available db-connections, and subsequently a list of tables within the ui of my plugin.

I checked the cookbook but cannot find a way to get further with this.

11

To get the information you want, you need to use the QSettings class. This uses a hierarchical structure, like the Windows registry. If you have the latest version of QGIS, you can see this hierarchy using Settings>Options>Advanced

The following code works from the Python Console. I've not tried this from a plugin or outside of QGIS, so some additional work might be required in these cases.

To see the hierarchy, use this in the QGIS python console...

from PyQt4.QtCore import QSettings
qs = QSettings()
for k in sorted(qs.allKeys())
    print k

The output gives some hints...

.. snip ..
Plugins/searchPathsForPlugins
Plugins/valuetool/mouseClick
PostgreSQL/connections/GEODEMO/allowGeometrylessTables
PostgreSQL/connections/GEODEMO/database
PostgreSQL/connections/GEODEMO/dontResolveType
PostgreSQL/connections/GEODEMO/estimatedMetadata    
.. snip ...

So you can get database connection details by filtering for the prefix PostgreSQL/Connections/

So in this case I have a connection called GEODEMO, I can get the username like so...

from PyQt4.QtCore import QSettings
qs = QSettings()
print qs.value("PostgreSQL/connections/GEODEMO/username")
>> steven

Once you have a database in mind, you can retrieve a list of tables using the PostGisDBConnector class.

import db_manager.db_plugins.postgis.connector as con
from qgis.core import QgsDataSourceURI

uri = QgsDataSourceURI()
uri.setConnection("127.0.0.1", "5432", "database_name", "username", "password")
c = con.PostGisDBConnector(uri)
print c
print c.getTables()

Note that the port should be a string, not a number.

  • 1
    Thanks, that works fine for me in a plugin, and basically no adjustments were needed. One thing though... When casting the return value of qs.value("PostgreSQL/connections/GEODEMO/port") to string then the latter uri.setConnection complained about the port! It isn't needed to cast to string. the normal return value as number is good enough. Also, the procedure assumes that user name and password are saved with the database connection. Here it is better to check for 'saveUsername' and 'savePassword' to create the possibility for user input in case one of the return values is 'false'. – Thomas Becker Feb 14 '16 at 20:36
6

My answer will be nearly the same as the previous one but you can avoid to loop all the settings and only obtain PostgreSQL connexions with

from PyQt4.QtCore import QSettings

s = QSettings()
s.beginGroup("PostgreSQL/connections")

print s.allKeys()
print s.value("GEODEMO/username") 

s.endGroup()
  • Yes, that works and answers the question. I needed a more general solution than only PostgreSQL connections, which wasn't retrievable from the initial question. However, good to know about the grouping! – Thomas Becker Feb 14 '16 at 20:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.