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.
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
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 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.
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
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
$ 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.
In the following script we check, if the installation of SQLAlchemy was successfull.
#!/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.
The following SQL scripts create tables used in this tutorial.
sqlite> .read cars.sql
For SQLite database, we read the SQL scripts with the
mysql> source cars.sql
For MySQL database, we use the
source command to read the SQL
testdb=> \i cars.sql
For PostgreSQL database, we use the
\i command to read
the SQL scripts.
-- 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
-- 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
The SQLAlchemy's documentation was used to create this tutorial.
This chapter was an introduction to the SQLAlchemy toolkit.