ddlgenerator.ipynb

{
 "metadata": {
  "name": "",
  "signature": "sha256:9231e291dcc0a7ac020b0da8f2cea3fb9ed4b1f28cd09edb59c157decbc090fb"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "heading",
     "level": 1,
     "metadata": {},
     "source": [
      "Relationalize All the Things"
     ]
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "ddlgenerator"
     ]
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "What is Data?"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "cat animals.csv"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "name,species,kg,notes\r\n",
        "Alfred,wart hog,22,loves turnips\r\n",
        "Gertrude,polar bear,312.7,deep thinker\r\n",
        "Emily,salamander,0.3,\r\n"
       ]
      }
     ],
     "prompt_number": 1
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "!ddlgenerator postgresql animals.csv"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "CREATE TABLE animals (\r\n",
        "\tname VARCHAR(8) NOT NULL, \r\n",
        "\tspecies VARCHAR(10) NOT NULL, \r\n",
        "\tkg DECIMAL(4, 1) NOT NULL, \r\n",
        "\tnotes VARCHAR(13) NOT NULL, \r\n",
        "\tUNIQUE (name), \r\n",
        "\tUNIQUE (species), \r\n",
        "\tUNIQUE (kg), \r\n",
        "\tUNIQUE (notes)\r\n",
        ");\r\n",
        "\r\n"
       ]
      }
     ],
     "prompt_number": 2
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "!ddlgenerator --inserts postgresql animals.csv"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "CREATE TABLE animals (\r\n",
        "\tname VARCHAR(8) NOT NULL, \r\n",
        "\tspecies VARCHAR(10) NOT NULL, \r\n",
        "\tkg DECIMAL(4, 1) NOT NULL, \r\n",
        "\tnotes VARCHAR(13) NOT NULL, \r\n",
        "\tUNIQUE (name), \r\n",
        "\tUNIQUE (species), \r\n",
        "\tUNIQUE (kg), \r\n",
        "\tUNIQUE (notes)\r\n",
        ");\r\n",
        "\r\n",
        "INSERT INTO animals (name, species, kg, notes) VALUES ('Alfred', 'wart hog', 22, 'loves turnips');\r\n",
        "INSERT INTO animals (name, species, kg, notes) VALUES ('Gertrude', 'polar bear', 312.7, 'deep thinker');\r\n",
        "INSERT INTO animals (name, species, kg, notes) VALUES ('Emily', 'salamander', 0.3, '');\r\n"
       ]
      }
     ],
     "prompt_number": 3
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "!psql -c \"SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'\""
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        " table_name \r\n",
        "------------\r\n",
        "(0 rows)\r\n",
        "\r\n"
       ]
      }
     ],
     "prompt_number": 4
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "!ddlgenerator --inserts postgresql animals.csv | psql"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "NOTICE:  CREATE TABLE / UNIQUE will create implicit index \"animals_name_key\" for table \"animals\"\r\n"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "NOTICE:  CREATE TABLE / UNIQUE will create implicit index \"animals_species_key\" for table \"animals\"\r\n",
        "NOTICE:  CREATE TABLE / UNIQUE will create implicit index \"animals_kg_key\" for table \"animals\"\r\n",
        "NOTICE:  CREATE TABLE / UNIQUE will create implicit index \"animals_notes_key\" for table \"animals\"\r\n",
        "CREATE TABLE\r\n",
        "INSERT 0 1\r\n"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "INSERT 0 1\r\n",
        "INSERT 0 1\r\n"
       ]
      }
     ],
     "prompt_number": 5
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "!psql -c \"SELECT * FROM animals\""
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "   name   |  species   |  kg   |     notes     \r\n",
        "----------+------------+-------+---------------\r\n",
        " Alfred   | wart hog   |  22.0 | loves turnips\r\n",
        " Gertrude | polar bear | 312.7 | deep thinker\r\n",
        " Emily    | salamander |   0.3 | \r\n",
        "(3 rows)\r\n",
        "\r\n"
       ]
      }
     ],
     "prompt_number": 6
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "xml"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "cat countries.xml"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "<?xml version=\"1.0\"?>\r\n",
        "<data>\r\n",
        "    <country name=\"Liechtenstein\">\r\n",
        "        <rank>1</rank>\r\n",
        "        <year>2008</year>\r\n",
        "        <gdppc>141100</gdppc>\r\n",
        "        <neighbor name=\"Austria\" direction=\"E\"/>\r\n",
        "        <neighbor name=\"Switzerland\" direction=\"W\"/>\r\n",
        "    </country>\r\n",
        "    <country name=\"Singapore\">\r\n",
        "        <rank>4</rank>\r\n",
        "        <year>2011</year>\r\n",
        "        <gdppc>59900</gdppc>\r\n",
        "        <neighbor name=\"Malaysia\" direction=\"N\"/>\r\n",
        "    </country>\r\n",
        "    <country name=\"Panama\">\r\n",
        "        <rank>68</rank>\r\n",
        "        <year>2011</year>\r\n",
        "        <gdppc>13600</gdppc>\r\n",
        "        <neighbor name=\"Costa Rica\" direction=\"W\"/>\r\n",
        "        <neighbor name=\"Colombia\" direction=\"E\"/>\r\n",
        "    </country>\r\n",
        "</data>\r\n"
       ]
      }
     ],
     "prompt_number": 7
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "!ddlgenerator --inserts postgresql countries.xml | psql -q"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "NOTICE:  CREATE TABLE will create implicit sequence \"countries_countries_id_seq\" for serial column \"countries.countries_id\"\r\n"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index \"countries_pkey\" for table \"countries\"\r\n",
        "NOTICE:  CREATE TABLE / UNIQUE will create implicit index \"countries_name_key\" for table \"countries\"\r\n",
        "NOTICE:  CREATE TABLE / UNIQUE will create implicit index \"countries_rank_key\" for table \"countries\"\r\n"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "NOTICE:  CREATE TABLE / UNIQUE will create implicit index \"countries_gdppc_key\" for table \"countries\"\r\n",
        "NOTICE:  CREATE TABLE / UNIQUE will create implicit index \"countries_neighbor_tag_key\" for table \"countries\"\r\n",
        "NOTICE:  CREATE TABLE / UNIQUE will create implicit index \"countries_neighbor_name_key\" for table \"countries\"\r\n",
        "NOTICE:  CREATE TABLE / UNIQUE will create implicit index \"countries_neighbor_direction_key\" for table \"countries\"\r\n",
        "NOTICE:  CREATE TABLE / UNIQUE will create implicit index \"neighbor_name_key\" for table \"neighbor\"\r\n"
       ]
      }
     ],
     "prompt_number": 8
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "!psql -c \"SELECT * FROM countries\""
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "   tag   |     name      | rank | year | gdppc  | countries_id | neighbor_tag | neighbor_name | neighbor_direction \r\n",
        "---------+---------------+------+------+--------+--------------+--------------+---------------+--------------------\r\n",
        " country | Liechtenstein |    1 | 2008 | 141100 |            1 |              |               | \r\n",
        " country | Singapore     |    4 | 2011 |  59900 |            2 | neighbor     | Malaysia      | f\r\n",
        " country | Panama        |   68 | 2011 |  13600 |            3 |              |               | \r\n",
        "(3 rows)\r\n",
        "\r\n"
       ]
      }
     ],
     "prompt_number": 9
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "json"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "cat menu.json"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "[\r\n",
        "  {\r\n",
        "    \"name\": \"soup\",\r\n",
        "    \"cost\": 4.99\r\n",
        "  }, \r\n",
        "  { \r\n",
        "    \"name\": \"sweet potatoes\",\r\n",
        "    \"cost\": 4.99\r\n",
        "  },\r\n",
        "  {\r\n",
        "    \"name\": \"nuts\",\r\n",
        "    \"warning\": \"contains nuts\", \r\n",
        "    \"cost\": 2.95 \r\n",
        "  }\r\n",
        "]\r\n",
        "\r\n"
       ]
      }
     ],
     "prompt_number": 10
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "!ddlgenerator --inserts postgresql menu.json | psql -q"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "NOTICE:  CREATE TABLE / UNIQUE will create implicit index \"menu_name_key\" for table \"menu\"\r\n"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "NOTICE:  CREATE TABLE / UNIQUE will create implicit index \"menu_warning_key\" for table \"menu\"\r\n"
       ]
      }
     ],
     "prompt_number": 11
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "!psql -c \"SELECT * FROM menu\""
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "      name      | cost |    warning    \r\n",
        "----------------+------+---------------\r\n",
        " soup           | 4.99 | \r\n",
        " sweet potatoes | 4.99 | \r\n",
        " nuts           | 2.95 | contains nuts\r\n",
        "(3 rows)\r\n",
        "\r\n"
       ]
      }
     ],
     "prompt_number": 12
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "yaml"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "cat knights.yaml"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "-\r\n",
        "  name: Lancelot\r\n",
        "  dob: 9 jan 471\r\n",
        "  kg: 82\r\n",
        "  brave: y\r\n",
        "- \r\n",
        "  name: Gawain\r\n",
        "  kg: 69.2\r\n",
        "  brave: y\r\n",
        "- \r\n",
        "  name: Robin\r\n",
        "  dob: 9 jan 471\r\n",
        "  brave: n\r\n",
        "- \r\n",
        "  name: Reepacheep\r\n",
        "  kg: 0.0691\r\n",
        "  brave: y\r\n"
       ]
      }
     ],
     "prompt_number": 13
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "!ddlgenerator --inserts postgresql knights.yaml | psql -q"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "NOTICE:  CREATE TABLE / UNIQUE will create implicit index \"knights_name_key\" for table \"knights\"\r\n"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "NOTICE:  CREATE TABLE / UNIQUE will create implicit index \"knights_kg_key\" for table \"knights\"\r\n"
       ]
      }
     ],
     "prompt_number": 14
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "!psql -c \"SELECT * FROM knights\""
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "    name    |         dob         |   kg    | brave \r\n",
        "------------+---------------------+---------+-------\r\n",
        " Lancelot   | 0471-01-09 00:00:00 | 82.0000 | t\r\n",
        " Gawain     |                     | 69.2000 | t\r\n",
        " Robin      | 0471-01-09 00:00:00 |         | f\r\n",
        " Reepacheep |                     |  0.0691 | t\r\n",
        "(4 rows)\r\n",
        "\r\n"
       ]
      }
     ],
     "prompt_number": 15
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "html"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "!ddlgenerator --inserts postgresql http://en.wikipedia.org/wiki/List_of_cities_in_Ohio | psql -q"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "NOTICE:  CREATE TABLE / UNIQUE will create implicit index \"generated_table0_city_key\" for table \"generated_table0\"\r\n"
       ]
      }
     ],
     "prompt_number": 16
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "!psql -c \"SELECT * FROM generated_table0 LIMIT 10\" "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "   city    | population |              county              \r\n",
        "-----------+------------+----------------------------------\r\n",
        " Akron     | 199,110    | Summit County\r\n",
        " Alliance  | 22,322     | Stark County and Mahoning County\r\n",
        " Amherst   | 12,021     | Lorain County\r\n",
        " Ashland   | 20,362     | Ashland County\r\n",
        " Ashtabula | 19,124     | Ashtabula County\r\n",
        " Athens    | 23,832     | Athens County\r\n",
        " Aurora    | 15,548     | Portage County\r\n",
        " Avon      | 21,193     | Lorain County\r\n",
        " Avon Lake | 22,581     | Lorain County\r\n",
        " Barberton | 26,550     | Summit County\r\n",
        "(10 rows)\r\n",
        "\r\n"
       ]
      }
     ],
     "prompt_number": 17
    },
    {
     "cell_type": "heading",
     "level": 3,
     "metadata": {},
     "source": [
      "warning"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "cat birds.yaml"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "-\r\n",
        "  common name: Northern Cardinal\r\n",
        "  scientific name: Carnidalis cardinalis\r\n",
        "  length in cm: 21\r\n",
        "  state: \r\n",
        "    - \r\n",
        "      name: Illinois\r\n",
        "      abbrev: IL\r\n",
        "    -\r\n",
        "      name: Indiana\r\n",
        "      abbrev: IN\r\n",
        "    - \r\n",
        "      name: Kentucky\r\n",
        "      abbrev: KY\r\n",
        "    - \r\n",
        "      name: North Carolina\r\n",
        "      abbrev: NC\r\n",
        "    - \r\n",
        "      name: Ohio\r\n",
        "      abbrev: OH\r\n",
        "    - \r\n",
        "      name: Virginia\r\n",
        "      abbrev: VA\r\n",
        "    - \r\n",
        "      name: West Virginia\r\n",
        "      abbrev: WV\r\n",
        "- \r\n",
        "  common name: Great Northern Loon\r\n",
        "  scientific name: Gavia immer\r\n",
        "  state:\r\n",
        "    - \r\n",
        "      name: Minnesota\r\n",
        "      abbrev: MN\r\n"
       ]
      }
     ],
     "prompt_number": 18
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "!ddlgenerator --inserts postgresql birds.yaml | psql -q"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "NOTICE:  CREATE TABLE will create implicit sequence \"birds_birds_id_seq\" for serial column \"birds.birds_id\"\r\n",
        "NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index \"birds_pkey\" for table \"birds\"\r\n"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "NOTICE:  CREATE TABLE / UNIQUE will create implicit index \"birds_common_name_key\" for table \"birds\"\r\n",
        "NOTICE:  CREATE TABLE / UNIQUE will create implicit index \"birds_scientific_name_key\" for table \"birds\"\r\n",
        "NOTICE:  CREATE TABLE / UNIQUE will create implicit index \"birds_length_in_cm_key\" for table \"birds\"\r\n",
        "NOTICE:  CREATE TABLE / UNIQUE will create implicit index \"state_name_key\" for table \"state\"\r\n",
        "NOTICE:  CREATE TABLE / UNIQUE will create implicit index \"state_abbrev_key\" for table \"state\"\r\n"
       ]
      }
     ],
     "prompt_number": 19
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "!psql -c \"SELECT * FROM birds\" "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "     common_name     |    scientific_name    | length_in_cm | birds_id \r\n",
        "---------------------+-----------------------+--------------+----------\r\n",
        " Northern Cardinal   | Carnidalis cardinalis |           21 |        1\r\n",
        " Great Northern Loon | Gavia immer           |              |        2\r\n",
        "(2 rows)\r\n",
        "\r\n"
       ]
      }
     ],
     "prompt_number": 20
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "!psql -c \"\\d state\" "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "             Table \"public.state\"\r\n",
        "  Column  |         Type          | Modifiers \r\n",
        "----------+-----------------------+-----------\r\n",
        " name     | character varying(14) | not null\r\n",
        " abbrev   | character varying(2)  | not null\r\n",
        " birds_id | integer               | not null\r\n",
        "Indexes:\r\n",
        "    \"state_abbrev_key\" UNIQUE CONSTRAINT, btree (abbrev)\r\n",
        "    \"state_name_key\" UNIQUE CONSTRAINT, btree (name)\r\n",
        "Foreign-key constraints:\r\n",
        "    \"state_birds_id_fkey\" FOREIGN KEY (birds_id) REFERENCES birds(birds_id)\r\n",
        "\r\n"
       ]
      }
     ],
     "prompt_number": 21
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "!psql -c \"SELECT * FROM state\" "
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "      name      | abbrev | birds_id \r\n",
        "----------------+--------+----------\r\n",
        " Illinois       | IL     |        1\r\n",
        " Indiana        | IN     |        1\r\n",
        " Kentucky       | KY     |        1\r\n",
        " North Carolina | NC     |        1\r\n",
        " Ohio           | OH     |        1\r\n",
        " Virginia       | VA     |        1\r\n",
        " West Virginia  | WV     |        1\r\n",
        " Minnesota      | MN     |        2\r\n",
        "(8 rows)\r\n",
        "\r\n"
       ]
      }
     ],
     "prompt_number": 22
    },
    {
     "cell_type": "heading",
     "level": 1,
     "metadata": {},
     "source": [
      "https://pypi.python.org/pypi/ddlgenerator"
     ]
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "https://github.com/catherinedevlin/ddl-generator"
     ]
    },
    {
     "cell_type": "markdown",
     "metadata": {},
     "source": [
      "- bug reports\n",
      "- test data sets\n",
      "- fixes\n",
      "- feature requests"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    }
   ],
   "metadata": {}
  }
 ]
}