Jorge Vargas wrote:
> 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?
To my knowledge you can't define a primary mapper against a free-form
text SQL statement- without knowing what the columns will be, the mapper
can't set up the attribute mappings in the target class.
There are probably other ways to set this up, but what I've done in the
past for a read-only query mapping is similar to how I've mapped some
views: first, spell out a placeholder Table in SA with all the columns
and types that the custom query returns. Then map against the Table,
but use the hand-written SQL for queries:
baz_table = Table('baz_placeholder', metadata, Column(...), ...)
mapper(Baz, baz_table)
sql = text('SELECT foo, bar FROM baz WHERE root_item = :root')
xyz_filtered_bazzes = \
session.query(Baz).from_statement(sql).params(root='xyz')
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
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
-~----------~----~----~----~------~----~------~--~---
