Flask SQLAlchemy Tutorial

Introduction

In this article, we’re going to learn the basics of SQLAlchemy by creating a data-driven web application using Flask, a Python framework. We’ll build a minimal Flask app that keeps track of your book collection.

At its most simple, the app will allow users to create new books, read all the existing books, update the books, and delete them. These operations – create, read, update, and delete – are more commonly known as “CRUD” and form the basis of nearly all web applications. we’ll more learn about CRUD operations later in the article.

But before we start playing with CRUD, let’s understand other parts of the application, beginning with SQLAlchemy.

What is SQLAlchemy?

Note that we have a Flask extension flask-sqlalchemy, which simplifies the use of SQLAlchemy by providing useful defaults and extra helpers, making it easier to accomplish common tasks. For this article, we’ll only be using plain SQLAlchemy – just so we have a basic understanding of SQLAlchemy before adding any extensions.

According to their website, “SQLAlchemy is the Python SQL toolkit and the Object Relational Mapper that gives application developers the full power and flexibility of SQL”.

After reading the definition above, the first question that pops up is what an Object Relational Mapper is. Object Relational Mapper, also known as ORM, is a technique used to write database queries using the object-oriented paradigm of your preferred language (in this case, Python).

In even simpler terms, an ORM can be thought of as a translator that translates code from one set of abstractions to another. In our case – from Python to SQL.

There are a lot of different reasons to use the ORM besides not having to craft SQL strings. Some of which are:

  • Speeding up web development since we don’t have to switch back and forth between writing Python and SQL
  • Eliminating repetitive code
  • Streamlining the workflow and queries the data more efficiently
  • Abstracting away the database system so switching between different databases becomes smooth
  • Generating boilerplate code for the basic CRUD operations

Let’s dig a little deeper.

Why do we use the ORM when we can write database queries using raw SQL? When we write queries using raw SQL, we pass them to our database as strings. The following query is written in raw SQL:

#imports sqlite
import sqlite3

#connects it to the books-collection database
conn = sqlite3.connect('books-collection.db')

#creates the cursor
c = conn.cursor()

#execute the query which creates the table called books with id and name
#as the columns
c.execute('''
          CREATE TABLE books
          (id INTEGER PRIMARY KEY ASC,
	     name varchar(250) NOT NULL)
          ''' )

#executes the query which inserts values in the table
c.execute("INSERT INTO books VALUES(1, 'The Bell Jar')")

#commits the executions
conn.commit()

#closes the connection
conn.close()

Now, there’s absolutely nothing wrong with using raw SQL to talk to databases unless we make a mistake in the query, such as a typo or connecting to a database that doesn’t exist, or trying to access a non-existent table. The Python compiler wouldn’t be able to help us.

SQLAlchemy is one of the many Python object-relational mapper implementations out there. If we’re working on a small-scale application, using raw SQL might work – but if we’re working on a large-scale data-driven website, using raw SQL can end up being complex and error-prone.

To work around this issue, we can write our queries as objects using an ORM instead of writing them as strings. ORM converts our code written in Python (or any other language) to SQL statements. It’s that simple!

Enough with the theory. Let’s get into the good stuff and start writing some code!

Creating a database with SQLAlchemy

Let’s create a file that will set up and configure our database. We can name this file anything, but for this article, let’s name it database_setup.py.

Back to Top