SQLAlchemy and Elixir

1
SQLAlchemy and Elixir (in a few minutes) Neil
Blakey-Milner http//nxsy.org/writings/ctpug-sq
lalchemy-elixir/
2
Installing SQLAlchemy

• easy_install SQLAlchemy
• Oh, and a DBAPI2 provider
• (pysqlite3 part of Python 2.5)

3
Connect to the database...

• from sqlalchemy import
• db create_engine('sqlite///tutorial.db')
• metadata BoundMetaData(db)

4
Declaring a table

• gtgtgt users_table Table('users', metadata,
• ... Column('user_id', Integer,
  primary_keyTrue),
• ... Column('user_name', String(40)),
• ... Column('password', String(10))
• ... )

5
Creating a table

• gtgtgt metadata.engine.echo True
• gtgtgt users_table.create()
• CREATE TABLE users (
• user_id INTEGER NOT NULL,
• user_name VARCHAR(40),
• password VARCHAR(10),
• PRIMARY KEY (user_id)
• )

6
Declaring a table from DB

• users_table Table('users', metadata,
  autoloadTrue)

7
Inserting into a table

• gtgtgt i users_table.insert()
• gtgtgt i.execute(user_name'Mary',
  password'secure')
• INSERT INTO users (user_name, password) VALUES
  (?, ?)
• 'Mary', 'secure'
• COMMIT

8
Inserting multiple lines

• gtgtgt i.execute('user_name''Tom',
  'user_name''Fred', 'user_name''Harry')
• INSERT INTO users (user_name) VALUES (?)
• 'Tom', 'Fred', 'Harry'
• COMMIT

9
Selecting

• gtgtgt r users_table.select(users_table.c.user_name
  'Harry').execute()
• SELECT users.user_id, users.user_name,
  users.password
• FROM users
• WHERE users.user_name ?
• 'Harry'
• gtgtgt print r.fetchone()
• (4, u'Harry', None)

10
Table relationships

• gtgtgt email_addresses_table Table('email_addresses
  ',
• ... metadata,
• ... Column('address_id', Integer,
  primary_keyTrue),
• ... Column('email_address', String(100),
  nullableFalse),
• ... Column('user_id', Integer,
• ... ForeignKey('users.user_id')))

11
Table relationships (2)

• gtgtgt email_addresses_table.create()
• CREATE TABLE email_addresses (
• address_id INTEGER NOT NULL,
• email_address VARCHAR(100) NOT NULL,
• user_id INTEGER,
• PRIMARY KEY (address_id),
• FOREIGN KEY(user_id) REFERENCES users
  (user_id)
• )

12
Selecting across tables (1)

• gtgtgt email_addresses_table.insert().execute(
• ... 'email_address''tom_at_tom.com',
• ... 'user_id'2,
• ... 'email_address''mary_at_mary.com',
• ... 'user_id'1)
• INSERT INTO email_addresses (email_address,
  user_id) VALUES (?, ?)
• 'tom_at_tom.com', 2, 'mary_at_mary.com', 1
• COMMIT

13
Selecting across tables (2)

• gtgtgt r users_table.join(
• ... email_addresses_table
• ... ).select(
• ... order_by users_table.c.user_id
• ... ).execute()
• SELECT users.user_id, users.user_name,
  users.password, email_addresses.address_id,
  email_addresses.email_address, email_addresses.use
  r_id
• FROM users JOIN email_addresses ON users.user_id
  email_addresses.user_id ORDER BY users.user_id

14
Selecting across tables (3)

• gtgtgt print row for row in r
• (1, u'Mary', u'secure', 2, u'mary_at_mary.com', 1),
• (2, u'Tom', None, 1, u'tom_at_tom.com', 2)

15
Mappers

• SQLAlchemy allows you to associate a Table object
  with a Python class

gtgtgt class User(object) ... pass gtgtgt
mapper(User, users_table) gtgtgt ed User() gtgtgt
ed.user_name 'Ed' gtgtgt ed.password
'edspassword' gtgtgt session.save(ed)
16
Elixir

• Elixir allows you to combine the steps, and use a
  declarative domain specific language to define
  the table.

class User(Entity) has_field('user_id',
Integer, primary_key True)
has_field('user_name', String(40))
has_field('password', String(10))
17
Unit of work (1)

• gtgtgt mary User.get_by(user_name "Mary")
• gtgtgt harry User.get_by(user_name "Harry")
• gtgtgt fred User.get_by(user_name Fred)
• gtgtgt mary.password marysnewpassword
• gtgtgt harry.password harrysnewpassword
• gtgtgt fred.delete()
• gtgtgt ed User(user_name Ed, password ed)

