sqlalchemy_many_to_many.py

import sqlalchemy as db
from sqlalchemy import Table, Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker


Base = declarative_base()

engine = db.create_engine("sqlite:///toto.db", echo=True)
Session = sessionmaker(bind=engine)
session = Session()


class Video(Base):
    __tablename__ = "video"
    id = Column(Integer, primary_key=True)
    filename = Column(String)
    releases = relationship("Release", secondary="video_release", backref="videos")


# CREATE TABLE video (
#        id INTEGER NOT NULL,
#        filename VARCHAR,
#        PRIMARY KEY (id)
# )


class Release(Base):
    __tablename__ = "release"
    id = Column(Integer, primary_key=True)
    url = Column(String)
    # Note: there is an implicit `videos` (list) member here


# CREATE TABLE release (
#         id INTEGER NOT NULL,
#         url VARCHAR,
#         PRIMARY KEY (id)
# )

VideoRelease = Table(
    "video_release",
    Base.metadata,
    Column("video_id", Integer, ForeignKey("video.id")),
    Column("release_id", Integer, ForeignKey("release.id")),
)

# CREATE TABLE video_release (
#         video_id INTEGER,
#         release_id INTEGER,
#         FOREIGN KEY(video_id) REFERENCES video (id),
#         FOREIGN KEY(release_id) REFERENCES release (id)
# )

Base.metadata.create_all(engine)

v1 = Video(filename="v1.mov")
r1 = Release(url="r1.com", videos=[v1])
v1.releases.append(r1)

session.add(v1)
session.add(r1)

# INSERT INTO video (url) VALUES ('v1.mov')
# INSERT INTO release (url) VALUES ('r1.com')
# INSERT INTO video_release (video_id, release_id) VALUES (1, 1)

session.commit()