SQLAlchemy, Oracle, and Oracle synonyms

[Shane Ó Conchúir]

Hi all,

I have used SQLAlchemy in the past with MySQL, representing tables with classes and adding relationships. This worked really well for me.

I am now trying to autogenerate SQLAlchemy models for an Oracle database with multiple schemas which uses tables, views, and public synonyms but am new to Oracle and do not know the correct approach here.

The database has:

• tables, typically in non-public schemas hidden from users;
• views ("CREATE VIEW..."),  typically in non-public schemas hidden from users;
• public synonyms (Oracle synonyms: "CREATE PUBLIC SYNONYM...", https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7001.htm) of views, used to expose a subset of the table data to users.

This is how I am modeling the database:

Tables
I create DeclarativeBase subclasses for each table. Relationships and foreign keys are added as usual. The owning schema is added as a class attribute. This seems fine.

Views

Each view is generated with a call to Table(). This seems to be the standard approach (http://docs.sqlalchemy.org/en/latest/core/reflection.html#reflecting-views). Is it the 'best' approach? How does this work with DeclarativeBase? I add the owning schema and the foreign keys to views.

Public synonyms

I am at a loss as to the best way to represent these. Should they be subclasses of DeclarativeBase? Wrappers around views with the schema set to 'public'?

Finally, is this a sensible approach? I did try using metadata and reflection to avoid explicitly writing any models but there seemed to be a startup cost in seconds to that when the script ran and it is also useful to have classes to augment with utility functions.

Any help is welcome!

Regards,

Shane

[Mike Bayer]

it's not clear if you are still working from reflection or if you are
just building out the classes at this point. if you wish for you
application to use the public synonyms for database access, then name
your tables and views using the synonym names as the names, and
disregard the private names. Alternatively, if you are going to
access the private names directly, disregard the synonym names
altogether.

>
> Any help is welcome!
>
>
> Regards,
>
> Shane
>

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Shane Ó Conchúir]

Hi Mike,

Sorry for the delay in response - I have not gotten back to this project yet to test your suggestions out.

I am not working from reflection currently and may not do so for a couple of reasons. I like your idea of renaming the tables - I was autogenerating the models (it is a large schema) but I should be able to rewrite the model generator using a dict mapping synonym names to the underlying objects. I'll let you know how I get on.