SQLAlchemy Reflection Using Metaclass with Column Override

Question

I have a set of dynamic database tables (Postgres 9.3 with PostGIS) that I am mapping using a python metaclass:

cls = type(str(tablename), (db.Model,), {'__tablename__':tablename})

where, db.Model is the db object via flask-sqlalchemy and tablename is a bit of unicode. The cls is then added to an application wide dictionary current_app.class_references (using Flask's current_app) to avoid attempts to instantiate the class multiple times.

Each table contains a geometry column, wkb_geometry stored in Well Known Binary. I want to map these to use geoalchemy2 with the final goal of retrieving GeoJSON.

If I was declaring the table a priori, I would use:

class GeoPoly():
    __tablename__ = 'somename'
    wkb_geometry = db.Column(Geometry("POLYGON"))
    #more columns...

Since I am trying to do this dynamically, I need to be able to override the reflection of cls1 with the known type.

Attempts:

1. Define the column explicitly, using the reflection override syntax.

   cls = type(str(tablename), (db.Model,), {'__tablename__':tablename,
       'wkb_geometry':db.Column(Geometry("POLYGON"))})
   

which returns the following on a fresh restart, i.e. the class has not yet been instantiated: InvalidRequestError: Table 'tablename' is already defined for this MetaData instance. Specify 'extend_existing=True' to redefine options and columns on an existing Table object

2. Use mixins with the class defined above (sans tablename):

   cls = type(str(tablename), (GeoPoly, db.Model), {'__tablename__':tablename})
   

Again MetaData issues.

3. Override the column definition attribute after the class is instantiated:

   cls = type(str(tablename), (db.Model,), {'__tablename__':tablename})
   current_app.class_references[tablename] = cls
   cls.wkb_geometry = db.Column(Geometry("POLYGON"))
   

Which results in:

InvalidRequestError: Implicitly combining column tablename.wkb_geometry with column tablename.wkb_geometry under attribute 'wkb_geometry'. Please configure one or more attributes for these same-named columns explicitly.

Is it possible to use the metadata construction to support dynamic reflection **and* *override a column known will be available on all tables?

Answer 1

I'm not sure if I exactly follow what you're doing, but I've overridden reflected columns in the past inside my own __init__ method on a custom metaclass that inherits from DeclarativeMeta. Any time the new base class is used, it checks for a 'wkb_geometry' column name, and replaces it with (a copy of) the one you created.

import sqlalchemy as sa
from sqlalchemy.ext.declarative import DeclarativeMeta, declarative_base

wkb_geometry = db.Column(Geometry("POLYGON"))

class MyMeta(DeclarativeMeta):
    def __init__(cls, clsname, parents, dct):
        for key, val in dct.iteritems():
            if isinstance(sa.Column) and key is 'wkb_geometry':
                dct[key] = wkb_geometry.copy()

MyBase = declarative_base(metaclass=MyMeta)

cls = type(str(tablename), (MyBase,), {'__tablename__':tablename})

This may not exactly work for you, but it's an idea. You probably need to add db.Model to the MyBase tuple, for example.

Answer 2

This is what I use to customize a particular column while relying on autoload for everything else. The code below assumes an existing declarative Base object for a table named my_table. It loads the metadata for all columns but overrides the definition of a column named polygon:

class MyTable(Base):
    __tablename__ = 'my_table'
    __table_args__ = (Column(name='polygon', type=Geometry("POLYGON"),
                      {'autoload':True})

Other arguments to the Table constructor can be provided in the dictionary. Note that the dictionary must appear last in the list!

The SQLAlchemy documentation Using a Hybrid Approach with __table__ provides more details and examples.