An object relational mapper maps a relational database system to objects. If you are unfamiliar with object orientated programming, read this tutorial first. The ORM is independent of which relational database system is used. From within Python, you can talk to objects and the ORM will map it to the database. In this article you will learn to use the SqlAlchemy ORM.
What an ORM does is shown in an illustration below:
Creating a class to feed the ORM
We create the file tabledef.py. In this file we will define a class Student. An abstract visualization of the class below:
Observe we do not define any methods, only variables of the class. This is because we will map this class to the database and thus won’t need any methods.
This is the contents of tabledef.py:
from sqlalchemy import *
The ORM created the database file tabledef.py. It will output the SQL query to the screen, in our case it showed:
CREATE TABLE student (
Thus, while we defined a class, the ORM created the database table for us. This table is still empty.
Inserting data into the database
The database table is still empty. We can insert data into the database using Python objects. Because we use the SqlAlchemy ORM we do not have to write a single SQL query. We now simply create Python objects that we feed to the ORM. Save the code below as dummy.py
The ORM will map the Python objects to a relational database. This means you do not have any direct interaction from your application, you simply interact with objects. If you open the database with SQLiteman or an SQLite database application you’ll find the table has been created:
Query the data
We can query all items of the table using the code below. Note that Python will see every record as a unique object as defined by the Students class. Save the code as demo.py
On execution you will see:
To select a single object use the filter() method. A demonstration below:
Finally, if you do not want the ORM the output any of the SQL queries change the create_engine statement to:
engine = create_engine('sqlite:///student.db', echo=False)