SQLAlchemy manual in Flask

by Alex
SQLAlchemy manual in Flask

You will need python 3.6+, SQLAlchemy and Flask libraries. Lesson code here. The versions of the libraries are in the requirements.txtfile This material will cover the basics of SQLAlchemy. Let’s create a web application in Flask, a Python language framework. It will be a minimalistic application that keeps track of books. It will be used to add new books, read existing books, update and delete them. These operations-creating, reading, updating, and deleting-are also known as “CRUD” and are the basis of almost all web applications. They will be discussed separately in this article. But before we move on to CRUD, let’s look at the individual elements of an application, starting with SQLAlchemy.

What is SQLAlchemy?

It’s worth noting that there is an extension for Flask called flask-sqlalchemy, which simplifies the SQLAlchemy process with some default values and other elements. They primarily make basic tasks easier. But this material will only use pure SQLAlchemy to get to grips with its basics without the various extensions. Manual on flask-sqlalchemy According to the library’s website, “SQLAlchemy is a set of SQL tools for Python and an object-relational mapping (ORM) tool that gives developers all the power and flexibility of SQL.” When reading this definition, the first question that comes up is: what is object-relational mapping? ORM is a technique used to write queries to databases using the object-oriented programming paradigms of a chosen language (Python in this case). To put it even more simply, an ORM is a kind of translator that translates code from one set of abstractions to another. In this case – from Python to SQL. There are plenty of reasons why you should use an ORM rather than manually constructing SQL strings. Here are some of them:

  • Accelerate web development by eliminating the need to switch between writing Python code and SQL
  • Eliminate repetitive code
  • Streamlined workflow and more efficient database queries
  • Abstraction of the database system, so switching between multiple databases becomes smoother
  • Generating template code for basic CRUD operations

Let’s go even deeper. Why use an ORM when you can write raw SQL? When we write queries in raw SQL, we pass them to the database as strings. The following query is written in raw SQL:

