SQLAlchemy is renowned for its object-relational mapping (ORM) based on a design pattern, where Python classes are mapped to database tables. In practice, this means that an extra abstraction layer is added, so we use the SQLAlchemy API to talk to the database instead of issuing SQL commands. SQLAlchemy takes care of the details behind the scene. The drawback is that you have to learn the API and may have to pay a small performance penalty. In this section, you will learn how to set up SQLAlchemy, and populate and query databases with SQLAlchemy.
The following is the command to install SQLAlchemy:
$ pip install sqlalchemy
The latest version of SQLAlchemy at the time of writing was 0.9.6. The download page for SQLAlchemy is available at http://www.sqlalchemy.org/download.html with links to installers and code repositories.
SQLAlchemy also has a support page available at http://www.sqlalchemy.org/support.html. After modifying the pkg_check.py script, we can display the modules of SQLAlchemy:
sqlalchemy version 0.9.6 sqlalchemy.connectors DESCRIPTION # connectors/__init__.py # Copyright (C) 2005-2014 the SQLAlchemy authors and contributors <see AUTHORS file> # # This module is sqlalchemy.databases DESCRIPTION Include imports from the sqlalchemy.dialects package for backwards compatibility with pre 0.6 versions. PACKAGE CONTENTS DATA __ sqlalchemy.dialects DESCRIPTION # dialects/__init__.py # Copyright (C) 2005-2014 the SQLAlchemy authors and contributors <see AUTHORS file> # # This module is p sqlalchemy.engine DESCRIPTION The engine package defines the basic components used to interface DB-API modules with higher-level statement construction, conne sqlalchemy.event DESCRIPTION # event/__init__.py # Copyright (C) 2005-2014 the SQLAlchemy authors and contributors <see AUTHORS file> # # This module is part sqlalchemy.ext DESCRIPTION # ext/__init__.py # Copyright (C) 2005-2014 the SQLAlchemy authors and contributors <see AUTHORS file> # # This module is part o sqlalchemy.orm DESCRIPTION See the SQLAlchemy object relational tutorial and mapper configuration documentation for an overview of how this module is used. sqlalchemy.sql DESCRIPTION # sql/__init__.py # Copyright (C) 2005-2014 the SQLAlchemy authors and contributors <see AUTHORS file> # # This module is part o sqlalchemy.testing DESCRIPTION # testing/__init__.py # Copyright (C) 2005-2014 the SQLAlchemy authors and contributors <see AUTHORS file> # # This module is pa sqlalchemy.util DESCRIPTION # util/__init__.py # Copyright (C) 2005-2014 the SQLAlchemy authors and contributors <see AUTHORS file> # # This module is part
SQLAlchemy requires us to define a superclass as follows:
from sqlalchemy.ext.declarative import declarative_base Base = declarative_base()
In this and the following sections, we will make use of a small database with two tables. The first table defines an observation station. The second table represents sensors in the stations. Each station has zero, one, or many sensors. A station is identified by an integer ID, which is automatically generated by the database. Also, a station is identified by a name, which is unique and mandatory.
A sensor has an integer ID as well. We keep track of the last value measured by the sensor. This value can have a multiplier related to it. The setup described in this section is expressed in the alchemy_entities.py file in this book's code bundle (you don't have to run this script, but it is used by another script):
from sqlalchemy import Column, ForeignKey, Integer, String, Float
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from sqlalchemy import create_engine
from sqlalchemy import UniqueConstraint
Base = declarative_base()
class Station(Base):
__tablename__ = 'station'
id = Column(Integer, primary_key=True)
name = Column(String(14), nullable=False, unique=True)
def __repr__(self):
return "Id=%d name=%s" %(self.id, self.name)
class Sensor(Base):
__tablename__ = 'sensor'
id = Column(Integer, primary_key=True)
last = Column(Integer)
multiplier = Column(Float)
station_id = Column(Integer, ForeignKey('station.id'))
station = relationship(Station)
def __repr__(self):
return "Id=%d last=%d multiplier=%.1f station_id=%d" %(self.id, self.last, self.multiplier, self.station_id)
if __name__ == "__main__":
print "This script is used by another script. Run python alchemy_query.py"Creating the tables will be deferred to the next section. In this section, we will prepare a script, which will populate the database (you don't have to run this; it is used by a script in a later section). With a DBSession object, we can insert data into the tables. An engine is needed too, but creating the engine will also be deferred until the next section.
- Create the
DBSessionobject as follows:Base.metadata.bind = engine DBSession = sessionmaker(bind=engine) session = DBSession()
- Let's create two stations:
de_bilt = Station(name='De Bilt') session.add(de_bilt) session.add(Station(name='Utrecht')) session.commit() print "Station", de_bilt
The rows are not inserted until we commit the session. The following is printed for the first station:
Station Id=1 name=De Bilt - Similarly, insert a sensor record as follows:
temp_sensor = Sensor(last=20, multiplier=.1, station=de_bilt) session.add(temp_sensor) session.commit() print "Sensor", temp_sensor
The sensor is in the first station; therefore, we get the following printout:
Sensor Id=1 last=20 multiplier=0.1 station_id=1
The database population code can be found in the populate_db.py file in this book's code bundle (again you don't need to run this code; it's used by another script):
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from alchemy_entities import Base, Sensor, Station
def populate(engine):
Base.metadata.bind = engine
DBSession = sessionmaker(bind=engine)
session = DBSession()
de_bilt = Station(name='De Bilt')
session.add(de_bilt)
session.add(Station(name='Utrecht'))
session.commit()
print "Station", de_bilt
temp_sensor = Sensor(last=20, multiplier=.1, station=de_bilt)
session.add(temp_sensor)
session.commit()
print "Sensor", temp_sensor
if __name__ == "__main__":
print "This script is used by another script. Run python alchemy_query.py"An engine is created from a URI as follows:
engine = create_engine('sqlite:///demo.db')In this URI, we specified that we are using SQLite and the data is stored in the file demo.db. Create the station and sensor tables with the engine we just created:
Base.metadata.create_all(engine)
For SQLAlchemy queries, we need a DBSession object again, as shown in the previous section.
Select the first row in the station table:
station = session.query(Station).first()
Select all the stations as follows:
print "Query 1", session.query(Station).all()
The following will be the output:
Query 1 [Id=1 name=De Bilt, Id=2 name=Utrecht]
Select all the sensors as follows:
print "Query 2", session.query(Sensor).all()
The following will be the output:
Query 2 [Id=1 last=20 multiplier=0.1 station_id=1]
Select the first sensor, which belongs to the first station:
print "Query 3", session.query(Sensor).filter(Sensor.station == station).one()
The following will be the output:
Query 3 Id=1 last=20 multiplier=0.1 station_id=1
We can again query with the pandas read_sql() method:
print read_sql("SELECT * FROM station", engine.raw_connection())You will get the following output:
id name 0 1 De Bilt 1 2 Utrecht [2 rows x 2 columns]
Inspect the alchemy_query.py file in this book's code bundle:
from alchemy_entities import Base, Sensor, Station
from populate_db import populate
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
import os
from pandas.io.sql import read_sql
engine = create_engine('sqlite:///demo.db')
Base.metadata.create_all(engine)
populate(engine)
Base.metadata.bind = engine
DBSession = sessionmaker()
DBSession.bind = engine
session = DBSession()
station = session.query(Station).first()
print "Query 1", session.query(Station).all()
print "Query 2", session.query(Sensor).all()
print "Query 3", session.query(Sensor).filter(Sensor.station == station).one()
print read_sql("SELECT * FROM station", engine.raw_connection())
try:
os.remove('demo.db')
print "Deleted demo.db"
except OSError:
pass