Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>For a quick and dirty solution I would suggest at least using two different columns to store different answers. You can also add a CHECK constraint to the database to ensure that exactly one of them is used for any row and the other is NULL. Than do the quick-n-dirty code to calculate total <code>Test</code> score.</p> <h2>The alternative</h2> <p>The idea is build the proper object model, map it to RDMBS and the question does not need to be asked. Also I expect that when using <a href="http://martinfowler.com/eaaCatalog/singleTableInheritance.html" rel="nofollow">Single Table Inheritance</a>, the resulting DB schema would be almost identical to the current implementation (you can see the model when you run the script with the option <code>echo=True</code>):</p> <pre><code>CREATE TABLE questions ( id INTEGER NOT NULL, text VARCHAR NOT NULL, type VARCHAR(10) NOT NULL, PRIMARY KEY (id) ) CREATE TABLE answer_options ( id INTEGER NOT NULL, question_id INTEGER NOT NULL, value INTEGER NOT NULL, type VARCHAR(10) NOT NULL, text VARCHAR, input INTEGER, PRIMARY KEY (id), FOREIGN KEY(question_id) REFERENCES questions (id) ) CREATE TABLE answers ( id INTEGER NOT NULL, type VARCHAR(10) NOT NULL, question_id INTEGER, test_id INTEGER, answer_option_id INTEGER, answer_input INTEGER, PRIMARY KEY (id), FOREIGN KEY(question_id) REFERENCES questions (id), FOREIGN KEY(answer_option_id) REFERENCES answer_options (id), --FOREIGN KEY(test_id) REFERENCES tests (id) ) </code></pre> <p>The code below is a complete working script that shows both the object model, its mapping to the database and the usage scenarios. As it is designed, the model is easily extendable with other types of questions/answers without any impact on existing classes. Basically you get less hacky and more flexible code simply because you have an object model which properly reflects your case. The code is below:</p> <pre><code>from sqlalchemy import create_engine, Column, Integer, SmallInteger, String, ForeignKey, Table, Index from sqlalchemy.orm import relationship, scoped_session, sessionmaker from sqlalchemy.ext.declarative import declarative_base # Configure test data SA engine = create_engine('sqlite:///:memory:', echo=True) session = scoped_session(sessionmaker(bind=engine)) Base = declarative_base() Base.query = session.query_property() class _BaseMixin(object): """ Just a helper mixin class to set properties on object creation. Also provides a convenient default __repr__() function, but be aware that also relationships are printed, which might result in loading relations. """ def __init__(self, **kwargs): for k,v in kwargs.items(): setattr(self, k, v) def __repr__(self): return "&lt;%s(%s)&gt;" % (self.__class__.__name__, ', '.join('%s=%r' % (k, self.__dict__[k]) for k in sorted(self.__dict__) if '_sa_' != k[:4] and '_backref_' != k[:9]) ) ### AnswerOption hierarchy class AnswerOption(Base, _BaseMixin): """ Possible answer options (choice or any other configuration). """ __tablename__ = u'answer_options' id = Column(Integer, primary_key=True) question_id = Column(Integer, ForeignKey('questions.id'), nullable=False) value = Column(Integer, nullable=False) type = Column(String(10), nullable=False) __mapper_args__ = {'polymorphic_on': type} class AnswerOptionChoice(AnswerOption): """ A possible answer choice for the question. """ text = Column(String, nullable=True) # when mapped to single-table, must be NULL in the DB __mapper_args__ = {'polymorphic_identity': 'choice'} class AnswerOptionInput(AnswerOption): """ A configuration entry for the input-type of questions. """ input = Column(Integer, nullable=True) # when mapped to single-table, must be NULL in the DB __mapper_args__ = {'polymorphic_identity': 'input'} ### Question hierarchy class Question(Base, _BaseMixin): """ Base class for all types of questions. """ __tablename__ = u'questions' id = Column(Integer, primary_key=True) text = Column(String, nullable=False) type = Column(String(10), nullable=False) answer_options = relationship(AnswerOption, backref='question') __mapper_args__ = {'polymorphic_on': type} def get_answer_value(self, answer): """ function to get a value of the answer to the question. """ raise Exception('must be implemented in a subclass') class QuestionChoice(Question): """ Single-choice question. """ __mapper_args__ = {'polymorphic_identity': 'choice'} def get_answer_value(self, answer): assert isinstance(answer, AnswerChoice) assert answer.answer_option in self.answer_options, "Incorrect choice" return answer.answer_option.value class QuestionInput(Question): """ Input type question. """ __mapper_args__ = {'polymorphic_identity': 'input'} def get_answer_value(self, answer): assert isinstance(answer, AnswerInput) value_list = sorted([(_i.input, _i.value) for _i in self.answer_options]) if not value_list: raise Exception("no input is specified for the question {0}".format(self)) if answer.answer_input &lt;= value_list[0][0]: return value_list[0][1] elif answer.answer_input &gt;= value_list[-1][0]: return value_list[-1][1] else: # interpolate in the range: for _pos in range(len(value_list)-1): if answer.answer_input == value_list[_pos+1][0]: return value_list[_pos+1][1] elif answer.answer_input &lt; value_list[_pos+1][0]: # interpolate between (_pos, _pos+1) assert (value_list[_pos][0] != value_list[_pos+1][0]) return value_list[_pos][1] + (value_list[_pos+1][1] - value_list[_pos][1]) * (answer.answer_input - value_list[_pos][0]) / (value_list[_pos+1][0] - value_list[_pos][0]) assert False, "should never reach here" ### Answer hierarchy class Answer(Base, _BaseMixin): """ Represents an answer to the question. """ __tablename__ = u'answers' id = Column(Integer, primary_key=True) type = Column(String(10), nullable=False) question_id = Column(Integer, ForeignKey('questions.id'), nullable=True) # when mapped to single-table, must be NULL in the DB question = relationship(Question) test_id = Column(Integer, ForeignKey('tests.id'), nullable=True) # @todo: decide if allow answers without a Test __mapper_args__ = {'polymorphic_on': type} def get_value(self): return self.question.get_answer_value(self) class AnswerChoice(Answer): """ Represents an answer to the *Choice* question. """ __mapper_args__ = {'polymorphic_identity': 'choice'} answer_option_id = Column(Integer, ForeignKey('answer_options.id'), nullable=True) answer_option = relationship(AnswerOption, single_parent=True) class AnswerInput(Answer): """ Represents an answer to the *Choice* question. """ __mapper_args__ = {'polymorphic_identity': 'input'} answer_input = Column(Integer, nullable=True) # when mapped to single-table, must be NULL in the DB ### other classes (Questionnaire, Test) and helper tables association_table = Table('questionnaire_question', Base.metadata, Column('id', Integer, primary_key=True), Column('questionnaire_id', Integer, ForeignKey('questions.id')), Column('question_id', Integer, ForeignKey('questionnaires.id')) ) _idx = Index('questionnaire_question_u_nci', association_table.c.questionnaire_id, association_table.c.question_id, unique=True) class Questionnaire(Base, _BaseMixin): """ Questionnaire is a compilation of questions. """ __tablename__ = u'questionnaires' id = Column(Integer, primary_key=True) name = Column(String, nullable=False) # @note: could use relationship with order or even add question number questions = relationship(Question, secondary=association_table) class Test(Base, _BaseMixin): """ Test is a 'test' - set of answers for a given questionnaire. """ __tablename__ = u'tests' id = Column(Integer, primary_key=True) # @todo: add user name or reference questionnaire_id = Column(Integer, ForeignKey('questionnaires.id'), nullable=False) questionnaire = relationship(Questionnaire, single_parent=True) answers = relationship(Answer, backref='test') def total_points(self): return sum(ans.get_value() for ans in self.answers) # -- end of model definition -- Base.metadata.create_all(engine) # -- insert test data -- print '-' * 20 + ' Insert TEST DATA ...' q1 = QuestionChoice(text="What is your fav pet?") q1c1 = AnswerOptionChoice(text="cat", value=1, question=q1) q1c2 = AnswerOptionChoice(text="dog", value=2, question=q1) q1c3 = AnswerOptionChoice(text="caiman", value=3) q1.answer_options.append(q1c3) a1 = AnswerChoice(question=q1, answer_option=q1c2) assert a1.get_value() == 2 session.add(a1) session.flush() q2 = QuestionInput(text="How many liters of beer do you drink a day?") q2i1 = AnswerOptionInput(input=0, value=0, question=q2) q2i2 = AnswerOptionInput(input=1, value=1, question=q2) q2i3 = AnswerOptionInput(input=3, value=5) q2.answer_options.append(q2i3) # test interpolation routine _test_ip = ((-100, 0), (0, 0), (0.5, 0.5), (1, 1), (2, 3), (3, 5), (100, 5) ) a2 = AnswerInput(question=q2, answer_input=None) for _inp, _exp in _test_ip: a2.answer_input = _inp _res = a2.get_value() assert _res == _exp, "{0}: {1} != {2}".format(_inp, _res, _exp) a2.answer_input = 2 session.add(a2) session.flush() # create a Questionnaire and a Test qn = Questionnaire(name='test questionnaire') qn.questions.append(q1) qn.questions.append(q2) session.add(qn) te = Test(questionnaire=qn) te.answers.append(a1) te.answers.append(a2) assert te.total_points() == 5 session.add(te) session.flush() # -- other tests -- print '-' * 20 + ' TEST QUERIES ...' session.expunge_all() # clear the session cache a1 = session.query(Answer).get(1) assert a1.get_value() == 2 # @note: will load all dependant objects (question and answer_options) automatically to compute the value a2 = session.query(Answer).get(2) assert a2.get_value() == 3 # @note: will load all dependant objects (question and answer_options) automatically to compute the value te = session.query(Test).get(1) assert te.total_points() == 5 </code></pre> <p>I hope that this version of the code answers all the questions asked in the comments.</p>
    singulars
    1. This table or related slice is empty.
    1. POMixed content (float || unicode) for database column
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USBenjamin
    1. USvan
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POMixed content (float || unicode) for database column
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTBountyClose
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COOn the 3th time I'm reading your answer I'm starting to figure out how it works but still have not clear which tables with which colums I'd end up with. In the quick-n-dirty solution you suggest (and I already implemented) I have 3 tables: *Question* (with quantitative / qualitative bool column), *Answers* (with `value` column for quantitatives and `description` for qualitatives), and *Score* (with `value` column holding `score` for qualitative questions or `input` for quantitative, according to `Question.quantitative). It works but I admit it's misleading. +1 anyway
      singulars
      1. PO
      1. USneurino
    2. CO@neurino: see Edit-1. If you have not used inheritance with ORM tools like SA before, there might be some learning curve, but IMO it is well worth it. If I find more time, I will make the code a working example.
      singulars
      1. PO
      1. USvan
    3. COthank you, I'm new to inheritance with ORM but I'm fine with learning
      singulars
      1. PO
      1. USneurino
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload