[Qgis-user] Doubt with qgis_projects table

[Jürgen E. Fischer]

Hi Jorge,

On Sun, 12. Feb 2023 at 17:18:20 +0000, Jorge Lopez via QGIS-User wrote:
> What I presume is that I need the way that column was generated, Martin
> Dobias created this in the core qgis and maybe he can provide some light on
> this.

The content column contains the qgz file.  To decode and update it you could do
something like this:

from PyQt5.QtCore import QByteArray
from PyQt5.QtSql import QSqlDatabase, QSqlQuery, QSql
from io import BytesIO
from zipfile import ZipFile, ZIP_DEFLATED

db = QSqlDatabase.addDatabase("QPSQL")
db.setConnectOptions("service=…")
db.open()

q = QSqlQuery(db)
q.exec_("SELECT content,metadata,name FROM qgis_projects WHERE name='project-name'")
q.next()

inzip = BytesIO(q.value(0))
outzip = BytesIO()

metadata = q.value(1)
name = q.value(2)

iz = ZipFile(inzip)
oz = ZipFile(outzip, mode='w', compression=ZIP_DEFLATED)

for m in iz.namelist():
    data = iz.read(m)
    if m.endswith(".qgs"):
        data = data.decode('UTF-8')

        # modify projects XML in data

        oz.writestr(m, data.encode("UTF-8"))
    else:
        oz.writestr(m, data)

oz.close()

q.prepare("UPDATE qgis_projects SET content=:content,metadata=:metadata WHERE name=:name")
v = QByteArray(outzip.getvalue())
q.bindValue(":content", v, QSql.In | QSql.Binary)
q.bindValue(":metadata", metadata)
q.bindValue(":name", name)
q.exec_()


Jürgen

-- 
Jürgen E. Fischer           norBIT GmbH             Tel. +49-4931-918175-31
Dipl.-Inf. (FH)             Rheinstraße 13          Fax. +49-4931-918175-50
Software Engineer           D-26506 Norden            https://www.norbit.de
QGIS release manager (PSC)  Germany                 IRC: jef on Libera|OFTC
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 833 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20230212/28182ea0/attachment.sig>