스프레드쉬트에서 데이터베이스로

데이터베이스에 데이터 저장

이제 (키값, 저자명) 짝 데이터를 갖게 되었다. 다음 단계는 관계형 데이터베이스에 데이터를 집어넣는 것이다. 그렇게 하면 질의 응답을 할 수 있다. 시작점이 이전 학습과정에서 나온 최종 스크립트는 다음과 같다:

# display-authors-2.py
# (키값, 저자명) 짝을 화면에 출력한다.

import sys
import csv

raw = open(sys.argv[1], 'r')
reader = csv.reader(raw);
for line in reader:
    key, authors = line[0], line[3]
    for auth in authors.split('; '): # semi-colon plus space instead of semi-colon
        print key, auth
raw.close()

상기 프로그램 실행결과는 다음과 같다:

8SW85SQM McClelland, James L
85QV9X5F McClelland, J. L.
85QV9X5F McNaughton, B. L.
85QV9X5F O'Reilly, R. C.
Z4X6DT6N Ratcliff, R.
F5DGU3Q4 McCloskey, M.
F5DGU3Q4 Cohen, N. J.
PNGQMCP5 Buciluǎ, Cristian
PNGQMCP5 Caruana, Rich
PNGQMCP5 Niculescu-Mizil, Alexandru

이제 원하는 바는 일련의 SQL insert 문장을 삽입하는 것이다:

insert into data values('8SW85SQM', 'McClelland, James L');
insert into data values('85QV9X5F', 'McClelland, J. L.');
insert into data values('85QV9X5F', 'McNaughton, B. L.');

그래서, 대신에 상기 데이터를 출력하도록 프로그램을 변경하자:

# convert-1.py
# 데이터베이스에 (키값, 저자명) 짝을 집어넣는 SQL 문장을 생성한다.

import sys
import csv

INSERT = "insert into data values('{0}', '{1}');"

raw = open(sys.argv[1], 'r')
reader = csv.reader(raw);
for line in reader:
    key, authors = line[0], line[3]
    for auth in authors.split('; '):
        print INSERT.format(key, auth)
raw.close()

