Introduction to Object-Relational Mappings - Python & SQL Alchemy

49 Pages·2012·1.98 MB·English

by  Devert Alexandre

UNIVERSITY OF SCIENCE AND TECHNOLOGY OF CHINA SCHOOL OF SOFTWARE ENGINEERING OF USTC
Introduction to Object-Relational Mappings
Python & SQL Alchemy
Devert Alexandre
December22,2012—Slide1/39
UNIVERSITY OF SCIENCE AND TECHNOLOGY OF CHINA SCHOOL OF SOFTWARE ENGINEERING OF USTC
Examples
For the examples, we will consider we built a database for a
famous delivery company, from the Futurama animated serie
DevertAlexandre—IntroductiontoObject-RelationalMappings—Slide2/39
UNIVERSITY OF SCIENCE AND TECHNOLOGY OF CHINA SCHOOL OF SOFTWARE ENGINEERING OF USTC
Mapping an object to a table
Let’s consider the crew table
A crew is an employee of the company, working aboard a ship
DevertAlexandre—IntroductiontoObject-RelationalMappings—Slide3/39
UNIVERSITY OF SCIENCE AND TECHNOLOGY OF CHINA SCHOOL OF SOFTWARE ENGINEERING OF USTC
Mapping an object to a table
From the SQL side, the crew table will look like this
name SQL type
id INTEGER
first name VARCHAR(256)
last name VARCHAR(256)
The primary key for this table will be the id attribute.
DevertAlexandre—IntroductiontoObject-RelationalMappings—Slide4/39
UNIVERSITY OF SCIENCE AND TECHNOLOGY OF CHINA SCHOOL OF SOFTWARE ENGINEERING OF USTC
Mapping an object to a table
In Python, we will define a Crew class as following
class Crew(Base):
tablename = ’crew’
id = Column(Integer, primary key = True)
first name = Column(String(256))
last name = Column(String(256))
def init (self , first name, last name):
self.first name = first name
self.last name = last name
The Crew class is mapping the crew table. This is specified
with the tablename static class member.
DevertAlexandre—IntroductiontoObject-RelationalMappings—Slide5/39
UNIVERSITY OF SCIENCE AND TECHNOLOGY OF CHINA SCHOOL OF SOFTWARE ENGINEERING OF USTC
Mapping an object to a table
In Python, we will define a Crew class as following
class Crew(Base):
tablename = ’crew’
id = Column(Integer, primary key = True)
first name = Column(String(256))
last name = Column(String(256))
def init (self , first name, last name):
self.first name = first name
self.last name = last name
The Crew class should derivate from our special base class :
Base
DevertAlexandre—IntroductiontoObject-RelationalMappings—Slide5/39
UNIVERSITY OF SCIENCE AND TECHNOLOGY OF CHINA SCHOOL OF SOFTWARE ENGINEERING OF USTC
Mapping an object to a table
In Python, we will define a Crew class as following
class Crew(Base):
tablename = ’crew’
id = Column(Integer, primary key = True)
first name = Column(String(256))
last name = Column(String(256))
def init (self , first name, last name):
self.first name = first name
self.last name = last name
Unlike with usual Python code, we have to declare the class
member variables. This is done with static class members,
and special function from SQL Alchemy.
DevertAlexandre—IntroductiontoObject-RelationalMappings—Slide5/39
UNIVERSITY OF SCIENCE AND TECHNOLOGY OF CHINA SCHOOL OF SOFTWARE ENGINEERING OF USTC
Mapping an object to a table
In Python, we will define a Crew class as following
class Crew(Base):
tablename = ’crew’
id = Column(Integer, primary key = True)
first name = Column(String(256))
last name = Column(String(256))
def init (self , first name, last name):
self.first name = first name
self.last name = last name
id being a primary key, it will be and should be generated by
the database, and only the database.
DevertAlexandre—IntroductiontoObject-RelationalMappings—Slide5/39
UNIVERSITY OF SCIENCE AND TECHNOLOGY OF CHINA SCHOOL OF SOFTWARE ENGINEERING OF USTC
Mapping an object to a table
To make this work, we need to import Column, String and
Integer from the SQLAlchemy module, as well as Base from
our database module
from sqlalchemy import Column, Integer, String
from database import Base
A good practice is to put all mapped class in one module,
let’s call it model.py
DevertAlexandre—IntroductiontoObject-RelationalMappings—Slide6/39
UNIVERSITY OF SCIENCE AND TECHNOLOGY OF CHINA SCHOOL OF SOFTWARE ENGINEERING OF USTC
Adding objects
To add objects to the database
1 Create a database, or connect to it there is one already
2 Create a session object
3 Add objects
4 Commit the session
DevertAlexandre—IntroductiontoObject-RelationalMappings—Slide7/39