Wednesday, August 10, 2016

IPND, stage 5, back-end, 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'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, count(*) as num
  from animals, taxonomy, ordernames
  where animals.species = and taxonomy.t_order = ordernames.t_order
  group by
  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


select, animals.species, 
from animals join diet 
on animals.species = diet.speices
where food = 'fish';
-- show item with 0 count
select, 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.building,
       from residences as a, residences as b
 where a.building = b.building
   and =
   and >
 order by a.building,;


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
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:
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.