+ + +

+ +

SQLAlchemy & Odin Data Access

Fifty Data Science

+
+

Simeon Simeonov

+ +

Agenda

+
+

SQLAlchemy - Design & overview

SQLAlchemy - A small practical example

Odin Data Access - Overview and examples

Q & A

+ +

What is SQLAlchemy?

+
+

SQLAlchemy is a Python library created by Mike Bayer to provide a high-level Pythonic interface to RDBMS such as PostgreSQL, SQLite, MySQL, Oracle, DB2.

SQLAlchemy includes RDBMS-independent SQL expression language and an object-relational mapper (ORM).

+ +

Why use SQLAlchemy?

+
+

portability - the programming interface is independent of the type of RDBMS and connector used
security - no more SQL injections
abstraction - no need to bother with complex JOINs
object-orientation - you work with objects and NOT tables and rows
performance - exploits the likehood of reusing a particular query
flexibility - you can override almost anything

+ +

Basic architecture

SQLAlchemy consists of several components, including the the ORM.

Engine- manages the connection pool and the RDBMS-independent SQL dialect layer
MetaData - used to collect and organize information about your table layout (schema)
Session - establishes all conversations with the RDBMS and represents a "holding zone" for all the objects which you've loaded or associated with it during its lifespan
SQL expression language - provides an API to execute your queries and updates against your tables, all from Python, and all in a database-independent way (low-level interface)
ORM - provides a convenient way to add database persistence to your Python objects withoutrequiring you to design your objects around the database, or the database around the objects (high-level interface)

+ +

Example

SQLAlchemy gives us the choice between classical mapping and the newer declarative mapping

+            
+              import sqlalchemy
+              sqlalchemy.__version__
+
+              from sqlalchemy import create_engine
+
+              # engine = create_engine("postgresql+psycopg2://user:zipassword@localhost/mydb" , echo=True)
+              # The string form of the URL is dialect+driver://user:password@host/dbname[?key=value..],
+              # where dialect is a database name such as mysql, oracle, postgresql, etc.,
+              # and driver the name of a DBAPI, such as psycopg2, pyodbc, cx_oracle
+              # The echo flag is a shortcut to setting up SQLAlchemy logging,
+              # which is accomplished via Python’s standard logging module.
+              # With it enabled, we’ll see all the generated SQL produced.
+              # engine = create_engine("sqlite:///library.db", echo=True)
+              engine = create_engine("sqlite:///:memory:", echo=True)
+
+              metadata = MetaData()
+
+              from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
+
+              authors_table = Table(
+                  "authors",
+                  metadata,
+                  Column("id", Integer, primary_key=True),
+                  Column("name", String),
+              )  # Column("name", String(50)) is possible
+
+              books_table = Table(
+                  "books",
+                  metadata,
+                  Column("id", Integer, primary_key=True),
+                  Column("title", String),
+                  Column("description", String),
+                  Column("author_id", ForeignKey('authors.id')),
+              )
+
+              metadata.create_all(engine)  # creates the tables
+            
+

+ +

Example (cont...)

Use of SQL expression language

+            
+              insert_stmt = authors_table.insert(bind=engine)
+              type(insert_stmt)
+              # Out: <class 'sqlalchemy.sql.expression.Insert'>
+              print(insert_stmt)
+              # Out: INSERT INTO authors (id, name) VALUES (:id,:name)
+
+              compiled_stmt = insert_stmt.compile()
+              print(compiled_stmt.params)
+              # Out: {'id': None, 'name': None}
+
+              insert_stmt.execute(name="Alexandre Dumas")  # insert a single entry
+              insert_stmt.execute([{"name": 'Mr X'},{'name': 'Mr Y'}])  # a list of entries
+
+              metadata.bind = engine  # no need to explicitly bind the engine from now on
+              select_stmt = authors_table.select(authors_table.c.id==2)
+              result = select_stmt.execute()
+              result.fetchall()
+              # Out: [(1, u'Mr X')]
+
+              del_stmt = authors_table.delete()
+              del_stmt.execute(whereclause=text("name='Mr Y'"))
+              del_stmt.execute()  # delete all
+            
+

+ +

Example (cont...)

Use of classical mapping

+            
+              from sqlalchemy.orm import mapper
+              from sqlalchemy.orm import relationship, backref
+
+              class Author:
+                  def __init__(self, name):
+                      self.name = name
+
+                  def __str__(self):
+                      return self.name
+
+
+              class Book:
+                  def __init__(self, title, description, author):
+                      self.title = title
+                      self.description = description
+                      self.author = author
+
+                  def __str__(self):
+                      return self.title
+
+              mapper(Book, books_table)
+              mapper(Author, authors_table, properties = {"books": relation(Book, backref="author")})
+            
+

