1 / 32

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

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 *

benedict-buckner
Download Presentation

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

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQLAlchemy and Elixir (in a few minutes) Neil Blakey-Milner http://nxsy.org/writings/ctpug-sqlalchemy-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 >>> users_table = Table('users', metadata, ... Column('user_id', Integer, primary_key=True), ... Column('user_name', String(40)), ... Column('password', String(10)) ... )

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

  6. Declaring a table from DB users_table = Table('users', metadata, autoload=True)

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

  8. Inserting multiple lines >>> i.execute({'user_name':'Tom'}, {'user_name':'Fred'}, {'user_name':'Harry'}) INSERT INTO users (user_name) VALUES (?) [['Tom'], ['Fred'], ['Harry']] COMMIT

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

  10. 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')))

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

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

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

  14. 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)]

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

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

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

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

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

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

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

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

  29. 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)>]

  30. 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)>]

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

  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

More Related