cx_Oracle, SqlAlchemy, Flask, specify schema Model

Readme.md

pallets-eco/flask-sqlalchemy#172

SQLAlchemy-sqlschema only works for PostgreSQL. Do NOT use http://sqlalchemy-sqlschema.readthedocs.io/en/latest/#implementation

https://stackoverflow.com/questions/27003515/how-to-specify-postgresql-schema-in-sqlalchemy-column-foreign-key-mixin

Best solution so far: http://docs.sqlalchemy.org/en/latest/core/metadata.html#sqlalchemy.schema.MetaData.params.schema But doesn't handle foreign keys well.

See demo.py for snippets.

demo.py

os.environ['TNS_ADMIN'] = os.path.join( 'oracle_tools', 'network', 'admin')

oracle_connection_string = 'oracle+cx_oracle://{username}:{password}@{tnsname}'.format(
        username='user',
        password='pass',
        tnsname='TNS_SPACE',
    )

oracle_schema = 'user_schema_1'
foreign_key_prefix = oracle_schema + '.'

app.config['SQLALCHEMY_DATABASE_URI'] = oracle_connection_string 


oracle_db_metadata = schema.MetaData(schema=oracle_schema)
db = SQLAlchemy(app, metadata=oracle_db_metadata)

user_groups_table = db.Table(db_table_prefix + 'user_groups', db.Model.metadata,
                             db.Column('...', db.Integer, db.ForeignKey()),
                             db.Column('...', db.Integer, db.ForeignKey(foreign_key_prefix + 'group.id')),
                             # schema=oracle_schema #  Not necessary because of the use of Metadata above
                             )

class User(db.Model):
    __tablename__ = 'user'
    # __table_args__ = {'schema': oracle_schema } # Not necessary because of the use of Metadata above
    user_name = db.Column('USER_NAME', db.String(250))