Jupyter-SQLAlchemy.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "-"
    }
   },
   "source": [
    "Introduction to SQLAlchemy and Jupyter Notebooks\n",
    "==========================================\n",
    "\n",
    "### Authors:\n",
    "- Lee Bergstrand\n",
    "- Matt McInnes\n",
    "\n",
    "**Note:** This tutorial is based off the [official SQLAlchemy ORM tutorial](http://docs.sqlalchemy.org/en/rel_1_0/orm/tutorial.html). "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Introduction to SQLAlchemy\n",
    "=========================="
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import sqlalchemy\n",
    "\n",
    "# Connect to the database...\n",
    "from sqlalchemy import create_engine\n",
    "engine = create_engine('sqlite:///:memory:', echo=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating Database-Backed Classes\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The ORM utilizes classes inheriting from the SQLAlchemy base class. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "Base = declarative_base()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Classes have RDBMS table attributes:\n",
    "- Columns have datatypes (Integer, Float, etc.)\n",
    "- Columns have constraints (Primary Key, Foreign Key, etc.)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import Column, Integer, String\n",
    "class User(Base):\n",
    "    __tablename__ = 'users'\n",
    "    \n",
    "    id = Column(Integer, primary_key=True)\n",
    "    name = Column(String)\n",
    "    fullname = Column(String)\n",
    "    password = Column(String)\n",
    "    \n",
    "    # Defines to_string() representation \n",
    "    def __repr__(self):\n",
    "        return \"<User(name='%s', fullname='%s', password='%s')>\" % (\n",
    "                self.name, self.fullname, self.password)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "SQLAlchemy will set up your tables for you..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "Base.metadata.create_all(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Issued SQL:\n",
    "\n",
    "```SQL\n",
    "CREATE TABLE users (\n",
    "    id INTEGER NOT NULL, name VARCHAR,\n",
    "    fullname VARCHAR,\n",
    "    password VARCHAR,\n",
    "    PRIMARY KEY (id)\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Creating a Session\n",
    "\n",
    "---"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from sqlalchemy.orm import sessionmaker\n",
    "Session = sessionmaker(bind=engine)\n",
    "session = Session()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Adding, Updating and Commiting Objects\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once you have your tables create you can then start creating objects."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<User(name='ed', fullname='Ed Jones', password='edspassword')>"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ed_user = User(name='ed', fullname='Ed Jones', password='edspassword')\n",
    "session.add(ed_user)\n",
    "ed_user"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When you do a query SQLAlchemy, your uncommited changes are flushed to the db (within a transaction). SQLAlchemy then requeries the database in order to gain the value of the objects primary key column."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "our_user = session.query(User).filter_by(name='ed').first()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Issued SQL:\n",
    "\n",
    "*Insert (Flush)*\n",
    "\n",
    "```SQL\n",
    "INSERT INTO users (name, fullname, password) VALUES ('ed', 'Ed Jones', 'edspassword')\n",
    "```\n",
    "\n",
    "*Query Select*\n",
    "\n",
    "```SQL\n",
    "SELECT users.id AS users_id,\n",
    "        users.name AS users_name,\n",
    "        users.fullname AS users_fullname,\n",
    "        users.password AS users_password\n",
    "FROM users\n",
    "WHERE users.name = 'ed'\n",
    " LIMIT 1 OFFSET 0\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "Since both of `end_user` and `our_user` have the same primary key the are mapped to the same object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ed_user is our_user"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "Multiple objects can be added to the session and objects can be modified."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "session.add_all([\n",
    "        User(name='wendy', fullname='Wendy Williams', password='foobar'),\n",
    "        User(name='mary', fullname='Mary Contrary', password='xxg527'),\n",
    "        User(name='fred', fullname='Fred Flinstone', password='blah')])\n",
    "\n",
    "ed_user.password = 'f8s7ccs'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "Finally, the transaction can be commited. The remaining changes are flushed to the database."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "session.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Issued SQL:\n",
    "\n",
    "*Flush remaining changes (inserts and updates)*\n",
    "\n",
    "```SQL\n",
    "UPDATE users SET password='f8s7ccs' WHERE users.id = 1\n",
    "\n",
    "INSERT INTO users (name, fullname, password) VALUES ('wendy', 'Wendy Williams', 'foobar')\n",
    "INSERT INTO users (name, fullname, password) VALUES ('mary', 'Mary Contrary', 'xxg527')\n",
    "INSERT INTO users (name, fullname, password) VALUES (fred', 'Fred Flinstone', 'blah')\n",
    "```\n",
    "\n",
    "*Finally, commit the changes*\n",
    "\n",
    "```SQL\n",
    "COMMIT\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Rolling Back\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In SQLAlchemy all database operations are performed within a transaction. \n",
    "\n",
    "For example we can edit and query for objects:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<User(name='Edwardo', fullname='Ed Jones', password='f8s7ccs')>,\n",
       " <User(name='fakeuser', fullname='Invalid', password='12345')>]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ed_user.name = 'Edwardo'\n",
    "\n",
    "fake_user = User(name='fakeuser', fullname='Invalid', password='12345')\n",
    "session.add(fake_user)\n",
    "\n",
    "session.query(User).filter(User.name.in_(['Edwardo', 'fakeuser'])).all()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can do a rollback and our changes are reset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "session.rollback()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Issued SQL:\n",
    "\n",
    "```SQL\n",
    "ROLLBACK\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Our objects are reset to their pervious states and our uncommited objects are removed."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "u'ed'"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ed_user.name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fake_user in session"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Querying\n",
    "---"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>\n",
      "<User(name='wendy', fullname='Wendy Williams', password='foobar')>\n",
      "<User(name='mary', fullname='Mary Contrary', password='xxg527')>\n",
      "<User(name='fred', fullname='Fred Flinstone', password='blah')>\n"
     ]
    }
   ],
   "source": [
    "for instance in session.query(User).order_by(User.id):\n",
    "    print(instance)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Issued SQL:\n",
    "\n",
    "```SQL\n",
    "SELECT users.id AS users_id,\n",
    "        users.name AS users_name,\n",
    "        users.fullname AS users_fullname,\n",
    "        users.password AS users_password\n",
    "FROM users ORDER BY users.id\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(u'ed', u'Ed Jones')\n",
      "(u'wendy', u'Wendy Williams')\n",
      "(u'mary', u'Mary Contrary')\n",
      "(u'fred', u'Fred Flinstone')\n"
     ]
    }
   ],
   "source": [
    "for name, fullname in session.query(User.name, User.fullname):\n",
    "    print(name, fullname)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### Issued SQL:\n",
    "\n",
    "```SQL\n",
    "SELECT users.name AS users_name,\n",
    "        users.fullname AS users_fullname\n",
    "FROM users\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Common Filtering Operators"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here’s an overview of some common operators used by the `filter()` fucntion:"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### equals:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>\n"
     ]
    }
   ],
   "source": [
    "for user in session.query(User).filter(User.name == 'ed'):\n",
    "    print(user)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### not equals:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<User(name='wendy', fullname='Wendy Williams', password='foobar')>\n",
      "<User(name='mary', fullname='Mary Contrary', password='xxg527')>\n",
      "<User(name='fred', fullname='Fred Flinstone', password='blah')>\n"
     ]
    }
   ],
   "source": [
    "for user in session.query(User).filter(User.name != 'ed'):\n",
    "    print(user)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### LIKE:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>\n",
      "<User(name='fred', fullname='Fred Flinstone', password='blah')>\n"
     ]
    }
   ],
   "source": [
    "for user in session.query(User).filter(User.name.like('%ed%')):\n",
    "    print(user)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### IN:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>\n",
      "<User(name='wendy', fullname='Wendy Williams', password='foobar')>\n"
     ]
    }
   ],
   "source": [
    "for user in session.query(User).filter(User.name.in_(['ed', 'wendy', 'jack'])):\n",
    "    print(user)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### NOT IN:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<User(name='mary', fullname='Mary Contrary', password='xxg527')>\n",
      "<User(name='fred', fullname='Fred Flinstone', password='blah')>\n"
     ]
    }
   ],
   "source": [
    "for user in session.query(User).filter(~User.name.in_(['ed', 'wendy', 'jack'])):\n",
    "    print(user)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### AND:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>\n"
     ]
    }
   ],
   "source": [
    "for user in session.query(User).filter(User.name == 'ed', User.fullname == 'Ed Jones'):\n",
    "    print(user)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### OR:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>\n",
      "<User(name='wendy', fullname='Wendy Williams', password='foobar')>\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import or_\n",
    "for user in session.query(User).filter(or_(User.name == 'ed', User.name == 'wendy')):\n",
    "    print(user)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Relationships\n",
    "---"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Multiple classes can be created and linked together using class relations."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import ForeignKey\n",
    "from sqlalchemy.orm import relationship\n",
    "\n",
    "class Address(Base):\n",
    "    __tablename__ = 'addresses'\n",
    "    id = Column(Integer, primary_key=True)\n",
    "    email_address = Column(String, nullable=False)\n",
    "    user_id = Column(Integer, ForeignKey('users.id'))\n",
    "    user = relationship(\"User\", back_populates=\"addresses\")\n",
    "    \n",
    "    def __repr__(self):\n",
    "        return \"<Address(email_address='%s')>\" % self.email_address"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "Once primary and foreign keys are established, classes can be connected together via relationships."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "User.addresses = relationship(\"Address\", order_by=Address.id, back_populates=\"user\")\n",
    "\n",
    "Base.metadata.create_all(engine) # Flush schema changes to the DBMS."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "In the above mappings:\n",
    "- ```user.addresses``` maps to a user's list of address objects.\n",
    "- ```address.user``` maps to an address's parent user object."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We create a \"Jack\" object and it initially has no addresses."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "jack = User(name='jack', fullname='Jack Bean', password='gjffdd')\n",
    "jack.addresses"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "We then add some address objects."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "jack.addresses = [Address(email_address='jack@google.com'), Address(email_address='j25@yahoo.com')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Address(email_address='j25@yahoo.com')>"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "jack.addresses[1]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "We see that the \"Jack\" object's first address's user parameter refers back to the \"Jack\" object."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<User(name='jack', fullname='Jack Bean', password='gjffdd')>"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "jack.addresses[1].user"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "When we add \"Jack\" to the session and commit, the two address objects of \"Jack\" are also committed in a cascading fashion."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Address(email_address='jack@google.com')>,\n",
       " <Address(email_address='j25@yahoo.com')>]"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "session.add(jack)\n",
    "\n",
    "session.commit()\n",
    "\n",
    "jack = session.query(User).filter_by(name='jack').one()\n",
    "jack.addresses"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 2",
   "language": "python",
   "name": "python2"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.11"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}