+ +

Example (cont...)

Doing the same thing the easy way with declarative mapping

+            
+              from sqlalchemy.ext.declarative import declarative_base
+              from sqlalchemy.orm import relationship, backref
+
+              Base = declarative_base()
+
+              class Author(Base):
+                  __tablename__ = "authors"
+
+                  id = Column(Integer, primary_key=True)
+                  name = Column(String)
+
+                  def __init__(self, name):
+                      self.name = name
+
+                  def __str__(self):
+                      return self.name
+
+
+              class Book(Base):
+                  __tablename__ = "books"  # self.__table__ will be available for our objects
+
+                  id = Column(Integer, primary_key=True)
+                  title = Column(String)
+                  description = Column(String)
+                  author_id = Column(Integer, ForeignKey("authors.id"))
+                  author = relationship(Author, backref=backref("books", order_by=title))
+
+                  def __init__(self, title, description, author):
+                      self.title = title
+                      self.description = description
+                      self.author = author
+
+                  def __str__(self):
+                      return self.title
+
+              Base.metadata.create_all(engine)# create tables
+            
+

+ +

Example (cont...)

Creating instances

+            
+              from sqlalchemy.orm import sessionmaker
+
+              Session = sessionmaker(bind=engine)  # bound session
+              session = Session()
+
+              author_1 = Author("Richard Dawkins")
+              author_2 = Author("Matt Ridley")
+
+              book_1 = Book("The Red Queen", "A popular science book", author_2)
+              book_2 = Book("The Selfish Gene", "A popular science book", author_1)
+              book_3 = Book("The Blind Watchmaker", "The theory of evolutio", author_1)  # typo
+
+              session.add(author_1)
+              session.add(author_2)
+              session.add(book_1)
+              session.add(book_2)
+              session.add(book_3)  # or simply session.add_all([author_1, author_2, book_1, book_2, book_3])
+
+              # session.flush()
+              session.commit()  # flushes (issues the statements and sends them to the RDBMS) and commits
+
+              book_3.description = "The theory of evolution"  # update the object
+              book_3 in session   # check whether the object is in the session
+              # Out: True
+
+              session.commit()
+            
+

+ +

Example (cont...)

Queries

+            
+              session.query(Book).order_by(Book.id)  # returns a Query instance with a .statement attribute
+              session.query(Book).order_by(Book.id).all()  # returns an object-list
+
+              # return all book objects where title == "The Selfish Gene"
+              session.query(Book).filter(Book.title == "The Selfish Gene").order_by(Book.id).all()
+
+              # using LIKE
+              session.query(Book).filter(Book.title.like("The%")).order_by(Book.id).all()
+
+              query = session.query(Book).filter(Book.id == 9).order_by(Book.id)
+              query.count()  # returns 0L
+              query.all()  # returns an empty list
+              query.first()  # returns None
+              query.one()  # raises NoResultFound exception
+
+              query = session.query(Book).filter(Book.id == 1).order_by(Book.id)
+              book_1 = query.one()
+              book_1.description  # returns "A popular science book"
+              book_1.author.books  # returns a list of Book-objects representing all the books from the same author.
+
+              # get a list of all Book-instances where the author"s name is "Richard Dawkins"
+              session.query(Book).filter(Book.author_id == Author.id).filter(Author.name == "Richard Dawkins").all()
+              session.query(Book).join(Author).filter(Author.name == "Richard Dawkins").all()
+              session.query(Book).\
+                  from_statement("SELECT b.* FROM books b, authors a WHERE b.author_id = a.id AND a.name=:name").\
+                  params(name="Richard Dawkins").all()
+            
+

+ +

... simply amazing!

+ +

Odin Data Access

https://gitlab.fifty.eu/odin/data-science/odin-data-access

Provides the odin_data_access module with some of the following entities:

session module for creating Session objects in a Fifty environment via get_session and get_session_from_env
DBBase - a tiny base-class that can be used as a mixin for mapping
DBTableAccessor - tiny wrapper for Session and Table as well a "facade" for Query and pandas

The DBTableAccessor:

represents a single RDBMS-table that is introspected during instance initialization (__init__)
uses a mix of ORM and SQL expression language functionality
contins a mapper for mapping custom classes to its corresponding Table

used by Batman and Chuck Norris (source: incomplete Google search)

+ +

Demo

Now watch this well-rehearsed demo...

+ +

Q & A

+ +