This article is part 1 of 10 in the series Python SQLAlchemy Tutorial

Last Updated: Thursday 12th December 2013

Python's SQLAlchemy and Object-Relational Mapping

A common task when programming any web service is the construction of a solid database backend. In the past, programmers would write raw SQL statements, pass them to the database engine and parse the returned results as a normal array of records. Nowadays, programmers can write Object-relational mapping (ORM) programs to remove the necessity of writing tedious and error-prone raw SQL statements that are inflexible and hard-to-maintain.

ORM is a programming technique for converting data between incompatible type systems in object-oriented programming languages. Usually, the type system used in an OO language such as Python contains types that are non-scalar, namely that those types cannot be expressed as primitive types such as integers and strings. For example, a Person object may have a list of Address objects and a list of PhoneNumber objects associated with it. In turn, an Address object may have a PostCode object, a StreetName object and a StreetNumber object associated with it. Although simple objects such as PostCodes and StreetNames can be expressed as strings, a complex object such as a Address and a Person cannot be expressed using only strings or integers. In addition, these complex objects may also include instance or class methods that cannot be expressed using a type at all.

In order to deal with the complexity of managing objects, people developed a new class of systems called ORM. Our previous example can be expressed as an ORM system with a Person class, a Address class and a PhoneNumber class, where each class maps to a table in the underlying database. Instead of writing tedious database interfacing code yourself, an ORM takes care of these issues for you while you can focus on programming the logics of the system.

The Old Way of Writing Database Code in Python

We're going to use the library sqlite3 to create a simple database with two tables Person and Address in the following design:

SQLAlchemy Person and Address DDL

Note: If you want to checkout how to use SQLite for Python, you might want to have a look at the SQLite in Python series.

In this design, we have two tables person and address and address.person_id is a foreign key to the person table. Now we write the corresponding database initialization code in a file sqlite_ex.py.

Notice that we have inserted one record into each table. Run the following command in your shell.

Now we can query the database example.db to fetch the records. Write the following code in a file sqlite_q.py.

And run the following statement in your shell.

In the previous example, we used an sqlite3 connection to commit the changes to the database and a sqlite3 cursor to execute raw SQL statements to CRUD (create, read, update and delete) data in the database. Although the raw SQL certainly gets the job done, it is not easy to maintain these statements. In the next section, we're going to use SQLAlchemy's declarative to map the Person and Address tables into Python classes.

Python's SQLAlchemy and Declarative

There are three most important components in writing SQLAlchemy code:

  • A Table that represents a table in a database.
  • A mapper that maps a Python class to a table in a database.
  • A class object that defines how a database record maps to a normal Python object.

Instead of having to write code for Table, mapper and the class object at different places, SQLAlchemy's declarative allows a Table, a mapper and a class object to be defined at once in one class definition.

The following declarative definitions specify the same tables defined in sqlite_ex.py:

Save the previous code into a file sqlalchemy_declarative.py and run the following command in your shell:

Now a new sqlite3 db file called "sqlalchemy_example.db" should be created in your current directory. Since the sqlalchemy db is empty right now, let's write some code to insert records into the database:

Save the previous code into a local file sqlalchemy_insert.py and run the command python sqlalchemy_insert.py in your shell. Now we have one Person object and one Address object stored in the database. Let's query the database using the classes defined in sqlalchemy_declarative.py:

Summary of Python's SQLAlchemy

In this article, we learned how to write database code using SQLAlchemy's declaratives. Compared to writing the traditional raw SQL statements using sqlite3, SQLAlchemy's code is more object-oriented and easier to read and maintain. In addition, we can easily create, read, update and delete SQLAlchemy objects like they're normal Python objects.

You might be wondering that if SQLAlchemy's just a thin layer of abstraction above the raw SQL statements, then it's not very impressive and you might prefer to writing raw SQL statements instead. In the following articles of this series, we're going to investigate various aspects of SQLAlchemy and compare it against raw SQL statements when they're both used to implement the same functionalities. I believe at the end of this series, you will be convinced that SQLAlchemy is superior to writing raw SQL statements.

About The Author

  • Josh

    Great tutorial,
    Thank you!

  • Sebasti├ín

    Amazing! Thanks from Chile!