Questions

many-to-many.py

import sqlalchemy
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship, backref

engine = sqlalchemy.create_engine('sqlite:///tmp.db')
Base = declarative_base()


class Answer(Base):
    __tablename__ = 'answers'
    user_id = Column(Integer, ForeignKey('users.id'), primary_key=True)
    question_id = Column(Integer, ForeignKey('questions.id'), primary_key=True)
    answer = Column(String)

    def __repr__(self):
        return f"<Answer({self.user_id}, {self.question_id}, {self.answer})>"


class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    username = Column(String, unique=True)

    answers = relationship('Answer', foreign_keys=[Answer.user_id],
                           backref=backref('user', lazy='joined'),
                           lazy='dynamic',
                           cascade='all, delete-orphan')

    def __repr__(self):
        return f"<User({self.username})>"


class Question(Base):
    __tablename__ = 'questions'
    id = Column(Integer, primary_key=True)
    question = Column(String)
    answers = relationship('Answer', foreign_keys=[Answer.question_id],
                           backref=backref('question', lazy='joined'),
                           lazy='dynamic',
                           cascade='all, delete-orphan')

    def __repr__(self):
        return f"<Question {self.question}>"


Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

u1 = User(username='gvr')

questions = [
    Question(question='What is your name?'),
    Question(question='What is your favourite number?'),
]


session.add_all([u1] + questions)
session.commit()

a1 = Answer(user=u1, question=questions[0], answer='guido')
session.add(a1)
session.commit()