Chapter_26_Optimizing_Database_Connections.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Examining SQL for Optimization"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "posts = Post.objects.all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT \"blog_post\".\"id\", \"blog_post\".\"title\", \"blog_post\".\"slug\", \"blog_post\".\"author_id\", \"blog_post\".\"text\", \"blog_post\".\"pub_date\" FROM \"blog_post\" ORDER BY \"blog_post\".\"pub_date\" DESC, \"blog_post\".\"title\" ASC\n"
     ]
    }
   ],
   "source": [
    "print(posts.query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT \"blog_post\".\"id\", \"blog_post\".\"title\", \"blog_post\".\"slug\", \"blog_post\".\"author_id\", \"blog_post\".\"text\", \"blog_post\".\"pub_date\", \"user_user\".\"id\", \"user_user\".\"password\", \"user_user\".\"last_login\", \"user_user\".\"is_superuser\", \"user_user\".\"email\", \"user_user\".\"is_staff\", \"user_user\".\"is_active\" FROM \"blog_post\" INNER JOIN \"user_user\" ON ( \"blog_post\".\"author_id\" = \"user_user\".\"id\" ) ORDER BY \"blog_post\".\"pub_date\" DESC, \"blog_post\".\"title\" ASC\n"
     ]
    }
   ],
   "source": [
    "print(\n",
    "    Post.objects\n",
    "    .select_related('author')\n",
    "    .query)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Prefetch SQL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "posts_startups = (\n",
    "    Post.objects.prefetch_related(\n",
    "        'startups',\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(posts_startups) == list(posts)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "str(posts.query) == str(posts_startups.query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT \"blog_post\".\"id\", \"blog_post\".\"title\", \"blog_post\".\"slug\", \"blog_post\".\"author_id\", \"blog_post\".\"text\", \"blog_post\".\"pub_date\" FROM \"blog_post\" ORDER BY \"blog_post\".\"pub_date\" DESC, \"blog_post\".\"title\" ASC\n"
     ]
    }
   ],
   "source": [
    "print(posts_startups.query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from django.db import connection\n",
    "from pprint import pprint"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "QUERY = 'SELECT \"blog_post\".\"id\", \"blog_post\".\"title\", \"blog_post\".\"slug\", \"blog_post\".\"author_id\", \"blog_post\".\"text\", \"blog_post\".\"pub_date\" FROM \"blog_post\" ORDER BY \"blog_post\".\"pub_date\" DESC, \"blog_post\".\"title\" ASC' - PARAMS = ()\n"
     ]
    }
   ],
   "source": [
    "print(connection.queries[-1]['sql'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "QUERY = 'SELECT (\"blog_post_startups\".\"post_id\") AS \"_prefetch_related_val_post_id\", \"organizer_startup\".\"id\", \"organizer_startup\".\"name\", \"organizer_startup\".\"slug\", \"organizer_startup\".\"description\", \"organizer_startup\".\"founded_date\", \"organizer_startup\".\"contact\", \"organizer_startup\".\"website\" FROM \"organizer_startup\" INNER JOIN \"blog_post_startups\" ON ( \"organizer_startup\".\"id\" = \"blog_post_startups\".\"startup_id\" ) WHERE \"blog_post_startups\".\"post_id\" IN (%s, %s, %s, %s, %s, %s) ORDER BY \"organizer_startup\".\"name\" ASC' - PARAMS = (6, 5, 4, 3, 2, 1)\n"
     ]
    }
   ],
   "source": [
    "print(connection.queries[-2]['sql'])\n",
    "# a prefetch causes a new query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from django.db import reset_queries\n",
    "reset_queries()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[]\n"
     ]
    }
   ],
   "source": [
    "pprint(connection.queries)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "posts = Post.objects.prefetch_related('startups')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[]\n"
     ]
    }
   ],
   "source": [
    "# the queryset never evaluated!\n",
    "pprint(connection.queries)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "posts = list(posts)  # force evaluation"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{'sql': 'QUERY = \\'SELECT \"blog_post\".\"id\", \"blog_post\".\"title\", '\n",
      "         '\"blog_post\".\"slug\", \"blog_post\".\"author_id\", \"blog_post\".\"text\", '\n",
      "         '\"blog_post\".\"pub_date\" FROM \"blog_post\" ORDER BY '\n",
      "         '\"blog_post\".\"pub_date\" DESC, \"blog_post\".\"title\" ASC\\' - PARAMS = '\n",
      "         '()',\n",
      "  'time': '0.000'},\n",
      " {'sql': 'QUERY = \\'SELECT (\"blog_post_startups\".\"post_id\") AS '\n",
      "         '\"_prefetch_related_val_post_id\", \"organizer_startup\".\"id\", '\n",
      "         '\"organizer_startup\".\"name\", \"organizer_startup\".\"slug\", '\n",
      "         '\"organizer_startup\".\"description\", '\n",
      "         '\"organizer_startup\".\"founded_date\", '\n",
      "         '\"organizer_startup\".\"contact\", \"organizer_startup\".\"website\" FROM '\n",
      "         '\"organizer_startup\" INNER JOIN \"blog_post_startups\" ON ( '\n",
      "         '\"organizer_startup\".\"id\" = \"blog_post_startups\".\"startup_id\" ) '\n",
      "         'WHERE \"blog_post_startups\".\"post_id\" IN (%s, %s, %s, %s, %s, %s) '\n",
      "         'ORDER BY \"organizer_startup\".\"name\" ASC\\' - PARAMS = (6, 5, 4, 3, '\n",
      "         '2, 1)',\n",
      "  'time': '0.000'}]\n"
     ]
    }
   ],
   "source": [
    "# two queries:\n",
    "#     the first for Post,\n",
    "#     the second for Startups related to the Post\n",
    "pprint(connection.queries)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{'sql': 'QUERY = \\'SELECT \"blog_post\".\"id\", \"blog_post\".\"title\", '\n",
      "         '\"blog_post\".\"slug\", \"blog_post\".\"author_id\", \"blog_post\".\"text\", '\n",
      "         '\"blog_post\".\"pub_date\" FROM \"blog_post\" ORDER BY '\n",
      "         '\"blog_post\".\"pub_date\" DESC, \"blog_post\".\"title\" ASC\\' - PARAMS = '\n",
      "         '()',\n",
      "  'time': '0.000'},\n",
      " {'sql': 'QUERY = \\'SELECT (\"blog_post_startups\".\"post_id\") AS '\n",
      "         '\"_prefetch_related_val_post_id\", \"organizer_startup\".\"id\", '\n",
      "         '\"organizer_startup\".\"name\", \"organizer_startup\".\"slug\", '\n",
      "         '\"organizer_startup\".\"description\", '\n",
      "         '\"organizer_startup\".\"founded_date\", '\n",
      "         '\"organizer_startup\".\"contact\", \"organizer_startup\".\"website\" FROM '\n",
      "         '\"organizer_startup\" INNER JOIN \"blog_post_startups\" ON ( '\n",
      "         '\"organizer_startup\".\"id\" = \"blog_post_startups\".\"startup_id\" ) '\n",
      "         'WHERE \"blog_post_startups\".\"post_id\" IN (%s, %s, %s, %s, %s, %s) '\n",
      "         'ORDER BY \"organizer_startup\".\"name\" ASC\\' - PARAMS = (6, 5, 4, 3, '\n",
      "         '2, 1)',\n",
      "  'time': '0.000'},\n",
      " {'sql': 'QUERY = \\'SELECT (\"organizer_startup_tags\".\"startup_id\") AS '\n",
      "         '\"_prefetch_related_val_startup_id\", \"organizer_tag\".\"id\", '\n",
      "         '\"organizer_tag\".\"name\", \"organizer_tag\".\"slug\" FROM '\n",
      "         '\"organizer_tag\" INNER JOIN \"organizer_startup_tags\" ON ( '\n",
      "         '\"organizer_tag\".\"id\" = \"organizer_startup_tags\".\"tag_id\" ) WHERE '\n",
      "         '\"organizer_startup_tags\".\"startup_id\" IN (%s, %s, %s) ORDER BY '\n",
      "         '\"organizer_tag\".\"name\" ASC\\' - PARAMS = (4, 4, 7)',\n",
      "  'time': '0.000'}]\n"
     ]
    }
   ],
   "source": [
    "reset_queries()\n",
    "# three queries:\n",
    "#     first for Post,\n",
    "#     the second for Startups associated with those Posts\n",
    "#     and then for the Tags associated with the Startups\n",
    "posts = list(\n",
    "    Post.objects.prefetch_related(\n",
    "        'startups__tags'\n",
    "    ),\n",
    ")\n",
    "posts_conn = connection.queries\n",
    "pprint(posts_conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reset_queries()\n",
    "# the following query is redundant\n",
    "posts = list(\n",
    "    Post.objects.prefetch_related(\n",
    "        'startups',\n",
    "        'startups__tags'\n",
    "    ),\n",
    ")\n",
    "posts_conn == connection.queries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Prefetch Objects"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reset_queries()\n",
    "posts = list(\n",
    "    Post.objects.prefetch_related(\n",
    "        Prefetch(\n",
    "            'startups__tags',\n",
    "        ),\n",
    "    )\n",
    ")\n",
    "posts_conn == connection.queries"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "posts = list(\n",
    "    Post.objects.prefetch_related(\n",
    "        Prefetch(\n",
    "            'startups__tags',\n",
    "            queryset=Tag.objects.all(),\n",
    "            to_attr='cached_tags',\n",
    "        ),\n",
    "    )\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# make sure none of the code calls the database!\n",
    "reset_queries()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Post: New Django Version on 2020-05-15>,\n",
       " <Post: More Django Info on 2015-04-08>,\n",
       " <Post: Django 1.8 Release on 2015-04-01>,\n",
       " <Post: Django Training on 2013-01-18>,\n",
       " <Post: Simple Robots for Sale on 2011-02-21>,\n",
       " <Post: Django 1.0 Release on 2008-09-03>]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "posts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "django_training = posts[3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Startup: JamBon Software>]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "django_training.startups.all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "django.db.models.query.QuerySet"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(django_training.startups.all())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Tag: Django>]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "jambon_software = django_training.startups.all()[0]\n",
    "jambon_software.cached_tags"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "list"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(jambon_software.cached_tags)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[]\n"
     ]
    }
   ],
   "source": [
    "# no database calls!\n",
    "pprint(connection.queries)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Tag: Django>]"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "jambon_software.tags.all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[{'sql': 'QUERY = \\'SELECT \"organizer_tag\".\"id\", \"organizer_tag\".\"name\", '\n",
      "         '\"organizer_tag\".\"slug\" FROM \"organizer_tag\" INNER JOIN '\n",
      "         '\"organizer_startup_tags\" ON ( \"organizer_tag\".\"id\" = '\n",
      "         '\"organizer_startup_tags\".\"tag_id\" ) WHERE '\n",
      "         '\"organizer_startup_tags\".\"startup_id\" = %s ORDER BY '\n",
      "         '\"organizer_tag\".\"name\" ASC LIMIT 21\\' - PARAMS = (4,)',\n",
      "  'time': '0.000'}]\n"
     ]
    }
   ],
   "source": [
    "pprint(connection.queries)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "5"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reset_queries()\n",
    "startups = list(\n",
    "    Startup.objects.prefetch_related(\n",
    "        Prefetch(\n",
    "            'blog_posts',\n",
    "            queryset=(\n",
    "                Post.objects\n",
    "                .select_related(\n",
    "                    'author__profile')),\n",
    "            to_attr='cached_posts',\n",
    "        ),\n",
    "        Prefetch(\n",
    "            'cached_posts__tags',\n",
    "            to_attr='cached_post_tags',\n",
    "        ),\n",
    "        Prefetch(\n",
    "            'tags',\n",
    "            to_attr='cached_tags',\n",
    "        ),\n",
    "        Prefetch(\n",
    "            'cached_tags__startup_set',\n",
    "        ),\n",
    "    )\n",
    ")\n",
    "len(connection.queries)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Startup: Arachnobots>,\n",
       " <Startup: Boundless Software>,\n",
       " <Startup: Game Congress>,\n",
       " <Startup: JamBon Software>,\n",
       " <Startup: Lightning Rod Consulting>,\n",
       " <Startup: Monkey Software>,\n",
       " <Startup: Simple Robots>,\n",
       " <Startup: Thingies>]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "reset_queries()\n",
    "startups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "jambon_software = startups[3]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Post: More Django Info on 2015-04-08>, <Post: Django Training on 2013-01-18>]"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "jambon_software.cached_posts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<User: django@jambonsw.com>"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "django_training = jambon_software.cached_posts[0]\n",
    "django_training.author"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'Andrew'"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "django_training.author.profile.name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Tag: Django>, <Tag: Web>]"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "django_training.cached_post_tags"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Tag: Django>]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "jambon_software.cached_tags"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[<Startup: JamBon Software>]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "django = jambon_software.cached_tags[0]\n",
    "django.startup_set.all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Arachnobots has the following competitors:\n",
      "    Simple Robots\n",
      "Boundless Software has the following competitors:\n",
      "    Lightning Rod Consulting\n",
      "Game Congress has the following competitors:\n",
      "    Monkey Software\n",
      "JamBon Software has the following competitors:\n",
      "Lightning Rod Consulting has the following competitors:\n",
      "    Boundless Software\n",
      "Monkey Software has the following competitors:\n",
      "    Game Congress\n",
      "Simple Robots has the following competitors:\n",
      "    Arachnobots\n",
      "    Thingies\n",
      "Thingies has the following competitors:\n",
      "    Simple Robots\n"
     ]
    }
   ],
   "source": [
    "for startup in startups:\n",
    "    print(\n",
    "        '{} has the following competitors:'\n",
    "        .format(startup))\n",
    "    for tag in startup.cached_tags:\n",
    "        for competitor in tag.startup_set.all():\n",
    "            if competitor.pk != startup.pk:\n",
    "                print(\n",
    "                    '    {}'\n",
    "                    .format(competitor))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[]\n"
     ]
    }
   ],
   "source": [
    "pprint(connection.queries)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Limiting Fields"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'name': 'augmented reality', 'slug': 'augmented-reality'},\n",
       " {'name': 'big data', 'slug': 'big-data'},\n",
       " {'name': 'django', 'slug': 'django'},\n",
       " {'name': 'education', 'slug': 'education'},\n",
       " {'name': 'ipython', 'slug': 'ipython'},\n",
       " {'name': 'javascript', 'slug': 'javascript'},\n",
       " {'name': 'jupyter', 'slug': 'jupyter'},\n",
       " {'name': 'mobile', 'slug': 'mobile'},\n",
       " {'name': 'node.js', 'slug': 'node-js'},\n",
       " {'name': 'php', 'slug': 'php'},\n",
       " {'name': 'python', 'slug': 'python'},\n",
       " {'name': 'ruby', 'slug': 'ruby'},\n",
       " {'name': 'ruby on rails', 'slug': 'ruby-on-rails'},\n",
       " {'name': 'video games', 'slug': 'video-games'},\n",
       " {'name': 'web', 'slug': 'web'},\n",
       " {'name': 'zend', 'slug': 'zend'}]"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(Tag.objects.values('name', 'slug'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT \"organizer_tag\".\"name\", \"organizer_tag\".\"slug\" FROM \"organizer_tag\" ORDER BY \"organizer_tag\".\"name\" ASC\n"
     ]
    }
   ],
   "source": [
    "print(Tag.objects.values('name', 'slug').query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['augmented reality',\n",
       " 'big data',\n",
       " 'django',\n",
       " 'education',\n",
       " 'ipython',\n",
       " 'javascript',\n",
       " 'jupyter',\n",
       " 'mobile',\n",
       " 'node.js',\n",
       " 'php',\n",
       " 'python',\n",
       " 'ruby',\n",
       " 'ruby on rails',\n",
       " 'video games',\n",
       " 'web',\n",
       " 'zend']"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(Tag.objects.values_list('name', flat=True))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT \"organizer_tag\".\"name\" FROM \"organizer_tag\" ORDER BY \"organizer_tag\".\"name\" ASC\n"
     ]
    }
   ],
   "source": [
    "print(Tag.objects.values_list('name', flat=True).query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT \"organizer_tag\".\"id\", \"organizer_tag\".\"name\", \"organizer_tag\".\"slug\" FROM \"organizer_tag\" ORDER BY \"organizer_tag\".\"name\" ASC\n"
     ]
    }
   ],
   "source": [
    "print(Tag.objects.all().query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT \"organizer_tag\".\"id\", \"organizer_tag\".\"name\" FROM \"organizer_tag\" ORDER BY \"organizer_tag\".\"name\" ASC\n"
     ]
    }
   ],
   "source": [
    "print(Tag.objects.defer('slug').query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT \"organizer_tag\".\"id\", \"organizer_tag\".\"name\", \"organizer_tag\".\"slug\" FROM \"organizer_tag\" ORDER BY \"organizer_tag\".\"name\" ASC\n"
     ]
    }
   ],
   "source": [
    "print(Tag.objects.defer('id').query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Tag has no field named 'pk'\n"
     ]
    }
   ],
   "source": [
    "from django.core.exceptions import FieldDoesNotExist\n",
    "try:\n",
    "    tags = list(Tag.objects.defer('pk'))\n",
    "except FieldDoesNotExist as e:\n",
    "    print(e)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT \"organizer_tag\".\"id\", \"organizer_tag\".\"name\" FROM \"organizer_tag\" ORDER BY \"organizer_tag\".\"name\" ASC\n"
     ]
    }
   ],
   "source": [
    "print(Tag.objects.only('name').query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "str(Tag.objects.defer('slug').query) == str(Tag.objects.only('name').query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "SELECT \"organizer_tag\".\"id\", \"organizer_tag\".\"name\", \"organizer_tag\".\"slug\" FROM \"organizer_tag\" ORDER BY \"organizer_tag\".\"name\" ASC\n"
     ]
    }
   ],
   "source": [
    "print(Tag.objects.only('name').values().query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ValuesQuerySet does not implement only()\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    tags = Tag.objects.values().only('name')\n",
    "except NotImplementedError as e:\n",
    "    print(e)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Django Shell-Plus",
   "language": "python",
   "name": "django_extensions"
  },
  "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.4.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}