In [37]:
!libreoffice data/aelfryth.odt
In [38]:
!libreoffice data/party.ods
In [39]:
!cat data/party.csv
In [40]:
from csv import DictReader
from pprint import pprint
with open('data/party.csv') as infile:
party = list(DictReader(infile))
pprint(party)
In [41]:
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)
In [42]:
!libreoffice data/party.multisheet.ods
In [43]:
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)
In [44]:
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)
In [45]:
!cat data/party.json
How much does all the fighters' equipment weigh?
In [46]:
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)
In [48]:
!echo "db.party.drop()" | mongo
In [49]:
!mongoimport --collection party --jsonArray data/party.json
In [50]:
!echo "db.party.find()" | mongo
How much weight are the party's fighters carrying?
In [51]:
!echo "db.party.find({class: 'Fighter'}, {'equipment': 1, _id: 0})" | mongo
In [52]:
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)
In [53]:
party.append( {'name': 'Mickey',
'class': 'Druid/Gunslinger/Paladin/Illusionist/Assassin',
'level': 87,
'psionic level': 19} )
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=#
In [54]:
!cat sql/ddl/create_char_tbl.sql
dnd=# \i sql/ddl/create_char_tbl.sql
CREATE TABLE
In [55]:
!cat sql/dml/ins_single_char.sql
dnd=# \i sql/dml/ins_single_char.sql
INSERT 0 1dnd=# 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)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)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
In [58]:
!cat sql/dml/godric2.sql
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.
In [59]:
!cat sql/ddl/create_equip_tbl.sql
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 14All 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)
In [60]:
!cat sql/dml/mickey.sql
dnd# \i sql/dml/mickey.sql
ERROR: column "psionic_level" of relation "character" does not exist
LINE 3: psionic_level, 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).
In [61]:
!cat sql/dml/thief_inventories.sql
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=# 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;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
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)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)DELETE = apocalypsednd=# 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
In [63]:
import psycopg2
import psycopg2.extras
conn = psycopg2.connect('dbname=dnd user=dungeonmaster password=gygax')
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()
Questions?
catherinedevlin.blogspot.com github.com/catherinedevlin/sql_quest
Images (except book covers) from British Library's public release
In [ ]: