SQL queries from Python.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "****\n",
    "# SQL queries using Python and SQLAlquemy\n",
    "****\n",
    "<p style=\"text-align: right\"><i>Jesus Perez Colino<br>First version: September 2015<br>Last revision November 2015</i></p>\n",
    "\n",
    "## About this notebook: \n",
    "Notebook prepared by **Jesus Perez Colino** Version 0.1, First Released: 01/12/2014, Alpha.  \n",
    "\n",
    "- This work is licensed under a [Creative Commons Attribution-ShareAlike 3.0 Unported License](http://creativecommons.org/licenses/by-sa/3.0/deed.en_US). This work is offered for free, with the hope that it will be useful.\n",
    "\n",
    "\n",
    "- **Summary**: This notebook (WORK IN PROGRESS) \n",
    "\n",
    "\n",
    "- **Python & packages versions** to reproduce the results of this notebook: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "---------------------- Reproducibility conditions for this notebook ----------------------\n",
      "Python version:     2.7.10 |Anaconda 2.4.0 (x86_64)| (default, Oct 19 2015, 18:31:17) \n",
      "[GCC 4.2.1 (Apple Inc. build 5577)]\n",
      "SQLalchemy version: 1.0.9\n",
      "IPython version:    4.0.0\n",
      "------------------------------------------------------------------------------------------\n"
     ]
    }
   ],
   "source": [
    "from sys import version \n",
    "import sqlalchemy\n",
    "import IPython\n",
    "print ' Reproducibility conditions for this notebook '.center(90,'-')\n",
    "print 'Python version:     ' + version\n",
    "print 'SQLalchemy version: ' + sqlalchemy.__version__\n",
    "print 'IPython version:    ' + IPython.__version__\n",
    "print '-'*90"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# What is SQLAlchemy\n",
    "\n",
    "**SQLAlchemy** is a library used to interact with a wide variety of databases. It will enables us to create data models and queries in a manner that feels like normal Python classes and statements. Created by Mike Bayer in 2005, it is probably the most common way of working with relational databases in Python. It can be used to connect to most common databases such as Postgres, MySQL, SQLite, Oracle, and many others. \n",
    "\n",
    "\n",
    "## Connecting to a Database\n",
    "\n",
    "To connect to a database, we need to create a **SQLAlchemy engine**. The **SQLAlchemy Engine** creates a common interface to the database to execute SQL statements. It does this by wrapping a pool of database connections and a dialect in a way that they can work together to provide uniform access to the backend database. This will enables our Python code not to worry about the differences between databases or DBAPIs. SQLAlchemy provides a function to create an engine for us given a connection string and optionally some additional keyword arguments. A connection string is a specially formatted string that provides:\n",
    "\n",
    "- Database type (Postgres, MySQL, etc.)\n",
    "- Dialect if the default for the database type (Psycopg2, PyMySQL, etc.)\n",
    "- Optional authentication details (username and password)\n",
    "- Location of the database (file or hostname of the database server)\n",
    "- Optional database server port"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import MetaData\n",
    "metadata = MetaData()\n",
    "\n",
    "from sqlalchemy import Table, Column, Integer, Numeric, String, ForeignKey, DateTime\n",
    "from datetime import datetime\n",
    "\n",
    "from sqlalchemy import create_engine\n",
    "engine = create_engine('sqlite:///:memory:')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Creating a database\n",
    "\n",
    "**Table** objects are initialized in SQLAlchemy Core in a supplied **MetaData object** by calling the **Table constructor** with the table name, metadata, and any additional arguments are assumed to be column objects. \n",
    "\n",
    "**Column** objects represent each field in the table. The columns are constructed by calling **Column** with a name, type, and then arguments that represent any additional SQL constructs and constraints. **Columns** define the fields that exists in our tables, and they provide the primary means by which we define other constraints through their keyword arguments."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "users = Table('users', metadata, \n",
    "              Column('user_id', Integer(), primary_key=True), # table’s primary key\n",
    "              Column('username', String(15), nullable=False, unique=True, index = True),\n",
    "              Column('email_address', String(255), nullable=False),\n",
    "              Column('phone', String(20), nullable=False),\n",
    "              Column('password', String(25), nullable=False),\n",
    "              Column('created_on', DateTime(), default=datetime.now),\n",
    "              Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now)\n",
    "             )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "engine = create_engine('sqlite:///:memory:')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Metadata** as a kind of catalog of **Table** objects with optional information about the engine and the connection. Those tables can be accessed via a dictionary, `MetaData.tables`. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "metadata.create_all(engine)\n",
    "connection = engine.connect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Keys and constraints** are used as a way to ensure that our data meets certain requirements prior to being stored in the database. The objects that represent keys and constraints can be found inside the base SQLAlchemy module, and three of the more common ones can be imported as shown here."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "CheckConstraint(<sqlalchemy.sql.elements.TextClause object at 0x1065a89d0>, name='unit_cost_positive')"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy import PrimaryKeyConstraint, UniqueConstraint, CheckConstraint\n",
    "\n",
    "PrimaryKeyConstraint('user_id', name='user_pk')\n",
    "UniqueConstraint('username', name='uix_username')\n",
    "CheckConstraint('unit_cost >= 0.00', name='unit_cost_positive')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import select, insert\n",
    "from sqlalchemy.exc import IntegrityError\n",
    "ins = insert(users).values(\n",
    "    username=\"me\",\n",
    "    email_address=\"me@me.com\",\n",
    "    phone=\"111-111-1111\",\n",
    "    password=\"password_me\"\n",
    ")\n",
    "try:\n",
    "    result = connection.execute(ins)\n",
    "except IntegrityError as error:\n",
    "    print(error.orig.message, error.params)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Quering a database\n",
    "\n",
    "To begin building a **query**, we start by using the `select()` function that is very analogous to the standard **SQL** `SELECT` statement:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, u'me', u'me@me.com', u'111-111-1111', u'password_me', datetime.datetime(2015, 11, 15, 12, 46, 27, 221513), datetime.datetime(2015, 11, 15, 12, 46, 27, 221523))\n",
      "--------------------------------------------------\n",
      "me\n",
      "--------------------------------------------------\n",
      "password_me\n"
     ]
    }
   ],
   "source": [
    "s = users.select()\n",
    "results_1 = connection.execute(s)\n",
    "for result in results_1:\n",
    "    print result\n",
    "    print '-'*50\n",
    "    print(result.username)\n",
    "    print '-'*50\n",
    "    print(result.password)\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Additionally we can query using raw SQL statements or use raw SQL:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(1, u'me', u'me@me.com', u'111-111-1111', u'password_me', u'2015-11-15 12:46:27.221513', u'2015-11-15 12:46:27.221523')]\n"
     ]
    }
   ],
   "source": [
    "result = connection.execute(\"select * from users\").fetchall()\n",
    "print result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "\n",
    "Base = declarative_base()\n",
    "\n",
    "class User(Base):\n",
    "    __tablename__ = 'users'\n",
    "    \n",
    "    user_id = Column(Integer(), primary_key=True)\n",
    "    username = Column(String(15), nullable=False, unique=True)\n",
    "    email_address = Column(String(255), nullable=False)\n",
    "    phone = Column(String(20), nullable=False)\n",
    "    password = Column(String(25), nullable=False)\n",
    "    created_on = Column(DateTime(), default=datetime.now)\n",
    "    updated_on = Column(DateTime(), default=datetime.now, onupdate=datetime.now)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Table('users', MetaData(bind=None), Column('user_id', Integer(), table=<users>, primary_key=True, nullable=False), Column('username', String(length=15), table=<users>, nullable=False), Column('email_address', String(length=255), table=<users>, nullable=False), Column('phone', String(length=20), table=<users>, nullable=False), Column('password', String(length=25), table=<users>, nullable=False), Column('created_on', DateTime(), table=<users>, default=ColumnDefault(<function <lambda> at 0x10674e050>)), Column('updated_on', DateTime(), table=<users>, onupdate=ColumnDefault(<function <lambda> at 0x10674e140>), default=ColumnDefault(<function <lambda> at 0x10674e0c8>)), schema=None)"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "User.__table__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "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.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}