Skip to content
Advertisement

SQLAlchemy nested model creation one-liner

I’m looking to create a new object from q2, which fails because the Question class is expecting options to be a dictionary of Options, and it’s receiving a dict of dicts instead.

So, unpacking obviously fails with a nested model.

What is the best approach to handle this? Is there something that’s equivalent to the elegance of the **dict for a nested model?

main.py

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

import models.base

from models.question import Question
from models.option import Option


engine = create_engine('sqlite:///:memory:')

models.base.Base.metadata.create_all(engine, checkfirst=True)
Session = sessionmaker(bind=engine)
session = Session()


def create_question(q):

    # The following hard coding works:
    # q = Question(text='test text',
    #                     frequency='test frequency',
    #                     options=[Option(text='test option')]
    #                     )

    question = Question(**q)
    session.add(question)
    session.commit()

q1 = {
    'text': 'test text',
    'frequency': 'test frequency'
}

q2 = {
    'text': 'test text',
    'frequency': 'test frequency',
    'options': [
        {'text': 'test option 123'},
    ]
}

create_question(q1)
# create_question(q2) FAILS

base.py

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

question.py

from sqlalchemy import *
from sqlalchemy.orm import relationship
from .base import Base


class Question(Base):

    __tablename__ = 'questions'

    id = Column(Integer, primary_key=True)

    text = Column(String(120), nullable=False)
    frequency = Column(String(20), nullable=False)
    active = Column(Boolean(), default=True, nullable=False)

    options = relationship('Option', back_populates='question')

    def __repr__(self):
        return "<Question(id={0}, text={1}, frequency={2}, active={3})>".format(self.id, self.text, self.frequency, self.active)

option.py

from sqlalchemy import *
from sqlalchemy.orm import relationship
from .base import Base


class Option(Base):

    __tablename__ = 'options'

    id = Column(Integer, primary_key=True)

    question_id = Column(Integer, ForeignKey('questions.id'))
    text = Column(String(20), nullable=False)

    question = relationship('Question', back_populates='options')

    def __repr__(self):
        return "<Option(id={0}, question_id={1}, text={2})>".format(self.id, self.question_id, self.text)

Advertisement

Answer

I liked the answer provided by @Abdou, but wanted to see if I couldn’t make it a bit more generic.

I ended up coming up with the following, which should handle any nested model.

from sqlalchemy import event, inspect


@event.listens_for(Question, 'init')
@event.listens_for(Option, 'init')
def received_init(target, args, kwargs):

    for rel in inspect(target.__class__).relationships:

        rel_cls = rel.mapper.class_

        if rel.key in kwargs:
            kwargs[rel.key] = [rel_cls(**c) for c in kwargs[rel.key]]

Listens for the init event of any specified models, checks for relationships that match the kwargs passed in, and then converts those to the matching class of the relationship.

If anyone knows how to set this up so it can work on all models instead of specifying them, I would appreciate it.

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement