Bottle – Adding SQLAlchemy to the Todo List Web App

In this article we will be taking the code from the previous article on Bottle and changing it such that it uses SQLAlchemy instead of just normal SQLite code. This will require you to download the bottle-sqlalchemy package from PyPI. You can also install it using “pip install bottle-sqlalchemy”, assuming you have pip installed. You will also need Bottle itself, of course. Once you’re ready, we can continue.

Adding SQLAlchemy to Bottle

The bottle-sqlalchemy package is a plugin for Bottle that makes adding SQLAlchemy to your web application very easy. But first, let’s actually create the database. We’ll use SQLAlchemy instead of the script from the other article. Here’s the code:

from sqlalchemy import create_engine, Column, Boolean, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Base = declarative_base()
engine = create_engine("sqlite:///todo.db", echo=True)

########################################################################
class TODO(Base):
    """
    TODO database class
    """
    __tablename__ = "todo"
    id = Column(Integer, primary_key=True)
    task = Column(String, nullable=False)
    status = Column(Boolean, nullable=False)

    #----------------------------------------------------------------------
    def __init__(self, task, status):
        """Constructor"""
        self.task = task
        self.status = status
        
#----------------------------------------------------------------------
def main():
    """
    Create the database and add data to it
    """
    Base.metadata.create_all(engine)
    create_session = sessionmaker(bind=engine)
    session = create_session()
    
    session.add_all([
        TODO('Read Google News', 0),
        TODO('Visit the Python website', 1),
        TODO('See how flask differs from bottle', 1),
        TODO('Watch the latest from the Slingshot Channel', 0)
        ])
    session.commit()

if __name__ == "__main__":
    main()

If you know SQLAlchemy, then you know what’s going on here. Basically, you need to create a class that represents the database and map it to a database “engine”. Then you create a session object to run queries and such. In this case, we insert four records. You’ll need to run this to create the database that the web app will be using.

Now we’re ready to look at the meat of the project:

from bottle import Bottle, route, run, debug
from bottle import redirect, request, template
from bottle.ext import sqlalchemy

from sqlalchemy import create_engine, Column, Boolean, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# --------------------------------
# Add SQLAlchemy app
# --------------------------------
app = Bottle()

Base = declarative_base()
engine = create_engine("sqlite:///todo.db", echo=True)
create_session = sessionmaker(bind=engine)

plugin = sqlalchemy.Plugin(
        engine,
        Base.metadata,
        keyword='db',
        create=True,
        commit=True,
        use_kwargs=False
)

app.install(plugin)

########################################################################
class TODO(Base):
    """
    TODO database class
    """
    __tablename__ = "todo"
    id = Column(Integer, primary_key=True)
    task = Column(String, nullable=False)
    status = Column(Boolean, nullable=False)

    #----------------------------------------------------------------------
    def __init__(self, task, status):
        """Constructor"""
        self.task = task
        self.status = status
        
    #----------------------------------------------------------------------
    def __repr__(self):
        """"""
        return "', method='GET')
def edit_item(no):
    """
    Edit a TODO item
    """
    session = create_session()
    result = session.query(TODO).filter(TODO.id==no).first()
    
    if request.GET.get('save','').strip():
        task = request.GET.get('task','').strip()
        status = request.GET.get('status','').strip()

        if status == 'open':
            status = 1
        else:
            status = 0
        
        result.task = task
        result.status = status
        session.commit()

        redirect("/")
    else:
        return template('edit_task', old=result, no=no)
    
#----------------------------------------------------------------------
@route("/new", method="GET")
def new_item():
    """
    Add a new TODO item
    """
    if request.GET.get("save", "").strip():
        task = request.GET.get("task", "").strip()
        status = 1
        
        session = create_session()
        new_task = TODO(task, status)
        session.add(new_task)
        session.commit()
        
        redirect("/")
    else:
        return template("new_task.tpl")
    
#----------------------------------------------------------------------
@route("/done")
def show_done():
    """
    Show all items that are done
    """
    session = create_session()
    result = session.query(TODO).filter(TODO.status==0).all()
    
    output = template("show_done", rows=result)
    return output
    
#----------------------------------------------------------------------
@route("/")
@route("/todo")
def todo_list():
    """
    Show the main page which is the current TODO list
    """
    session = create_session()
    result = session.query(TODO).filter(TODO.status==1).all()
    myResultList = [(item.id, item.task) for item in result]
    output = template("make_table", rows=myResultList)
    return output

#----------------------------------------------------------------------
if __name__ == "__main__":
    debug(True)
    run()

Let’s break this down a bit. First we need to a Bottle object so we can add a plugin. Then we create a declarative_base object that we’ll use to sub-class from for our class representation of the database. Next we create the SQLAlchemy engine and a sessionmaker object. Finally we create the plugin itself and install it. Starting at line 56, we get into the actual Bottle code (where the edit_item function begins). If you had run the original version, you may have noticed a deprecation warning about using wildcard filters. We have remedied that issue in this article by changing the route construct for the edit_item function from @route(‘/edit/:no’, method=’GET’) to @route(‘/edit/‘, method=’GET’). This also allows us to remove the @validation decorator.

You’ll notice that in each of the functions, we create a session object to run the page’s queries against. Take a look at the main function, todo_list. The result that is returned from out query is a list of objects. The template expects a list of tuples or a list of lists, so we use a list comprehension to create a list of tuples. But what if wanted to change the template itself? Well we do that with the other two templates. Let’s take a look at the show_done.tpl code:

%#template to generate a HTML table from a list of tuples (or list of lists, or tuple of tuples or ...)
Your Finished TODO Items:


%for row in rows:
  
%end

    
{{row.id}}
    
{{row.task}}
    
 Edit
  
Create New item

This code used to be almost exactly the same as the code that is found in the main template, make_table.tpl. Since Bottle’s template code is almost Python, we can use dot notation to access the row object’s properties. This allows us to clean up the code a lot and get at the row.id and row.task very easily. You’ll also note that the code for the queries themselves are shorter because we don’t have all that extra connection setup and tear down to deal with when we use SQLAlchemy. Other than that, the application remains the same.

Now you should be able to create your own Bottle application that includes SQLAlchemy.

Download the Source

• todo_sa.zip