On 5/8/07, HD Mail <[EMAIL PROTECTED]> wrote:
> Mike Orr wrote:
> > Is it possible to make a mapper class that loads & modifies only some
> > fields in a table rather than all the fields, yet still autoloads the
> > fields rather than having hardcoded column types?
> If this is a batch type process you are undertaking and will be doing
> repeatedly, don't succumb to the temptation of using ORM.
> Your
> performance will suffer.
I noticed the ORM was taking a few seconds longer in my batch job.
Today I converted my web application from Select to ORM so I could add
some methods to the mapped classes, and even tried a relationship with
a backreference (!). That worked fine, but I notice it's doing nested
selects again.
The essential part of my example is:
class Incident <= _incidents select <= _incidents_table
(Adds a calculated column and restricts the rows.)
class Entry <= _entries select <= _entries_table
(Restricts the rows.)
Incident.entries is a 1:many relationship, with backref Entry.incident
_incident_table = Table("Incident", _meta, autoload=True)
_ic = _incident_table.columns
_entry_table = Table("Entry", _meta,
Column("orr_id", Integer, ForeignKey(_ic.orr_id),
autoload=True)
_ec = _entry_table.columns
_mydate_column = func.date( func.coalesce( ... ).label("mydate")
_incidents = select(
[_incident_table, _mydate_column]
_ic.is_public).alias("incidents")
_entries = select([_entry_table], _ec.is_public).alias("entries")
class Incident(object):
@property
def pretty_name(self):
return "%s, %s" % (self.name, self.location)
class Entry(object):
pass
mapper(Entry, _entries)
mapper(Incident, _incidents, properties={
"entries": relation(Entry, backref="incident"),
}
Now here's my query.
def list_entries_in_categories(orr_id, categories, limit):
where = and_(
Entry.c.orr_id = orr_id,
Entry.c.category.in_(*categories),
)
ctx.current.query(Entry).select(where,
order_by=[desc(Entry.c.entry_date)],
limit=limit+1)
return entries
SELECT entries.category AS entries_category, entries.title AS
entries_title, entries.thumb200 AS entries_thumb200, entries.creator
AS entries_creator, entries.doctype AS entries_doctype,
entries.filename AS entries_filename, entries.content AS
entries_content, entries.entry_id AS entries_entry_id,
entries.entry_date AS entries_entry_date, entries.is_public AS
entries_is_public, entries.size AS entries_size, entries.orr_id AS
entries_orr_id
FROM (SELECT `Entry`.entry_id AS entry_id, `Entry`.orr_id AS orr_id,
`Entry`.entry_date AS entry_date, `Entry`.creator AS creator,
`Entry`.title AS title, `Entry`.category AS category, `Entry`.content
AS content, `Entry`.filename AS filename, `Entry`.thumb200 AS
thumb200, `Entry`.doctype AS doctype, `Entry`.size AS size,
`Entry`.is_public AS is_public
FROM `Entry`
WHERE `Entry`.is_public) AS entries
WHERE entries.orr_id = %s AND entries.category IN (%s, %s, %s, %s, %s,
%s, %s, %s, %s, %s) ORDER BY entries.entry_date DESC
LIMIT 11
2007-05-08 17:07:25,916 INFO sqlalchemy.engine.base.Engine.0x..ac
[6153, 1, 2, 3, 4, 6, 7, 8, 9, 10, 11]
I suppose MySQL optimizes the queries and I shouldn't worry about it,
but it's a pity SQLAlchemy doesn't combine the two selects into one
when it compiles the SQL. I'm showing an Entry query because it's
shorter; the Incident table has some three dozen columns that are
duplicated between the main select and the subselect.
I tried the new .filter() method but it seems to add only where
conditions, not order_by/offset/limit, and I got an "unexpected
keyword argument" error on 'order_by'. Maybe it didn't like the fact
that I didn't have a where condition to add? So I went back to
.select() because it just works.
But I'm impressed that the ORM is getting the answer right. That's
been my main reason for hesitating on ORMs, their code is so complex
it's difficult to audit.
--
Mike Orr <[EMAIL PROTECTED]>
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
