11  데이터베이스 프로그래밍

11.1 파이썬

마무리 하면서, 파이썬 같은 범용 프로그래밍 언어에서 데이터베이스를 어떻게 접근하는지 살펴보자. 다른 언어도 거의 같은 모델을 사용한다. 라이브러리와 함수 이름이 다를지 모르지만, 개념은 동일한다.

survey.db라는 이름의 파일에 저장된 SQLite 데이터베이스에서 위도와 경도를 선택하는 짧은 파이썬 프로그램이 다음에 있다.

```{python}
import sqlite3

connection = sqlite3.connect("survey.db")
cursor = connection.cursor()
cursor.execute("SELECT Site.lat, Site.long FROM Site;")
results = cursor.fetchall()
for r in results:
    print(r)
cursor.close()
connection.close()
```

(-49.85, -128.57)
(-47.15, -126.72)
(-48.87, -123.4)

sqlite3 라이브러리를 가져오면서 프로그램이 시작된다. MySQL, DB2 또는 다른 데이터베이스에 접속한다면 다른 라이브러리를 사용할 것이지만, 기능은 동일하다. 따라서 데이터베이스 A에서 데이터베이스 B로 변경한다면 프로그램의 다른 부분은 많이 변경할 필요가 없다.

두 번째 줄은 데이터베이스 연결을 설정한다. SQLite를 사용하므로 필요한 것은 데이터베이스 파일 이름뿐이다. 일반적인 다른 데이터베이스 시스템은 사용자 이름과 비밀번호를 전달해야 한다.

세 번째 줄은 연결을 통해 커서를 생성한다. 편집기 커서처럼, 데이터베이스의 어느 위치에 있는지를 추적하는 것이 커서의 역할이다.

네 번째 줄에서 커서를 사용하여 사용자 대신 데이터베이스에 쿼리를 실행하도록 요청한다. 쿼리는 SQL로 작성되며 문자열 형태로 cursor.execute에 전달된다. SQL이 제대로 작성되었는지 확인하는 것은 사용자 귀책이다. 제대로 작성되지 않았거나 실행 중 문제가 발생하면 데이터베이스가 오류를 출력한다.

다섯 번째 줄에서 cursor.fetchall 호출에 응답하여 데이터베이스가 쿼리 결과를 반환한다. 결과는 각 레코드마다 하나의 항목을 가진 결과집합(result set) 리스트다. 리스트를 반복하여 각 항목을 출력하면(여섯 번째, 일곱 번째 줄), 각각이 각 필드에 하나의 요소를 갖는 튜플(tuple)임을 알 수 있다.

마지막으로, 여덟 번째와 아홉 번째 줄에서 커서와 데이터베이스 연결을 종료한다. 데이터베이스는 한 번에 열 수 있는 연결과 커서의 수가 제한되어 있기 때문이다. 연결을 설정하는 데 시간이 걸리기 때문에, 연결을 열고 하나의 작업을 수행한 후 연결을 닫았다가 몇 마이크로초 후에 다른 작업을 위해 다시 연결하는 것은 피해야 한다. 대신, 프로그램이 수행되는 동안 하나의 연결을 생성하고 유지하는 것이 일반적이다.

실제 응용 프로그램에서 쿼리는 사용자가 제공하는 값에 따라 달라진다. 예를 들어, 다음 함수는 사용자 ID를 매개변수로 받아 이름을 반환한다.

```{python}
import sqlite3

def get_name(database_file, person_id):
    query = "SELECT personal || ' ' || family FROM Person WHERE id='" + person_id + "';"

    connection = sqlite3.connect(database_file)
    cursor = connection.cursor()
    cursor.execute(query)
    results = cursor.fetchall()
    cursor.close()
    connection.close()

    return results[0][0]

print("Full name for dyer:", get_name('survey.db', 'dyer'))
```

Full name for dyer: William Dyer

함수의 첫번째 행에 문자열 결함을 사용해서 사용자가 넘겨준 사용자 ID를 포함하는 쿼리를 완성한다. 단순하게 보일지 모르지만, 만약 누군가 다음 문자열을 입력값으로 준다면 무슨 일이 일어날까?

dyer'; DROP TABLE Survey; SELECT '

