[sqlalchemy] Re: How to load a complex data set into an SA aware object?
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 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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: How to load a complex data set into an SA aware object?
On Feb 18, 2008, at 12:20 PM, jason kirtland wrote:
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')
since its usually the WHERE clause where various craziness goes on,
you can just define that part of the select() as text:
sometable.select().where(any kind of text goes here)
then just map to that. the mapper only cares about the exported
columns.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: How to load a complex data set into an SA aware object?
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)
The only modification of the original query needed is changing the
variable portion to a named bind parameter with the :colon syntax.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
