Category: sqlite / MySql / psql

Python DB-API is a library method call sqlite and data.

writing DB-API

import sqlite3
conn = sqlite3.connect("Cookies")
cursor = conn.cursor()
cursor.execute(
    "select host_key from cookies limit 10")

results = cursor.fetchall()
print results
conn.close()

Watch how DB-API work below.

import sqlite3

# Fetch some student records from the database.
db = sqlite3.connect("students")
c = db.cursor()
query = "select name, id from students;"
c.execute(query)
rows = c.fetchall()

# First, what data structure did we get?
print "Row data:"
print rows

# And let's loop over it too:
print
print "Student names:"
for row in rows:
  print "  ", row[0]

db.close()

Result
Row data:
[(u’Jade Harley’, 441304), (u’Harry Evans-Verres’, 172342), (u’Taylor Hebert’, 654321), (u’Diane Paiwonski’, 773217), (u’Melpomene Murray’, 102030), (u’Robert Oliver Howard’, 124816), (u’Hoban Washburne’, 186753), (u’Trevor Bruttenholm’, 162636), (u’Jonathan Frisby’, 917151)]

Student names:
Jade Harley
Harry Evans-Verres
Taylor Hebert
Diane Paiwonski
Melpomene Murray
Robert Oliver Howard
Hoban Washburne
Trevor Bruttenholm
Jonathan Frisby

order by [ASC | DESC]

restart

sudo /etc/rc.d/init.d/postgresql restart

[vagrant@localhost web]$ psql -U postgres
psql (8.4.20)
“help” でヘルプを表示します.

postgres=#

QUERY = '''
select animals.name 
from animals join diet on animals.species = diet.species
where food = 'fish';
'''

having

QUERY = '''
select species, count(*) as num 
from animals group by species
having num = 1;
'''

QUERY = '''
select species, count(*) as num 
from animals group by species
having num = 1;
'''

QUERY = '''
select diet.food, count(*) as num
from animals join diet on animals.species = diet.species
group by food
having num = 1;
'''

although same RDB principle, different environments.

mysql, postgresql, oracle, etc
-> TCP/IP network with DB server and data on disk

sqlite
-> DB library and data on disk

.headers on
.mode column
update users set score = 0, name ='* ' || name where score < 60;
delete from users where score = 0;
select * from users;
&#91;/sql&#93;

transaction
&#91;sql&#93;
begin transaction;
update users set score = score - 10 where name = 'sato';
update users set score = score + 10 where name = 'kimura';
commit;
select * from users;
rollback;
&#91;/sql&#93;

trigger
&#91;sql&#93;
.headers on
.mode column
create table messages (message);

create trigger new_winner update of score on users when new.score > 100
  begin
   insert into messages (message) values (
     'name: ' || new name ||
     ' ' || old. score ||
     ' ->' || new.score
   );
   end;

update users set score = score + 30;
select * from messages;

index

create index score_index on users(score);
create unique index name_index on users(name);

.indices users
.schema users

drop index if exists score_index;
drop index if exists name_index;

日時

select datetime('now', '+09:00:00');
select date('2016-12-01', '+3 months', 'start of month', '-1 day');

create table if not exists users (
  id integer primary key,
  name varchar(255) not null,
  score integer default 10 check(score >= 0),
  team text,
  created datetime default (datetime('now', '+09:00:00'))
);

複数テーブルの作成

drop table if exists posts;
create table if not exists posts (
  id integer primary key,
  title text,
  body text,
  created datetime default (datetime('now', '+09:00:00'))
);

insert into posts (id, title, body) values (1, 't1', 'b1');
insert into posts (id, title, body) values (2, 't2', 'b2');

drop table if exists comments;
create table if not exists comments (
    id integer primary key,
    post_id integer,
    comment text,
    created datetime default (datetime('now', '+09:00:00'))
  );
  insert into comments (id, post_id, comment) values (1, 1, 'c1');
  insert into comments (id, post_id, comment) values (2, 1, 'c2');

.headers on
.mode column

select * from posts;
select * from comments;

内部結合 inner join、外部結合　left outer join、交差結合 cross join

select * from posts inner join comments on posts.id = comments.post_id;
select * from posts join comments on posts.id = comments.post_id;
select posts.id, posts.title, comment from posts join comments on posts.id = comments.post_id;
select * from posts left outer join comments on posts.id = comments.post_id;
select * from posts cross join comments;

row id ※integer primary key -> row id

select rowid, * from users;

dump

.output users.dump.sql
.dump users

外部データの取り込み

drop table if exists users;
create table users (
  id integer primary key,
  name,
  score
);
insert into users (name, score) values ('a', 10);
insert into users (name, score) values ('b', 20);
insert into users (name, score) values ('c', 30);

create table temp(name, score);

.mode csv
.import users.csv temp

insert into users(name, score) select name, score from temp;
select * from users;

drop table temp;

.mode csv
.output users_out.csv
select * from users;