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: >> m_new = Movies() >> m_new.id=8 >> m_new.title = 'new movie' >> session.add(m_new) here is your SQL statement, attempting to INSERT id #8: >>> 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) > > 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.