18
Unit of Work (2)

• gtgtgt objectstore.flush()
• BEGINUPDATE user SET password? WHERE
  user.user_id ?'marysnewpassword', 1UPDATE
  user SET password? WHERE user.user_id
  ?'harrysnewpassword', 2INSERT INTO user
  (user_name, password) VALUES (?, ?)'ed',
  'ed'DELETE FROM user WHERE user.user_id
  ?3COMMIT

19
Elixir Relationships (1)

• class Movie(Entity)
• has_field('title', Unicode(30))
• has_field('year', Integer)
• has_field('description', Unicode)
• belongs_to('genre', of_kind'Genre')
  
• def __repr__(self)
• return 'ltMovie "s" (d)gt' (self.title,
  self.year)
• class Genre(Entity)
• has_field('name', Unicode(15))
• has_many('movies', of_kind'Movie')
  
• def __repr__(self)
• return 'ltGenre "s"gt' self.name

20
Elixir Relationships (2)

• scifi Genre('Science Fiction')
• action Genre('Action')
• alien Movie(title"Alien", year1979)
• starwars Movie(title"Star Wars", year1977)
• brunner Movie(title"Blade Runner", year1982)
• frcon Movie(title"The French Connection",
  year1971)
• prof Movie(title"The Professional", year1994)
• scifi.movies.append(alien)
• scifi.movies.append(starwars)
• scifi.movies.append(brunner)
• action.movies.append(frcon)
• action.movies.append(prof)

21
Elixir Relationships (3)

• CREATE TABLE genre (
• name VARCHAR(15),
• id INTEGER NOT NULL,
• PRIMARY KEY (id)
• )
• CREATE TABLE movie (
• id INTEGER NOT NULL,
• year INTEGER,
• description TEXT,
• title VARCHAR(30),
• genre_id INTEGER,
• PRIMARY KEY (id),
• CONSTRAINT movie_genre_fk FOREIGN
  KEY(genre_id) REFERENCES genre (id)
• )
• CREATE INDEX ix_movie_genre_id ON movie
  (genre_id)

22
Elixir Relationships (4)

• BEGIN
• INSERT INTO genre (name) VALUES (?)
• 'Science Fiction'
• INSERT INTO genre (name) VALUES (?)
• 'Action'
• INSERT INTO movie (year, description, title,
  genre_id) VALUES (?, ?, ?, ?)
• 1979, None, 'Alien', 1
• INSERT INTO movie (year, description, title,
  genre_id) VALUES (?, ?, ?, ?)
• 1977, None, 'Star Wars', 1
• INSERT INTO movie (year, description, title,
  genre_id) VALUES (?, ?, ?, ?)
• 1982, None, 'Blade Runner', 1
• INSERT INTO movie (year, description, title,
  genre_id) VALUES (?, ?, ?, ?)
• 1971, None, 'The French Connection', 2
• INSERT INTO movie (year, description, title,
  genre_id) VALUES (?, ?, ?, ?)
• 1994, None, 'The Professional', 2
• COMMIT

23
Elixir Relationships (5)

• class Movie(Entity)
• has_field('title', Unicode(30))
• has_field('year', Integer)
• has_field('description', Unicode)
• has_and_belongs_to_many('genre',
  of_kind'Genre')
• def __repr__(self)
• return 'ltMovie "s" (d)gt' (self.title,
  self.year)
• class Genre(Entity)
• has_field('name', Unicode(15))
• has_and_belongs_to_many('movies',
  of_kind'Movie')
• def __repr__(self)
• return 'ltGenre "s"gt' self.name

24
Elixir Relationships (6)

• scifi Genre(name 'Science Fiction')
• action Genre(name 'Action')
• thriller Genre(name 'Thriller')
• crime Genre(name 'Crime')
• alien Movie(title"Alien", year1979)
• starwars Movie(title"Star Wars", year1977)
• brunner Movie(title"Blade Runner", year1982)
• frcon Movie(title"The French Connection",
• year1971)
• prof Movie(title"The Professional", year1994)
• manch Movie(title"Manchurian Candidate",
  year1962)

25
Elixir Relationships (7)

• scifi.movies.append(alien)
• scifi.movies.append(starwars)
• scifi.movies.append(brunner)
• thriller.movies.append(alien)
• frcon.genres.append(action)
• frcon.genres.append(crime)
• frcon.genres.append(thriller)
• prof.genres.extend(action, crime)
• manch.genres.extend(action, thriller)

