08-create.ipynb

   1 {
   2  "metadata": {
   3   "name": ""
   4  },
   5  "nbformat": 3,
   6  "nbformat_minor": 0,
   7  "worksheets": [
   8   {
   9    "cells": [
  10     {
  11      "cell_type": "heading",
  12      "level": 2,
  13      "metadata": {},
  14      "source": [
  15       "Creating and Modifying Data"
  16      ]
  17     },
  18     {
  19      "cell_type": "markdown",
  20      "metadata": {
  21       "cell_tags": [
  22        "objectives"
  23       ]
  24      },
  25      "source": [
  26       "#### Objectives\n",
  27       "\n",
  28       "*   Write queries that creates tables.\n",
  29       "*   Write queries to insert, modify, and delete records."
  30      ]
  31     },
  32     {
  33      "cell_type": "markdown",
  34      "metadata": {},
  35      "source": [
  36       "So far we have only looked at how to get information out of a database,\n",
  37       "both because that is more frequent than adding information,\n",
  38       "and because most other operations only make sense\n",
  39       "once queries are understood.\n",
  40       "If we want to create and modify data,\n",
  41       "we need to know two other pairs of commands.\n",
  42       "  \n",
  43       "The first pair are `create table` and `drop table`.\n",
  44       "While they are written as two words,\n",
  45       "they are actually single commands.\n",
  46       "The first one creates a new table;\n",
  47       "its arguments are the names and types of the table's columns.\n",
  48       "For example,\n",
  49       "the following statements create the four tables in our survey database:\n",
  50       "\n",
  51       "~~~\n",
  52       "create table Person(ident text, personal text, family text);\n",
  53       "create table Site(name text, lat real, long real);\n",
  54       "create table Visited(ident integer, site text, dated text);\n",
  55       "create table Survey(taken integer, person text, quant real, reading real);\n",
  56       "~~~\n",
  57       "\n",
  58       "We can get rid of one of our tables using:\n",
  59       "\n",
  60       "~~~\n",
  61       "drop table Survey;\n",
  62       "~~~\n",
  63       "\n",
  64       "Be very careful when doing this:\n",
  65       "most databases have some support for undoing changes,\n",
  66       "but it's better not to have to rely on it.\n",
  67       "  \n",
  68       "Different database systems support different data types for table columns,\n",
  69       "but most provide the following:\n",
  70       "\n",
  71       "<table>\n",
  72       "  <tr> <td>integer</td> <td>a signed integer</td> </tr>\n",
  73       "  <tr> <td>real</td> <td>a floating point number</td> </tr>\n",
  74       "  <tr> <td>text</td> <td>a character string</td> </tr>\n",
  75       "  <tr> <td>blob</td> <td>a \"binary large object\", such as an image</td> </tr>\n",
  76       "</table>\n",
  77       "\n",
  78       "Most databases also support Booleans and date/time values;\n",
  79       "SQLite uses the integers 0 and 1 for the former,\n",
  80       "and represents the latter as discussed [earlier](#a:dates).\n",
  81       "An increasing number of databases also support geographic data types,\n",
  82       "such as latitude and longitude.\n",
  83       "Keeping track of what particular systems do or do not offer,\n",
  84       "and what names they give different data types,\n",
  85       "is an unending portability headache.\n",
  86       "  \n",
  87       "When we create a table,\n",
  88       "we can specify several kinds of constraints on its columns.\n",
  89       "For example,\n",
  90       "a better definition for the `Survey` table would be:\n",
  91       "\n",
  92       "~~~\n",
  93       "create table Survey(\n",
  94       "    taken   integer not null, -- where reading taken\n",
  95       "    person  text,             -- may not know who took it\n",
  96       "    quant   real not null,    -- the quantity measured\n",
  97       "    reading real not null,    -- the actual reading\n",
  98       "    primary key(taken, quant),\n",
  99       "    foreign key(taken) references Visited(ident),\n",
 100       "    foreign key(person) references Person(ident)\n",
 101       ");\n",
 102       "~~~\n",
 103       "\n",
 104       "Once again,\n",
 105       "exactly what constraints are avialable\n",
 106       "and what they're called\n",
 107       "depends on which database manager we are using.\n",
 108       "  \n",
 109       "Once tables have been created,\n",
 110       "we can add and remove records using our other pair of commands,\n",
 111       "`insert` and `delete`.\n",
 112       "The simplest form of `insert` statement lists values in order:\n",
 113       "\n",
 114       "~~~\n",
 115       "insert into Site values('DR-1', -49.85, -128.57);\n",
 116       "insert into Site values('DR-3', -47.15, -126.72);\n",
 117       "insert into Site values('MSK-4', -48.87, -123.40);\n",
 118       "~~~\n",
 119       "\n",
 120       "We can also insert values into one table directly from another:\n",
 121       "\n",
 122       "~~~\n",
 123       "create table JustLatLong(lat text, long text);\n",
 124       "insert into JustLatLong select lat, long from site;\n",
 125       "~~~\n",
 126       "\n",
 127       "Deleting records can be a bit trickier,\n",
 128       "because we have to ensure that the database remains internally consistent.\n",
 129       "If all we care about is a single table,\n",
 130       "we can use the `delete` command with a `where` clause\n",
 131       "that matches the records we want to discard.\n",
 132       "For example,\n",
 133       "once we realize that Frank Danforth didn't take any measurements,\n",
 134       "we can remove him from the `Person` table like this:\n",
 135       "\n",
 136       "~~~\n",
 137       "delete from Person where ident = \"danforth\";\n",
 138       "~~~\n",
 139       "\n",
 140       "But what if we removed Anderson Lake instead?\n",
 141       "Our `Survey` table would still contain seven records\n",
 142       "of measurements he'd taken,\n",
 143       "but that's never supposed to happen:\n",
 144       "`Survey.person` is a foreign key into the `Person` table,\n",
 145       "and all our queries assume there will be a row in the latter\n",
 146       "matching every value in the former.\n",
 147       "  \n",
 148       "This problem is called [referential integrity](https://github.com/swcarpentry/bc/blob/master/gloss.md#referential-integrity):\n",
 149       "we need to ensure that all references between tables can always be resolved correctly.\n",
 150       "One way to do this is to delete all the records\n",
 151       "that use `'lake'` as a foreign key\n",
 152       "before deleting the record that uses it as a primary key.\n",
 153       "If our database manager supports it,\n",
 154       "we can automate this\n",
 155       "using [cascading delete](https://github.com/swcarpentry/bc/blob/master/gloss.md#cascading-delete).\n",
 156       "However,\n",
 157       "this technique is outside the scope of this chapter.\n",
 158       "\n",
 159       "> Many applications use a hybrid storage model\n",
 160       "> instead of putting everything into a database:\n",
 161       "> the actual data (such as astronomical images) is stored in files,\n",
 162       "> while the database stores the files' names,\n",
 163       "> their modification dates,\n",
 164       "> the region of the sky they cover,\n",
 165       "> their spectral characteristics,\n",
 166       "> and so on.\n",
 167       "> This is also how most music player software is built:\n",
 168       "> the database inside the application keeps track of the MP3 files,\n",
 169       "> but the files themselves live on disk."
 170      ]
 171     },
 172     {
 173      "cell_type": "markdown",
 174      "metadata": {},
 175      "source": [
 176       "#### Challenges\n",
 177       "\n",
 178       "1.  Write an SQL statement to replace all uses of `null`\n",
 179       "    in `Survey.person`\n",
 180       "    with the string `'unknown'`.\n",
 181       "\n",
 182       "2.  One of our colleagues has sent us a [CSV](https://github.com/swcarpentry/bc/blob/master/gloss.md#csv) file\n",
 183       "    containing temperature readings by Robert Olmstead,\n",
 184       "    which is formatted like this:\n",
 185       "\n",
 186       "    ~~~\n",
 187       "    Taken,Temp\n",
 188       "    619,-21.5\n",
 189       "    622,-15.5\n",
 190       "    ~~~\n",
 191       "\n",
 192       "    Write a small Python program that reads this file in\n",
 193       "    and prints out the SQL `insert` statements needed\n",
 194       "    to add these records to the survey database.\n",
 195       "    Note: you will need to add an entry for Olmstead\n",
 196       "    to the `Person` table.\n",
 197       "    If you are testing your program repeatedly,\n",
 198       "    you may want to investigate SQL's `insert or replace` command.\n",
 199       "\n",
 200       "3.  SQLite has several administrative commands that aren't part of the SQL standard.\n",
 201       "    One of them is `.dump`,\n",
 202       "    which prints the SQL commands needed to re-create the database.\n",
 203       "    Another is `.load`,\n",
 204       "    which reads a file created by `.dump` and restores the database.\n",
 205       "    A colleague of yours thinks that storing dump files (which are text) in version control\n",
 206       "    is a good way to track and manage changes to the database.\n",
 207       "    What are the pros and cons of this approach?\n",
 208       "    (Hint: records aren't stored in any particular order.)"
 209      ]
 210     },
 211     {
 212      "cell_type": "markdown",
 213      "metadata": {
 214       "cell_tags": [
 215        "keypoints"
 216       ]
 217      },
 218      "source": [
 219       "#### Key Points\n",
 220       "\n",
 221       "*   Database tables are created using queries that specify their names and the names and properties of their fields.\n",
 222       "*   Records can be inserted, updated, or deleted using queries.\n",
 223       "*   It is simpler and safer to modify data when every record has a unique primary key."
 224      ]
 225     }
 226    ],
 227    "metadata": {}
 228   }
 229  ]
 230 }