Hello -
I'm learning SA at the moment (using 0.4), so I apologize if any
questions go against the grain or assume something silly, I'm looking
for the "SA answer" to how to do the following things. Similarly, I'm
going to throw some possible syntax up, but I don't know the SA style
well enough yet to know if I'm choosing a good fit, so if there's
isn't a pre-existing implementation that it turns out I missed, I'll
happily code up additions with others' input on syntax.
Ok, my learning task (and a real goal of using SA) is to benefit from
the mapper by being able to swap out actual table definitions and
selects as necessary, just changing the mappers and continuing to use
the mapped objects unchanged. I think that's pretty in-line with what
SA is trying to make possible? My test scenario is two sets of data
(draft/preview and live), with two equivalent models (one table with a
boolean flag, or two tables). Here's some stubs to give some context:
class Data(object):
...
class LiveData(object):
...
class PreviewData(object):
...
# single table:
data_table = Table('data', ..., Column('field1', String,
primary_key=True), Column('field2', String), Column('is_live',
Boolean, nullable=False))
mapper(Data, data_table)
mapper(LiveData, select([data_table.c.field1,
data_table.c.field2]).where(data_table.c.is_live==True).alias('live_data'))
mapper(PreviewData, select([data_table.c.field1,
data_table.c.field2]).where(data_table.c.is_live==False).alias('preview_data'))
# OR separate tables:
live_table = Table('live_data', ..., Column('field1', String,
primary_key=True), Column(field2', String))
preview_table = Table('preview_data', ..., Column('field1', String,
primary_key=True), Column(field2', String))
mapper(LiveData, live_table)
mapper(PreviewData, preview_table)
mapper(Data, select([preview_table, literal_column('0',
Boolean).label('is_live')]).union(select([live_data,
literal_column('1', Boolean).label('is_live')]).alias('data')
This has worked really well up to a point, trying to use the UOW/
session concept and only referring to the mapped objects not the
tables/queries, here are the areas where I'm failing:
1. Inserting into mapped selects.
SA seems to be pretty smart here and makes a decent attempt (according
to the docs, inserts into any table whose primary key is involved in
the select, appears to be working fine). However, my simple example
points out two (possibly generic) reasons to need to go beyond that:
a. Further conditions on inserting. In the separate table model, the
mapped select is to a union and based on the constructed column
"is_live" I need the insert to go to one of the base tables. Today,
because both base tables contribute their PKs, the insert of creating
a Data object always goes to both.
b. Implied/constructed values not present in the source objects. In
the single table model, an insert of a LiveData object should add an
is_live=True to the mapped insertion into Data. Today, with
nullable=False on data_table.is_live an insert to LiveData fails with
IntegrityError, with nullable=True is_live is set to NULL.
A naive attempt at syntax for defining these, an "insert_conversion"
argument to mapper of a dictionary of functions keyed by table.
functions would be (source_values_dict) =>
(insert_values_dict_for_this_table) or None. The conditional case is
handled by returning None, open questions would be is the
source_values for all tables or just the table's columns, and whether
arbitrary tables can be given a function in this dictionary or only
those with PKs in the mapped select.
mapper(LiveData, live_query, insert_conversion={data_table: lambda v:
dict(v, is_live=True)})
...
mapper(Data, data_query, insert_conversion={live_table: lambda v: v if
v['is_live'] else None, prevew_table: lambda v: v if not v['is_live']
else None})
(As we go into the second issue, keep in mind that the above problems
for inserts also apply to updates and deletes - I'm not sure whether
the same functions can apply across i/u/d however, or if you'd need to
specify separately. This example scenario can use the same ones, with
some restrictions: funcs cannot assume any column values are included
for update, which does mean updates would still have to be stopped for
ambiguity, and only the boolean return of the function matters for
delete.)
2. Updating/deleting against querysets using the mapped objects
Some of my usecases for this example include making bulk updates/
deletes against sets of data, for instance "make all preview data
live". The combination I'm trying to reach here is both:
a. A set operation, not iterative.
b. Using the mapped objects, ie PreviewData or Data.
(Note that (a) probably conflicts with 1. above as specified, although
not at a syntax level.)
A whole series of things that don't work:
# Selects can't be converted to updates
session.query(Data).filter_by(is_live=False).update(is_live=True) #
OR .update().values(dict(is_live=True))
# session.update is entirely different...
session.update(Data, ..., values=dict(is_live=False))
# ...and I can't construct the restriction without referring to
objects on the wrong side of the mapper anyways
session.execute(update(Data, ...))
# ... and update objects don't have filter_by and Data objects don't
have .update
So if I give up criteria b. above (a sizeable blow), this works:
session.execute(data_table.update(data_table.c.is_live==False,
values=dict(is_live=True)))
However, if I say "ok, the free-to-change-schema primary goal just
means I have to leave the table object names alone - if data is no
longer a base table but a unioned select, i'll just still call it
data_table", then the above code stops working (Alias objects have no
attribute 'update'), and even this fails:
# data_query name still used for clarity
session.execute(update(data_query, data_query.c.is_live==False,
values=dict(is_live=True)))
because it still tries to update the "data" table (the alias name)
rather than deal with the select (unlike the attempted intelligent
update that occurs with session.save()/flush() as in 1. above).
-------
Ok, enough for tonight. Am I way off base? It seems that many/all of
the problems for 1. must be handled internally in some fashion for the
polymorphic behaviors of mapper, but I haven't found my way into that
code yet - does it seem sensible/possible to try to generalize that to
relationships other than subclass/superclass, or to db models that
don't have a string type column? For the iterative problem and the
syntax, perhaps the conversion dict's values could be sql expressions,
although they'd pretty much have to be selects although context is
unclear and now inserts would be different from update/delete. For 2,
another consideration that might have kept this off the table so far
would be managing session/uow state when updating objects that may or
may not be loaded yet.
Appreciate any discussion that comes out of this,
- Luke
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
