<jupyter_start><jupyter_text>SQLQuestCatherine DevlinOhio LinuxFest 2015, Oct 3https://github.com/catherinedevlin/sql_quest![knights](img/knights.jpg)  Me* Database administrator since 1999* Python programmer since 2003* First chair of [PyOhio](http://pyohio.org)* catherinedevlin.blogspot.com, @catherinedevlin* [Your employee](https://18f.gsa.gov) You* Total SQL n00b.  Please!  Adventure Synopsis* Basic: CSV* Advanced: JSON* Expert: Relational![duel with monster](img/monster_duel.jpg)  In the beginning...![D&D character sheet](img/charsheet.png)  Into ElectronicaAll but useless electronically<jupyter_code>!libreoffice data/aelfryth.odt<jupyter_output><empty_output><jupyter_text>![character sheet as word processing document](img/wordproc.png)  CSV<jupyter_code>!libreoffice data/party.ods<jupyter_output><empty_output><jupyter_text>![spreadsheet of party info](img/spreadsheet1.png)<jupyter_code>!cat data/party.csv
from csv import DictReader
from pprint import pprint
with open('data/party.csv') as infile:
    party = list(DictReader(infile))
    
pprint(party)<jupyter_output>[{'charisma': '14',
  'class': 'Fighter',
  'constitution': '15',
  'dexterity': '15',
  'equip 1 cost': '7',
  'equip 1 magic': '',
  'equip 1 materials': 'wood, cloth',
  'equip 1 name': 'shield',
  'equip 1 notes': '',
  'equip 1 quantity': '1',
  'equip 1 weight each': '10',
  'equip 2 cost': '4',
  'equip 2 magic': '',
  'equip 2 materials': 'cloth',
  'equip 2 name': 'Tent',
  'equip 2 notes': 'sleeps 4',
  'equip 2 quantity': '1',
  'equip 2 weight each': '15',
  'equip 3 cost': '30',
  'equip 3 magic': '',
  'equip 3 materials': 'wood',
  'equip 3 name': 'bow',
  'equip 3 notes': '',
  'equip 3 quantity': '1',
  'equip 3 weight each': '2',
  'equip 4 cost': '0.1',
  'equip 4 magic': '',
  'equip 4 materials': 'wood, iron',
  'equip 4 name': 'arrows',
  'equip 4 notes': '',
  'equip 4 quantity': '12',
  'equip 4 weight each': '0.1',
  'intelligence': '11',
  'level': '5',
  'name': 'Aelfryth',
  'role': 'Thane',
  'strength': '16',
  'wisdom': '14'},
 {'charisma': '12',
  'class[...]<jupyter_text>Answering questionsHow much weight are the party's fighters carrying?<jupyter_code>total_weight = 0
for character in party:
    if character['class'] == 'Fighter':
        for eq_index in range(1, 4):
            try:
                quantity = int(character['equip %d quantity' % eq_index])
                weight_each = float(character['equip %d weight each' % eq_index])
                total_weight += quantity * weight_each
            except (TypeError, ValueError):
                pass
print(total_weight)<jupyter_output>56.0<jupyter_text>Save vs. Confusion* custom program* hardcoded number of equipment slots![lost](img/lost.jpg)  Relational (from CSV)<jupyter_code>!libreoffice data/party.multisheet.ods<jupyter_output><empty_output><jupyter_text>![spreadsheet with equipment info relationalized](img/spreadsheet2.png)<jupyter_code>from csv import DictReader
from pprint import pprint
with open('data/party.multisheet/stats-Table 1.csv') as infile:
    stats = list(DictReader(infile))
with open('data/party.multisheet/equipment-Table 1.csv') as infile:
    equipment = list(DictReader(infile))
pprint(stats)
total_weight = 0
for character in stats:
    if character['class'] == 'Fighter':
        for itm in equipment:
            if itm['owner'] == character['name']:
                try:
                    quantity = int(itm['quantity'])
                    weight_each = float(itm['weight each'])
                    total_weight += quantity * weight_each
                except (TypeError, ValueError):
                    pass
print(total_weight)<jupyter_output>57.2<jupyter_text>Still ouch![pained face](img/ouch.jpg)  JSONarbitrary structure<jupyter_code>!cat data/party.json<jupyter_output>[
  {
    "class": "Thief",
    "role": "Runaway Thrall",
    "level": 3,
    "equipment": {
      "torches": {
        "quantity": 3,
        "materials": [
          "wood"
        ],
        "weight each": 2.0,
        "magic": "",
        "notes": "",
        "cost": 2.0
      },
      "cudgel": {
        "quantity": 1,
        "materials": [
          "wood"
        ],
        "weight each": 3.0,
        "magic": "",
        "notes": "",
        "cost": 3.0,
        "damage": "d6"
      },
      "cookpot": {
        "quantity": 1,
        "materials": [
          "iron"
        ],
        "weight each": 10.0,
        "magic": "",
        "notes": "",
        "cost": 10.0
      },
      "dagger": {
        "quantity": 1,
        "materials": [
          "iron"
        ],
        "weight each": 1.0,
        "magic": "",
        "notes": "",
        "cost": 1.0,
        "damage": "d4"
      }
    },
    "name": "Wigstan",
    "stats":[...]<jupyter_text>How much does all the fighters' equipment weigh?<jupyter_code>import json
import glob
total_weight = 0
with open('data/party.json') as infile:
    for character in json.load(infile):
        if character['class'] == 'Fighter':
            for itm in character['equipment'].values():
                total_weight += (itm['quantity'] * itm['weight each'])
print(total_weight)<jupyter_output>57.2<jupyter_text>Database management system* Multiple access* Performance* \> memory* \> 1 machine Document databases* Mongo* RethinkDB* PostgreSQL<jupyter_code>!echo "db.party.drop()" | mongo
!mongoimport --collection party --jsonArray data/party.json
!echo "db.party.find()" | mongo<jupyter_output>MongoDB shell version: 3.0.6
connecting to: test
{ "_id" : ObjectId("560f2d4d44fdf959bdca0f1a"), "class" : "Sourceror", "role" : "Enigma", "level" : 5, "equipment" : { "Unfailing Flint" : { "quantity" : 1, "materials" : [ "stone" ], "weight each" : 0.1, "magic" : "Y", "notes" : "Immediately ignites in any weather", "cost" : 0.1 }, "lamp" : { "quantity" : 1, "materials" : [ "clay" ], "weight each" : 0.2, "magic" : "", "notes" : "", "cost" : 0.2 }, "Oil of Revelation" : { "notes" : "Burned in any lamp, its light reveals all illusions, disguises, invisible", "cost" : 1, "quantity" : 1, "materials" : [ "oil", "spices" ], "weight each" : 1, "magic" : "Y" } }, "name" : "Leofflaed", "stats" : { "dexterity" : 14, "charisma" : 11, "strength" : 8, "wisdom" : 11, "intelligence" : 17, "constitution" : 12 } }
{ "_id" : ObjectId("560f2d4d44fdf959bdca0f1b"), "class" : "Fighter", "role" : "Thane", "level" : 5, "equipment" : { "arrows" : { "quantity" : 12, "materials" : [ "wood", "iron" ], "weight each[...]<jupyter_text>How much weight are the party's fighters carrying?<jupyter_code>!echo "db.party.find({class: 'Fighter'}, {'equipment': 1, _id: 0})" | mongo
from pymongo import MongoClient
client = MongoClient()
total_weight = 0.0
for character in client.test.party.find({'class':'Fighter'}):
    for itm in character['equipment'].values():
        total_weight += itm['weight each'] * itm['quantity']
print(total_weight)<jupyter_output>57.2<jupyter_text>Consistency<jupyter_code>party.append( {'name': 'Mickey',
               'class': 'Druid/Gunslinger/Paladin/Illusionist/Assassin',
               'level': 87,
               'psionic level': 19} )<jupyter_output><empty_output><jupyter_text>JSON schema enforcementkwalify, marshmallow, ...  RDBMSRelational DataBase Management System* PostgreSQL* SQLite* MySQL / MariaDB  RDBMS provides* scale* data consistency* query language: SQL* tools  DDLData Definition Language![AD&D Players' Handbook](https://upload.wikimedia.org/wikipedia/en/0/09/PlayersHandbook8Cover.jpg) Create a database and connect to it    $ psql template1 dungeonmaster    Password for user dungeonmaster:     psql (9.3.6)    Type "help" for help.    template1= create database dnd;    CREATE DATABASE    template1= \c dnd    You are now connected to database "dnd" as user "dungeonmaster".    dnd=<jupyter_code>!cat sql/ddl/create_char_tbl.sql<jupyter_output>CREATE TABLE character (
    name TEXT,
    class TEXT,
    role TEXT,
    level INTEGER,
    strength INTEGER,
    intelligence INTEGER,
    wisdom INTEGER,
    dexterity INTEGER,
    constitution INTEGER,
    charisma INTEGER );<jupyter_text>dnd= \i sql/ddl/create_char_tbl.sql     CREATE TABLE  DMLData Manipulation Language<jupyter_code>!cat sql/dml/ins_single_char.sql<jupyter_output>INSERT INTO character (
  name, class, role, level,
  strength, intelligence, wisdom, dexterity,
  constitution, charisma)
VALUES (
  'Aelfryth', 'Fighter', 'Thane', 5,
  16, 11, 14, 15,
  15, 14);<jupyter_text>dnd= \i sql/dml/ins_single_char.sql    INSERT 0 1  SELECT    dnd= SELECT * FROM character;       name   |  class  | role  | level | strength | intelligence | wisdom | dexterity | constitution | charisma     ----------+---------+-------+-------+----------+--------------+--------+-----------+--------------+----------     Aelfryth | Fighter | Thane |     5 |       16 |           11 |     14 |        15 |           15 |       14    (1 row)  Mass insert    dnd= DELETE FROM character;    DELETE 4    dnd= \copy character FROM 'data/party.multisheet/stats-Table 1.csv' WITH csv HEADER;    COPY 4  Selective SELECT    dnd= SELECT name, class, role FROM character;       name    |   class   |      role          -----------+-----------+----------------     Aelfryth  | Fighter   | Thane     Godric    | Fighter   | Warband Member     Leofflaed | Sourceror | Enigma     Wigstan   | Thief     | Runaway Thrall    (4 rows)  WHERE    dnd= SELECT name, class, role     dnd- FROM   character    dnd- WHERE  class = 'Fighter';       name   |  class  |      role          ----------+---------+----------------     Aelfryth | Fighter | Thane     Godric   | Fighter | Warband Member    (2 rows)  AND    dnd= SELECT name, class, role, intelligence    dnd- FROM   character    dnd- WHERE  class = 'Fighter'    dnd- AND    intelligence > 10;       name   |  class  | role  | intelligence     ----------+---------+-------+--------------     Aelfryth | Fighter | Thane |           11    (1 row)  Level up    dnd= SELECT name, level FROM character;       name    | level     -----------+-------     Aelfryth  |     5     Godric    |     4     Leofflaed |     5     Wigstan   |     3    (4 rows)  UPDATE    dnd= UPDATE character    dnd- SET    level = 4    dnd- WHERE  name = 'Wigstan';    UPDATE 1    dnd= SELECT name, level FROM character;                                                                                    name      | level     -----------+-------     Aelfryth  |     5     Godric    |     4     Leofflaed |     5     Wigstan   |     4    (4 rows)  Unique identifiers     dnd= INSERT INTO character    dnd- SELECT * FROM character    dnd- WHERE name = 'Wigstan';    INSERT 0 1    dnd= SELECT name, class, level FROM character;       name    |   class   | level     -----------+-----------+-------     Aelfryth  | Fighter   |     5     Godric    | Fighter   |     4     Leofflaed | Sourceror |     5     Wigstan   | Thief     |     4     Wigstan   | Thief     |     4    (5 rows)  Primary Key numeric    dnd= ALTER TABLE character ADD    dnd-   id SERIAL PRIMARY KEY;    ALTER TABLE    dnd= SELECT id, name, class FROM character;     id |   name    |   class       ----+-----------+-----------      1 | Aelfryth  | Fighter      2 | Godric    | Fighter      3 | Leofflaed | Sourceror      4 | Wigstan   | Thief      5 | Wigstan   | Thief    (5 rows)    dnd= UPDATE character     dnd- SET    name = 'Wigmund'    dnd- WHERE  id = 5;    UPDATE 1    dnd= ALTER TABLE character DROP id;    ALTER TABLE  Text PK      dnd= ALTER TABLE character    dnd- ADD PRIMARY KEY (name);    ALTER TABLE<jupyter_code>!cat sql/dml/godric2.sql<jupyter_output>INSERT INTO character (
  name, class, role, level,
  strength, intelligence, wisdom, dexterity,
  constitution, charisma)
VALUES (
  'Godric', 'Ranger', 'Guide', 4,
  14, 15, 13, 15,
  16, 7);<jupyter_text>dnd= \i sql/dml/godric2.sql     psql:sql/dml/godric2.sql:8: ERROR:  duplicate key value violates unique constraint "character_pkey"    DETAIL:  Key (name)=(Godric) already exists.  Relations<jupyter_code>!cat sql/ddl/create_equip_tbl.sql<jupyter_output>CREATE TABLE equipment (
    name TEXT,
    quantity INTEGER,
    weight_each NUMERIC(4,1),
    cost NUMERIC(6,2),
    magic TEXT,
    materials TEXT,
    notes TEXT,
    owner TEXT REFERENCES character (name)
  );<jupyter_text>dnd= \i sql/ddl/create_equip_tbl.sql     CREATE TABLE    dnd= \copy equipment FROM 'data/party.multisheet/equipment-Table 1.csv' WITH csv HEADER    COPY 14  Join All Leofflaed's gear    dnd= SELECT c.name, e.name, e.magic    FROM   character c    JOIN   equipment e ON (e.owner = c.name)    WHERE  c.name = 'Leofflaed';           name    |       name        | magic     -----------+-------------------+-------     Leofflaed | Oil of Revelation | Y     Leofflaed | Unfailing Flint   | Y     Leofflaed | lamp              |  Weight of all fighters' gear    dnd= SELECT SUM(quantity * weight_each)    dnd- FROM   equipment e    dnd- JOIN   character c ON (e.owner = c.name)    dnd- WHERE  class = 'Fighter';     sum      ------     57.2    (1 row)  Constraints![Chess players](img/chess.jpg)<jupyter_code>!cat sql/dml/mickey.sql<jupyter_output>INSERT INTO character (
  name, class, role, level,
  psionic_level,
  strength, intelligence, wisdom, dexterity,
  constitution, charisma)
VALUES (
  'Mickey', 'Druid/Gunslinger/Paladin/Illusionist/Assassin', 'Demigod', 87,
  19,
  19, 19, 18, 18,
  19, 17);<jupyter_text>dnd \i sql/dml/mickey.sql    ERROR:  column "psionic_level" of relation "character" does not exist    LINE 3:   psionic_level,  Column constraints      CREATE TABLE character (        name TEXT,        class VARCHAR(14),        ...    dnd= ALTER TABLE character ALTER COLUMN class TYPE VARCHAR(14);        INSERT INTO character (    ...    ERROR:  value too long for type character varying(14)  Foreign key constraints  Check constraints      CREATE TABLE character (        ...        level INTEGER,        ...              dnd= ALTER TABLE character ADD CONSTRAINT reaonable_level CHECK (level < 15);    ERROR:  new row for relation "character" violates check constraint "character_level_check"    DETAIL:  Failing row contains (Mickey, Druid, Demigod, 87, 19, 19, 18, 18, 19, 17).          Transactions![transaction](img/transaction.jpg)<jupyter_code>!cat sql/dml/thief_inventories.sql<jupyter_output>SELECT name, owner
FROM   equipment
WHERE  owner LIKE 'Wig%';<jupyter_text>dnd= \i sql/dml/thief_inventories.sql      name   |  owner      ---------+---------     cudgel  | Wigstan     cookpot | Wigstan     dagger  | Wigstan     torches | Wigstan    (4 rows)     dnd= UPDATE equipment    dnd- SET    owner = 'Wigmund'    dnd- WHERE  owner = 'Wigstan'    dnd- AND    name = 'cudgel';    UPDATE 1    dnd= \i sql/dml/thief_inventories.sql       name   |  owner      ---------+---------     cookpot | Wigstan     dagger  | Wigstan     torches | Wigstan     cudgel  | Wigmund    (4 rows)     dnd= BEGIN TRANSACTION;    BEGIN    dnd= UPDATE equipment    dnd- SET    owner = 'Wigmund'    dnd- WHERE  owner = 'Wigstan'    dnd- AND    name = 'dagger';    UPDATE 1    dnd= SELECT name, owner    dnd- FROM   equipment    dnd- WHERE  owner LIKE 'Wig%';      name   |  owner      ---------+---------     cookpot | Wigstan     torches | Wigstan     cudgel  | Wigmund     dagger  | Wigmund    (4 rows)     dnd= ROLLBACK;    ROLLBACK    dnd= SELECT name, owner    dnd- FROM   equipment    dnd- WHERE  owner LIKE 'Wig%';      name   |  owner      ---------+---------     cookpot | Wigstan     torches | Wigstan     dagger  | Wigstan     cudgel  | Wigmund    (4 rows)    opposite is `COMMIT`     dnd= update equipment set owner = 'Wigstan' WHERE owner = 'Wigmund';    UPDATE 0    dnd=   Aggregate functions    dnd= SELECT MAX(charisma) FROM character;     max     -----      14    (1 row)    Think line count    dnd= SELECT name, MAX(charisma) FROM character;    ERROR:  column "character.name" must appear in the GROUP BY clause or be used in an aggregate function    LINE 1: SELECT name, MAX(charisma) FROM character;  Subquery    dnd= SELECT name, charisma    dnd- FROM   character    dnd- WHERE  charisma = (SELECT MAX(charisma) FROM character);       name   | charisma     ----------+----------     Aelfryth |       14    (1 row)  GROUP BY    dnd= SELECT owner, SUM(cost)     dnd- FROM   equipment    dnd- GROUP BY owner    dnd- ORDER BY SUM(cost) DESC;       owner   |  sum       -----------+--------     Leofflaed | 550.50     Aelfryth  |  41.10     Godric    |   5.12     Wigstan   |   2.42    (4 rows)  Outer joins    dnd= SELECT c.name, c.class, SUM(e.cost)    dnd- FROM   character c    dnd- JOIN   equipment e ON (e.owner = c.name)    dnd- GROUP BY c.name, c.class    dnd- ORDER BY SUM(e.cost) DESC;       name    |   class   |  sum       -----------+-----------+--------     Leofflaed | Sourceror | 550.50     Aelfryth  | Fighter   |  41.10     Godric    | Fighter   |   5.12     Wigstan   | Thief     |   2.42    (4 rows)     dnd= SELECT c.name, c.class, SUM(e.cost)    FROM   character c    LEFT OUTER JOIN equipment e ON (e.owner = c.name)    GROUP BY c.name, c.class    ORDER BY SUM(e.cost) DESC;       name    |   class   |  sum       -----------+-----------+--------     Wigmund   | Thief     |            Leofflaed | Sourceror | 550.50     Aelfryth  | Fighter   |  41.10     Godric    | Fighter   |   5.12     Wigstan   | Thief     |   2.42    (5 rows)    ... optionally, `NULLS LAST`  Traps![trap book](img/grimtooth.jpg)  The NULL trap (3-value logic)    dnd= SELECT name, magic     dnd- FROM   equipment    dnd- WHERE  magic IS NOT NULL;           name        | magic     -------------------+-------     battleaxe         | +1     Oil of Revelation | Y     Unfailing Flint   | Y    (3 rows)    dnd= SELECT name, magic    dnd- FROM   equipment    dnd- WHERE  magic = NULL;     name | magic     ------+-------    (0 rows)     dnd= SELECT 1 = 1;     ?column?     ----------     t    (1 row)    dnd= SELECT 1 = 2;     ?column?     ----------     f    (1 row)    dnd= SELECT NULL = NULL;     ?column?     ----------    (1 row)    dnd= SELECT NULL != NULL;     ?column?     ----------    (1 row)     dnd= SELECT c.name    dnd- FROM   character c    dnd- JOIN   equipment e ON (e.owner = c.name)    dnd- AND    e.magic != NULL;     name     ------    (0 rows)        dnd= SELECT c.name AS owner, e.name                                                               dnd= FROM   character c                                                                           dnd= JOIN   equipment e ON (c.name = e.owner)                                                     dnd= WHERE  magic IS NULL;       owner   |   name       -----------+----------     Aelfryth  | shield     Aelfryth  | Tent     Aelfryth  | bow     Aelfryth  | arrows     Godric    | 30’ rope     Godric    | torches     Leofflaed | lamp     Wigstan   | cookpot     Wigstan   | torches     Wigstan   | dagger     Wigmund   | cudgel    (11 rows)  Missing WHERE trap     dnd= SELECT name, level    dnd- FROM   character;       name    | level     -----------+-------     Aelfryth  |     5     Godric    |     4     Leofflaed |     5     Wigstan   |     5     Wigmund   |     5    (5 rows)    dnd= UPDATE character    dnd- SET    level = 6;    UPDATE 5    dnd= SELECT name, level    dnd- FROM   character;       name    | level     -----------+-------     Aelfryth  |     6     Godric    |     6     Leofflaed |     6     Wigstan   |     6     Wigmund   |     6    (5 rows)  Unqualified `DELETE` = apocalypse    dnd= BEGIN TRANSACTION;    BEGIN    dnd= DELETE FROM equipment;    DELETE 14    dnd= SELECT * FROM equipment;     name | quantity | weight_each | cost | magic | materials | notes | owner | damage     ------+----------+-------------+------+-------+-----------+-------+-------+--------    (0 rows)    dnd= ROLLBACK;    ROLLBACK  Triggers![woman with crossbow](img/crossbow2.jpg)  Programming<jupyter_code>import psycopg2
import psycopg2.extras
conn = psycopg2.connect('dbname=dnd user=dungeonmaster password=<PASSWORD>')
curs = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
curs.execute("SELECT * FROM character")
for character in curs.fetchall():
    print(character)
    if character['class'] == 'Fighter':
        print('{name}, get in front!'.format(**character))
conn.close()<jupyter_output>['Aelfryth', 'Fighter', 'Thane', 5, 16, 11, 14, 15, 15, 14]
Aelfryth, get in front!
['Godric', 'Fighter', 'Warband Member', 4, 17, 9, 7, 11, 16, 12]
Godric, get in front!
['Leofflaed', 'Sourceror', 'Enigma', 5, 8, 17, 11, 14, 12, 11]
['Wigstan', 'Thief', 'Runaway Thrall', 4, 14, 12, 9, 15, 14, 10]
['Wigmund', 'Thief', 'Runaway Thrall', 4, 14, 12, 9, 15, 14, 10]
{'fraction_non_alphanumeric': 0.15076225395600154, 'fraction_numerical': 0.023784253184098804, 'mean_word_length': 2.1695718654434253, 'pattern_counts': {'":': 36, '<': 61, '<?xml version=': 0, '>': 63, 'https://': 3, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 16, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '18', 'dir': 'jupyter-structured-clean-dedup', 'id': '157135', 'license': 'CC0-1.0', 'n_tokens_mistral': 7288, 'n_tokens_neox': 6642, 'n_words': 1924, 'path': 'sqlquest.ipynb', 'repo_name': 'catherinedevlin/sql_quest', 'size': '20723'}