#import sqlite
import sqlite3
# connect to the book collection database
conn = sqlite3.connect('books-collection.db')
# create a cursor object to work with the database
c = conn.cursor()
# make a query that creates a table of books with an id and a name
name varchar(250) NOT NULL)
''' )
# executes a query that inserts values into the table
c.execute("INSERT INTO books VALUES(1, 'Pure Python')")
# save the job
# close the connection

There’s nothing wrong with using raw SQL to refer to databases, as long as you don’t make a mistake in the query. This could be, for example, a typo in the name of the database being accessed or an incorrect table name. The Python compiler can’t help you here. SQLAlchemy is one of the many ORM tools for Python. When working with small applications, pure SQL may work. But if it’s a large site with lots of data, this approach is more error-prone and simply more complicated.

Creating Database with SQLAlchemy

Let’s create a file to configure the database. You can name it whatever you want, but let it be database_setup.py.

import sys
# to configure the databases
from sqlalchemy import Column, ForeignKey, Integer, String
# to define table and model
from sqlalchemy.ext.declarative import declarative_base
# to create relations between tables
from sqlalchemy.orm import relationship
# for configuration
from sqlalchemy import create_engine
# creating a declarative_base instance
Base = declarative_base()
# here we add classes
# creates a create_engine instance at the end of the file
engine = create_engine('sqlite:///books-collection.db')

At the top of the file, import all modules necessary to set up and create databases. Import Column, ForeignKey, Integer and String to define columns in tables. Then we import the declarative_base extension. Base = declarative_base() creates a base class to define the declarative class and assigns it to the Base variable. According to the documentation, declarative_base() returns a new base class that all related classes inherit. These are the table, mapper(), and objects of the class within its definition. Next, create an instance of the create_engine class that points to the database with engine = create_engine('sqlite:///books-collection.db'). You can name the database whatever you want, but here let it be books-collection. The last configuration step is to add Base.metadata.create_all(engine). This will add classes (we’ll write them later) as tables to the created database. After setting up the database, create classes. In SQLAlchemy, classes are object-oriented or declarative representations of a table in the database.

# we create the Book class by inheriting it from the Base class.
class Book(Base):
__tablename__ = 'book'
id = Column(Integer, primary_key=True)
title = Column(String(250), nullable=False)
author = Column(String(250), nullable=False)
genre = Column(String(250))

For this tutorial, one table is enough: Book. It will contain 4 columns: id, title, author, and genre. Integer and String are used to define the type of values that will be stored in the columns. The column with title, author and genre are strings, and id is a number. There are many class attributes that are used to define columns, but let’s look at the ones already used:

  1. primary_key: when True, indicates the value used to identify each unique row in the table.
  2. String(250): String is the type of value, and the value in parentheses is the maximum length of the row.
  3. Integer: specifies the type of value (integer).
  4. nullable: if False, it means there must be a value to create a string.

This is where the configuration process ends. If you now use the python command database_setup.py on the command line, an empty database books-collection.db will be created. You can now fill it with data and try to manipulate it.

CRUD with SQLAlchemy by Example

At the beginning we briefly touched on the subject of CRUD operations. Now it’s time to use them. Let’s create another file and name it populate.py (or whatever name you want).

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
# import the Book and Base classes from the database_setup.py file
from database_setup import Book, Base
engine = create_engine('sqlite:///books-collection.db')
# Bind engine to Base class metadata
# so that declaratives can access it via a DBSession instance
Base.metadata.bind = engine
DBSession = sessionmaker(bind=engine)
# The DBSession() instance is responsible for all access to the database
# and represents the "middle ground" for all objects
# loaded into the database session object.
session = DBSession()

First of all, import all dependencies and some classes from the database_setup.py file. Then, we will tell the program which database we want to interact with. This is done with the create_engine function. To create a connection between the class definitions and the tables in the database, use the command Base.metadata.bind. To create, delete, read, or update records in the database, SQLAlchemy provides an interface called Session. To run queries, you need to add and commit (make a commit) a query. We use the flush() method. It transfers changes from memory to the database transaction buffer without committing the change.


The standard process for creating a record is as follows:

entryName = ClassName(property="value", property="value" ... )
# To save our ClassName object, we add it to our session:
To save the changes to our database and commit
transaction, we use commit(). Any change,
made to objects in the session will not be saved
into the database until you call session.commit().

You can create the first book using the following command:

bookOne = Book(title='Pure Python', author='Dan Bade', genre='computer literature')


Depending on what you want to read, different functions are used. Let’s look at two ways to use them in an application. session.query(Book).all() – returns a list of all books session.query(Book).first() – returns the first result or None if there is no row


To update the records in the database, you need to do the following:

  1. Find the book
  2. Reset values
  3. Add a new record
  4. Commit the session to the database (make a commit)

If you haven’t noticed yet, there is an error in the bookOne record. The book Pure Python was written by Dan Bader, not Dan Bader. Let’s update the author’s name using the 4 steps described. To search for an entry, we use filter(), which filters queries based on the attributes of the entries. The following query will return the book with id=1 (i.e., “Pure Python”)

editedBook = session.query(Book).filter_by(id=1).one()

To reset and commit the author’s name, the following commands are needed:

editedBook.author = "Dan Bader"

You can use all(), one(), or first() to search for an entry, depending on the expected result. But there are a few nuances that are important to keep in mind.

  1. all() – returns results in a list
  2. one() – returns one result or throws exception. Throws exception sqlaclhemy.orm.exc.NoResultFoud if no result was found or sqlaclhemy.orm.exc.NoResultFoud if several results were returned
  3. first() – returns the first query result or None if it contains no strings, but no exceptions


Deleting values from the database is almost the same as updating:

  1. Find record
  2. Delete record
  3. Commit session
bookToDelete = session.query(Book).filter_by(title='Pure Python').one()

Now that the database is set up and there is a basic understanding of CRUD operations, it’s time to write a little Flask application. But we won’t go into the framework itself. You can read more about it in other materials. Let’s create a new file app.py in the same folder as database_setup.py and populate.py. Then import the necessary dependencies.

from flask import flask, render_template, request, redirect, url_for
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from database_setup import Base, Book
app = Flask(__name__)
# connect and create a database session
engine = create_engine('sqlite:///books-collection.db?check_same_thread=False')
Base.metadata.bind = engine
DBSession = sessionmaker(bind=engine)
session = DBSession()
# page that will display all the books in the database
# This function works in read mode.
def showBooks():
books = session.query(Book).all()
return render_template("books.html", books=books)
# This function will create a new book and save it to the database.
@app.route('/books/new/', methods=['GET', 'POST'])
def newBook():
if request.method == 'POST':
newBook = Book(title=request.form['name'], author=request.form['author'], genre=request.form['genre'])
return redirect(url_for('showBooks'))
return render_template('newBook.html')
# This function will allow us to update the books and save them to the database.
@app.route("/books//edit/", methods=['GET', 'POST'])
def editBook(book_id):
editedBook = session.query(Book).filter_by(id=book_id).one()
if request.method == 'POST':
if request.form['name']:
editedBook.title = request.form['name']
return redirect(url_for('showBooks'))
return render_template('editBook.html', book=editedBook)
# This function is for deleting books
@app.route('/books//delete/', methods=['GET', 'POST'])
def deleteBook(book_id):
bookToDelete = session.query(Book).filter_by(id=book_id).one()
if request.method == 'POST':
return redirect(url_for('showBooks', book_id=book_id))
return render_template('deleteBook.html', book=bookToDelete)
if __name__ == '__main__':
app.debug = True

Finally, we have to create the templates: books.html, newBook.html, editBook.html and deleteBook.html. To do this, create a folder with templates in Flask templates on the same level as the app.py file. Inside it, create four files. books.html


    1. {% for book in books %}

    2. {{book.title}} by {{book.author}}

Change Delete

{% endfor %}

Now newBook.html.

Add a Book

Next, editBook.html.


And deleteBook.html.

Are you sure you want to delete {{book.title}}?


If you run app .py and go to https://localhost:4996/books in your browser, a list of books is displayed. Add a few and if it works, it looks like this: приложение, которое ведет учет книг.

App extension and conclusions

If you’ve gotten to this point, you now know a bit more about how SQLAlchemy works. It’s an important and lengthy topic, and this material only talked about the basics, so work with other CRUD operations and add new features to the application. You can add a Shelf table to the database to track your reading progress, or even implement authentication with authorization. This will make the app more scalable, and also allow other users to add their own books.

Related Posts