[sqlalchemy] Re: Mapper bug involving select with labeled foreign key target and clause default?
Thanks for the response Mike... comments below. On Oct 28, 12:52 am, Michael Bayer [EMAIL PROTECTED] wrote: On Oct 27, 2007, at 8:33 PM, Brian Beck wrote: without trying it yet the first thing that seems wrong is that your select has no join condition between a_table and b_table (nor are you using a join(), which would figure it out for you based on foreign keys, so your foreign key doesn't impact the equation much here on the select side). the select will return the cartesian product between a and b which is definitely not what you want. Doesn't matter -- same thing happens with or without the join condition. (The cartesian product was intentional.) the actual error seems that the b_id column is tripping off a refresh of the instance's row, but when it issues your select(), its still not getting back what it wants. since the mapper seems confused by the primary key of the select (which is likely because you have a_id represented twice), you might want to look into setting up your 'the_id_of_a' property at the mapper level as a synonym() or column_property() attribute. I dont see what good it does embedded into that select(). or, try explicitly setting the mapper's primary_key attribute to the desired columns from your select(). Pretend the Select is more complicated and needs to reference both IDs (which may have come from subqueries) -- in this case any mapper features (like synonym) don't help, the Select needs to exist first! Isn't it an error that b_id trips off a refresh? The correct values are inserted and should be available in last_inserted_ids for the mapper to populate instance with... --~--~-~--~~~---~--~~ 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] Mapper bug involving select with labeled foreign key target and clause default?
Sorry for the confusing subject. If I map against a select in which
one of the tables has a foreign key whose default value is set to a
clause involving another table's column (also in the select), and that
column is labeled, the correct values are inserted, but an exception
is raised when accessing the foreign key attribute on the instance
(the instance is in an invalid state).
Minimalist example:
# Boilerplate setup:
engine = create_engine('sqlite:///test.db', echo=True)
metadata = MetaData(bind=engine)
Session = sessionmaker(bind=engine)
session = Session()
# The tables:
a_table = Table('a', metadata,
Column('a_id', Integer, primary_key=True),
Column('a_val', Integer, default=0) # Only here to prevent bare
insert statement
)
# Don't get caught up on why we might want to do this... :)
max_a_id = func.max(a_table.c.a_id).select().as_scalar()
b_table = Table('b', metadata,
Column('b_id', None, ForeignKey(a_table.c.a_id),
primary_key=True, default=max_a_id),
Column('b_val', Integer, default=0) # Only here to prevent bare
insert statement
)
class AB(object):
pass
# Or this!
s = select([
a_table, # Get a_id, a_val...
b_table, # Get b_id, b_val...
a_table.c.a_id.label('the_id_of_a') # Get a_id as the_id_of_a...
]).alias('ab')
ab_mapper = mapper(AB, s)
ab = AB()
session.save(ab)
session.commit()
ab.the_id_of_a == 1
ab.a_id == None # Wrong :(
ab.b_id # Exception! (Traceback at bottom)
Aaand that's all. It might seem a little convoluted but it also seems
like it should work... especially since the expected values are
inserted into the database.
Thoughts?
Traceback:
/Users/exogen/Projects/revision-env/lib/python2.5/SQLAlchemy-0.4.0-
py2.5.egg/sqlalchemy/orm/attributes.py in __get__(self, obj, owner)
38 if obj is None:
39 return self
--- 40 return self.impl.get(obj._state)
41
42 def get_history(self, obj, **kwargs):
/Users/exogen/Projects/revision-env/lib/python2.5/SQLAlchemy-0.4.0-
py2.5.egg/sqlalchemy/orm/attributes.py in get(self, state, passive)
242 if passive:
243 return PASSIVE_NORESULT
-- 244 value = callable_()
245 if value is not ATTR_WAS_SET:
246 return self.set_committed_value(state,
value)
/Users/exogen/Projects/revision-env/lib/python2.5/SQLAlchemy-0.4.0-
py2.5.egg/sqlalchemy/orm/strategies.py in lazyload()
217 row = result.fetchone()
218 for prop in group:
-- 219
sessionlib.attribute_manager.set_committed_value(instance, prop.key,
row[prop.columns[0]])
220 return attributes.ATTR_WAS_SET
221 finally:
type 'exceptions.TypeError': 'NoneType' object is unsubscriptable
--~--~-~--~~~---~--~~
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: SQLAlchemy 0.4 beta2 released
On Aug 14, 11:29 am, Michael Bayer [EMAIL PROTECTED] wrote: I just put beta2 up. The important things in this release are major MS-SQL and Oracle fixes, after we got some on-the-ground testing done. Theres also a new function we're planning to use in Pylons called engine_from_config(), which reads a configuration dictionary (such as from a .ini file) and returns an Engine instance. Also some enhancements to Session.execute() which may become important as we move more towards the session-as-executor model; if you are using multiple engines, execute() will search through the ClauseElement for tables that it can link to bound engines, thus reducing the need to pass a mapper along to it. 0.4 looks awesome. Some names jump out at me though: * sessionmaker - Strange that it's a noun, and not a verb (like make_session) * bind/binds arguments - Strange that it's a verb, and not a noun (why not engine/engines?) Keep up the good work! -- Brian Beck / Adventurer of the First Order / www.brianbeck.com --~--~-~--~~~---~--~~ 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] TextMate bundle for SQLAlchemy
Hey alchemists, Last night I put up a TextMate bundle for SQLAlchemy. It has a few snippets and templates that should make writing models pretty quick. If you've never used TextMate or snippets in TextMate, check out the one-minute screencast demonstrating a few of them! Screencast: http://blog.case.edu/bmb12/2007/06/30/SQLAlchemy_Bundle.mov Download: http://www.pagodacms.org/files/SQLAlchemy_Bundle.tar.gz Blog entry: http://blog.case.edu/bmb12/2007/06/sqlalchemy_bundle_for_textmate Improvements and ideas welcome... happy hacking! -- Brian Beck / Adventurer of the First Order / http://www.brianbeck.com --~--~-~--~~~---~--~~ 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] Using MAX in a relation() without an inner SELECT
Hi,
We've got a CMS model with a revisioned page tree. Our pages have a
revision number and a parent page (it's a self-referential mapper).
One property we'd like to have in our assign_mapper call is a
latest_parent relation -- a property that returns the page with
page_id = parent_id and revision_number = MAX revision_number for that
page_id.
We've isolated our case into a simple example, where our table looks
like this:
pages_table = Table('pages', metadata,
Column('page_id', Integer, primary_key=True),
Column('revision_number', Integer, primary_key=True),
Column('parent_id', Integer, ForeignKey('pages.page_id'),
nullable=True),
)
We got the property working in the following complete example:
http://dpaste.com/hold/9205/
However, the query generated by that relation has 2 SELECTs, and the
result we want is possible with a single, very simple SELECT (using
page_id = 3 as an example):
SELECT *, MAX(pages.revision_number) FROM pages WHERE pages.page_id =
3
...and we can (sort-of) accomplish this in SQLAlchemy with this
select():
select([*, func.max(pages_table.c.revision_number)],
pages_table.c.page_id == 3)
However, that returns the tuple given by dbapi and not an instance of
the mapped class. We also can't figure out how to get this behavior in
a relation(), so that just the above SELECT is executed and not nested
SELECTs (as in the pasted example).
Is there some other kind of MapperProperty besides relation that will
let us do this? Any help is appreciated.
Brian Beck / Adventurer of the First order / www.brianbeck.com
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
