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