On Wed, Mar 28, 2018 at 7:07 PM, Matthew Moisen <[email protected]> wrote: > 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) > );
this is unfortunate because we have a per-FROM clause "hint" feature that is capable of capturing this, however for the Oracle backend these hints are added as indexing hints using comments above the SELECT (see http://www.dba-oracle.com/t_oracle_index_hint_syntax.htm). we would have to implement parsing of the text passed to select().with_hint() to look for the keyword "PARTITION" to know where we need to put the "hint" in which case, feel free to raise a ticket for this, or if you had the interest, a PR can be generated from the POC I'm adding below. to force quoting on or off for a specific string, use quoted_name: from sqlalchemy.sql.elements import quoted_name t = table(quoted_name("foo partition p0", quote=False)) The other approach would be to produce a @compiles recipe against Select, where in this case I propose just monkeypatching the compiler hooks for these hints, which would also implement essentially how the feature would work for real. See below. The format_from_hint_text allows you to place the hint within the FROM text as needed, and is only invoked if the get_from_hint_text() produces a non-None string. Finally, the get_select_hint_text() method needs to not deliver an index-style hint for a hint that includes the phrase "PARTITION". from sqlalchemy import * from sqlalchemy.ext.compiler import compiles from sqlalchemy.sql.expression import Select @compiles(Select, "oracle") def visit_select(element, compiler, **kw): if getattr(compiler, "_our_monkeypatch", False): return def format_from_hint_text(sqltext, table, hint, iscrud): tokens = sqltext.split(" ", 1) return "%s %s %s" % ( tokens[0], hint, tokens[-1] if len(tokens) > 1 else "" ) def get_from_hint_text(table, text): if text.startswith("PARTITION"): return text else: return None _get_select_hint_text = compiler.get_select_hint_text def get_select_hint_text(byfroms): return _get_select_hint_text( dict( (table, text) for table, text in byfroms.items() if not text.startswith("PARTITION") ) ) compiler.get_select_hint_text = get_select_hint_text compiler.get_from_hint_text = get_from_hint_text compiler.format_from_hint_text = format_from_hint_text compiler._our_monkeypatch = True return compiler.visit_select(element, **kw) if __name__ == '__main__': from sqlalchemy import Table, MetaData, Column, Integer, select from sqlalchemy.dialects import oracle t = Table( 'sometable', MetaData(), Column('a', Integer), Column('b', Integer) ) f = t.alias('f') stmt = select([f]).with_hint(f, "PARTITION (P0)") print(stmt.compile(dialect=oracle.dialect())) stmt = select([t]).with_hint(t, "PARTITION (P0)") print(stmt.compile(dialect=oracle.dialect())) > > 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 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 [email protected]. > To post to this group, send email to [email protected]. > 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
