Insert new one-to-many data into tables with SQLAlchemy ORM

Question

I'm trying to insert rows into a database that has three tables:

• Artists
• Songs
• Media (like a youtube video)

Artists has a one-to-many relationship with Songs and Songs has one-to-many relationship with Media. I'm getting stuck when there's a situation where the artist, song, and media are new and need to be added to the db.

I figured I could add the artist, commit it, then retrieve the artist object, and then use the artist object to add the song, applying the same idea to song > media. But this seems like this isn't the most efficient way to do it.

I tried adding the each object first then commiting at the end:

new_artist = models.Artist(
    name = artist_name,
    )
db.session.add(artist)

new_song = models.Song(
    name = song_name,
    artist = artist,
    )
db.session.add(song)

new_media = models.Media(
    song = song,
    media_id = media_id,
    user=user
    )
db.session.add(media)
db.session.commit()

But I'm getting this error:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/path/python2.7/site-packages/sqlalchemy/orm/scoping.py", line 114, in do
    return getattr(self.registry(), name)(*args, **kwargs)
  File "/path/python2.7/site-packages/sqlalchemy/orm/session.py", line 703, in commit
    self.transaction.commit()
  File "/path/python2.7/site-packages/sqlalchemy/orm/session.py", line 361, in commit
    self._prepare_impl()
  File "/path/python2.7/site-packages/sqlalchemy/orm/session.py", line 340, in _prepare_impl
    self.session.flush()
  File "/path/python2.7/site-packages/sqlalchemy/orm/session.py", line 1718, in flush
    self._flush(objects)
  File "/path/python2.7/site-packages/sqlalchemy/orm/session.py", line 1789, in _flush
    flush_context.execute()
  File "/path/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 331, in execute
    rec.execute(self)
  File "/path/python2.7/site-packages/sqlalchemy/orm/unitofwork.py", line 475, in execute
    uow
  File "/path/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 64, in save_obj
    table, insert)
  File "/path/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 558, in _emit_insert_statements
    execute(statement, params)
  File "/path/python2.7/site-packages/sqlalchemy/engine/base.py", line 1449, in execute
    params)
  File "/path/python2.7/site-packages/sqlalchemy/engine/base.py", line 1584, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/path/python2.7/site-packages/sqlalchemy/engine/base.py", line 1698, in _execute_context
    context)
  File "/path/python2.7/site-packages/sqlalchemy/engine/base.py", line 1691, in _execute_context
    context)
  File "/path/python2.7/site-packages/sqlalchemy/engine/default.py", line 331, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.InterfaceError

Any ideas on how I can accomplish this?

Answer 1

Could you try this, and let me know what are you getting?

Basically, i just put the correct reference variables.

new_artist = models.Artist(
    name = artist_name,
    )
db.session.add(new_artist)

new_song = models.Song(
    name = song_name,
    artist = new_artist,
    )
db.session.add(new_song)

new_media = models.Media(
    song = new_song,
    media_id = media_id,
    user=user
    )
db.session.add(new_media)
db.session.commit()