프로젝트 이름 뒤에는 쓰레기(garbage)처럼 보이지만, 매우 주의깊게 고른 쓰레기다. 만약 이 문자열을 쿼리에 삽입하면, 결과는 다음과 같다.

SELECT personal || ' ' || family FROM Person WHERE id='dyer'; DROP TABLE Survey; SELECT '';

만약 쿼리를 실행하게 된다면, 데이터베이스에 있는 테이블 중의 하나를 삭제한다.

이것을 SQL 주입 공격(SQL injection attack)이라고 하며, 수년에 걸쳐 수천 개의 프로그램을 공격하는 데 사용되었다. 특히, 사용자로부터 데이터를 받는 많은 웹사이트들이 값을 쿼리에 직접 삽입하는데, 이를 신중하게 검사하지 않는 경우가 많다.

악의를 가진 사용자가 다양한 많은 방식으로 쿼리에 명령어를 몰래 밀어넣으려고 한다. 이러한 위협을 다루는 가장 안전한 방식은 인용부호 같은 문자를 대체 상응값으로 대체하는 것이다. 그렇게 해서 안전하게 문자열 내부에 사용자가 입력한 무엇이든지 넣을 수 있다. 문자열로 문장을 작성하는 대신에 미리 준비된 쿼리(prepared statement)를 사용해서 작업할 수 있다. 만약에 미리 준비된 쿼리를 사용한다면, 예제 프로그램은 다음과 같다.

import sqlite3

def get_name(database_file, person_id):
    query = "SELECT personal || ' ' || family FROM Person WHERE id=?;"

    connection = sqlite3.connect(database_file)
    cursor = connection.cursor()
    cursor.execute(query, [person_id])
    results = cursor.fetchall()
    cursor.close()
    connection.close()

    return results[0][0]

print("Full name for dyer:", get_name('survey.db', 'dyer'))

Full name for dyer: William Dyer

주요 변경사항은 쿼리 문자열과 execute 호출에 있다. 직접 쿼리를 포맷하는 대신, 값을 삽입하고 싶은 쿼리 템플릿에 물음표를 넣는다. execute를 호출할 때, 쿼리에 있는 물음표 수만큼의 값을 포함하는 리스트를 제공한다. 라이브러리는 순서대로 값과 물음표를 매칭하고, 값의 특수 문자를 이스케이프된 대응물로 변환하여 사용하기 안전하게 만든다.

sqlite3의 커서를 사용하여 데이터베이스에 변경을 가할 수도 있다. 예를 들어, 새로운 이름을 삽입하는 것과 같이. 예를 들어, add_name이라는 새로운 함수를 다음과 같이 정의할 수 있다:

```{python}
import sqlite3

def add_name(database_file, new_person):
    query = "INSERT INTO Person (id, personal, family) VALUES (?, ?, ?);"

    connection = sqlite3.connect(database_file)
    cursor = connection.cursor()
    cursor.execute(query, list(new_person))
    cursor.close()
    connection.close()


def get_name(database_file, person_id):
    query = "SELECT personal || ' ' || family FROM Person WHERE id=?;"

    connection = sqlite3.connect(database_file)
    cursor = connection.cursor()
    cursor.execute(query, [person_id])
    results = cursor.fetchall()
    cursor.close()
    connection.close()

    return results[0][0]

# Insert a new name
add_name('survey.db', ('barrett', 'Mary', 'Barrett'))
# Check it exists
print("Full name for barrett:", get_name('survey.db', 'barrett'))
```

IndexError: list index out of range

SQLite3 버전 2.5 이상에서는 앞서 설명한 get_name 함수가 IndexError: list index out of range 오류를 발생시킨다. add_name을 사용하여 테이블에 Mary의 항목을 추가했음에도 불구하고 발생된다. 이유는 데이터베이스에 변경사항을 저장하기 위해 연결을 종료하기 전에 connection.commit()을 수행해야 하기 때문이다.

```{python}
import sqlite3

def add_name(database_file, new_person):
    query = "INSERT INTO Person (id, personal, family) VALUES (?, ?, ?);"

    connection = sqlite3.connect(database_file)
    cursor = connection.cursor()
    cursor.execute(query, list(new_person))
    cursor.close()
    connection.commit()
    connection.close()


def get_name(database_file, person_id):
    query = "SELECT personal || ' ' || family FROM Person WHERE id=?;"

    connection = sqlite3.connect(database_file)
    cursor = connection.cursor()
    cursor.execute(query, [person_id])
    results = cursor.fetchall()
    cursor.close()
    connection.close()

    return results[0][0]

# Insert a new name
add_name('survey.db', ('barrett', 'Mary', 'Barrett'))
# Check it exists
print("Full name for barrett:", get_name('survey.db', 'barrett'))
```

