How compatible are psycopg and Plone 5 and what should one pay attention to?

Question

I would like to use the Python library psycopg in a Plone 5 project to retrieve the triggers from the database (PostgreSQL 12).

My first naive idea with psycopg in pure Python and psycopg 2 2.9.1:

connection.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
   cur = connection.cursor()
   cur.execute("LISTEN new_Id;")

while True:
    select.select([connection], [], [])
    connection.poll()
    connection.commit()

    while connection.notifies:
        notify = connection.notifies.pop()
        print("Got NOTIFY:", notify.pid, notify.channel, notify.payload)

Therefore I would like to know if this library can be used in Plone 5/Zope 4 and if this is possible what should be considered ?

Answer 1

Take a look at zope.sqlalchemy. It is a wrapper around SQLAlchemy with an integration to the Zope transaction system. For PostgreSQL you still need a database driver (psycopg2 is possible).

Here is a simple example:

from zope.sqlalchemy import register
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker, scoped_session
import transaction

engine = create_engine("postgresql+psycopg2://scott:tiger@localhost/test")

DBSession = scoped_session(sessionmaker(bind=engine))
register(DBSession)

session = DBSession()

result = session.execute("DELETE FROM objects WHERE id=:id", {"id": 2})
row = result.fetchone()

transaction.commit()