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:
ORM Object Relational Mapping. We communicate with the database using the ORM and only use Python objects and classes.
Related course:
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:
Class definition
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 *
from sqlalchemy import create_engine, ForeignKey
from sqlalchemy import Column, Date, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref
engine = create_engine('sqlite:///student.db', echo=True)
Base = declarative_base()
class Student(Base):
""""""
__tablename__ = "student"
id = Column(Integer, primary_key=True)
username = Column(String)
firstname = Column(String)
lastname = Column(String)
university = Column(String)
def __init__(self, username, firstname, lastname, university):
""""""
self.username = username
self.firstname = firstname
self.lastname = lastname
self.university = university
Base.metadata.create_all(engine)
Execute with:
python tabledef.py
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 (
id INTEGER NOT NULL,
username VARCHAR,
firstname VARCHAR,
lastname VARCHAR,
university VARCHAR,
PRIMARY KEY (id)
)
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
import datetime
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from tabledef import *
engine = create_engine('sqlite:///student.db', echo=True)
Session = sessionmaker(bind=engine)
session = Session()
user = Student("james","James","Boogie","MIT")
session.add(user)
user = Student("lara","Lara","Miami","UU")
session.add(user)
user = Student("eric","Eric","York","Stanford")
session.add(user)
session.commit()
Execute with:
python 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:
Data in database table.
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
import datetime
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from tabledef import *
engine = create_engine('sqlite:///student.db', echo=True)
Session = sessionmaker(bind=engine)
session = Session()
for student in session.query(Student).order_by(Student.id):
print student.firstname, student.lastname
On execution you will see:
James Boogie
Lara Miami
Eric York
To select a single object use the filter() method. A demonstration below:
import datetime
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from tabledef import *
engine = create_engine('sqlite:///student.db', echo=True)
Session = sessionmaker(bind=engine)
session = Session()
for student in session.query(Student).filter(Student.firstname == 'Eric'):
print student.firstname, student.lastname
Output:
Eric York
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)
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4