Full name for barrett: Mary Barrett

연습문제

객관식

1. 문제: SQL 프로그래밍에서 변수를 선언하기 위해 사용하는 키워드는 무엇입니까?
   1. DECLARE
   2. DEFINE
   3. SET
   4. VARIABLE

2. 문제: SQL에서 조건부 로직을 구현할 때 사용되는 구문은 무엇입니까?
   1. IF-ELSE
   2. SWITCH
   3. FOR
   4. WHILE

3. 문제: SQL에서 예외 처리를 위해 사용하는 구문은 무엇입니까?
   1. ERROR
   2. TRY-CATCH
   3. THROW
   4. RAISE

테이블 채우기 vs. 값 출력하기

Pressure라는 테이블 하나로 구성된 original.db라는 파일에 새로운 데이터베이스를 생성하는 파이썬 프로그램을 작성해보자. 테이블에는 reading이라는 단일 필드가 있으며, 10.0에서 25.0 사이의 100,000개의 무작위 숫자를 삽입한다. 이 프로그램을 실행하는 데 얼마나 걸리는가? 단순히 이 무작위 숫자를 파일에 쓰는 프로그램을 실행하는 데는 얼마나 걸리는가?

이를 위해 먼저 sqlite3 모듈과 random 모듈을 사용할 수 있다. 데이터베이스와 테이블을 생성하고, 무작위 숫자를 생성하여 테이블에 삽입하는 코드를 작성한다. 그리고 이 프로세스가 얼마나 걸리는지 측정한다. 파일에 숫자를 쓰는 프로그램도 비슷한 방식으로 작성하고 실행 시간을 비교한다.

SQL 필터링 vs. 파이썬 필터링

original.db와 동일한 구조를 가진 새 데이터베이스 backup.db를 생성하는 파이썬 프로그램을 작성하고, original.db에서 20.0보다 큰 모든 값을 backup.db로 복사한다. 어느 것이 더 빠른가? 쿼리에서 값을 필터링하는 것, 아니면 모든 것을 메모리에 읽어 파이썬에서 필터링하는 것.

삽입문 생성

동료중의 한명이 Robert Olmstead가 측정한 온도 측정치를 포함하는 다음과 같은 형식의 CSV 파일을 보내왔다.

Taken,Temp
619,-21.5
622,-15.5

survey 데이터베이스에 레코드로 추가하려고 CSV 파일을 읽고 SQL insert문을 출력하는 작은 파이썬 프로그램을 작성하세요. Person 테이블에 Olmstead 항목을 추가할 필요가 있을 것이다. 반복적으로 프로그램을 테스트하려면, SQL INSERT or REPLACE 문을 자세히 살펴볼 필요도 있다.

11.2 R 언어

마무리 하면서, R 같은 범용 프로그래밍 언어에서 데이터베이스를 어떻게 접근하는지 살펴보자. 다른 언어도 거의 같은 모델을 사용한다. 라이브러리와 함수 이름이 다를지 모르지만, 개념은 동일한다.

survey.db라는 이름의 파일에 저장된 SQLite 데이터베이스에서 위도와 경도를 선택하는 짧은 R 프로그램이 다음에 있다.

```{r}
library(RSQLite)
connection <- dbConnect(SQLite(), "data/survey.db")
results <- dbGetQuery(connection, "SELECT Site.lat, Site.long FROM Site;")
print(results)
dbDisconnect(connection)
```

     lat    long
1 -49.85 -128.57
2 -47.15 -126.72
3 -48.87 -123.40

RSQLite 라이브러리를 가져오면서 프로그램이 시작된다. MySQL, DB2 또는 다른 데이터베이스에 접속한다면 다른 라이브러리를 사용할 것이지만, 기능은 동일하다. 따라서 데이터베이스 A에서 데이터베이스 B로 변경한다면 프로그램의 다른 부분은 많이 변경할 필요가 없다.

