Did not recognize type 'geometry' on Postgresql with PostGIS

[agronholm]

Originally reported by: rah lee (Bitbucket: rajanski, GitHub: rajanski)

---

Hi Am I doing something wrong or does sqlacodegen not work for PostGIS-enabled Postgresql databases

---

• Bitbucket: https://bitbucket.org/agronholm/sqlacodegen/issue/18

[agronholm]

Original comment by Alex Grönholm (Bitbucket: agronholm, GitHub: agronholm):

---

SQLACodegen does not recognize custom types, only built-in ones. It wouldn't know where to import those from anyway.

[agronholm]

Original comment by Alex Grönholm (Bitbucket: agronholm, GitHub: agronholm):

---

Out of curiosity, does it fail on reflection or in the code generation phase?

[agronholm]

Original comment by Neil Albrock (Bitbucket: neilalbrock, GitHub: neilalbrock):

---

I've recently encountered this issue as well. With SQLAlchemy 0.9.9 I get the following:

#!python

/home/atomised/.virtualenvs/importizer/local/lib/python2.7/site-packages/sqlalchemy/dialects/postgresql/base.py:2079: SAWarning: Did not recognize type 'geometry' of column 'geom'
  name, format_type, default, notnull, domains, enums, schema)
/home/atomised/.virtualenvs/importizer/local/lib/python2.7/site-packages/sqlalchemy/dialects/postgresql/base.py:2079: SAWarning: Did not recognize type 'geometry' of column 'extent'
  name, format_type, default, notnull, domains, enums, schema)

With the latest SQLAlchemy 1.0.4 I get this:

#!python

/home/atomised/.virtualenvs/importizer/local/lib/python2.7/site-packages/sqlalchemy/dialects/postgresql/base.py:2421: SAWarning: Did not recognize type 'geometry' of column 'geom
'
  (attype, name))
/home/atomised/.virtualenvs/importizer/local/lib/python2.7/site-packages/sqlalchemy/dialects/postgresql/base.py:2421: SAWarning: Did not recognize type 'geometry' of column 'exte
nt'
  (attype, name))
Traceback (most recent call last):
  File "/home/atomised/.virtualenvs/importizer/bin/sqlacodegen", line 11, in <module>
    sys.exit(main())
  File "/home/atomised/.virtualenvs/importizer/local/lib/python2.7/site-packages/sqlacodegen/main.py", line 40, in main
    generator = CodeGenerator(metadata, args.noindexes, args.noconstraints, args.nojoined, args.noinflect)
  File "/home/atomised/.virtualenvs/importizer/local/lib/python2.7/site-packages/sqlacodegen/codegen.py", line 536, in __init__
    model = ModelClass(table, links[table.name], inflect_engine, not nojoined)
  File "/home/atomised/.virtualenvs/importizer/local/lib/python2.7/site-packages/sqlacodegen/codegen.py", line 300, in __init__
    relationship_ = ManyToOneRelationship(self.name, target_cls, constraint, inflect_engine)
  File "/home/atomised/.virtualenvs/importizer/local/lib/python2.7/site-packages/sqlacodegen/codegen.py", line 412, in __init__
    colname = constraint.columns[0]
  File "/home/atomised/.virtualenvs/importizer/local/lib/python2.7/site-packages/sqlalchemy/util/_collections.py", line 191, in __getitem__
    return self._data[key]
KeyError: 0

I hope that helps, it would certainly be good if all Postgres column types could be handled. Let me know if I can offer any assistance.

[agronholm]

Original comment by Alex Grönholm (Bitbucket: agronholm, GitHub: agronholm):

---

SQLAlchemy 1.0 broke sqlacodegen. I'll have to fix that. Although, can you point me to the proper SQLAlchemy types that these should map to?

[agronholm]

Original comment by Alex Grönholm (Bitbucket: agronholm, GitHub: agronholm):

---

Release 1.1.6 fixes compatibility with SQLAlchemy 1.0. But it still doesn't have the PostgreSQL geometry types.

[agronholm]

Original comment by Alex Grönholm (Bitbucket: agronholm, GitHub: agronholm):

---

Has anyone successfully reflected a database with such columns?

[agronholm]

Original comment by Alex Grönholm (Bitbucket: agronholm, GitHub: agronholm):

---

This needs to be fixed upstream so that the geometry column type is recognized. If this conclusion is wrong, reopen the issue.

[agronholm]

Original comment by Francisco Puga (Bitbucket: fpuga, GitHub: fpuga):

---

I also think that will be great to be able to create models with the geometry type ( from geoalchemy2 import Geometry )

I'm using sqlacodegen==1.1.6, SQLAlchemy==1.0.11, GeoAlchemy2==0.2.6. When using this tool a get:

$ sqlacodegen postgresql://...
..../local/lib/python2.7/site-packages/SQLAlchemy-1.0.11-py2.7-linux-x86_64.egg/sqlalchemy/dialects/postgresql/base.py:2505: SAWarning: Did not recognize type 'geometry' of column 'the_geom'

The rest of the models are correctly created but for the 'geometry' columns NullType is used.

#!python

the_geom = Column(NullType, index=True)

It's not a big deal, just search for NullType and replace with the correct values. But it will be useful that sqlacodegen can get the custom types from geoalchemy

[agronholm]

Original comment by Alex Grönholm (Bitbucket: agronholm, GitHub: agronholm):

---

How do you propose I do that when sqlacodegen relies on SQLAlchemy's reflection?

[agronholm]

Original comment by Alex Grönholm (Bitbucket: agronholm, GitHub: agronholm):

---

Answering my own question: just importing geoalchemy2 is enough. Alright, should be a simple enough change. Expect this in 2.0.

[agronholm]

Original comment by Francisco Puga (Bitbucket: fpuga, GitHub: fpuga):

---

I will be monitoring the ticket to test it and give feedback when the patch will be ready.

[agronholm]

Original comment by kwilcox (Bitbucket: kwilcox, GitHub: kwilcox):

---

https://bitbucket.org/agronholm/sqlacodegen/pull-requests/2/adding-in-a-contribpy-file-for-external/diff

[agronholm]

Closing this as it is superseded by #29.