Mark Lam
blogworkarchiveinfo
← blog

Making a Database Model and Using it

Posted 5. July 2020. 3 min read.

This is a continuation of my Flask-Postgres series. Read about how to connect Postgres with Flask and how to set up flask with docker. In the last post I talked about how to set up a database with flask, and in this post I will take a deeper look at how to work with flask-alchemy. In this example I will create a simple todo list.

The first step of storing todos to my database is to create a database model for Todos in models.py. Create a class called Todo, and supply data types as columns. A list of flask alchemy data types can be found here. For my Todos, I want to keep track of an ID, the task, its completion status, create date and goal time. I also create a function to represent the data which is helpful when testing the database. The full class looks like:

class Todo(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    body = db.Column(db.String(140))
    createtime = db.Column(db.DateTime, index=True, default=datetime.utcnow)
    goaltime = db.Column(db.Integer)
    status = db.Column(db.Boolean, default=False)

    def __repr__(self):
      return '<Todo {}>'.format(self.body)

With models.py changed, you must update the database by running: flask db migrate -m "todos table" then run flask db upgrade to save the changes.

To test your database, login to the python interpretor and load the data base and models with: 

>>> from app import db
>>> from app.models import Todo

Next add a todo entry with: 

>>> todo = Todo(body="Create a test", goaltime=30)
>>> db.session.add(todo)
>>> db.session.commit()

>>> todos = Todo.query.all()
>>> for todo in todos:
...     print(todo.id, todo.body, todo.goaltime, todo.createtime)
...
1 Create a test 30 2020-07-06 03:42:29.359589 30 4


>>> for todo in todos:
...     db.session.delete(todo)
...

With the database working, we'll now set up routes to create an api to work with the database. I'll start by setting up routes which allow us to interact with the database. Open up the routes.py file and import jsonify and the Todo class.

# routes.py

#
from flask import jsonify
from app.models import Todo

I'll create a route to /api that accepts some different methods. When there is a GET request, to the api I want to return a list of todos to the client in JSON which I will eventually render in their browser. I also want to create a way to create todos with a POST request.

@app.route('/api', methods=['GET', 'POST'])
def api():
    if request.method == 'GET':
        todos = []
        data = Todo.query.all()
        
        for todo in data:
            item = {
                "id": todo.id, 
                "todo": todo.body, 
                "createtime": todo.createtime, 
                "goaltime": todo.goaltime, 
                "status": todo.status
                }
            todos.append(item.copy())
        print(todos)
        return jsonify(todos)
    elif requed.method == 'POST':
        data = request.json
        item = Todo(body=data["todo"], goaltime=data["goal"]) 
        db.session.add(item)
        db.session.commit()
        print (item, "ADDED!")
        print (data["todo"])
        return jsonify(data)

With the routes written, we can test the api endpoints using curl. First use a POST request to save a todo to save a todo, then list all the todos by sending a GET request to the end point. 

$ curl -i -H "Content-Type: application/json" -X POST -d '{"todo": "eat", "goal": "60"}' http://localhost:5000/api

$ curl -X GET http://localhost:5000/api
[{"createtime":"Sun, 05 Jul 2020 03:54:52 GMT","goaltime":30,"id":2,"status":false,"todo":"cry"},{"createtime":"Sun, 05 Jul 2020 03:55:15 GMT","goaltime":60,"id":3,"status":false,"todo":"eat"}]

Next we'll make a user interface for the API.

# tutorial # Linux