첫번째 변경사항이 INSERT 정의로, 삽입 문장에 대한 형식 문자열(format string)이 된다. 두번째 변경사항은 키값과 저자명을 직접적으로 출력하는 대신에, 데이터 값을 str.format 을 사용하는 INSERT` 안으로 삽입하는 것이다.

잘 동작한다, 하지만 "동작한다"라는 말은 단지 "분명한 오류없이 작업완료가 되도록 동작한다"라는 의미다. 더 가까이 검사하면, 문제가 두가지가 보인다:

1. 실제로 어떤 누구도 데이터를 삽입하는데 데이터베이스를 생성해주지는 않는다.

2. 저자명에 단일 인용부호를 포함할 수 있다.

첫번째 문제는 풀기 쉽다 -- 프로그램 시작부분에 다음 코드를 추가한다.

CREATE = 'create table data(key text not null, author text not null);'

어떤 insert 문장을 출력하기 전에 출력한다. 두번째 문제는 더 까다롭다: 만약 "O'Mear, Fiona" 같은 저자명을 INSERT해서 끼워넣으려면, 결과가 다음과 같이 된다:

"insert into data values('RJS8QDC4', 'O'Mear, Fiona');"

상기 방식은 적법한 파이썬 방법이 아니다. 문제 해결방식은 단일 인용부호 대신에 문자열 주위를 이중 인용부호를 사용하는 것이다. 왜냐하면 사람 이름에 이중 인용부호는 포함될 수 없기 때문이다. 변경사항을 마치고 나면, 전체 프로그램은 다음과 같다:

# convert-2.py
# 키값과 저자명에 대한 데이터베이스 생성.

import sys
import csv

CREATE = 'create table data(key text not null, author text not null);'
INSERT = 'insert into data values("{0}", "{1}");'

print CREATE

raw = open(sys.argv[1], 'r')
reader = csv.reader(raw);
for line in reader:
    key, authors = line[0], line[3]
    for auth in authors.split('; '):
        print INSERT.format(key, auth)
raw.close()

프로그램을 실행해보자:

$ python code/convert-2.py data/bibliography.csv | head -5
create table data(key text not null, author text not null);
insert into data values("8SW85SQM", "McClelland, James L");
insert into data values("85QV9X5F", "McClelland, J. L.");
insert into data values("85QV9X5F", "McNaughton, B. L.");
insert into data values("85QV9X5F", "O'Reilly, R. C.");

결과가 상당히 좋아 보인다. 그래서, 실제 데이터베이스를 생성하는데 이것을 사용하기로 한다:

$ python code/convert-2.py data/bibliography.csv | sqlite3 bibliography.db

상기 파이프라인 작업은 저자 컴퓨터에서 실행하는데 약 4초 걸렸고, 205 킬로바이트 bibliography.db 파일을 생성했다. 데이터베이스가 담고 있는 것을 살펴보자:

$ sqlite3 bibliography.db
SQLite version 3.8.5 2014-08-15 22:37:57
Enter ".help" for usage hints.

sqlite> .schema
CREATE TABLE data(key text not null, author text not null);

sqlite> select * from data limit 10;
8SW85SQM|McClelland, James L
85QV9X5F|McClelland, J. L.
85QV9X5F|McNaughton, B. L.
85QV9X5F|O'Reilly, R. C.
Z4X6DT6N|Ratcliff, R.
F5DGU3Q4|McCloskey, M.
F5DGU3Q4|Cohen, N. J.
PNGQMCP5|Buciluǎ, Cristian
PNGQMCP5|Caruana, Rich
PNGQMCP5|Niculescu-Mizil, Alexandru

결과가 좋아보인다. 그래서, 질의를 던져보자:

select author, count(*) from data group by author order by count(*) desc limit 10;

Bengio, Yoshua|122
Bengio, Y.|111
Hinton, Geoffrey E.|78
LeCun, Yann|56
Hinton, G. E.|45
Salakhutdinov, Ruslan|34
LeCun, Y.|31
Vincent, Pascal|29
Jordan, M. I.|27
Frasconi, P.|25

첫번째로 보이는 것은 프로그램 작업이 성과를 내고 있다는 것이다: 누가 가장 다작하는 저자인지 명령문 하나로 이제는 알아낼 수 있다. 두번째로 보이는 것이 아직 작업을 완수한 것은 아니라는 것이다: "Bengio, Yoshua"와 "Bengio, Y."는 거의 확실히 동일한 사람이다. 마찬가지로 "LeCun, Yann"와 "LeCun, Y."도 동일인이다. 정말로 누가 가장 많은 논문을 썼는지 알아내려고 한다면, 동일인에 대한 다른 이름을 맞출 필요가 있다.

저자명을 정규화하는 대신에, 대답할 수 있는 다른 질문을 살펴보자. 누가 누구와 공동으로 논문을 썼을까?

select a.author, b.author from data a join data b on a.key=b.key and a.author > b.author limit 10;

McNaughton, B. L.|McClelland, J. L.
O'Reilly, R. C.|McClelland, J. L.
O'Reilly, R. C.|McNaughton, B. L.
McCloskey, M.|Cohen, N. J.
Caruana, Rich|Buciluǎ, Cristian
Niculescu-Mizil, Alexandru|Buciluǎ, Cristian
Niculescu-Mizil, Alexandru|Caruana, Rich
Rigamonti, Roberto|Fua, Pascal
Rigamonti, Roberto|Lepetit, Vincent
Sironi, Amos|Fua, Pascal

(a.author > b.author 을 사용하게 되면 왜 완전히 다른 저자명 짝이 한번만 나오게 한다.) 다른 저자 짝이 얼마나 자주 함께 논문을 작성했는지 알고자 한다면 어떨까?

select a.author, b.author, count(*)
from data a join data b
on a.key=b.key and a.author > b.author
group by a.author, b.author
order by count(*) desc
limit 10;

Vincent, Pascal|Bengio, Yoshua|27
Roux, Nicolas Le|Bengio, Yoshua|20
Delalleau, Olivier|Bengio, Yoshua|19
Bengio, Y.|Bengio, S.|18
Larochelle, Hugo|Bengio, Yoshua|15
Roux, Nicolas Le|Delalleau, Olivier|15
Vincent, P.|Bengio, Y.|15
Chapados, N.|Bengio, Y.|14
Gori, M.|Frasconi, P.|14
Salakhutdinov, Ruslan|Hinton, Geoffrey E.|14

다시, 데이터 정규화 문제가 있다: "Vincent, Pascal" 와 "Bengio, Yoshua" 짝은 거의 확실히 "Bengio, Y." 와 "Bengio, S." 짝과 같다. 하지만, 해당 문제는 수작업으로 이 데이터를 분석할 때도 있는 것이다. 데이터를 데이터베이스에 넣게 되면, 새로운 질의를 쉽게 할 수 있어, 그렇지 않다면 다룰 수 없었던 연구주제를 다룰 수 있게 한다. 마지막 단계는 작업한 스크립트를 Git에 커밋하고 커피 한잔 하면서 자축할 시간만 남았다.