A simple library using Flask and SQLAlchemy

Question

This is the main.py script that does most of the work of Adding movies (Movie objects) as well as modifying and removing:

from flask import Flask, render_template, request, flash, url_for, redirect
import sqlite3
from datetime import date
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from movie import Movie

app = Flask(__name__)
app.secret_key = "LOL"
Base = declarative_base()
engine = create_engine('sqlite:///adatabase.db', connect_args={'check_same_thread': False}, echo=True)
Base.metadata.create_all(engine)
Base.metadata.bind = engine
Session = (sessionmaker(bind=engine)) #scoped_session
Base.metadata.create_all(engine)
session = Session()

@app.route('/')
@app.route('/home')
def home():
    return render_template('home.html')

@app.route('/form', methods = ['POST','GET'])
def form():
    try:
        if request.method == 'POST':
            title = request.form['title']
            release_date = request.form['release_date']
            print(release_date)
            session.add(Movie(title,date(int(release_date.split('-')[0]),int(release_date.split('-')[1]),int(release_date.split('-')[2]))))
            session.commit()
            session.close()
            return redirect(url_for('table'))
    except:
        flash("An error occured while writing to database")
        return redirect(url_for('home'))
    return render_template('form.html', title = "Form")

@app.route('/table')
def table():
    con = sqlite3.connect('adatabase.db')
    con.row_factory = sqlite3.Row
    cur = con.cursor()
    cur.execute('select * from movies')
    movies = cur.fetchall()
    return render_template('table.html',movies = movies, title = "Movies")

@app.route('/delete/<int:post_id>')
def delete(post_id):
    query = session.query(Movie).filter(Movie.id == post_id).first()
    session.delete(query)
    session.commit()
    session.close()
    return redirect(url_for('table'))

@app.route('/modify/<int:post_id>', methods = ['POST','GET'])
def modify(post_id):
    query = session.query(Movie).filter(Movie.id == post_id).first()
    if request.method == 'POST':
        title = request.form['title']
        release_date = request.form['release_date']
        session.delete(query)
        session.add(Movie(title,date(int(release_date.split('-')[0]),int(release_date.split('-')[1]),int(release_date.split('-')[2]))))
        session.commit()
        session.close()
        return redirect(url_for('table'))
    return render_template('edit.html',num = post_id,title = query.title,date = query.release_date)

if __name__ == '__main__':
    app.run(debug = True)

I have a Movie class defined in another script movie.py:

from sqlalchemy import Column, String, Integer, Date, Table, ForeignKey
from sqlalchemy.orm import relationship
from base import Base

movies_actors_association = Table(
'movies_actors', Base.metadata,
Column('movie_id', Integer, ForeignKey('movies.id')),
Column('actor_id', Integer, ForeignKey('actors.id'))
)

class Movie(Base):
    __tablename__ = 'movies'

    id = Column(Integer, primary_key=True)
    title = Column(String)
    release_date = Column(Date)
    actors = relationship('Actor',secondary=movies_actors_association)
    def __init__(self,title,release_date):
        self.title = title
        self.release_date = release_date

    def __repr__(self):
        return self.title

