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...
On Mon, Jul 19, 2010 at 5:19 PM, Michael Bayer <[email protected]> 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 <[email protected]> >> 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 <[email protected]> >>>> 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 >>>>>> <[email protected]> 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 [email protected]. >> 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. >> > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to [email protected]. > 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. > > -- ------------------------------ 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 [email protected]. 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.
