alias() in Oracle generates incompatible (<subquery>) AS alias_name

[Karl]

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?

[Michael Bayer]

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

--
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 sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Karl]

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

To unsubscribe from this group, send email to sqlalchemy+unsubscribe@googlegroups.com.