Finally, I have 5 HTML files that I think would just clutter up the post (if it isn't already) that are just home, form, table, edit, and base (the one that all of them extend, containing hyperlinks to all other pages).

The library works great. I was having an SQLAlchemy check_same_thread issue but I've added connect_args={'check_same_thread': False}, echo=True) to the engine=create_engine() and now it runs smoothly.

I know that I should probably have several files in which I do databases, classes, page switching and such but I'm not sure about the exact structure. It also feels like I'm missing some things as some example code I've seen uses db = SQLAlchemy(app) but others just use the session and engine.

Answer 1

Your database management is kind of a mess:

• SQLAlchemy recommend to keep your sessions scope the same than your requests scope;
• Having a single, global, session and closing it after a request mean that you can only ever make a single request for the whole lifetime of your application (which is not very useful);
• (I suspect that because of that) You mix using SQLAlchemy sessions and plain sqlite connection, this is bad as a single tool should perform all these operations;
• You mix table creation with application operations, these should be separated into two different scripts/task: your web server operate on the table, and an administration task (either by hand or with a dedicated script) is responsible for creating them beforehand.

For simplification of these tasks, a library have been developed: Flask-SQLAlchemy

You can have the following layout:

movie.py

from flask_sqlalchemy import SQLAlchemy


db = SQLAlchemy()


class Movie(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String, nullable=False)
    release_date = db.Column(db.Date, nullable=False)

# I’ll let you manage actors accordingly

main.py

from flask import Flask, render_template, request, flash, url_for, redirect
from movie import Movie, db


app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///adatabase.db'
db.init_app(app)


# regular route definitions


if __name__ == '__main__':
    app.run(debug=True)

And then, the table creation can simply be done by launching into your Python interpreter and doing:

>>> from movie import db
>>> from main import app
>>> app.app_context().push()
>>> db.create_all()

No need to embed this logic into your web-server. Or, at the very least, put it into a function in your main.py, you don't have to run this every time you launch your server.

---

Now to the part about your web server. The kind of operations you display here is know as CRUD. This usually requires two kind of routes:

1. A general route to list all items of a kind and to create new ones;
2. A specific route to manage a single element (read, update, delete).

The general route usually respond to GET and POST, the specific one usually respond to GET, PUT and DELETE.

A rough sketch of the application would be:

from datetime import datetime
from contextlib import suppress

from flask import Flask, render_template, request, redirect, url_for
from movie import Movie, db


app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///adatabase.db'
db.init_app(app)


@app.route('/', methods=['GET'])
@app.route('/movies', methods=['POST', 'GET'])
def movies():
    if request.method == 'POST':
        title = request.form['title']
        release_date = request.form['release_date']

        db.session.add(Movie(title, parse_release_date(release_date)))
        db.session.commit()

    movies = Movie.query.all()
    return render_template('movies.html', movies=movies)


@app.route('/movies/<int:post_id>', methods=['GET', 'PUT', 'DELETE'])
def movie(post_id):
    the_movie = Movie.query.filter(Movie.id == post_id).first()

    if request.method == 'DELETE':
        db.session.delete(the_movie)
        db.session.commit()
        return redirect(url_for('movies'))

    if request.method == 'PUT':
        with suppress(KeyError):
            the_movie.title = request.form['title']
        with suppress(KeyError):
            the_movie.release_date = parse_release_date(request.form['release_date'])
        db.session.commit()

    return render_template('single_movie.html', movie=the_movie)


def parse_release_date(date):
    parsed_date = datetime.strptime(date, '%Y-%m-%d')
    return parsed_date.date()


if __name__ == '__main__':
    app.run(debug=True)

Then you just need a simple movies.html displaying the list of movies and providing a form to add a new one; and a single_movie.html presenting the informations of a movie and providing a form to update it as well as a delete button.

Answer 2

Document with docstrings.

Your functions and ORM models are currently not providing any useful documentation regarding their respective function within your program.

Don't initialize the database on module level:

You should put that into a function:

Base.metadata.create_all(engine)
Base.metadata.bind = engine
Session = (sessionmaker(bind=engine)) #scoped_session
Base.metadata.create_all(engine)
session = Session()

Don't do Pokémon Exception handling.

try:
    if request.method == 'POST':
        title = request.form['title']
        release_date = request.form['release_date']
        print(release_date)
        session.add(Movie(title,date(int(release_date.split('-')[0]),int(release_date.split('-')[1]),int(release_date.split('-')[2]))))
        session.commit()
        session.close()
        return redirect(url_for('table'))
except:
    flash("An error occured while writing to database")
    return redirect(url_for('home'))
return render_template('form.html', title = "Form")

This will catch any instance of BaseException and thus also KeyboardInterrupts and SytemExits. Also the error message is misleading, since it would also be printed on KeyErrors raised by request.form['title'] or request.form['release_date'], which implies a user input related error, rather than an error in writing to the database.

I suggest you familiarize yourself with the sqlalchemy exception APIs^{1, 2} and handle anticipated exceptions selectively.

---

1. http://docs.sqlalchemy.org/en/latest/core/exceptions.html
2. http://docs.sqlalchemy.org/en/latest/orm/exceptions.html