Re: [sqlalchemy] problem with multi-table mapping - how to configure merge/cascade behaviour on mappers based on join/select?
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,
Re: [sqlalchemy] problem with multi-table mapping - how to configure merge/cascade behaviour on mappers based on join/select?
On Jul 22, 2010, at 9:02 AM, Harry Percival wrote: 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? that is correct.the primary key of (8,2, 6) does not exist in the database for this particular view - therefore the operation is determined to be an INSERT. Perhaps more granular decision-making would be an interesting feature for future versions? Or does it just get impossibly complex for arbitrary selectables? That's what relationship() is for. If we were to try to build such functionality into a mapping of three tables, it would require configuational elements that specify which tables are grouped together in what way, which tables should be SELECTed from when a merge is detected, and...then you've just reinvented separate mappers with relationship() between them.Also, if a mapping of multiple tables is now an ad-hoc structure, what would query(MoviesAndDirectorsAndGenres).get((8, 2, 6)) return ? A partial object ? Or None ? If the latter, how is that not what merge() should do, you've defined MoviesAndDirectorsAndGenres as the lowest level of granularity for this selectable yourself, SQLAlchemy should assume that's not what you meant and dig into each table individually ? There's a bigger picture going on here. 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
Re: [sqlalchemy] problem with multi-table mapping - how to configure merge/cascade behaviour on mappers based on join/select?
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 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().
Re: [sqlalchemy] problem with multi-table mapping - how to configure merge/cascade behaviour on mappers based on join/select?
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
Re: [sqlalchemy] problem with multi-table mapping - how to configure merge/cascade behaviour on mappers based on join/select?
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
Re: [sqlalchemy] problem with multi-table mapping - how to configure merge/cascade behaviour on mappers based on join/select?
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
Re: [sqlalchemy] problem with multi-table mapping - how to configure merge/cascade behaviour on mappers based on join/select?
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... 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
Re: [sqlalchemy] problem with multi-table mapping - how to configure merge/cascade behaviour on mappers based on join/select?
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)
Re: [sqlalchemy] problem with multi-table mapping - how to configure merge/cascade behaviour on mappers based on join/select?
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.
[sqlalchemy] problem with multi-table mapping - how to configure merge/cascade behaviour on mappers based on join/select?
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()? thanks in advance! HP -- 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.
Re: [sqlalchemy] problem with multi-table mapping - how to configure merge/cascade behaviour on mappers based on join/select?
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.
Re: [sqlalchemy] problem with multi-table mapping - how to configure merge/cascade behaviour on mappers based on join/select?
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? 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.