MariaDB - Geometry column - model generation

[dedtadeas]

Hi,

I am facing an issue with the generation of the 'declarative' model from MariaDB. I have some tables with geometry columns.
SQL:

CREATE TABLE geofences
(
    geofence_id INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(255) NOT NULL,
    geometry    POLYGON      NOT NULL,
    description TEXT,        
    type        INT          NOT NULL, 
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    color_hex   VARCHAR(255) NOT NULL,
    margin      INT          NOT NULL comment 'margin in meters',
    max_alt     INT          NOT NULL comment 'maximal allowed altitude',
    min_alt     INT          NOT NULL comment 'minimal allowed altitude',
    action      INT          NOT NULL comment 'required action if fence breached'
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

Python code

import subprocess
from connectors.config import *
...
    command = ['sqlacodegen_v2', connection_string, '--outfile', output_file]
    command.extend(['--generator', generator])
    result = subprocess.run(command, capture_output=True, text=True)
...

Created model:

class Geofences(Base):
    __tablename__ = 'geofences'

    geofence_id = mapped_column(INTEGER(11), primary_key=True)
    name = mapped_column(String(255), nullable=False)
    geometry = mapped_column(NullType, nullable=False)
    type = mapped_column(INTEGER(11), nullable=False)
    color_hex = mapped_column(String(255), nullable=False)
    description = mapped_column(MEDIUMTEXT)
    created_at = mapped_column(TIMESTAMP, server_default=text('current_timestamp()'))
    updated_at = mapped_column(TIMESTAMP, server_default=text('current_timestamp() ON UPDATE current_timestamp()'))
    margin = mapped_column(INTEGER(11), comment='margin in meters')
    max_alt = mapped_column(INTEGER(11), comment='maximal allowed altitude')
    min_alt = mapped_column(INTEGER(11), comment='minimal allowed altitude')

    geofence_route_association: Mapped[List['GeofenceRouteAssociation']] = relationship('GeofenceRouteAssociation', uselist=True, back_populates='geofence')

Issue: I need the geometry to be set to "mapped_column(Geometry('POLYGON'), nullable=False)" with import of "from geoalchemy2 import Geometry"

I can hardcode the change into the script, however, this seems to be sqlacodegen_v2 issue.

Thank you for any help!
Best from
T

[agronholm]

Where does this sqlacodegen_v2 come from?

[agronholm]

Could be, and also you're not using the latest release candidate of sqlacodegen like I asked you to either.

[dedtadeas]

Now
running with packages:
sqlalchemy 2.0.21
sqlacodegen 3.0.0rc3

with connection string:
connection_string = f"mariadb+pymysql://{USER}:{PASSWORD}@{IP}:{PORT}/{DATABASE}"

still getting NullType for geometry without geoalchemy import:

class Geofences(Base):
    __tablename__ = 'geofences'

    geofence_id: Mapped[int] = mapped_column(INTEGER(11), primary_key=True)
    name: Mapped[str] = mapped_column(String(255))
    geometry: Mapped[Any] = mapped_column(NullType)
    type: Mapped[int] = mapped_column(INTEGER(11))
    color_hex: Mapped[str] = mapped_column(String(255))
    description: Mapped[Optional[str]] = mapped_column(MEDIUMTEXT)
    created_at: Mapped[Optional[datetime.datetime]] = mapped_column(TIMESTAMP, server_default=text('current_timestamp()'))
    updated_at: Mapped[Optional[datetime.datetime]] = mapped_column(TIMESTAMP, server_default=text('current_timestamp() ON UPDATE current_timestamp()'))
    margin: Mapped[Optional[int]] = mapped_column(INTEGER(11), comment='margin in meters')
    max_alt: Mapped[Optional[int]] = mapped_column(INTEGER(11), comment='maximal allowed altitude')
    min_alt: Mapped[Optional[int]] = mapped_column(INTEGER(11), comment='minimal allowed altitude')

    geofence_route_association: Mapped[List['GeofenceRouteAssociation']] = relationship('GeofenceRouteAssociation', back_populates='geofence')

There change in output after running python script.py:

Models generated successfully!
Using geoalchemy2 0.14.1

Same behavior when running directly from the terminal. Note: I am running the command from venv.

[agronholm]

Are the MariaDB dialect and the PyMySQL driver capable of detecting the geometry columns? If not, then there's nothing I can do on my end.

[dedtadeas]

Thank you for feedback. I don't know. I am not sure I am able to check.
Based on "Using Geoalchemy2.." output, I guess the column type Geometry is somewhat recognized...

[agronholm]

Where is this mentioned?