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 PostCode
s and StreetName
s 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:
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.
http://www.pythoncentral.io/introductory-tutorial-python-sqlalchemy/