Introduction to SQLAlchemy

last modified July 6, 2020

This is SQLAlchemy tutorial. It covers It covers the basics of the SQLAlchemy SQL Toolkit and Object Relational Mapper. In this tutorial, we will work with the PostgreSQL, MySQL, and SQLite databases.

SQLAlchemy

The SQLAlchemy SQL Toolkit and Object Relational Mapper is a comprehensive set of tools for working with databases and Python. It provides a full suite of well-known enterprise-level persistence patterns, designed for efficient and high-performing database access. SQLAlchemy and Django's ORM are two of the most widely used object-relational mapping tools in the Python community.

The SQLAlchemy has three ways of working with database data:

Raw SQL
SQL Expression language
ORM

Unlike many other ORM (Object Reational Mapping) tools, SQLAlchemy allows to use pure SQL statements. We can always resort to raw SQL. The SQL Expression API allows you to build SQL queries using Python objects and operators. The Expression API is an abstraction of the pure SQL statements and deals with various implementation differences between databases. The SQLAlchemy Object Relational Mapper presents a method of associating user-defined Python classes with database tables. The SQLAlchemy ORM is based on the SQL Expression language.

SQLAlchemy components

SQLAlchemy consists of several components. Engine is the starting point of any SQLAlchemy application. The engine is an abstraction of the database and its API. It works with the connection pool and the Dialect component to deliver the SQL statements from the SQLAlchemy to the database. The engine is created using the create_engine function. It can be used to directly interact with a database, or can be passed to a Session object to work with the object-relational mapper.

Dialect is the system SQLAlchemy uses to communicate with various types of DBAPI implementations and databases. All dialects require that an appropriate DBAPI driver is installed. SQLAlchemy has dialects for many popular database systems including Firebird, Informix, Microsoft SQL Server, MySQL, Oracle, PostgreSQL, SQLite, or Sybase. The Dialect is created from the supplied connection string.

MetaData comprises of Python objects that describe tables and other schema-level objects. Database metadata can be expressed by explicitly naming the various components and their properties, using constructs such as Table, Column, or ForeignKey. MetaData can be easily generated by SQLAlchemy using a process called reflection.

Inside the ORM, the primary interface for persistence operations is the Session. The Session establishes all conversations with the database and represents a container for all the objects which we have loaded or associated with it during its lifespan. It provides the entry point to acquire a Query object, which sends queries to the database using the Session object’s current database connection, populating result rows into objects that are then stored in the Session.

Installations

Here we show how to install SQLAlchemy and other necessary packages on Debian-based Linux system.

$ sudo apt-get install python-pip
$ sudo pip install SQLAlchemy

With the pip Python package manager, we install SQLAlchemy.

$ sudo apt-get install python-psycopg2
$ sudo apt-get install python-mysqldb

We install the DBAPI drivers for PostgreSQL and MySQL. SQLAlchemy depends on these modules. The sqlite module is distributed with Python.

$ sudo apt-get install mysql-server

The above command install MySQL server. For additional installation instructions, you can check the MySQL Python tutorial.

$ sudo apt-get install postgresql

The above command install PostgreSQL server. For additional instructions, you can check the PostgreSQL Python tutorial.

$ sudo apt-get install sqlite3

Finally, we install the sqlite3 command line interface.

SQLAlchemy version

In the following script we check, if the installation of SQLAlchemy was successfull.

sqlalchemy_version.py

#!/usr/bin/python
# -*- coding: utf-8 -*-

import sqlalchemy

print sqlalchemy.__version__

We print the version of SQLAlchemy.

$ ./version.py 
1.0.8

The current version of SQLAlchemy used is 1.0.8.

Tables used

The following SQL scripts create tables used in this tutorial.

sqlite> .read cars.sql

For SQLite database, we read the SQL scripts with the .read command.

mysql> source cars.sql

For MySQL database, we use the source command to read the SQL scripts.

testdb=> \i cars.sql

For PostgreSQL database, we use the \i command to read the SQL scripts.

cars.sql

-- SQL for the Cars table

BEGIN TRANSACTION;
DROP TABLE IF EXISTS Cars;

CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name TEXT, Price INTEGER);
INSERT INTO Cars VALUES(1, 'Audi', 52642);
INSERT INTO Cars VALUES(2, 'Mercedes', 57127);
INSERT INTO Cars VALUES(3, 'Skoda', 9000);
INSERT INTO Cars VALUES(4, 'Volvo', 29000);
INSERT INTO Cars VALUES(5, 'Bentley', 350000);
INSERT INTO Cars VALUES(6, 'Citroen', 21000);
INSERT INTO Cars VALUES(7, 'Hummer', 41400);
INSERT INTO Cars VALUES(8, 'Volkswagen', 21600);
COMMIT;

This is the Cars table.

authors_books.sql

-- SQL for the Authors & Books tables

BEGIN TRANSACTION;
DROP TABLE IF EXISTS Books;
DROP TABLE IF EXISTS Authors;

CREATE TABLE Authors(AuthorId INTEGER PRIMARY KEY, Name TEXT);
INSERT INTO Authors VALUES(1, 'Jane Austen');
INSERT INTO Authors VALUES(2, 'Leo Tolstoy');
INSERT INTO Authors VALUES(3, 'Joseph Heller');
INSERT INTO Authors VALUES(4, 'Charles Dickens');

CREATE TABLE Books(BookId INTEGER PRIMARY KEY, Title TEXT, AuthorId INTEGER, 
    FOREIGN KEY(AuthorId) REFERENCES Authors(AuthorId));
INSERT INTO Books VALUES(1,'Emma',1);
INSERT INTO Books VALUES(2,'War and Peace',2);
INSERT INTO Books VALUES(3,'Catch XII',3);
INSERT INTO Books VALUES(4,'David Copperfield',4);
INSERT INTO Books VALUES(5,'Good as Gold',3);
INSERT INTO Books VALUES(6,'Anna Karenia',2);
COMMIT;

These are thee Authors and the Books tables.

Sources

The SQLAlchemy's documentation was used to create this tutorial.

This chapter was an introduction to the SQLAlchemy toolkit.