demo of sql

demo0

{
 "metadata": {
  "name": "",
  "signature": "sha256:bbbc5656b2b4c49bbc4736cc7a82b55d4ecd4eb9bd4ad0b47403d953b72b67e1"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "import psycopg2\n",
      "\n",
      "## SQL queries at their lowest level in an IPython Environment\n",
      "## "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 1
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "conn = psycopg2.connect('dbname=gtap_misc')\n",
      "curs = conn.cursor()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 2
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "#conn.rollback()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 8
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "from IPython.display import Image\n",
      "\n",
      "Image(url='http://ct.light42.com/plevin_misc/imgs/gaez_ne2_tmp13.png')\n"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "html": [
        "<img src=\"http://ct.light42.com/plevin_misc/imgs/gaez_ne2_tmp13.png\"/>"
       ],
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 12,
       "text": [
        "<IPython.core.display.Image at 0x7fb5bc037dd0>"
       ]
      }
     ],
     "prompt_number": 12
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "tSQL = '''\n",
      "  select gid, sovereignt, sov_a3\n",
      "FROM gaez18_ne2\n",
      "WHERE gridcode::integer = 13;\n",
      "'''\n",
      "\n",
      "curs.execute(tSQL)\n",
      "res = curs.fetchall()\n",
      "print res"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "[(191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (39, 'Canada', 'CAN'), (39, 'Canada', 'CAN'), (191, 'Russia', 'RUS'), (39, 'Canada', 'CAN'), (39, 'Canada', 'CAN'), (39, 'Canada', 'CAN'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (39, 'Canada', 'CAN'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (39, 'Canada', 'CAN'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (39, 'Canada', 'CAN'), (39, 'Canada', 'CAN'), (39, 'Canada', 'CAN'), (39, 'Canada', 'CAN'), (238, 'United States of America', 'US1'), (39, 'Canada', 'CAN'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (238, 'United States of America', 'US1'), (238, 'United States of America', 'US1'), (39, 'Canada', 'CAN'), (238, 'United States of America', 'US1'), (238, 'United States of America', 'US1'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (215, 'Sweden', 'SWE'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (238, 'United States of America', 'US1'), (39, 'Canada', 'CAN'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (39, 'Canada', 'CAN'), (110, 'Iceland', 'ISL'), (238, 'United States of America', 'US1'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (238, 'United States of America', 'US1'), (191, 'Russia', 'RUS'), (39, 'Canada', 'CAN'), (191, 'Russia', 'RUS'), (238, 'United States of America', 'US1'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (191, 'Russia', 'RUS'), (39, 'Canada', 'CAN'), (39, 'Canada', 'CAN'), (39, 'Canada', 'CAN'), (39, 'Canada', 'CAN'), (238, 'United States of America', 'US1'), (238, 'United States of America', 'US1'), (39, 'Canada', 'CAN'), (238, 'United States of America', 'US1'), (39, 'Canada', 'CAN'), (191, 'Russia', 'RUS'), (238, 'United States of America', 'US1'), (39, 'Canada', 'CAN'), (39, 'Canada', 'CAN'), (153, 'Mongolia', 'MNG'), (39, 'Canada', 'CAN'), (153, 'Mongolia', 'MNG'), (153, 'Mongolia', 'MNG'), (238, 'United States of America', 'US1'), (238, 'United States of America', 'US1'), (40, 'Switzerland', 'CHE'), (112, 'Italy', 'ITA'), (16, 'Austria', 'AUT'), (42, 'China', 'CH1'), (119, 'Kazakhstan', 'KAZ'), (153, 'Mongolia', 'MNG'), (153, 'Mongolia', 'MNG'), (238, 'United States of America', 'US1'), (153, 'Mongolia', 'MNG'), (119, 'Kazakhstan', 'KAZ'), (191, 'Russia', 'RUS'), (119, 'Kazakhstan', 'KAZ'), (119, 'Kazakhstan', 'KAZ'), (42, 'China', 'CH1'), (153, 'Mongolia', 'MNG'), (42, 'China', 'CH1'), (153, 'Mongolia', 'MNG'), (42, 'China', 'CH1'), (153, 'Mongolia', 'MNG'), (153, 'Mongolia', 'MNG'), (42, 'China', 'CH1'), (119, 'Kazakhstan', 'KAZ'), (42, 'China', 'CH1'), (238, 'United States of America', 'US1'), (238, 'United States of America', 'US1'), (238, 'United States of America', 'US1'), (238, 'United States of America', 'US1'), (42, 'China', 'CH1'), (42, 'China', 'CH1'), (42, 'China', 'CH1'), (238, 'United States of America', 'US1'), (238, 'United States of America', 'US1'), (238, 'United States of America', 'US1'), (238, 'United States of America', 'US1'), (224, 'Tajikistan', 'TJK'), (238, 'United States of America', 'US1'), (238, 'United States of America', 'US1'), (238, 'United States of America', 'US1'), (42, 'China', 'CH1'), (2, 'Afghanistan', 'AFG'), (42, 'China', 'CH1'), (42, 'China', 'CH1'), (2, 'Afghanistan', 'AFG'), (42, 'China', 'CH1'), (240, 'Uzbekistan', 'UZB'), (104, 'India', 'IND'), (118, 'Kashmir', 'KAS'), (119, 'Kazakhstan', 'KAZ'), (42, 'China', 'CH1'), (42, 'China', 'CH1'), (121, 'Kyrgyzstan', 'KGZ'), (174, 'Pakistan', 'PAK'), (224, 'Tajikistan', 'TJK'), (42, 'China', 'CH1'), (42, 'China', 'CH1'), (42, 'China', 'CH1'), (42, 'China', 'CH1'), (170, 'Nepal', 'NPL'), (42, 'China', 'CH1'), (42, 'China', 'CH1'), (170, 'Nepal', 'NPL'), (42, 'China', 'CH1'), (42, 'China', 'CH1'), (42, 'China', 'CH1'), (170, 'Nepal', 'NPL'), (42, 'China', 'CH1'), (42, 'China', 'CH1'), (170, 'Nepal', 'NPL'), (42, 'China', 'CH1'), (170, 'Nepal', 'NPL'), (42, 'China', 'CH1'), (104, 'India', 'IND'), (177, 'Peru', 'PER'), (177, 'Peru', 'PER'), (177, 'Peru', 'PER'), (177, 'Peru', 'PER'), (177, 'Peru', 'PER'), (177, 'Peru', 'PER'), (177, 'Peru', 'PER'), (177, 'Peru', 'PER'), (32, 'Bolivia', 'BOL'), (9, 'Argentina', 'ARG'), (41, 'Chile', 'CHL'), (32, 'Bolivia', 'BOL'), (177, 'Peru', 'PER'), (9, 'Argentina', 'ARG'), (9, 'Argentina', 'ARG'), (9, 'Argentina', 'ARG'), (41, 'Chile', 'CHL'), (9, 'Argentina', 'ARG'), (41, 'Chile', 'CHL'), (9, 'Argentina', 'ARG'), (41, 'Chile', 'CHL')]\n"
       ]
      }
     ],
     "prompt_number": 10
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "tSQL = ''' \n",
      "SELECT gid, gridcode,  sovereignt, \n",
      "       sov_a3, adm0_dif, level, type, admin, adm0_a3, geou_dif, geounit, \n",
      "       gu_a3, su_dif, subunit, su_a3, brk_diff, name, name_long, brk_a3, \n",
      "       brk_name, brk_group, abbrev, postal, formal_en, formal_fr, note_adm0, \n",
      "       note_brk, name_sort, name_alt, \n",
      "       pop_est, gdp_md_est, pop_year, lastcensus, gdp_year, \n",
      "       economy, income_grp, fips_10, iso_a2, iso_a3, iso_n3, \n",
      "       un_a3, wb_a2, wb_a3, woe_id, adm0_a3_is, adm0_a3_us, adm0_a3_un, \n",
      "       adm0_a3_wb, continent, region_un, subregion, region_wb, name_len, \n",
      "       long_len, abbrev_len\n",
      "  FROM gaez18_ne2;\n",
      "  '''\n",
      "\n",
      "curs.execute(tSQL)\n",
      "res = curs.fetchall()\n",
      "print res[0]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "(191, Decimal('13'), 'Russia', 'RUS', 0.0, 2.0, 'Sovereign country', 'Russia', 'RUS', 0.0, 'Russia', 'RUS', 0.0, 'Russia', 'RUS', 0.0, 'Russia', 'Russian Federation', 'RUS', 'Russia', None, 'Rus.', 'RUS', 'Russian Federation', None, None, None, 'Russian Federation', None, 140041247.0, 2266000.0, -99.0, 2010.0, -99.0, '3. Emerging region: BRIC', '3. Upper middle income', None, 'RU', 'RUS', '643', '643', 'RU', 'RUS', -99.0, 'RUS', 'RUS', -99.0, -99.0, 'Europe', 'Europe', 'Eastern Europe', 'Europe & Central Asia', 6.0, 18.0, 4.0)\n"
       ]
      }
     ],
     "prompt_number": 3
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "print curs.description"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "(Column(name='gid', type_code=23, display_size=None, internal_size=4, precision=None, scale=None, null_ok=None), Column(name='gridcode', type_code=1700, display_size=None, internal_size=10, precision=10, scale=0, null_ok=None), Column(name='sovereignt', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='sov_a3', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='adm0_dif', type_code=701, display_size=None, internal_size=8, precision=None, scale=None, null_ok=None), Column(name='level', type_code=701, display_size=None, internal_size=8, precision=None, scale=None, null_ok=None), Column(name='type', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='admin', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='adm0_a3', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='geou_dif', type_code=701, display_size=None, internal_size=8, precision=None, scale=None, null_ok=None), Column(name='geounit', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='gu_a3', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='su_dif', type_code=701, display_size=None, internal_size=8, precision=None, scale=None, null_ok=None), Column(name='subunit', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='su_a3', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='brk_diff', type_code=701, display_size=None, internal_size=8, precision=None, scale=None, null_ok=None), Column(name='name', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='name_long', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='brk_a3', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='brk_name', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='brk_group', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='abbrev', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='postal', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='formal_en', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='formal_fr', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='note_adm0', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='note_brk', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='name_sort', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='name_alt', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='pop_est', type_code=701, display_size=None, internal_size=8, precision=None, scale=None, null_ok=None), Column(name='gdp_md_est', type_code=701, display_size=None, internal_size=8, precision=None, scale=None, null_ok=None), Column(name='pop_year', type_code=701, display_size=None, internal_size=8, precision=None, scale=None, null_ok=None), Column(name='lastcensus', type_code=701, display_size=None, internal_size=8, precision=None, scale=None, null_ok=None), Column(name='gdp_year', type_code=701, display_size=None, internal_size=8, precision=None, scale=None, null_ok=None), Column(name='economy', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='income_grp', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='fips_10', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='iso_a2', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='iso_a3', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='iso_n3', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='un_a3', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='wb_a2', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='wb_a3', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='woe_id', type_code=701, display_size=None, internal_size=8, precision=None, scale=None, null_ok=None), Column(name='adm0_a3_is', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='adm0_a3_us', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='adm0_a3_un', type_code=701, display_size=None, internal_size=8, precision=None, scale=None, null_ok=None), Column(name='adm0_a3_wb', type_code=701, display_size=None, internal_size=8, precision=None, scale=None, null_ok=None), Column(name='continent', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='region_un', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='subregion', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='region_wb', type_code=1043, display_size=None, internal_size=254, precision=None, scale=None, null_ok=None), Column(name='name_len', type_code=701, display_size=None, internal_size=8, precision=None, scale=None, null_ok=None), Column(name='long_len', type_code=701, display_size=None, internal_size=8, precision=None, scale=None, null_ok=None), Column(name='abbrev_len', type_code=701, display_size=None, internal_size=8, precision=None, scale=None, null_ok=None))\n"
       ]
      }
     ],
     "prompt_number": 4
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    }
   ],
   "metadata": {}
  }
 ]
}