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.
