On Thu, Nov 30, 2017 at 4:24 PM, Massimiliano della Rovere
<massimiliano.dellarov...@gmail.com> wrote:
> Greetings,
> I'm writing to ask some hints for my problem.
>
> I am using SQLAlchemy Core (no ORM, because everything it is wrapped by
> sqlachemy_aio - everything happens inside a asyncio loop).
>
> The program I'm working on can be deployed with a PostgreSQL or an Oracle
> DB, customer choice, I have no power on this.
> My problem is writing Table(s) definitions whose name and schema change
> depending on the DB that will be used.
> If the table name is "table" and the schema name is "schema" (I mean the
> schema= parameter of sqlalchemy.schema.Table):
> * with PostgreSQL, the table name should be "table" and the schema "schema".
> * with Oracle, the table name should be "schema_table" and there should be
> no schema (the default one for the user logging onto Oracle).
>
> With PostgreSQL, the program uses multiple table grouped in different
> schemas, so different Table instances will be grouped in different schema,
> and there will be inter-schema Foreign Keys.
> Also the solution should be compatible with Alembic, as it is used for
> versioning.
> When the program is deployed, a file contains the information whether the DB
> in use is PostgreSQL or Oracle.
>
> One idea (but I think it won't work with Alembic), is to create a new class
> that inherits from sqlalchemy.schema.Table and overrides the __new__ method:
>
> # python3code
> class CustomTable(Table):
> def __new__(cls, *args, **kw):
> database_type = read_database_type()
> if database_type == "oracle":
> try:
> schema = kw.pop("schema")
> except KeyError:
> pass
> else:
> tablename = f"{schema}_{args[0]}"
> return super().__new__(cls, tablename, *args[1:], **kw)
> return super().__new__(cls, *args, **kw)
>
> It would be nice to create a similar class for Alembic, to customize the
> op.create_table and op.drop_table statement.
> Before writing further code, I'd like to ask whether there are built in
> mechanisms in sqlalchemy to address this problem.
I would use string rewriting at the SQL execution level. Use a
easily-identifiable naming scheme for your schemas and tables such
that you can regular expression at the statement level. like
"__SCHEMA_schemaname" and "__TABLE_tablename", or maybe something less
jarring than that. Then for Oracle you need to regexp for
"__SCHEMA_schemaname\.__TABLE_tablename" and replace with your value,
and on Postgresql you need to just filter out "__SCHEMA_" and
"__TABLE_". The event you'd use is before_cursor_execute:
http://docs.sqlalchemy.org/en/latest/core/events.html?highlight=before_cursor_execute#sqlalchemy.events.ConnectionEvents.before_cursor_execute
using the second form, which includes retval=True.
>
> --
> 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+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
