Thanks Michael, much appreciated. I realized that this afternoon when I
tried executing the statement and it worked, verified also with my
engine(echo=True).
Great job on the ORM!
On Tuesday, May 15, 2012 1:31:44 PM UTC-7, Michael Bayer wrote:
>
> When you say "print statement", you are not using the oracle dialect, it
> uses a default dialect. To compile against a specific dialect it's like
> query.statement.compile(dialect=oracle.dialect()) where oracle dialect is
> from sqlalchemy.dialects import oracle.
>
> Sent from my iPhone
>
> On May 14, 2012, at 5:33 PM, Karl wrote:
>
> Background: I'm recently new to SQLA but not new to data access
> layers/object relational mapping.
>
> Problem: I'm trying to alias a subquery in sqlachemy in Oracle but the AS
> keyword gets added to the alias, which is not Oracle compatible.
> Oracle 10.02.03
> cx_Oracle 5.0.4
> sqlalchemy: 0.7.5
>
> Given a simple table:
>
> foo = Table('foo', metadata,
> Column('id',
> types.Integer,
> nullable=False,
> primary_key=True),
> Column('name_txt',
> types.String(32),
> nullable=True,
> primary_key=False),
> )
> class Foo(CustomBase):
> __table__ = foo
> __fields__ = ["id", "name"]
> id = foo.c.id
> name = foo.c.name_txt
>
> from sqlalchemy import create_engine
> from sqlalchemy.orm import sessionmaker
> engine =
> create_engine('oracle+cx_oracle://tc:tc@opc',echo=True,use_ansi=False) #
> tried both True and False
> Session = sessionmaker(bind=engine)
> session = Session()
>
> f = session.query(Foo).subquery().alias('f')
> print session.query(f)
> SELECT f.name_txt AS f_name_txt, f.id AS f_id
> FROM (SELECT foo.name_txt AS name_txt, foo.id AS id
> FROM foo) *AS* f
>
> What I need the expression to be is
> SELECT f.name_txt AS f_name_txt, f.id AS f_id
> FROM (SELECT foo.name_txt AS name_txt, foo.id AS id
> FROM foo) f -- note the lack of the AS keyword
>
> I've searched the forums (it's difficult to search on "AS") and tried
> varying the ansi level of the connection and started digging around, the
> closest I could see was a comment in the oracle dialect:
> /usr/share/pyshared/sqlalchemy/dialects/oracle/base.py line 481:
> def visit_alias(self, alias, asfrom=False, ashint=False, **kwargs):
> """Oracle doesn't like ``FROM table AS alias``. Is the AS
> standard SQL??"""
> ...
> and in the class itself:
> print sqlalchemy.sql.expression.alias.__doc__
> #<snip>
> For :func:`.select` objects, the effect is that of creating a named
> subquery, i.e. ``(select ...) AS aliasname``.
> #</snip>
>
> Can anyone suggest where I've strayed? Configuration configuration, bug,
> feature?
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To view this discussion on the web visit
> https://groups.google.com/d/msg/sqlalchemy/-/AVgvNh_iBc8J.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/JjbT8CACHQ8J.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
