Databases and SQL

Databases are used to store data. SQLite is a simple database engine which uses SQL for queries. SQL (Structured Query Language) is a language used to interface with databases. A database consists of tables, which consist of columns called fields and rows called records. Each column has a fixed data type and each row is a single entry into the database. For example, users could be a table, username could be a field with type text and each user would have a record.

Data types

There are 4 data types in SQLite: text, integer, real and blob (binary data).

Basic operations

Create table:

CREATE TABLE users(
  id INTEGER PRIMARY KEY,
  username TEXT UNIQUE NOT NULL,
  password TEXT NOT NULL
);

Note: A column with type INTEGER PRIMARY KEY will auto-increment.
Delete table:

DROP TABLE users;

Insert record:

INSERT INTO users (username, password) VALUES ('bob', '513e35...0e7252');

Select record:

SELECT username, password FROM users WHERE id = 1;

Update record:

UPDATE users SET username = 'steve' WHERE id = 1;

Delete record:

DELETE FROM users WHERE id = 1;

Additional parts

ORDER BY column - Ascending order the results by column.
ORDER BY column DESC - Descending order the results by column.
LIMIT n - Give the first $n$ results.

Relationships

It is possible to create relationships between tables using foreign keys. This is a field in one table which refers to the primary key in another.

SQLAlchemy

SQLAlchemy is a Python ORM (Object-Relational Mapping) for working with databases as objects in Python instead of raw SQL. There is a wrapper for Flask, flask-sqlalchemy, which can be used to work with databases in Flask applications.

Integrating into a Flask app

To add a database to an app, do the following:

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///dbname.db"
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
db = SQLAlchemy(app)

The app is now linked to the database but needs models to work with it.

Executing raw SQL

It is still possible to execute raw SQL queries through SQLAlchemy using db.session.execute.

from sqlalchemy import text
db.session.execute(sqlalchemy.text("SELECT * FROM users WHERE id = :val"), {"val": 1})

Database operations

Using the same examples as for raw SQL, the models file would be

from app import db

class User(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    username = db.Column(db.Text, nullable = False, unique = True)
    password = db.Column(db.Text, nullable = False)

This can then be created by running db.create_all().

A record can be inserted by creating an instance of the model class, then using db.session.add:

new_user = User(username = "bob", password = "513e35...0e7252")
db.session.add(new_user)
db.session.commit()

For changes to be saved, you need to commit them using db.session.commit().

There are two ways to get a record from the database. The first is using db.session.query:
db.session.query(User).get(1). This gets the User object with id 1. The other, simpler method is using User.query.get(1) which returns the same thing.

To get records by something other than id you can use filter_by. User.query.filter_by(name = "bob") is equivalent to SELECT * FROM users WHERE name = ’bob’. This will return a list of results even if there is only one result or None if there are none. User.query.filter_by(name = "bob").first() will return the first record that matches and User.query.filter_by(name = "bob").all() will return all matching records.
To get all records in a table use User.query.all()

A record can be updated by getting it, then modifying it's attributes and commiting.

user = User.query.get(1)
user.name = "steve"
db.session.commit()

A record can be deleted by getting it and using db.session.delete.

user = User.query.get(1)
db.session.delete(user)
db.session.commit()

Relationships

Suppose we have two tables User and Post as follows:

from app import db

class User(db.Model):
  id = db.Column(db.Integer, primary_key = True)
  username = db.Column(db.Text, nullable = False, unique = True)
  password = db.Column(db.Text, nullable = False)
  
class Post(db.Model):
  id = db.Column(db.Integer, primary_key = True)
  title = db.Column(db.Text, nullable = False)
  content = db.Column(db.Text, nullable = False)

To create a relationship between these tables we can use db.ForeignKey and db.relationship.

from app import db

class User(db.Model):
  id = db.Column(db.Integer, primary_key = True)
  username = db.Column(db.Text, nullable = False, unique = True)
  password = db.Column(db.Text, nullable = False)
  posts = db.relationship("post", backref = "author", lazy = "dynamic")
  
class Post(db.Model):
  id = db.Column(db.Integer, primary_key = True)
  title = db.Column(db.Text, nullable = False)
  content = db.Column(db.Text, nullable = False)
  author_id = db.Column(db.Integer, db.ForeignKey("user.id"))

Now Post has a column containing the author's id which is marked as a foreign key which is the id field of the user table.
The relationship gives User a new attribute posts which is a list of all of the posts whose author_ids are the user's. The backref means that Post now has an author attribute so you can easily get it's author's User object. lazy = dynamic just means that a query object is returned instead of a list of objects so you can then call something like filter_by on it.

Computer Science Notes