Last active
November 28, 2024 04:22
-
-
Save jdekarske/3c727eedf7c4e015776980a4e5f932d3 to your computer and use it in GitHub Desktop.
This is a demonstration of how association objects work without the python typing syntax. turns out that was old syntax https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html#association-object
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| from sqlalchemy import ForeignKey | |
| from sqlalchemy import Integer | |
| from sqlalchemy import String | |
| from sqlalchemy.orm import DeclarativeBase | |
| from sqlalchemy.orm import relationship | |
| from sqlalchemy import create_engine | |
| from sqlalchemy.orm import Session | |
| from sqlalchemy import Column | |
| # This script demonstrates a quiz bank with questions and answers. | |
| # Questions can appear in multiple quizzes. | |
| class Base(DeclarativeBase): | |
| pass | |
| class QuizQuestion(Base): | |
| __tablename__ = "quiz_question" | |
| quiz_id = Column(Integer, ForeignKey("quiz.id"), primary_key=True, nullable=False) | |
| question_id = Column(Integer, ForeignKey("question.id"), primary_key=True, nullable=False) | |
| index = Column(Integer, nullable=False) | |
| # association between QuizQuestion -> Question | |
| question = relationship("Question", back_populates="quizzes") | |
| # association between QuizQuestion -> Quiz | |
| quiz = relationship("Quiz", back_populates="questions") | |
| class Quiz(Base): | |
| __tablename__ = "quiz" | |
| id = Column(Integer, primary_key=True) | |
| # many-to-many relationship to Question, bypassing the `QuizQuestion` class | |
| # questions = relationship( "Question", secondary="quiz_question", back_populates="quizzes") | |
| # association between Quiz -> QuizQuestion -> Question | |
| questions = relationship("QuizQuestion", | |
| back_populates="quiz", | |
| order_by="QuizQuestion.index") | |
| class Question(Base): | |
| __tablename__ = "question" | |
| id = Column(Integer, primary_key=True) | |
| text = Column(String) | |
| answer = Column(String) | |
| # many-to-many relationship to Parent, bypassing the `Association` class | |
| # quizzes = relationship( "Quiz", secondary="quiz_question", back_populates="questions") | |
| # association between Question -> QuizQuestion -> Quiz | |
| quizzes = relationship( "QuizQuestion", back_populates="question") | |
| # Create example questions | |
| def create_sample_questions(): | |
| # Dog questions | |
| q1 = Question( | |
| text="What breed of dog is known for its spotted coat?", | |
| answer="Dalmatian" | |
| ) | |
| q2 = Question( | |
| text="Which dog breed was originally bred to herd sheep?", | |
| answer="Border Collie" | |
| ) | |
| # Cat questions | |
| q3 = Question( | |
| text="What is a group of cats called?", | |
| answer="Clowder" | |
| ) | |
| q4 = Question( | |
| text="How many whiskers does the average cat have?", | |
| answer="24" | |
| ) | |
| # General pet questions | |
| q5 = Question( | |
| text="What is the most popular pet in the world?", | |
| answer="Fish" | |
| ) | |
| q6 = Question( | |
| text="What vitamin do cats and dogs need that humans can make naturally?", | |
| answer="Vitamin C" | |
| ) | |
| # Create quizzes | |
| dog_quiz = Quiz() | |
| cat_quiz = Quiz() | |
| pet_quiz = Quiz() | |
| # Add questions to quizzes with indexes | |
| dog_quiz.questions = [ | |
| QuizQuestion(question=q1, index=0), | |
| QuizQuestion(question=q2, index=1) | |
| ] | |
| cat_quiz.questions = [ | |
| QuizQuestion(question=q3, index=0), | |
| QuizQuestion(question=q4, index=1) | |
| ] | |
| pet_quiz.questions = [ | |
| QuizQuestion(question=q5, index=0), | |
| QuizQuestion(question=q6, index=1), | |
| QuizQuestion(question=q1, index=2), # Reusing dog question | |
| QuizQuestion(question=q3, index=3) # Reusing cat question | |
| ] | |
| return [q1, q2, q3, q4, q5, q6], [dog_quiz, cat_quiz, pet_quiz] | |
| def setup_database(): | |
| # Create an in-memory SQLite database for testing | |
| engine = create_engine("sqlite:///:memory:", echo=False) | |
| # Create all tables | |
| Base.metadata.create_all(engine) | |
| # Create a session | |
| session = Session(engine) | |
| return session | |
| def test_transactions(): | |
| session = setup_database() | |
| try: | |
| # Create sample data | |
| questions, quizzes = create_sample_questions() | |
| # Add all objects to the session | |
| session.add_all(questions) | |
| session.add_all(quizzes) | |
| # Commit the transaction | |
| session.commit() | |
| # Test some queries | |
| # Get all quizzes | |
| all_quizzes = session.query(Quiz).all() | |
| print("\nAll quizzes:") | |
| for quiz in all_quizzes: | |
| print(f"\nQuiz {quiz.id}:") | |
| for assoc in quiz.questions: | |
| print(f" Question {assoc.index}: {assoc.question.text}") | |
| print(f" Answer: {assoc.question.answer}") | |
| print("\n\n") | |
| print("Quizzes belonging to question 1: ") | |
| question_1 = session.get(Question, 1) | |
| for assoc in question_1.quizzes: | |
| print(f" Quiz {assoc.quiz.id}") | |
| except Exception as e: | |
| print(f"An error occurred: {e}") | |
| session.rollback() | |
| finally: | |
| session.close() | |
| if __name__ == "__main__": | |
| test_transactions() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment