{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.schema import MetaData, Table, Index, Column\n",
    "from sqlalchemy.sql import and_, or_, not_, select, text\n",
    "from sqlalchemy.types import Text, Integer, Float, String\n",
    "\n",
    "engine = create_engine('sqlite:///:memory:', echo=False)\n",
    "\n",
    "metadata = MetaData()\n",
    "\n",
    "user = Table('user', metadata,\n",
    "    Column('user_id', Integer, primary_key=True),\n",
    "    Column('user_name', String(16), nullable=False),\n",
    "    Column('email_address', String(60)),\n",
    "    Column('password', String(20), nullable=False)\n",
    ")\n",
    "user.create(engine)\n",
    "\n",
    "with engine.connect() as conn:\n",
    "    for name in ('jack', 'james', 'jenny'):\n",
    "        email = '{name}@gmail.com'.format(name=name)\n",
    "        ins = user.insert().values(user_name=name, email_address=email, password=\"bad\")\n",
    "        result = conn.execute(ins) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Full results: [(1, 'jack', 'jack@gmail.com', 'bad'), (2, 'james', 'james@gmail.com', 'bad'), (3, 'jenny', 'jenny@gmail.com', 'bad')]\n"
     ]
    }
   ],
   "source": [
    "with engine.connect() as conn:\n",
    "    query = select([user])\n",
    "    found = conn.execute(query).fetchall()\n",
    "\n",
    "print(\"Full results: {results}\".format(results=found))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL: SELECT \"user\".user_id, \"user\".user_name, \"user\".email_address, \"user\".password \n",
      "FROM \"user\" \n",
      "WHERE \"user\".user_name = :user_name_1 OR \"user\".user_name = :user_name_2\n",
      "\n",
      "Returns: [(1, 'jack', 'jack@gmail.com', 'bad'), (2, 'james', 'james@gmail.com', 'bad')].\n",
      "That makes sense.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "query_with_disjunction = query.where(or_(user.c.user_name == \"jack\", user.c.user_name == \"james\"))\n",
    "\n",
    "print(\"SQL: {}\".format(str(query_with_disjunction)))\n",
    "\n",
    "with engine.connect() as conn:\n",
    "    found = conn.execute(query_with_disjunction).fetchall()\n",
    "    \n",
    "print(\"\\nReturns: {results}.\\nThat makes sense.\\n\".format(results=found))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SQL: SELECT \"user\".user_id, \"user\".user_name, \"user\".email_address, \"user\".password \n",
      "FROM \"user\" \n",
      "WHERE NOT (\"user\".user_name = :user_name_1 OR \"user\".user_name = :user_name_2)\n",
      "\n",
      "Returns: [(3, 'jenny', 'jenny@gmail.com', 'bad')]\n",
      "That makes sense.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "disjunction = or_(user.c.user_name == \"jack\", user.c.user_name == \"james\")\n",
    "query_with_negated_disjunction = query.where(not_(disjunction))\n",
    "\n",
    "print(\"SQL: {}\".format(str(query_with_negated_disjunction)))\n",
    "\n",
    "with engine.connect() as conn:\n",
    "    found = conn.execute(query_with_negated_disjunction).fetchall()\n",
    "    \n",
    "print(\"\\nReturns: {results}\\nThat makes sense.\\n\".format(results=found))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Produces the SQL: SELECT \"user\".user_id, \"user\".user_name, \"user\".email_address, \"user\".password \n",
      "FROM \"user\" \n",
      "WHERE NOT \"user\".user_name = :user_name_1 OR \"user\".user_name = :user_name_2\n",
      ".Notice that the 'not' does NOT put parentheses around the disjunction\n",
      "\n",
      "Returns: [(1, 'jack', 'jack@gmail.com', 'bad'), (3, 'jenny', 'jenny@gmail.com', 'bad')]\n",
      "\n",
      "That doesn't make sense for 2 reasons: 1) we'd expect the same result as the unlabelled disjunction, i.e. just jenny and 2) even if it's correct to apply the 'not' only to the first clause, the result should be james and jenny, not jack and jenny.\n"
     ]
    }
   ],
   "source": [
    "disjunction_with_label = disjunction.label(\"labelled_disjunction\")\n",
    "\n",
    "query_with_negated_disjunction_with_label = query.where(not_(disjunction_with_label))\n",
    "\n",
    "print(\"Produces the SQL: {}\\n.Notice that the 'not' does NOT put parentheses around the disjunction\".format(str(query_with_negated_disjunction_with_label)))\n",
    "\n",
    "with engine.connect() as conn:\n",
    "    found = conn.execute(query_with_negated_disjunction_with_label).fetchall()\n",
    "    \n",
    "print(\"\\nReturns: {results}\\n\\nThat doesn't make sense for 2 reasons: 1) we'd expect the same result as the unlabelled disjunction, i.e. just jenny and 2) even if it's correct to apply the 'not' only to the first clause, the result should be james and jenny, not jack and jenny.\".format(results=found))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "If we pre-bind the variables and run directly, we get james and jenny:\n"
     ]
    }
   ],
   "source": [
    "literal_query = str(query_with_negated_disjunction_with_label.compile(compile_kwargs={\"literal_binds\": True}))\n",
    "\n",
    "print(\"If we pre-bind the variables and run directly, we get james and jenny:\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"[(2, 'james', 'james@gmail.com', 'bad'), (3, 'jenny', 'jenny@gmail.com', 'bad')]\""
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with engine.connect() as conn:\n",
    "    res = conn.execute(literal_query).fetchall()\n",
    "str(res)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}