{"id":1944,"date":"2023-07-06T12:45:18","date_gmt":"2023-07-06T10:45:18","guid":{"rendered":"https:\/\/hannes.enjoys.it\/blog\/?p=1944"},"modified":"2023-07-09T18:15:41","modified_gmt":"2023-07-09T16:15:41","slug":"remotely-controlling-qgis-through-postgresql-queries-%f0%9f%a4%aa","status":"publish","type":"post","link":"https:\/\/hannes.enjoys.it\/blog\/2023\/07\/remotely-controlling-qgis-through-postgresql-queries-%f0%9f%a4%aa\/","title":{"rendered":"Remotely controlling QGIS through PostgreSQL queries \ud83e\udd2a"},"content":{"rendered":"\n<p><em>I just wanted to see if it was possible, it is not a sane thing to do.<\/em><\/p>\n\n\n\n<p>PostgreSQL has a <a href=\"https:\/\/www.postgresql.org\/docs\/current\/sql-notify.html\">notification event system<\/a> where you <code>NOTIFY<\/code> on a notification channel and clients can <code>LISTEN<\/code> for events.<\/p>\n\n\n\n<p>QGIS already supports this for a convenient, remotely triggered refresh function of PostGIS layers by listening on the <code>qgis<\/code> channel if a PostgreSQL connection exists:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Load a PostGIS layer<\/li>\n\n\n\n<li>Set its symbology to something dynamically randomized so you see changes if the map is refreshed<\/li>\n\n\n\n<li>In the layer&#8217;s rendering options set it to refresh on notification<\/li>\n\n\n\n<li>In a PostgreSQL client trigger a notification event, e.g. <code>NOTIFY qgis, 'hi';<\/code> or <code>SELECT pg_notify('qgis', 'refresh plz!');<\/code><\/li>\n\n\n\n<li>The layer is refreshed in QGIS<\/li>\n\n\n\n<li>You can set the layer(s) to refresh on any event on the qgis channel or only for specific messages. For example you could set each PostGIS layer to refresh on a different message, e.g. their table name and use that in a PostgreSQL trigger.<\/li>\n<\/ul>\n\n\n\n<p>This is currently bound to existing PostGIS layers and as far as I know, you cannot do more than refresh layers. <em>Edit<\/em>: <a href=\"https:\/\/oslandia.com\/en\/2017\/10\/07\/refresh-your-maps-from-postgresql\/\">You can also trigger layer actions (thanks to the great people at Oslandia)!<\/a><\/p>\n\n\n\n<p>But&#8230;<\/p>\n\n\n\n<p>QGIS comes with <a href=\"https:\/\/doc.qt.io\/qt-5\/reference-overview.html\">PyQt<\/a> and PyQt (of course) has its own API to let you talk to database servers. And luckily its <a href=\"https:\/\/doc.qt.io\/qt-5\/qsqldriver.html\">QSqlDriver<\/a> class even has a <a href=\"https:\/\/doc.qt.io\/qt-5\/qsqldriver.html#notification-1\">notification<\/a> signal that let&#8217;s you react to notification events in the most convenient way.<\/p>\n\n\n\n<p>So I wrote something:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"python\" class=\"language-python\">from qgis.PyQt.QtSql import QSqlDatabase, QSqlQuery, QSqlDriver\n\n\ndef exec_notification(name, source, payload):\n    \"\"\"ZOMG don't ever do this!\"\"\"\n    try:\n        exec(payload.replace('\\\\n', '\\n'))\n    except SyntaxError as e:\n        iface.messageBar().pushMessage(\"PG NOTIFY\", str(e))\n\ndb = QSqlDatabase.addDatabase(\"QPSQL\")\ndb.setConnectOptions(\"service=foo\")  # using ~\/.pg_service.conf\nif not db.open():\n    raise Exception(db.lastError().databaseText())\ndb.driver().subscribeToNotification(\"qgis_qtsql_channel\")\n\ndb.driver().notification[\n    str, QSqlDriver.NotificationSource, \"QVariant\"\n].connect(exec_notification)<\/code><\/pre>\n\n\n\n<p>Run it in QGIS&#8217; script editor and now you can remotely execute any Python code by sending it from PostgreSQL. For example:<\/p>\n\n\n\n<figure class=\"wp-block-video\"><video height=\"680\" style=\"aspect-ratio: 1920 \/ 680;\" width=\"1920\" controls src=\"https:\/\/hannes.enjoys.it\/blog\/wp-content\/uploads\/x11grab.mkv.mp4\"><\/video><\/figure>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"sql\" class=\"language-sql\">SELECT pg_notify(\n\t'qgis_qtsql_channel',\n\tarray_to_string(\n\t\tarray[\n\t\t\t'project = QgsProject.instance()',\n\t\t\t'project.read(\"map.qgz\")'\n\t\t],\n\t\t'\\n'\n\t)\n)\n\n\nSELECT pg_notify(\n\t'qgis_qtsql_channel',\n\t'iface.mapCanvas().redrawAllLayers()'\n);\n\n\nSELECT pg_notify(\n\t'qgis_qtsql_channel',\n\t'iface.actionExit().trigger()'\n);<\/code><\/pre>\n\n\n\n<p>Of course <strong>this is a horrible idea and you should never run code that a third-party sends to you<\/strong>. Instead you should <strong>write specific methods on the Python side<\/strong> and sent <strong>well defined messages to control<\/strong> exactly which methods get executed. But, hey it works!<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Ideas<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Trigger a notification if a PostgreSQL-stored QGIS project gets updated and in QGIS notify the user if they have said project open.<\/li>\n\n\n\n<li>Make a tiny plugin that lets the user specify a mapping of message -> Python code scripts<\/li>\n\n\n\n<li>TBC<\/li>\n<\/ul>\n\n\n\n<h1 class=\"wp-block-heading\">Addendum<\/h1>\n\n\n\n<p>At first I had used a simple <code>db.driver().notification.connect(on_notification)<\/code>, expecting the slot to simply receive all the arguments that the notification signal would send. But this led to Qt using a different notification signal (just sending the channel name) or something because the name is overloaded. Qt super explainer <a href=\"https:\/\/stackoverflow.com\/users\/6622587\/eyllanesc\">eyllanesc<\/a> once again saved the day by showing that one can (and in this case needs to) <a href=\"https:\/\/stackoverflow.com\/questions\/64837062\/how-to-connect-to-the-qsqldriver-notification-signal\/64837440#64837440\">specify the correct signature<\/a> so Qt picks the correct signal to connect to. This was a major learning for me.<\/p>\n\n\n\n<p>For debugging I used another slot function connected to the signal which uses both the QGIS message log and the message bar to inform the user about incoming notification events, take it if you want:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code lang=\"python\" class=\"language-python\">def on_notification(name, source, payload):\n    message = f\"{name} from {source}: {payload}\"\n    QgsMessageLog.logMessage(message, \"PG NOTIFY\")\n    iface.messageBar().pushMessage(\"PG NOTIFY\", message)<\/code><\/pre>\n\n\n\n<p>A bad surprise to me was that my QGIS installation on Ubuntu 22.04 (using https:\/\/qgis.org\/ubuntu) did not have a dependency graph that included PostgreSQL support in QtSql. Ouch! I would have thought this would be usable in any standard QGIS installation out of the box. I had to install <code>libqt5sql5-psql<\/code>. On Windows this seems to be no issue, both a MSI installer and an advanced OSGeo4W install had the necessary modules. Phew!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I just wanted to see if it was possible, it is not a sane thing to do. PostgreSQL has a notification event system where you NOTIFY on a notification channel and clients can LISTEN for events. QGIS already supports this for a convenient, remotely triggered refresh function of PostGIS layers by listening on the qgis [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[59,49,51,31],"tags":[],"class_list":["post-1944","post","type-post","status-publish","format-standard","hentry","category-funny","category-postgis","category-postgresql","category-qgis"],"_links":{"self":[{"href":"https:\/\/hannes.enjoys.it\/blog\/wp-json\/wp\/v2\/posts\/1944","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/hannes.enjoys.it\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/hannes.enjoys.it\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/hannes.enjoys.it\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/hannes.enjoys.it\/blog\/wp-json\/wp\/v2\/comments?post=1944"}],"version-history":[{"count":12,"href":"https:\/\/hannes.enjoys.it\/blog\/wp-json\/wp\/v2\/posts\/1944\/revisions"}],"predecessor-version":[{"id":1963,"href":"https:\/\/hannes.enjoys.it\/blog\/wp-json\/wp\/v2\/posts\/1944\/revisions\/1963"}],"wp:attachment":[{"href":"https:\/\/hannes.enjoys.it\/blog\/wp-json\/wp\/v2\/media?parent=1944"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/hannes.enjoys.it\/blog\/wp-json\/wp\/v2\/categories?post=1944"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/hannes.enjoys.it\/blog\/wp-json\/wp\/v2\/tags?post=1944"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}