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.