두 번째 줄은 데이터베이스 연결을 설정한다. SQLite를 사용하므로 필요한 것은 데이터베이스 파일 이름뿐이다. 일반적인 다른 데이터베이스 시스템은 사용자 이름과 비밀번호를 전달해야 한다.

세 번째 줄은 연결을 통해 커서를 생성한다. 편집기 커서처럼, 데이터베이스의 어느 위치에 있는지를 추적하는 것이 커서의 역할이다.

네 번째 줄에서 커서를 사용하여 사용자 대신 데이터베이스에 쿼리를 실행하도록 요청한다. 쿼리는 SQL로 작성되며 문자열 형태로 cursor.execute에 전달된다. SQL이 제대로 작성되었는지 확인하는 것은 사용자 귀책이다. 제대로 작성되지 않았거나 실행 중 문제가 발생하면 데이터베이스가 오류를 출력한다.

다섯 번째 줄에서 cursor.fetchall 호출에 응답하여 데이터베이스가 쿼리 결과를 반환한다. 결과는 각 레코드마다 하나의 항목을 가진 결과집합(result set) 리스트다. 리스트를 반복하여 각 항목을 출력하면(여섯 번째, 일곱 번째 줄), 각각이 각 필드에 하나의 요소를 갖는 튜플(tuple)임을 알 수 있다.

마지막으로, 여덟 번째와 아홉 번째 줄에서 커서와 데이터베이스 연결을 종료한다. 데이터베이스는 한 번에 열 수 있는 연결과 커서의 수가 제한되어 있기 때문이다. 연결을 설정하는 데 시간이 걸리기 때문에, 연결을 열고 하나의 작업을 수행한 후 연결을 닫았다가 몇 마이크로초 후에 다른 작업을 위해 다시 연결하는 것은 피해야 한다. 대신, 프로그램이 수행되는 동안 하나의 연결을 생성하고 유지하는 것이 일반적이다.

실제 응용 프로그램에서 쿼리는 사용자가 제공하는 값에 따라 달라진다. 예를 들어, 다음 함수는 사용자 ID를 매개변수로 받아 이름을 반환한다.

```{r}
library(RSQLite)

connection <- dbConnect(SQLite(), "data/survey.db")

getName <- function(personID) {
  query <- paste0("SELECT personal || ' ' || family FROM Person WHERE id =='",
                  personID, "';")
  return(dbGetQuery(connection, query))
}

print(paste("full name for dyer:", getName('dyer')))

dbDisconnect(connection)
```

full name for dyer: William Dyer

함수의 첫번째 행에 문자열 결함을 사용해서 사용자가 넘겨준 사용자 ID를 포함하는 쿼리를 완성한다. 단순하게 보일지 모르지만, 만약 누군가 다음 문자열을 입력값으로 준다면 무슨 일이 일어날까?

dyer'; DROP TABLE Survey; SELECT '

프로젝트 이름 뒤에는 쓰레기(garbage)처럼 보이지만, 매우 주의깊게 고른 쓰레기다. 만약 이 문자열을 쿼리에 삽입하면, 결과는 다음과 같다.

SELECT personal || ' ' || family FROM Person WHERE id='dyer'; DROP TABLE Survey; SELECT '';

만약 쿼리를 실행하게 된다면, 데이터베이스에 있는 테이블 중의 하나를 삭제한다.

이것을 SQL 주입 공격(SQL injection attack)이라고 하며, 수년에 걸쳐 수천 개의 프로그램을 공격하는 데 사용되었다. 특히, 사용자로부터 데이터를 받는 많은 웹사이트들이 값을 쿼리에 직접 삽입하는데, 이를 신중하게 검사하지 않는 경우가 많다.

악의를 가진 사용자가 다양한 많은 방식으로 쿼리에 명령어를 몰래 밀어넣으려고 한다. 이러한 위협을 다루는 가장 안전한 방식은 인용부호 같은 문자를 대체 상응값으로 대체하는 것이다. 그렇게 해서 안전하게 문자열 내부에 사용자가 입력한 무엇이든지 넣을 수 있다. 문자열로 문장을 작성하는 대신에 미리 준비된 쿼리(prepared statement)를 사용해서 작업할 수 있다. 만약에 미리 준비된 쿼리를 사용한다면, 예제 프로그램은 다음과 같다.