26
Elixir Relationship (8)

• CREATE TABLE genre (
• name VARCHAR(15), id INTEGER NOT NULL,
• PRIMARY KEY (id)
• )
• CREATE TABLE movie (
• id INTEGER NOT NULL, year INTEGER,
• description TEXT, title VARCHAR(30),
• PRIMARY KEY (id)
• )
• CREATE TABLE movie_genres__genre_movies (
• genre_id INTEGER, movie_id INTEGER,
• CONSTRAINT genre_movies_fk FOREIGN
  KEY(genre_id)
• REFERENCES genre (id),
• CONSTRAINT movie_genres_fk FOREIGN
  KEY(movie_id)
• REFERENCES movie (id)
• )

27
Elixir Relationship (9)

• BEGIN
• INSERT INTO movie (year, description, title)
  VALUES (?, ?, ?)
• 1979, None, 'Alien'
• ...
• INSERT INTO movie (year, description, title)
  VALUES (?, ?, ?)
• 1994, None, 'Manchurian Candidate'
• INSERT INTO genre (name) VALUES (?)
• 'Science Fiction'
• ...
• INSERT INTO genre (name) VALUES (?)
• 'Crime'
• INSERT INTO movie_genres__genre_movies (genre_id,
  movie_id) VALUES (?, ?)
• 2, 4, 4, 4, 3, 4, 2, 5, 4, 5, 2, 6,
  3, 6
• INSERT INTO movie_genres__genre_movies (genre_id,
  movie_id) VALUES (?, ?)
• 3, 1, 1, 1, 1, 2, 1, 3
• COMMIT

28
More queries (1)

• gtgtgt Movie.select(Movie.join_to('genres')
  (Genre.c.name "Science Fiction"))
• SELECT movie.title AS movie_title,
  movie.description AS movie_description, movie.id
  AS movie_id, movie.year AS movie_year
• FROM movie, movie_genres__genre_movies, genre
• WHERE (movie.id movie_genres__genre_movies.movie
  _id AND genre.id movie_genres__genre_movies.genr
  e_id) AND genre.name ? ORDER BY movie.oid
• 'Science Fiction'
• ltMovie "Alien" (1979)gt,
• ltMovie "Star Wars" (1977)gt,
• ltMovie "Blade Runner" (1982)gt

29
More queries (2)

• gtgtgt Movie.select(Movie.join_to('genres')
  (Genre.c.name "Science Fiction")
  (Movie.c.year lt 1980))
• SELECT movie.title AS movie_title,
  movie.description AS movie_description, movie.id
  AS movie_id, movie.year AS movie_year
• FROM movie, movie_genres__genre_movies, genre
• WHERE ((movie.id movie_genres__genre_movies.movi
  e_id AND genre.id movie_genres__genre_movies.gen
  re_id) AND genre.name ?) AND movie.year lt ?
  ORDER BY movie.oid
• 'Science Fiction', 1980
• ltMovie "Alien" (1979)gt, ltMovie "Star Wars"
  (1977)gt

30
More queries (3)

• gtgtgt from sqlalchemy import and_
• gtgtgt Movie.select(and_(Movie.join_to('genres'),
  Genre.c.name "Science Fiction",
  Movie.c.year.between(1978, 1982)))
• SELECT movie.title AS movie_title,
  movie.description AS movie_description, movie.id
  AS movie_id, movie.year AS movie_year
• FROM movie, movie_genres__genre_movies, genre
• WHERE (movie.id movie_genres__genre_movies.movie
  _id AND genre.id movie_genres__genre_movies.genr
  e_id) AND genre.name ? AND movie.year BETWEEN ?
  AND ? ORDER BY movie.oid
• 'Science Fiction', 1978, 1982
• ltMovie "Alien" (1979)gt, ltMovie "Blade Runner"
  (1982)gt

31
More queries (4)

• def getTags(limit None, min None)
• s select(Tag.c.tag_id, func.count(Post.c.po
  st_id), Tag.join_to('posts'),
  group_byTag.c.tag_id,
• order_bydesc(func.count(Post.c.post_id))
  )
• if limit
• s.limit limit
• if min
• s.having func.count(Post.c.post_id) gt
  min
• r s.execute()
• for tag_id, num in r
• yield Tag.get(tag_id), num

32
Other features

• Deferred loading of class attributes (ie, columns
  in the table)
• Inheritance single and multiple, polymorphic or
  not
• Mapping a class against an arbitrary select
  clause
• Multiple column primary keys
• Ordered lists of associated items
• Extensible association relations