slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
SQLAlchemy and Elixir (in a few minutes) Neil Blakey-Milner PowerPoint Presentation
Download Presentation
SQLAlchemy and Elixir (in a few minutes) Neil Blakey-Milner

Loading in 2 Seconds...

play fullscreen
1 / 32

SQLAlchemy and Elixir (in a few minutes) Neil Blakey-Milner - PowerPoint PPT Presentation


  • 171 Views
  • Uploaded on

SQLAlchemy and Elixir (in a few minutes) Neil Blakey-Milner http://nxsy.org/writings/ctpug-sqlalchemy-elixir/. Installing SQLAlchemy. easy_install SQLAlchemy Oh, and a DBAPI2 provider (pysqlite3 part of Python 2.5). Connect to the database. from sqlalchemy import *

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'SQLAlchemy and Elixir (in a few minutes) Neil Blakey-Milner' - benedict-buckner


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

SQLAlchemy and Elixir

(in a few minutes)

Neil Blakey-Milner

http://nxsy.org/writings/ctpug-sqlalchemy-elixir/

installing sqlalchemy
Installing SQLAlchemy
  • easy_install SQLAlchemy
  • Oh, and a DBAPI2 provider
  • (pysqlite3 part of Python 2.5)
connect to the database
Connect to the database...

from sqlalchemy import *

db = create_engine('sqlite:///tutorial.db')

metadata = BoundMetaData(db)

declaring a table
Declaring a table

>>> users_table = Table('users', metadata,

... Column('user_id', Integer, primary_key=True),

... Column('user_name', String(40)),

... Column('password', String(10))

... )

creating a table
Creating a table

>>> metadata.engine.echo = True

>>> users_table.create()

CREATE TABLE users (

user_id INTEGER NOT NULL,

user_name VARCHAR(40),

password VARCHAR(10),

PRIMARY KEY (user_id)

)

declaring a table from db
Declaring a table from DB

users_table = Table('users', metadata, autoload=True)

inserting into a table
Inserting into a table

>>> i = users_table.insert()

>>> i.execute(user_name='Mary', password='secure')

INSERT INTO users (user_name, password) VALUES (?, ?)

['Mary', 'secure']

COMMIT

inserting multiple lines
Inserting multiple lines

>>> i.execute({'user_name':'Tom'}, {'user_name':'Fred'}, {'user_name':'Harry'})

INSERT INTO users (user_name) VALUES (?)

[['Tom'], ['Fred'], ['Harry']]

COMMIT

selecting
Selecting

>>> 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']

>>> print r.fetchone()

(4, u'Harry', None)

table relationships
Table relationships

>>> email_addresses_table = Table('email_addresses',

... metadata,

... Column('address_id', Integer, primary_key=True),

... Column('email_address', String(100), nullable=False),

... Column('user_id', Integer,

... ForeignKey('users.user_id')))

table relationships 2
Table relationships (2)

>>> 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)

)

selecting across tables 1
Selecting across tables (1)

>>> email_addresses_table.insert().execute(

... {'email_address':'tom@tom.com',

... 'user_id':2},

... {'email_address':'mary@mary.com',

... 'user_id':1})

INSERT INTO email_addresses (email_address, user_id) VALUES (?, ?)

[['tom@tom.com', 2], ['mary@mary.com', 1]]

COMMIT

selecting across tables 2
Selecting across tables (2)

>>> 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.user_id

FROM users JOIN email_addresses ON users.user_id = email_addresses.user_id ORDER BY users.user_id

selecting across tables 3
Selecting across tables (3)

>>> print [row for row in r]

[(1, u'Mary', u'secure', 2, u'mary@mary.com', 1),

(2, u'Tom', None, 1, u'tom@tom.com', 2)]

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

>>> class User(object):

... pass

>>> mapper(User, users_table)

>>> ed = User()

>>> ed.user_name = 'Ed'

>>> ed.password = 'edspassword'

>>> session.save(ed)

