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. >>>> >>>> >>> >>> >>> >>> -- >>> ------------------------------ >>> 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. >> >> > > > > -- > ------------------------------ > 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.