How to add a new database connection to QGIS with Python?

Question

I have a plugin for QGIS that helps to populate databases. The user has the option to create a new database or to use an existing connection. If the user chooses to make a new database, I would also like to add this database as a new connection in the browser panel :

I can find lots of help on connecting to the database, which I have been able to do, but nothing for how to add a new connection. Is this possible?

Answer 1

I figured it out myself in the end. The database connection information is stored in PyQt4.QtCore.QSettings().

Using this page: http://pyqt.sourceforge.net/Docs/PyQt4/pyqt_qsettings.html

I was able to add all of the required properties & credentials to the connection list.

Answer 2

Since a few versions of QGIS (at least QGIS 3.16 for this API), there is a proper API for adding a database connection, without touching to the QgsSettings.

uri = QgsDataSourceUri()
uri.setConnection("HOST", "5432", "DATABASE", "USER", "PASSWORD")

config = {
  "saveUsername": True,
  "savePassword": True,
  "estimatedMetadata": True,
  "metadataInDatabase": True,
}

metadata = QgsProviderRegistry.instance().providerMetadata('postgres')
connection = metadata.createConnection(uri.uri(), config)
connection: QgsAbstractDatabaseProviderConnection
connection.store("new name")
# iface.browserModel().reload()

It's possible to use these intermediate variables :

• QgsDataSourceUri
• QgsAbstractDatabaseProviderConnection
• QgsAbstractProviderConnection

For instance, it's possible to have the QgsDataSourceUri from a layer directly.

I'm not sure why some settings need to be a string or a proper boolean, the result is somehow different.

Answer 3

Faced the same case. If someone is looking for a complete solution here is a sample for adding a PostgreSQL:

settings = QSettings()

settings.setValue('PostgreSQL/connections/your_connection_name/host', 'host_name')
settings.setValue('PostgreSQL/connections/your_connection_name/database', 'database_name')
settings.setValue('PostgreSQL/connections/your_connection_name/username', 'user_name')
settings.setValue('PostgreSQL/connections/your_connection_name/password', 'password_name')
settings.setValue('PostgreSQL/connections/your_connection_name/port', port_number)
settings.setValue('PostgreSQL/connections/your_connection_name/allowGeometrylessTables', 'false') 
settings.setValue('PostgreSQL/connections/your_connection_name/authcfg', '')
settings.setValue('PostgreSQL/connections/your_connection_name/dontResolveType', 'false')
settings.setValue('PostgreSQL/connections/your_connection_name/estimatedMetadata', 'false')
settings.setValue('PostgreSQL/connections/your_connection_name/geometryColumnsOnly', 'false')
settings.setValue('PostgreSQL/connections/your_connection_name/projectsInDatabase', 'false')
settings.setValue('PostgreSQL/connections/your_connection_name/publicOnly', 'false')
settings.setValue('PostgreSQL/connections/your_connection_name/savePassword', 'true')
settings.setValue('PostgreSQL/connections/your_connection_name/saveUsername', 'true')
settings.setValue('PostgreSQL/connections/your_connection_name/service', '')
settings.setValue('PostgreSQL/connections/your_connection_name/sslmode', 'SslAllow')

iface.mainWindow().findChildren(QWidget, 'Browser')[0].refresh() # if connection is needed to be displayed immediately in browser

Params after port are from my connection setting and not set by default.