elixir
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))

unit of work 1
Unit of work (1)

>>> mary = User.get_by(user_name = "Mary")

>>> harry = User.get_by(user_name = "Harry")

>>> fred = User.get_by(user_name = “Fred”)

>>> mary.password = “marysnewpassword”

>>> harry.password = “harrysnewpassword”

>>> fred.delete()

>>> ed = User(user_name = “Ed”, password = “ed”)

unit of work 2
Unit of Work (2)

>>> objectstore.flush()

BEGINUPDATE user SET password=? WHERE user.user_id = ?['marysnewpassword', 1]UPDATE user SET password=? WHERE user.user_id = ?['harrysnewpassword', 2]INSERT INTO user (user_name, password) VALUES (?, ?)['ed', 'ed']DELETE FROM user WHERE user.user_id = ?[3]COMMIT

elixir relationships 1
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 '<Movie "%s" (%d)>' % (self.title, self.year)

class Genre(Entity):

has_field('name', Unicode(15))

has_many('movies', of_kind='Movie')

def __repr__(self):

return '<Genre "%s">' % self.name

elixir relationships 2
Elixir Relationships (2)

scifi = Genre('Science Fiction')

action = Genre('Action')

alien = Movie(title="Alien", year=1979)

starwars = Movie(title="Star Wars", year=1977)

brunner = Movie(title="Blade Runner", year=1982)

frcon = Movie(title="The French Connection", year=1971)

prof = Movie(title="The Professional", year=1994)

scifi.movies.append(alien)

scifi.movies.append(starwars)

scifi.movies.append(brunner)

action.movies.append(frcon)

action.movies.append(prof)

elixir relationships 3
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)

elixir relationships 4
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

elixir relationships 5
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 '<Movie "%s" (%d)>' % (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 '<Genre "%s">' % self.name

elixir relationships 6
Elixir Relationships (6)

scifi = Genre(name = 'Science Fiction')

action = Genre(name = 'Action')

thriller = Genre(name = 'Thriller')

crime = Genre(name = 'Crime')

alien = Movie(title="Alien", year=1979)

starwars = Movie(title="Star Wars", year=1977)

brunner = Movie(title="Blade Runner", year=1982)

frcon = Movie(title="The French Connection",

year=1971)

prof = Movie(title="The Professional", year=1994)

manch = Movie(title="Manchurian Candidate", year=1962)

elixir relationships 7
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])

elixir relationship 8
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)

)

elixir relationship 9
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

more queries 1
More queries (1)

>>> 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.genre_id) AND genre.name = ? ORDER BY movie.oid

['Science Fiction']

[<Movie "Alien" (1979)>,

<Movie "Star Wars" (1977)>,

<Movie "Blade Runner" (1982)>]

more queries 2
More queries (2)

>>> Movie.select(Movie.join_to('genres') & (Genre.c.name == "Science Fiction") & (Movie.c.year < 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.movie_id AND genre.id = movie_genres__genre_movies.genre_id) AND genre.name = ?) AND movie.year < ? ORDER BY movie.oid

['Science Fiction', 1980]

[<Movie "Alien" (1979)>, <Movie "Star Wars" (1977)>]

more queries 3
More queries (3)

>>> from sqlalchemy import and_

>>> 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.genre_id) AND genre.name = ? AND movie.year BETWEEN ? AND ? ORDER BY movie.oid

['Science Fiction', 1978, 1982]

[<Movie "Alien" (1979)>, <Movie "Blade Runner" (1982)>]

more queries 4
More queries (4)

def getTags(limit = None, min = None):

s = select([Tag.c.tag_id, func.count(Post.c.post_id)], Tag.join_to('posts'), group_by=[Tag.c.tag_id],

order_by=[desc(func.count(Post.c.post_id))])

if limit:

s.limit = limit

if min:

s.having = func.count(Post.c.post_id) > min

r = s.execute()

for tag_id, num in r:

yield Tag.get(tag_id), num

other features
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