In order to store the data related to the survey application in the database, we should define a model called Question. This code lives in survey/models.py file.

models.py

class Question(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    question_text = db.Column(db.String(200))
    number_of_yes_votes = db.Column(db.Integer, default=0)
    number_of_no_votes = db.Column(db.Integer, default=0)
    number_of_maybe_votes = db.Column(db.Integer, default=0)

In the preceding code, we defined the Question model which extends from db.Model. It contains five fields to store the data related to a specific survey:

Now let us go ahead and add a constructor method, which enables us to set the instance variables for the Question object that we created in the previous lines of code:

class Question(db.Model):
    ...
    ...

    def __init__(self,
                 question_text,
                 number_of_yes_votes=0,
                 number_of_no_votes=0,
                 number_of_maybe_votes=0):

        self.question_text = question_text

        self.number_of_yes_votes = number_of_yes_votes
        self.number_of_maybe_votes = number_of_maybe_votes
        self.number_of_no_votes = number_of_no_votes

The preceding __init__() method takes the Question object and its values as parameters. Then, it will set the instance variables of the object that we passed.

Now, we will create a method called vote() which increments the counter variables for the 'yes', 'no' and 'maybe' votes.

class Question(db.Model):
    ...
    ...

    def vote(self, vote_type):
        if vote_type == 'yes':
            self.number_of_yes_votes += 1
        elif vote_type == 'no':
            self.number_of_no_votes += 1
        elif vote_type == 'maybe':
            self.number_of_maybe_votes += 1
        else:
            raise Exception("Invalid vote type")

In the preceding lines of code, we defined a vote() method, which takes the Question object as its first argument and the vote_type as its second argument. Based on the vote_type ('yes', 'no', or 'maybe'), the corresponding number_of_<vote_type>_votes of the Question object that we passed gets incremented.

Now that we are done with defining the models related to our application using the database instance object called db, we need to create corresponding tables in the databases. For that, we need to call the method create_all(), which is present in the database instance — db.

In our application, we generally call this function before invoking the server defined in runserver.py file.

Now, we have the database models ready. Let us query the data from the database using the SQLAlchemy's ORM. We'll perform the basic create, retrieve, update, and delete (CRUD) operations on our database instance — db.

Before making queries, let us move to our project root directory and fire up the Python console to execute the following commands:

>>> from survey import app, db
>>> from survey.models import Question

Now, let us create a Question object in the database. Creating an object using SQLAlchemy's ORM involves three essential steps as shown in the following code:

>>> question = Question("Are you an American?")
>>> db.session.add(question)
>>> db.session.commit()

We can see that:

Retrieving the objects from the database is very simple using the ORM. The following query retrieves all the objects from the database:

>>> Question.query.all()
[<Question 1 - u'Are you an American?'>]

We can also retrieve a model object from the database using its primary key. If we look at the Question model, we have a primary key with the column name id. Now, let us go ahead and access it.

>>> Question.query.get(1)
<Question 1 - u'Are you an American?'>

It is time to vote a survey. Fetch the object with id value 1 and use its vote() method to increase the number of votes of that choice.

>>> question = Question.query.get(1)
>>> question.number_of_yes_votes
0
>>> question.vote('yes')
>>> db.session.add(question)
>>> db.session.commit()

Let us learn how to delete a record from the database using the db.session.delete() method as shown in the following code:

>>> question = Question.query.get(1)
>>> db.session.delete(question)
>>> db.session.commit()

If you try to access the same object, it will result in the None value.

>>> print Question.query.get(1)
None