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 [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.
