On Feb 14, 2008 10:51 AM, jason kirtland <[EMAIL PROTECTED]> wrote:
>
>
> Jorge Vargas wrote:
> > Hi,
> >
> > I'm working on a project where I got several "read only tables" that
> > are dependent on a third-party, I got several vainlla SQL queries to
> > get the data I need of them and I was wondering which will be the best
> > way to load them up into SA. The queries themselfs are quite complex
> > with several inner joins and other nasty SQL, the queries don't change
> > except for 1 paramenter I need to pass in which is the "root item" i'm
> > looking for.
> >
> > I was wondering if there was a way
> > - I could create a class with no Table object that will be populated
> > from the resulting query,
> > - or if I should go with a db view and/or stored procedure, (how will
> > I call that form sa?)
> > - or if I should translate the raw query into SA's sqlexpresions
> > - or should I just bypass SA and do a raw dbapi call?
> >
> > which will be the best way to handle this situation? Keep in mind this
> > data is read-only so the only function I need is getInfo(itemId),
> > which will execute the query and return Table-like object.
>
> If you've already got the complex SQL and it's working for you, might as
> well use it:
>
> query = text('SELECT foo, bar FROM baz WHERE root_item = :root')
> resultset = connection.execute(query, root=123)
>
ok I tried that and it works standalone, but then when I try to map it
to a table,
http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_mapper_selects
it is still asking me for a Table object.
<class 'sqlalchemy.exceptions.InvalidRequestError'>: Could not find
any Table objects in mapped table
I poked into SA's code and found the following:
func text() creates an instance of _TextClause which inherits from
ClauseElement which comes from Object
on the other hand Selectable extends ClauseElement too, so they belong
to different inheritance trees
since Selectable is just a marker class
http://www.sqlalchemy.org/trac/browser/sqlalchemy/trunk/lib/sqlalchemy/sql/expression.py#L1559
I guess making _TextCLause extend Selectable should make this work,
and have no side effects.
Is this correct?
> The only modification of the original query needed is changing the
> variable portion to a named bind parameter with the :colon syntax.
so at the ORM layer, this :colon will turn into a class attribute colon?
or I should do query(Obj).params(colon='foo')
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
