I have an Oracle partitioned table created like so: DROP TABLE foos;
CREATE TABLE foos ( bar VARCHAR2(10) ) PARTITION BY HASH (bar) ( PARTITION P0, PARTITION P1, PARTITION P2, PARTITION P3 ); CREATE TABLE hellos ( bar VARCHAR2(10) ); I want to be able to issue deep, complicated queries against it with SQLAlchemy, ideally without changing the query too much to get it working with SQLAlchemy. Here is a contrived example: SELECT fo.foo_bar, fo.hello_bar FROM ( SELECT f.bar foo_bar, h.bar hello_bar FROM foos *PARTITION (P0)* f JOIN hellos h ON f.bar = h.bar WHERE f.bar = 'baz' ) fo; Is there a sqlalchemy customization that can be made to handle this? I basically need to insert the string 'PARTITION (P0)' between the table name and the alias name. `sel.suffix_with` doesn't work as it will suffix the end of the query (after the WHERE clause), unless I use a needless subquery with no clauses. Here is some set up code: from sqlalchemy import Table, Column, String, MetaData, select, text, table metadata = MetaData() foos = Table('foos', metadata, Column('bar', String(10))) hellos = Table('hellos', metadata, Column('bar', String(10))) # To print a regular query without the PARTITION (P0): f = foos.alias('f') h = hellos.alias('h') sel = select([f.c.bar.label('foo_bar'), h.c.bar.label('hello_bar')]) sel = sel.where(f.c.bar == 'baz') sel = sel.select_from(f.join(h, f.c.bar==h.c.bar)) sel = sel.alias('fo') out_sel = select(sel.columns) >>> print out_sel SELECT fo.foo_bar, fo.hello_bar FROM (SELECT f.bar AS foo_bar, h.bar AS hello_bar FROM foos AS f JOIN hellos AS h ON f.bar = h.bar WHERE f.bar = :bar_1) AS fo # I know that I can get this contrived example working like this, but I would rather not and I believe it won't cover all my use cases: *partition_sel = select([foos.c.bar]).suffix_with('PARTITION (P0)')* f = partition_sel.alias('f') h = hellos.alias('h') sel = select([f.c.bar.label('foo_bar'), h.c.bar.label('hello_bar')]) sel = sel.where(f.c.bar == 'baz') sel = sel.select_from(f.join(h, f.c.bar==h.c.bar)) sel = sel.alias('fo') out_sel = select(sel.columns) >>> print out_sel SELECT fo.foo_bar, fo.hello_bar FROM (SELECT f.bar AS foo_bar, h.bar AS hello_bar FROM *(SELECT foos.bar AS bar FROM foos PARTITION (P0) )* AS f JOIN hellos AS h ON f.bar = h.bar WHERE f.bar = :bar_1) AS fo I tried using `table` to insert custom text as mentioned in the SQL Expression Language Tutorial <http://docs.sqlalchemy.org/en/latest/core/tutorial.html#using-more-specific-text-with-table-literal-column-and-column> but it prints it out using quotes: from sqlalchemy import table f = table(*'foos PARTITION (P0)'*, *foos.columns).alias('f') h = hellos.alias('h') sel = select([f.c.bar.label('foo_bar'), h.c.bar.label('hello_bar')]) sel = sel.where(f.c.bar == 'baz') sel = sel.select_from(f.join(h, f.c.bar==h.c.bar)) sel = sel.alias('fo') out_sel = select(sel.columns) >>> print out_sel SELECT fo.foo_bar, fo.hello_bar FROM (SELECT f.bar AS foo_bar, h.bar AS hello_bar FROM "foos PARTITION (P0)" AS f JOIN hellos AS h ON f.bar = h.bar WHERE f.bar = :bar_1) AS fo Perhaps if I could disable the use of quotes just for this one query, it would work. However I wouldn't want to disable quotes engine-wide Thanks and best regards, Matthew -- 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.