Regarding the slowness I was experiencing mapping a select (which
caused a nested SELECT in the SQL), I converted the inner select to a
MySQL VIEW and the speed improved dramatically. The MySQL manual says
it manages a select against a view by merging them into a single
select if it can, but it doesn't seem to do that with nested selects.
"EXPLAIN SELECT ... FROM the_view WHERE ..."
shows it's actually selecting from the underlying table (i.e., it
lists the real table name), while eanwhile,
"EXPLAIN SELECT ... FROM (SELECT ... FROM the_table) AS my_alias WHERE ..."
does two selects rather than merging the SQL into one.
The one problem I discovered is that MySQL does not propagate the
primary key to the view, which causes SQLAlchemy to raise:
<class 'sqlalchemy.exceptions.ArgumentError'>: Could not assemble any
primary key columns for mapped table 'IN_Incident'.
I had to explicitly tell SQLAlchemy which columns are primary keys:
incidents = Table("IN_Incident", meta,
Column("orr_id", Integer, primary_key=True),
autoload=True)
entries = Table("IN_Entry", meta,
Column("entry_id", Integer, primary_key=True),
Column("orr_id", Integer, ForeignKey(incidents.c.orr_id)),
autoload=True)
--
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
-~----------~----~----~----~------~----~------~--~---
