Generate SQL string using schema.CreateTable fails with postgresql ARRAY
Question: I'd like to generate the verbatim CREATE TABLE .sql string from a sqlalchemy
class containing a postgresql ARRAY.

The following works fine without the ARRAY column:

    
    
    from sqlalchemy.dialects.postgresql import ARRAY
    from sqlalchemy import *
    from geoalchemy import *
    from sqlalchemy.ext.declarative import declarative_base
    
    metadata=MetaData(schema='refineries')
    
    Base=declarative_base(metadata)
    
    class woodUsers (Base):
        __tablename__='gquery_wood'
        id=Column('id', Integer, primary_key=True)
        name=Column('name', String)
        addr=Column('address', String)
        jsn=Column('json', String)
        geom=GeometryColumn('geom', Point(2))
    

this woks just as i'd like it to:

    
    
    In [1]: from sqlalchemy.schema import CreateTable
    In [3]: tab=woodUsers()
    
    In [4]: str(CreateTable(tab.metadata.tables['gquery_wood']))
    Out[4]: '\nCREATE TABLE gquery_wood (\n\tid INTEGER NOT NULL, \n\tname VARCHAR,      \n\taddress VARCHAR, \n\tjson VARCHAR, \n\tgeom POINT, \n\tPRIMARY KEY (id)\n)\n\n'
    

however when I add a postgresql ARRAY column in it fails:

    
    
    class woodUsers (Base):
        __tablename__='gquery_wood'
        id=Column('id', Integer, primary_key=True)
        name=Column('name', String)
        addr=Column('address', String)
        types=Column('type', ARRAY(String))
        jsn=Column('json', String)
        geom=GeometryColumn('geom', Point(2))
    

the same commands as above result in a long traceback string ending in:

    
    
    /usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.pyc in _compiler_dispatch(self, visitor, **kw)
         70             getter = operator.attrgetter("visit_%s" % visit_name)
         71             def _compiler_dispatch(self, visitor, **kw):
    ---> 72                 return getter(visitor)(self, **kw)
         73         else:
         74             # The optimization opportunity is lost for this case because the
    
    AttributeError: 'GenericTypeCompiler' object has no attribute 'visit_ARRAY'
    

If the full traceback is useful, let me know and I will post.

I think this has to do with specifying a dialect for the compiler (?) but im
not sure. I'd really like to be able to generate the sql without having to
create an engine. I'm not sure if this is possible though, thanks in avance.


Answer: There's probably a complicated solution that involves digging in
sqlalchemy.dialects. You should first try it with an engine though. Fill in a
bogus connection url and just don't call connect().

