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 <karl.do...@gmail.com> 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 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.
