[How to use] `updateTableTriggers() error: not existing Table or Column` during `create_all()` with computed column

[nexushoratio]

Describe the problem

I get the following messages when creating a table with a computed point column:

updateTableTriggers() error: not existing Table or Column
updateTableTriggers() error: not existing Table or Column

Everything appears to work properly, but those are scary looking messages and I want to make sure I'm not doing anything wrong. And if I am doing things right, how to suppress or avoid them.

Show what you tried to do.

import os
import platform

import geoalchemy2
import sqlalchemy
from sqlalchemy import orm

@sqlalchemy.event.listens_for(sqlalchemy.engine.Engine, 'connect')
def on_connect(dbapi_connection, _connection_record):
    """Defaults for our connection."""
    dbapi_connection.enable_load_extension(True)
    dbapi_connection.load_extension('mod_spatialite')
    dbapi_connection.enable_load_extension(False)
    cur = dbapi_connection.execute('SELECT CheckSpatialMetaData();')
    if cur.fetchone()[0] < 1:
        dbapi_connection.execute('SELECT InitSpatialMetaData(1);')

engine = sqlalchemy.create_engine(os.getenv('DB_URL'))
conn = engine.connect()

print(platform.freedesktop_os_release()['PRETTY_NAME'])
print(f'{platform.python_version()=}')
print(f'{sqlalchemy.__version__=}')
print(f'{conn.dialect.driver=}')
print(f'{conn.dialect.server_version_info=}')
print(f'{geoalchemy2.__version__=}')
print(f'{geoalchemy2._get_spatialite_version(conn)=}')

Base = orm.declarative_base()

class Test(Base):
    __tablename__ = 'test'

    item = sqlalchemy.Column(sqlalchemy.String, primary_key=True)
    point = sqlalchemy.Column(
        geoalchemy2.Geometry('POINT', srid=4326),
        sqlalchemy.Computed(
            'ST_Point(0.0, 0.0)', persisted=True))

print('\ncreating...')
Base.metadata.create_all(engine)

Describe what you expected.

The updateTableTriggers() messages should no be present.

$ rm test.db; env SQLALCHEMY_WARN_20=1 DB_URL=sqlite:///test.db python t.py
Debian GNU/Linux 12 (bookworm)
platform.python_version()='3.11.2'
sqlalchemy.__version__='1.4.46'
conn.dialect.driver='pysqlite'
conn.dialect.server_version_info=(3, 40, 1)
geoalchemy2.__version__='0.12.5'
geoalchemy2._get_spatialite_version(conn)='5.0.1'

creating...
updateTableTriggers() error: not existing Table or Column
updateTableTriggers() error: not existing Table or Column

If I execute the resulting 'CREATE TABLE` directly via /usr/bin/spatialite, I do not see similar messages.

Error

No response

Additional context

This seems restricted to computed columns. I strongly suspect I am doing something wrong, I just can't figure it out yet.

Using up-to-date Debian/stable (see output for various bits). My preference is not to get involved with things like pip and what not, so trying to go with what comes on the system.

But, also:

$ dpkg -l | grep chemy
ii  python3-geoalchemy2                  0.12.5-1                                  all          SQLAlchemy extension for spatial databases using PostGIS
ii  python3-sqlalchemy                   1.4.46+ds1-1                              all          SQL toolkit and Object Relational Mapper for Python 3
ii  python3-sqlalchemy-ext:amd64         1.4.46+ds1-1+b1                           amd64        SQL toolkit and Object Relational Mapper for Python3 - C extension

GeoAlchemy 2 Version in Use

0.12.5

[adrien-berchet]

Hi @nexushoratio I think I never tried with compute geometry columns before but I can reproduce your issue. As far as I can see, the computed column is properly created (except for MySQL) despite the error messages. I think you can ignore them but it would be nice to support this feature properly in GeoAlchemy2. I will see what I can do but I don't have much time for this atm (maybe @sdp5, @jjgarrett0 or @krishnaglodha ?). And unfortunately it's a bit hard to hide these error messages because they are not sent by python code, so you could use https://github.com/minrk/wurlitzer to capture them for example, or just create a specific context manager based on this: https://eli.thegreenplace.net/2015/redirecting-all-kinds-of-stdout-in-python/

[nexushoratio]

FWIW, in my real (toy) app, I'm keeping the lat,lng string a the primary key and using a computed column to parse the string to create a point. Effectively moving the logic from python to sql.