```{r}
library(RSQLite)
connection <- dbConnect(SQLite(), "data/survey.db")

getName <- function(personID) {
  query <- "SELECT personal || ' ' || family FROM Person WHERE id == ?"
  return(dbGetPreparedQuery(connection, query, data.frame(personID)))
}

print(paste("full name for dyer:", getName('dyer')))

dbDisconnect(connection)
```

full name for dyer: William Dyer

The key changes are in the query string and the dbGetQuery call (we use dbGetPreparedQuery instead). Instead of formatting the query ourselves, we put question marks in the query template where we want to insert values. When we call dbGetPreparedQuery, we provide a dataframe that contains as many values as there are question marks in the query. The library matches values to question marks in order, and translates any special characters in the values into their escaped equivalents so that they are safe to use.

주요 변경사항은 쿼리 문자열과 dbGetQuery 호출에 있다. (대신 dbGetPreparedQuery을 사용 ) 직접 쿼리를 포맷하는 대신, 값을 삽입하고 싶은 쿼리 템플릿에 물음표를 넣는다. dbGetPreparedQuery를 호출할 때, 쿼리에 있는 물음표 수만큼의 값을 포함하는 데이터프레임을 제공한다. 라이브러리는 순서대로 값과 물음표를 매칭하고, 값의 특수 문자를 이스케이프된 대응물로 변환하여 사용하기 안전하게 만든다.

11.2.1 R 도우미 함수

R의 데이터베이스 인터페이스 패키지들(예: RSQLite)은 데이터베이스를 탐색하고 전체 테이블을 한 번에 읽거나 쓰는 데 유용한 공통된 도우미 함수(helper function)들을 공유한다.

데이터베이스의 모든 테이블을 보기 위해서는 dbListTables()를 사용한다.

connection <- dbConnect(SQLite(), "survey.db")
dbListTables(connection)

"Person"  "Site"    "Survey"  "Visited"

테이블 모든 칼럼 이름을 보려면 dbListFields()를 사용한다.

dbListFields(connection, "Survey")

"taken"   "person"  "quant"   "reading"

전체 테이블을 데이터프레임으로 읽으려면 dbReadTable()을 사용한다.

dbReadTable(connection, "Person")

        id  personal   family
1     dyer   William     Dyer
2       pb     Frank  Pabodie
3     lake  Anderson     Lake
4      roe Valentina  Roerich
5 danforth     Frank Danforth

데이터베이스에 전체 테이블을 쓰기 위해 dbWriteTable()을 사용한다. R이 행 이름을 별도 열로 쓰는 것을 방지하고자 할 경우, row.names = FALSE 인수를 설정한다. 예시에서 R에 내장된 iris 데이터셋을 survey.db 데이터베이스에 테이블로 쓰는 방법은 다음과 같다.

```{r}
dbWriteTable(connection, "iris", iris, row.names = FALSE)
head(dbReadTable(connection, "iris"))
```

  Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1          5.1         3.5          1.4         0.2  setosa
2          4.9         3.0          1.4         0.2  setosa
3          4.7         3.2          1.3         0.2  setosa
4          4.6         3.1          1.5         0.2  setosa
5          5.0         3.6          1.4         0.2  setosa
6          5.4         3.9          1.7         0.4  setosa

그리고 항상 그렇듯이 완료되면 데이터베이스 연결을 닫는 것을 잊지 말자.

```{r}
dbDisconnect(connection)
```

연습문제

테이블 채우기 vs. 값 출력하기

Pressure라는 테이블 하나로 구성된 original.db라는 파일에 새로운 데이터베이스를 생성하는 R 프로그램을 작성해보자. 테이블에는 reading이라는 단일 필드가 있으며, 10.0에서 25.0 사이의 100,000개의 무작위 숫자를 삽입한다. 이 프로그램을 실행하는 데 얼마나 걸리는가? 단순히 이 무작위 숫자를 파일에 쓰는 프로그램을 실행하는 데는 얼마나 걸리는가?

SQL 필터링 vs. R 필터링

original.db와 동일한 구조를 가진 새 데이터베이스 backup.db를 생성하는 R 프로그램을 작성하고, original.db에서 20.0보다 큰 모든 값을 backup.db로 복사한다. 어느 것이 더 빠른가? 쿼리에서 값을 필터링하는 것, 아니면 모든 것을 메모리에 읽어 R에서 필터링하는 것.