Hi Michael,
I'm using a manual workaround, so this is only out of curiosity now.
Cf yr point 1:
> 1. Merging MoviesAndDirectorsAndGenres, with no primary key identifiers set,
> sets it as "pending" in the Session. This now indicates that an INSERT into
> all three tables will take place during flush, since the object had no
> primary key when being merged.
not quite sure what 'primary key identifiers' are, but am assuming
that you mean explicitly setting the attributes that are mapped to the
join's composite primary key. let's try that:
new = MoviesAndDirectorsAndGenres()
new.movies_movie_id=8 #does not already exist in the movies table
new.movies_title='new movie'
new.directors_director_id=2 #already exists in the directors table
new.genres_genre_id=6 #already exists too
so, the new object now has a primary key - (8,2,6). this is a new pk
for the join, but it is made up of pks from each of the constituent
tables - 2 of which are existing, and 1 of which is new.
The call to merge fails in the same way:
2010-07-22 13:40:46,063 INFO sqlalchemy.engine.base.Engine.0x...de90
SELECT <truncated...>
FROM movies JOIN movie_directors ON movies.movie_id =
movie_directors.movie_id JOIN directors ON directors.director_id =
movie_directors.director_id JOIN genres ON genres.genre_id =
movies.genre_id) AS mdg
WHERE mdg.movies_movie_id = ? AND mdg.directors_director_id = ? AND
mdg.genres_genre_id = ?
2010-07-22 13:40:46,075 INFO sqlalchemy.engine.base.Engine.0x...de90 (8, 2, 6)
2010-07-22 13:40:46,079 INFO sqlalchemy.engine.base.Engine.0x...de90
INSERT INTO genres (genre_id, name, description) VALUES (?, ?, ?)
2010-07-22 13:40:46,081 INFO sqlalchemy.engine.base.Engine.0x...de90
(6, None, None)
2010-07-22 13:40:46,084 INFO sqlalchemy.engine.base.Engine.0x...de90 ROLLBACK
attempted to add new composite object, failed with (IntegrityError)
PRIMARY KEY must be unique u'INSERT INTO genres (genre_id, name,
description) VALUES (?, ?, ?)' (6, None, None)
If I understand correctly, calling session.merge() on a composite
object does not make several different decisions about whether to do
inserts or updates across each constituent table - it simply makes
one, global decision based on its own composite pk?
Perhaps more granular decision-making would be an interesting feature
for future versions? Or does it just get impossibly complex for
arbitrary selectables?
anyways, thanks for all your help.
Harry
On Tue, Jul 20, 2010 at 5:14 PM, Michael Bayer <mike...@zzzcomputing.com> wrote:
>
> On Jul 20, 2010, at 11:51 AM, Harry Percival wrote:
>
>> attached. feel free to ignore the 'sqlite' folder, which is only
>> needed for ironpython. the error definitely occurs in cpython 2.6.3.
>
> 1. Merging MoviesAndDirectorsAndGenres, with no primary key identifiers set,
> sets it as "pending" in the Session. This now indicates that an INSERT into
> all three tables will take place during flush, since the object had no
> primary key when being merged.
>
> 2. Existing Genres, Directors, and Movies objects are attached to the new
> MoviesAndDirectorsAndGenres object. These attachments specify a primary
> join condition that requests that the primary key of each of the Genres,
> Directors, and Movies be populated into each of the director_id, genre_id,
> and movie_id attributes of the MoviesAndDirectorsAndGenres during a flush.
>
> 3. The flush takes place, the director_id, genre_id, and movie_id attributes
> of the pending MoviesAndDirectorsAndGenres are populated with integer values
> that happen to already exist in those tables, and the INSERT fails.
>
> Since you don't really want an INSERT to take place here, I would recommend
> using only Genres, Directors, and Movies objects.
>
> It would be very helpful if you could not send any more zipfiles, only a
> single .py script is needed for test case illustration. This makes my life
> easier and also for the many people that will be reading your post in the
> future.
>
>
>
>>
>> rgds,
>> hp
>>
>> On Tue, Jul 20, 2010 at 3:54 PM, Michael Bayer <mike...@zzzcomputing.com>
>> wrote:
>>>
>>> On Jul 20, 2010, at 10:46 AM, Michael Bayer wrote:
>>>
>>> On Jul 20, 2010, at 6:05 AM, Harry Percival wrote:
>>>
>>> I'm clear on that. I'm only using session.add to do an insert when I
>>>
>>> know i definitely want to. But you'll see I used session.merge on the
>>>
>>> composite object, yet it still attempts to do an insert for rows that
>>>
>>> already exist in its constituent tables...
>>>
>>>
>>> you are setting a primary key, and adding right here:
>>>
>>> my apologies, thats not the INSERT that fails.
>>>
>>> please attach a full reproducing test script.
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> On Mon, Jul 19, 2010 at 5:19 PM, Michael Bayer <mike...@zzzcomputing.com>
>>> wrote:
>>>
>>> On Jul 19, 2010, at 12:04 PM, Harry Percival wrote:
>>>
>>> Michael, thanks, as ever, for your help.
>>>
>>> So, I think I've managed to specify the relationships:
>>>
>>>
>>> j = join(movies_table,md_table).join(directors_table).join(genres_table)
>>>
>>> js = j.select(use_labels=True).alias('mdg')
>>>
>>> r0 = relationship(Movies,
>>>
>>> primaryjoin=(js.c.movies_movie_id==movies_table.c.movie_id),
>>>
>>> foreign_keys=([js.c.movies_movie_id])
>>>
>>> )
>>>
>>> r1 = relationship(Directors,
>>>
>>> primaryjoin=(js.c.directors_director_id==directors_table.c.director_id),
>>>
>>> foreign_keys=([js.c.directors_director_id])
>>>
>>> )
>>>
>>> r2 = relationship(Genres,
>>>
>>> primaryjoin=(js.c.genres_genre_id==genres_table.c.genre_id),
>>>
>>> foreign_keys=([js.c.genres_genre_id])
>>>
>>> )
>>>
>>> mapper(MoviesAndDirectorsAndGenres,
>>>
>>> js,
>>>
>>> properties={'movie':r0,
>>>
>>> 'director':r1,
>>>
>>> 'genre':r2
>>>
>>> },
>>>
>>> passive_updates=False)
>>>
>>>
>>> To test it, I'm trying to create a new composite object, based on a
>>>
>>> new movie but existing director & genre:
>>>
>>>
>>>
>>> session=Session()
>>>
>>> m_new = Movies()
>>>
>>> m_new.id=8
>>>
>>> m_new.title = 'new movie'
>>>
>>> session.add(m_new)
>>>
>>> d2 = session.query(Directors).get(2)
>>>
>>> print '***************director=',d2
>>>
>>> g6 = session.query(Genres).get(6)
>>>
>>> print '***************genre=',g6
>>>
>>> oo_new = MoviesAndDirectorsAndGenres()
>>>
>>> oo_new.movie = m_new
>>>
>>> oo_new.director = d2
>>>
>>> oo_new.genre = g6
>>>
>>> try:
>>>
>>> # another_new = session.merge(new)
>>>
>>> session.merge(oo_new)
>>>
>>> but sqla is doing some really weird stuff:
>>>
>>> 2010-07-19 16:56:01,877 INFO sqlalchemy.engine.base.Engine.0x...37f0 BEGIN
>>>
>>> 2010-07-19 16:56:01,878 INFO sqlalchemy.engine.base.Engine.0x...37f0 INSERT
>>> INTO
>>>
>>> movies (movie_id, title, description, genre_id, release_date) VALUES
>>>
>>> (?, ?, ?,?, ?)
>>>
>>> 2010-07-19 16:56:01,880 INFO sqlalchemy.engine.base.Engine.0x...37f0
>>>
>>> (8, 'new movie', None, None, None)
>>>
>>> 2010-07-19 16:56:01,882 INFO sqlalchemy.engine.base.Engine.0x...37f0
>>>
>>> SELECT directors.director_id AS directors_director_id, directors.name
>>>
>>> AS directors_name
>>>
>>> FROM directors
>>>
>>> WHERE directors.director_id = ?
>>>
>>> 2010-07-19 16:56:01,885 INFO sqlalchemy.engine.base.Engine.0x...37f0 (2,)
>>>
>>> ***************director= <test_multitable.Directors object at 0x021E2030>
>>>
>>> 2010-07-19 16:56:01,888 INFO sqlalchemy.engine.base.Engine.0x...37f0
>>>
>>> SELECT genres.genre_id AS genres_genre_id, genres.name AS genres_name,
>>>
>>> genres.description AS genres_description
>>>
>>> FROM genres
>>>
>>> WHERE genres.genre_id = ?
>>>
>>> 2010-07-19 16:56:01,891 INFO sqlalchemy.engine.base.Engine.0x...37f0 (6,)
>>>
>>> ***************genre= <test_multitable.Genres object at 0x021E21B0>
>>>
>>> 2010-07-19 16:56:01,894 INFO sqlalchemy.engine.base.Engine.0x...37f0
>>>
>>> INSERT INTO genres (genre_id, name, description) VALUES (?, ?, ?)
>>>
>>> 2010-07-19 16:56:01,895 INFO sqlalchemy.engine.base.Engine.0x...37f0
>>>
>>> (6, None, None)
>>>
>>> 2010-07-19 16:56:01,898 INFO sqlalchemy.engine.base.Engine.0x...37f0
>>> ROLLBACK
>>>
>>> attempted to add new composite object, failed with (IntegrityError)
>>>
>>> PRIMARY KEY must be unique u'INSERT INTO genres (genre_id, name,
>>>
>>> description) VALUES (?, ?, ?)' (6, None, None)
>>>
>>> Traceback (most recent call last):
>>>
>>> File "test_multitable.py", line 105, in <module>
>>>
>>> session.commit()
>>>
>>> File "sqlalchemy\orm\session.py", line 653, in commit
>>>
>>> <etc etc>
>>>
>>> IntegrityError: (IntegrityError) PRIMARY KEY must be unique u'INSERT INTO
>>> genres
>>>
>>> (genre_id, name, description) VALUES (?, ?, ?)' (6, None, None)
>>>
>>>
>>> it seems to trying to insert a new genre, even though I've told it to
>>>
>>> use an existing one?
>>>
>>> I suppose, once I've done the insert into the movies table, i could
>>>
>>> retrieve the new composite object via a session.query, instead of
>>>
>>> trying to do a session.merge. but this wouldn't work for creating a
>>>
>>> new Director object, since I also need to create an entry into the
>>>
>>> bridging table, something i'm hoping sqla could manage for me...
>>>
>>> if you say x = new Foo() x.id = 7; session.add(x); that is an INSERT, no
>>> matter what the ID is, and will fail as above if that primary key already
>>> exists. If you OTOH say x = session.merge(x), it will be an INSERT or an
>>> UPDATE depending on whether or not primary key id #7 exists in the database
>>> already or not.
>>>
>>> You can get an overview of what the various methods do at:
>>>
>>> http://www.sqlalchemy.org/docs/session.html#id1
>>>
>>>
>>>
>>>
>>>
>>> rgds,
>>>
>>> hp
>>>
>>>
>>> On Mon, Jul 19, 2010 at 3:31 PM, Michael Bayer <mike...@zzzcomputing.com>
>>> wrote:
>>>
>>> On Jul 19, 2010, at 10:24 AM, Harry Percival wrote:
>>>
>>> OK, so I will treat any classes mapped to a join of multiple tables as
>>>
>>> being a read-only API, and manually manage the write-API using
>>>
>>> relationship().
>>>
>>> It doesn't look like I can define a relationship from the composite
>>>
>>> mapped class to individual tables though?
>>>
>>>
>>> #single table mappers:
>>>
>>> mapper(Movies,movies_table)
>>>
>>> mapper(Directors,directors_table)
>>>
>>> mapper(Genres,genres_table)
>>>
>>>
>>> j = join(movies_table,md_table).join(directors_table).join(genres_table)
>>>
>>> r0 = relationship(Movies,
>>>
>>> primaryjoin=(movies_table.c.movie_id==md_table.c.movie_id),
>>>
>>> foreign_keys=([md_table.c.movie_id]))
>>>
>>> r1 =
>>> relationship(Directors)#,primaryjoin=(and_(movies_table.c.movie_id==md_table.c.movie_id,md_table.c.director_id==directors_table.c.director_id)))#,secondaryjoin=(md_table.c.director_id==directors_table.c.director_id),secondary=md_table)
>>>
>>> r2 = relationship(Genres)
>>>
>>> mapper(MoviesAndDirectorsAndGenres,
>>>
>>> j.select(use_labels=True).alias('mdg'),
>>>
>>> j.select(...).alias() is the selectable that you have to define all your
>>> primaryjoins against. So you need to assign that on the outside to a
>>> variable first.
>>>
>>>
>>>
>>>
>>>
>>> properties={'movie':r0,
>>>
>>> 'director':r1,
>>>
>>> 'genre':r2
>>>
>>> },
>>>
>>> passive_updates=False)
>>>
>>> Tried lots of permutations of this, but it doesn't look like I can get
>>>
>>> sqla to understand a 1-to-1 relationship between the
>>>
>>> multiple-table-mapped MoviesAndDirectorsAndGenres objects and the
>>>
>>> Movies, Directors, or Genres objects they're built out of...
>>>
>>> I think perhaps I'm just trying to do something that SQLA really
>>>
>>> wasn't designed for...
>>>
>>> hp
>>>
>>> On Mon, Jul 19, 2010 at 2:29 AM, Michael Bayer <mike...@zzzcomputing.com>
>>> wrote:
>>>
>>> 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.
>>>
>>>
>>> --
>>>
>>> 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.
>>>
>>>
>>> --
>>> 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.
>>
>> <test_multitable.zip>
>
> --
> 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.
