postgresql geometry types

[Роберт Шотланд]

We are beginning to use the PostgreSQL (12) geometric types (initially the 'point' datatype) in our SA model, and was disappointed to find that the sqlalchemy postgresql dialect modules appear not to have these. 

So I wrote a function using schema.CreateColumn to generate the postgresql DDL according to instructions in the info={} dictionary

@compiles(schema.CreateColumn, 'postgresql')

def compile_cc(element, compiler, **kw):

    """

    Replace the type specified in the Column specification with the one supplied in info={'type': 'OTHER TYPE'}

    :param element:     # information about the Column specification

    :param compiler:    # dialect

    :param kw:          # keywords

    :return:            # replacement string

    """

    col = element.element

    if "type" not in col.info:

        return compiler.visit_create_column(element, **kw)  # if no 'type' key in col.info, return unchanged

    else:

        return compiler.visit_create_column(element, **kw).replace(str(col.type), col.info['type'])  # return new type

In my model the column is defined as below:

point_ra_dec = Column(VARCHAR, Computed('point(ra, "dec")'), info={'type': 'point'})

However, alembic appears to be unaware of this, and generates 

ALTER TABLE ephemeris ADD COLUMN point_ra_dec VARCHAR GENERATED ALWAYS AS (point(ra, "dec")) STORED;

What I want, of course is:

ALTER TABLE ephemeris ADD COLUMN point_ra_dec point GENERATED ALWAYS AS (point(ra, "dec")) STORED;

What furtther do I need to do to get this to work?

Oh, and are the postgreSQL geometric types planned for the 1.4 SA release?

Rob

[Mike Bayer]

Hi -

I think you want to be using geoalchemy2:

https://geoalchemy-2.readthedocs.io/en/latest/

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/0a8021ac-e373-4875-a82a-ec065240615fo%40googlegroups.com.