Create table in one schema, update geometry_columns View in Public schema using GeoAlchemy2

Question

Using the SQLAlchemy ORM with GeoAlchemy2, I can create the table in the public schema and the geometry_columns view gets updated as expected and the created table also includes a gist index on the geom column automatically:

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from geoalchemy2 import Geometry
from sqlalchemy import Column, Integer

engine = create_engine('postgresql://username:pwd@localhost/my_db')

Base = declarative_base()

class Aa19(Base):
    __tablename__ = 'aa19'
    id = Column(Integer, primary_key=True)
    geom = Column(Geometry('POINT', 4326))

Aa19.__table__.create(engine)

But what I want to accomplish is to create a table in one schema, while ensuring the geometry_columns view in the public schema gets updated accordingly. I can't figure this out. I thought by including a variable that sets the search_path maybe I could do this, and then the create_engine() call used above gets replaced like this:

db_schema = 'target_schema,public'
engine = create_engine('postgresql://username:pwd@localhost/my_db', 
                       echo=True,
                       connect_args = {'options':'-csearch_path={}'.format(db_schema)})

But this doesn't work and I get the error message:

ProgrammingError: (psycopg2.ProgrammingError) relation "public.aa19" does not exist

That's fairly obvious and not surprising. I've also tried to set the search path to just my target_schema, hoping that it would automatically update the geometry_columns view in the public schema. But this doesn't work out either and I get the message:

ProgrammingError: (psycopg2.ProgrammingError) type "geometry" does not exist
LINE 4:  geom geometry(POINT,4326)

My question is: How can I create a table with geom column (and gist index) in one schema, AND populate the geometry_columns metadata in the public schema?

I'm new to this technique of interacting with an RDBMS using python. I've gone through the whole ORM tutorial and most of the Core tutorial, as well as having perused the docs. I'm sure the solution is in there somewhere, but I haven't come across it.

Answer 1

Not 100% sure this is what you're after, but executing a basic

from sqlalchemy                 import create_engine
from sqlalchemy.ext.declarative import declarative_base
from geoalchemy2                import Geometry
from sqlalchemy                 import Column, Integer

engine = create_engine( 'postgresql://user:pwd@host/db' )
Base   = declarative_base()

class Test( Base ):

    __tablename__  = 'test'
    __table_args__ = { 'schema': 'other' }

    id   = Column( Integer, primary_key=True )
    geom = Column( Geometry( 'POINT', 4326 ) )

Test.__table__.create( engine )

will set up table test in schema other, with the geom column registered in geometry_columns.

The __table_args__ attribute accepts all arguments normally passed to the Table class constructor; define your schema here. Note that the schema has to be present; AFAIK SQLAlchemy does not provide means to create PostgreSQL Schemas by itself other than executing a DDL as a query.