On Jul 15, 2010, at 5:11 PM, Harry Percival wrote:
> thanks Michael. I really appreciate your help.
>
> How should use .merge()? I've tried both:
>>>>
> another_new = MoviesAndDirectorsAndGenres() #init another_new as blank row
> another_new = session.merge(new) #attempt to merge with my 'new'
> object that has desired attributes
>
> and
>>>>
> new = session.merge(another_new) #attempt to merge blank object to my
> new object with desired attributes
>
> the former fails trying to add a genre row that already exists, the
> latter fails trying to add an object with all null values.
>
> the thing is, I'm really not trying to merge anything. I'm trying to
> create a new object, which happens to be made up partially of new
> entries in some tables, and existing entries in other tables.
> genres_genre_id refers to the primary key of the genres table, and a
> genre with genre_id=6 already exists.
>
>
> am i going to have to map an ORM class to each of the composite
> tables, and use references to them via relationship() instead of
> hoping sql can figure it out by itself from the tables that make up
> the join construct?
>
> can supply a sqlite database and some source code if it helps?
It's typically appropriate to map tables individually and connect them via
relationship(), if you want to be writing rows to them independently. That's
what the phrase "partially of new entries in some tables, and existing entries
in other tables." implies.
>
> rgds,
> Harry
>
> On Thu, Jul 15, 2010 at 9:48 PM, Michael Bayer <mike...@zzzcomputing.com>
> wrote:
>>
>> On Jul 15, 2010, at 2:52 PM, Harry Percival wrote:
>>
>>> A new problem, which seems to occur in both IronPython and normal Python:
>>>
>>> I have a database with tables for movies, directors, genres (and a
>>> bridging table movie_directors)
>>> I have a class mapped to a join of all three of the above
>>>
>>>>>>
>>> j = join(movies_table,md_table).join(directors_table).join(genres_table)
>>> mapper(MoviesAndDirectorsAndGenres,j.select(use_labels=True).alias('moviesdirectorsgenres'))
>>>
>>> this works fine. i can query the database ok using this mapper.
>>>
>>> The tables already have some values in.
>>> Now, I want to create a new composite object, that references some of
>>> the existing values, but not all - say it's a new movie by an existing
>>> director in an existing genre:
>>>
>>>>>>
>>> new = MoviesAndDirectorsAndGenres()
>>> new.movies_movie_id=8 #does not already exist in the table
>>> new.directors_director_id=2 #already exists in the table
>>> new.genres_genre_id=6 #already exists too
>>> session.add(new)
>>>
>>> this fails with a constraint error / integrity error, because SQLA
>>> tries to re-insert genre #6 even though it already exists. it also
>>> happens if i use session.merge(new). it also happens if i fully
>>> specify all of the attributes of each movie/director/genre correctly.
>>>
>>> Is this expected behaviour? is there any way to get sqla to
>>> intelligently only do inserts when necessary? is it something to do
>>> with cascade configuration, and if so, how do i configure cascades on
>>> a mapper that's not based on relationship() but based on join()?
>>
>> you should be using merge(), and you should ensure that the objects being
>> merged have the correct primary key values. If "genre #6" refers to some
>> other column that isn't the primary key of that row, then you'd have to
>> ensure you put the correct primary key value on your object first before
>> merging it.
>>
>> you can always check what decision merge() made by asking, "obj in
>> session.new" versus "obj in session.dirty".
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>> To post to this group, send email to sqlalch...@googlegroups.com.
>> To unsubscribe from this group, send email to
>> sqlalchemy+unsubscr...@googlegroups.com.
>> For more options, visit this group at
>> http://groups.google.com/group/sqlalchemy?hl=en.
>>
>>
>
>
>
> --
> ------------------------------
> Harry J.W. Percival
> ------------------------------
> Italy Mobile: +39 389 095 8959
> UK Mobile: +44 (0) 78877 02511
> Skype: harry dot percival
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
