IPND, stage 5, back-end, SQL

[TOC]

SQL

Structured query language

Database has several tables for different purposes.

basic query

select food from diet where species = "orangutan";
select * from tablename limit 10 offset 2;
select name, birthdate from animals where species != 'gorilla';
insert into tablename  values ( val1, val2, … );

select name from animals, diet where animals.species=diet.species and diet.food='fish';

select count (species),species from animals group by species order by count(species) DESC;
select animals group by species having num=1; # after aggregation

select ordernames.name, count(*) as num
  from animals, taxonomy, ordernames
  where animals.species = taxonomy.name and taxonomy.t_order = ordernames.t_order
  group by ordernames.name
  order by num desc;

update posts set content='cheese' where content like '%spam%';

delete from posts where content='cheese';

Create/drop database/table, primary key, references

create database name;
create table fish(id serial primary key, name text);
create table postal_places(postal_code text,country text, name text, primary key(postal_code, country));
create table sales(sku text references products, sale_date date, count integer); # use references for foreign key

drop database name;
drop table name;

delete from tablename;
insert into tablename values ('This is text!');

\c name # connect to database

join

select animals.name, animals.species, diet.food 
from animals join diet 
on animals.species = diet.speices
where food = 'fish';
-- show item with 0 count
select products.name, products.sku, count(sales.sku) as num
  from products left join sales
    on products.sku = sales.sku
  group by products.sku;
-- self join
select a.id, b.id, a.building, a.room
       from residences as a, residences as b
 where a.building = b.building
   and a.room = b.room
   and a.id > b.id
 order by a.building, a.room;

subQuery

select name, weight from players, (select avg(weight) as av from players) as subq where weight< av;
create view name as select ...

Python DB-API

use PosreSQL

always use 2 command windows

1. for python

python forum.py
http://localhost:8000/ # you can open it on a browser

1. for psql

psql forum   # load forum database, equal to" \i forum.sql"
select 2+2 as a, 4+4 as b;
select * from posts;
select * from posts \watch  # update very 2 seconds, so you can see what's added
\d posts  # get columns and type
\dt  # list all tables
\H #switch b/w plain text vs HTML

loop hole

something fun: http://xkcd.com/

sql injection attack: '); delete from posts; —

script injeciton attack

DB-API using sqlite3

import sqlite3
with sqlite3.connect("chinook.db") as conn:
    cursor = conn.cursor()
    rows = cursor.execute('select * from artist limit 10;').fetchall()
    conn.commit() # required if insert new data
for row in rows: print row

normalized design, which makes it easier to write effective code using a database.
A Simple Guide to Five Normal Forms in Relational Database Theory

normalized table

1. Every row has the same number of columns,
2. There’s a unique key. Everything in a row says something about the key.
3. Facts that don’t relate to the key belong in different tables,
4. Tables should’t imply relationships that don’t exist.

​