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.
