본문 바로가기
Python/파이썬 프로그래밍 연습

데이터베이스 작업

by 가므자 2012. 4. 25.

이 주제에서는 시간이 지나면서 어떻게 데이터베이스 패키지를 통하여 데이터가 저장되고 조작되는지 살펴보겠습니다. 개인 주소록 같이 파일을 사용하여 소량의 데이터를 저장하는 법을 이미 보았습니다. 그러나 파일을 사용하면 복잡도가 급격하게 증가합니다. 데이터의 복잡도가 증가할 수록 그리고 데이터의 양이 증가할 수록 그리고 (검색, 정렬, 여과 등등) 연산의 복잡도가 증가할 수록 말입니다. 이를 극복하기 위하여 여러 데이터베이스 패키지가 존재합니다. 아래의 파일 관리를 책임지고 데이터를 보다 조작하기 쉬운 추상적인 형태로 노출시켜 줍니다. 어떤 패키지는 단순히 코드 라이브러리여서 이미 살펴 본 파일 연산을 단순하게 해 줄 뿐입니다. 예를 들어 파이썬에 따라오는 pickle 모듈과 shelve 모듈이 그런 라이브러리입니다. 이 주제에서는 더 강력한 패키지에 집중하겠습니다. 방대한 크기의 복잡한 데이터를 처리하도록 설계된 상업적 벤더 수준의 패키지를 살펴보겠습니다.

살펴 볼 특별한 패키지는 SQLite입니다. 이 패키지는 열린 소프 자유 패키지입니다. 설치하고 사용하기 쉽지만 초보 그리고 일반 프로그래머가 요구하는 데이터 조작을 대부분 처리할 수 있습니다. 아주 거대한 데이터 집합이 있을 경우에만 - 레코드가 수 백만개를 넘을 경우에만 - 더 강력한 패키지를 고려할 필요가 있습니다. 그렇더라도 SQLite로부터 얻은 지식을 거의 대부분 새로운 패키지에 써 먹을 수 있을 것입니다.

SQLite 패키지는 여기에서 내려받을 수 있습니다. 그리고 플랫폼에 맞게 명령-줄 패키지를 가져와야 합니다 (원하시다면 SQLite용으로 유용한 IDE가 있습니다. 그러나 이 자습서에서는 필요하지 않습니다.). 웹 사이트의 지시를 따라서 패키지를 설치하시면 시작할 준비가 될 것입니다.

관계형 데이터베이스 개념

관계형 데이터베이스이 기본 개념은 아주 간단합니다. 단순히 테이블(tables) 집합일 뿐으로서 테이블에서 셀 하나가 또다른 테이블의 행을 가리킬 수 있습니다. 열은 필드(fields)로 그리고 행은 레코드(records)라고 부릅니다.

사원 데이터를 가진 테이블은 다음과 같이 보일 것입니다:

EmpID

Name

HireDate

Grade

ManagerID

1020304

John Brown

20030623

Foreman

1020311

1020305

Fred Smith

20040302

Labourer

1020304

1020307

Anne Jones

19991125

Labourer

1020304

여기에서 두 가지 관례에 주목하세요:

1.     각 행을 유일하게 식별하기 위하여 ID 필드가 있습니다. 이는 기본 키(Primary Key)라고 부릅니다. 다른 키를 가지는 것도 가능합니다. 그러나 관례적으로 언제나 ID 필드 하나로 유일하게 레코드를 식별합니다. 이 덕분에 예를 들어 사원은 이름을 바꾸기로 결정할 수 있습니다!

2.     또다른 행에 대하여 기본 키가 들어 있는 필드를 가짐으로써 행 하나를 또다른 행에 연결할 수 있습니다. 그리하여 사원의 관리자는 ManagerID 필드로 식별되고 이는 간단히 또다는 EmpID 엔트리를 가리킵니다. 데이터를 살펴보면 Fred와 Anne 모두 John이 관리하는데, 그는 이 번에는 다른 누군가가 관리합니다. 그에 관한 상세한 정보는 이 테이블에서는 보이지 않습니다.

테이블 하나에서 데이터를 연결하는 것만으로 제한되지 않습니다. Salary라는 또다른 테이블을 만들 수 있습니다. 이 테이블들은 Grade에 관련되고 그래서 다음과 같은 테이블을 얻습니다:

SalaryID

Grade

Amount

000010

Foreman

60000

000011

Labourer

35000

이제 John과 같이 사원(Employee)의 직급을 알 수 있습니다. 그들이 조장(Foreman)이라는 것을 알 수 있고, 다음 월급(Salary) 테이블을 보면 조장(Foreman)이 $60000의 보수를 받는다는 것을 알 수 있습니다.

관계형 데이터베이스라는 이름이 붙은 이유는 테이블 행을 관계(relationships)로 연결하는 바로 이 능력 덕분입니다. 다른 데이터베이스 유형으로 네트워크(network) 데이터베이스와 계통적(hierarchical) 데이터베이스 그리고 평범한-파일(flat-file) 데이터베이스가 있습니다. 관계형 데이터베이스가 가장 일반적입니다.

훨씬 더 섬세한 질의도 할 수 있으며 다음 몇 섹션에 걸쳐서 이렇게 하는 법을 살펴보겠습니다. 그러나 그렇게 하기 전에 먼저 데이터베이스를 만들고 데이터를 집어 넣어 보는 편이 좋겠습니다.

구조화된 질의 언어

SQL((Structured Query Language) 세큐엘이나 'S' 'Q' 'L'로 발음함)은 관계형 데이터베이스를 조작하는데 사용되는 표준 도구입니다. SQL에서 표현식은 종종 질의(query)라고 지칭됩니다.

SQL은 두 부분으로 구성되는데, 첫 부분은 DDL(Data Definition Language)입니다. 이는 데이터베이스의 모양, 즉 그의 구조를 만들고 조작하는데 사용되는 명령어의 집합입니다. DDL은 상당히 데이터베이스 종속적 경향이 있어서 데이터베이스 공급자마다 약간씩 SQL 명령어의 DDL 집합 구문이 다릅니다.

SQL의 다른 부분은 DML(Data Manipulation Language)입니다. DML은 데이터베이스 사이에 고도로 표준화되어 있으며 데이터베이스의 내용을 조작하는데 사용됩니다. 대부분의 시간을 DDL보다는 DML을 사용하는 데 보내실 것입니다.

간략하게 DDL을 살펴보겠습니다. 데이터베이스 테이블을 (CREATE 명령어로) 만들고 (DROP 명령어로) 파괴하는 정도로 마치겠습니다. 그 정도로도 (INSERT, SELECT, UPDATE, DELETE 등등의) DML 명령어를 사용하여 재미있는 방식으로 데이터를 채우고 열람할 수 있습니다.

테이블 만들기

SQL로 테이블을 만들려면 CREATE 명령어를 사용합니다. 사용법은 아주 쉬우며 다음과 같은 형태를 취합니다: 

CREATE TABLE tablename (fieldName, fieldName,....);

SQL 서술문이 쌍반점으로 끝난 것에 주목하세요. 또한 SQL은 대소문자를 구별하지 않습니다. 파이썬과는 다르게 공백문자나 들여쓰기 수준에 신경쓰지 않습니다. 앞으로 보시겠지만 어떤 스타일 관례가 사용되지만 엄격하게 지켜지는 것은 아니며 SQL 자체는 조금도 그에 신경쓰지 않습니다!

사원 테이블과 월급 테이블을 SQLite로 만들어 봅시다. 제일 먼저 할 일은 인터프리터를 기동시키는 일입니다. 이는 파일이름을 인자로 하여 호출하기만 하면 되는 간단한 일입니다. 데이터베이스가 존재하면 그것이 열리며, 존재하지 않으면 생성됩니다. 그리하여 사원 데이터베이스를 만들기 위해 SQLite를 다음과 같이 시작합니다: 

E:\PROJECTS\SQL> sqlite3 employee.db

employee.db라는 빈 데이터베이스가 생성됩니다. 그리고 sqlite> 프롬프트에서 SQL 명령어를 타자할 준비가 되었습니다. 그래서 테이블을 몇 개 만들어 봅시다: 

sqlite> create table Employee
   ...> (EmpID,Name,HireDate,Grade,ManagerID);
sqlite> create table Salary
   ...> (SalaryID, Grade,Amount);
sqlite>.tables
Employee     Salary
sqlite>

필드 리스트를 따로 한 줄에 옮긴 것을 주목하세요. 그러면 보기가 좀 더 쉽기 때문입니다. 필드는 이름 순서로 나열되지만 데이터 유형 같은 다른 정보는 정의되지 않았습니다. 이는 SqlLite의 독특한 특성입니다. 대부분의 다른 데이터베이스는 그 이름과 더불어 유형을 정의하도록 요구합니다. SqlLite에서도 그렇게 할 수 있습니다. 이에 관해서는 나중에 이 자습서에서 더 자세하게 살펴보겠습니다.

또 create 서술문이 작동하는지 테스트해 본 것에 주목하세요. .tables 명령어를 사용하여 데이터베이스에 있는 테이블을 모두 나열하여 보았습니다. SQLite는 이런 점 명령어(dot commands)가 여러 개 있습니다. 점 명령어는 데이터베이스 관하여 알아 보는데 사용합니다. .help 명령어는 그 리스트를 제공합니다.

테이블을 만들때 할 일이 많습니다. 각 열에 데이터 유형을 선언해야 할 뿐만 아니라 그 값들에 대하여 상수(constraints)도 선언해야 합니다 (예를 들어 NOT NULL은 그 값이 필수이며 반드시 채워져야 한다는 뜻입니다 - 보통 기본 키 필드는 NOT NULL과 UNIQUE로 만들어야 합니다.) 또한 어느 필드가 기본 키(PRIMARY KEY)가 될지도 지정해야 합니다. 이런 고급 생성 옵션들은 나중에 더 자세하게 살펴보겠습니다.

지금은 기본 테이블 정의를 이대로 두고 떠나서 데이터 조작이라는 더 흥미로운 주제로 나아가 보겠습니다.

데이터 삽입하기

테이블을 만든 후에 제일 먼저 할 일은 데이터로 채우는 일입니다! 이는 SQL INSERT 서술문으로 합니다. 기본 구조는 아주 간단합니다: 

INSERT INTO  ( column1, column2... ) VALUES ( value1, value2... );

질의를 사용하여 데이터베이스에서 데이터를 선택하는 INSERT의 대안적인 형태가 있습니다. 그러나 그것은 너무 고급이라서 여기에서는 그냥 SQLite 매뉴얼을 읽어 보시기를 권장합니다.

그래서 이제 사원 테이블에 행을 삽입하려면 다음과 같이 합니다: 

sqlite> insert into Employee (EmpID, Name, HireDate, Grade, ManagerID)
   ...> values ('1020304','John Brown','20030623','Foreman','1020311');
sqlite> insert into Employee (EmpID, Name, HireDate, Grade, ManagerID)
   ...> values ('1020305','Fred Smith','20040302','Labourer','1020304');
sqlite> insert into Employee (EmpID, Name, HireDate, Grade, ManagerID)
   ...> values ('1020307','Anne Jones','19991125','Labourer','1020304');

월급 테이블에는 다음과 같이 합니다: 

sqlite> insert into Salary (SalaryID, Grade,Amount)
   ...> values('000010','Foreman','60000');
sqlite> insert into Salary (SalaryID, Grade,Amount)
   ...> values('000011','Labourer','35000');

완성입니다. 이제 두 개의 테이블을 만들었고 위의 소개에서 기술한 값들에 상응하는 데이터로 채웠습니다.

테이터 뽑아내기

데이터는 SQL의 SELECT 명령어로 데이터베이스에서 추출됩니다. Select는 SQL의 심장이며 가장 복잡한 구조입니다. 가장 기본적인 형태로 시작하겠습니다. 그리고 진행해 가면서 특징을 추가해 보겠습니다. 가장 기본적인 Select 서술문은 다음과 같이 보입니다: 

SELECT column1, column2... FROM table1,table2...;

그래서 모든 사원의 이름을 선택하려면 다음과 같이 할 수 있습니다: 

sqlite> SELECT Name from Employee;

그러면 사원 테이블에 있는 이름을 모두 리스트에 담아 돌려줍니다. 이 경우, 겨우 세 명 뿐이지만 데이터베이스가 거대하다면 아마도 원하는 것보다 더 많은 정보가 반환될 것입니다. 출력을 제어하려면 좀 세련되게 검색할 필요가 있습니다. SQL에서 이렇게 하려면 다음과 같이 WHERE 절을 Select 서술문에 덧붙이면 됩니다: 

SELECT col1,col2... FROM table1,table2... WHERE condition;

조건(condition)은 무엇이든 복합 불리언 표현식이면 됩니다. 그리고 보시겠지만 그 안에 select 서술문을 내포시킬 수도 있습니다.

where 절을 사용하여 섬세하게 이름을 검색해 봅시다. 직급이 근로자(labourers)인 사원의 이름만 찾아 보겠습니다: 

sqlite> select Name 
   ...> from Employee
   ...> where Employee.Grade = 'Labourer';

이제 두 개의 이름만 돌려 받습니다. AND, OR, NOT 등등의 불리언 연산자를 사용하여 조건을 확장할 수 있습니다. = 조건을 사용한 것에 주목하세요. 문자열의 대소문자는 중요합니다. 'labourer'라고 사용하면 작동하지 않습니다! 나중에 이 한계를 극복하는 법을 살펴보겠습니다.

where 절에서 점 표기법을 사용하여 Grade 필드를 표기했음을 주목하세요. 이 경우 오직 하나의 테이블만으로 작업하기 때문에 실제로는 점 표기법이 필요하지 않습니다. 그러나 테이블이 여러 개 지정되어 있다면 필드가 어느 테이블에 속해 있는지 명시해야 할 필요가 있습니다. 일예로 질의를 바꾸어서 보수가 $50,000이 넘는 사원의 이름을 모두 찾아 봅시다. 그렇게 하려면 두 테이블 모두에서 데이터를 고려해야 합니다: 

sqlite> select Name, Amount from Employee, Salary
   ...> where Employee.Grade = Salary.Grade
   ...> and   Salary.Amount > '50000';

짐작하듯이 이름 하나만 돌려받습니다 - 조장(foreman)의 이름을 돌려 받는군요. 그러나 월급도 돌려받는다는 것을 주목하세요. 왜냐하면 선택된 열의 리스트 뒤에 Amount를 추가했기 때문입니다. 두 부분을 where 절에서 불리언 and 연산자를 사용하여 조합했다는 것도 주목하세요. 첫 부분은 공통 필드가 같다는 것을 확인함으로써 두 테이블을 하나로 연결합니다. 이를 SQL에서 조인(join)이라고 부릅니다.

고지 1: 선택할 필드가 두 테이블에서 오기 때문에 두 테이블을 모두 지정해야 결과를 돌려받습니다. 필드 이름의 순서는 돌려받는 데이터의 순서와 같지만 테이블의 순서는 상관이 없습니다. 지정된 필드가 그 테이블에 나타나기만 하면 됩니다.

고지 2: 두 개의 유일 필드 이름을 지정했습니다. 두 테이블에 모두 나타나는 직급(Grade)도 화면에 표시하고 싶다면, 점 표기법을 사용하여 어느 테이블의 직급을 원하는지 지정해야 합니다. 다음과 같이: 

sqlite> select Employee.Grade, Name, Amount 
   ...> from Employee, Salary
   etc/...

(물론 SELECT에 대한 SQL 문서에서 더 많이 읽어 보실 수 있지만) 다루어 보고 싶은 Select의 마지막 특징은 출력을 정렬하는 능력입니다. 데이터베이스는 일반적으로 찾기 쉬운 순서로 또는 삽입된 순서로 데이터를 보유합니다. 어느 경우든 보통 화면에 보여주고 싶은 순서는 아닙니다! 이를 다루려면 Select 서술문의 ORDER BY 절을 사용하면 됩니다. 

SELECT columns FROM tables WHERE expression ORDER BY columns;

마지막 ORDER BY 절에서 여러 열을 받을 수 있음을 주목하세요. 이렇게 하면 기본, 2차, 3차 등등의 순서로 정렬할 수 있습니다.

이를 이용하여 HireDate로 정렬된 사원 이름 리스트를 얻어 봅시다: 

sqlite> select Name from Employee
   ...> order by HireDate;

이 정도면 다 설명한 셈입니다. 이보다 더 쉬울 수는 없습니다! 이제 한 가지만 언급할 가치가 남아 있습니다. where 절을 사용하지 않았습니다. 만약 사용한다면 order by 절 보다 앞에 두어야 합니다. 그래서 SQL은 where 절을 빼먹어도 신경쓰지 않지만, where 서술문 안에 order by 절이 있는지는 신경씁니다.

데이터 추출은 이 정도로 충분합니다. 어떻게 데이터를 수정할 수 있는지 알아봅시다.

데이터 바꾸기

데이터베이스에서 데이터를 바꾸는 방법은 두 가지가 있습니다. 레코드의 내용을 바꿀 수 있습니다. 또는 더 과감하게 레코드를 지우거나 심지어 테이블 전체를 홀랑 지울 수도 있습니다. 기존 레코드의 내용을 바꾸는 일이 더 일반적이며 UPDATE SQL 명령어를 사용하여 수정합니다.

기본적인 형태는 다음과 같습니다: 

UPDATE table SET column = value WHERE condition;

샘플 데이터베이스에 조장의 보수를 $70,000로 변경해서 시험해 볼 수 있습니다. 

sqlite> update Salary
   ...> set Amount ='70000'
   ...> where Grade = 'Foreman';

주목할 한 가지는 지금까지 삽입하고 선택한 데이터가 모두 유형이 문자열이라는 것입니다. SQLite는 실제로 데이터를 문자열로 저장하지만 숫자를 비롯하여 다른 유형의 데이터를 몇 가지 지원합니다. 그래서 계산을 더 쉽게 하기 위하여 월급을 수치 형식으로 지정할 수 있습니다. 다음 섹션에서 그렇게 하는 법을 살펴보겠습니다.

상당히 과감한 형태의 수정은 행 또는 행의 집합을 지우는 것입니다. 이는 SQL DELETE FROM 명령어를 사용하는데, 다음과 같이 보입니다: 

DELETE FROM Table WHERE condition

그래서 Anne Jones를 사원 테이블에서 제거하고 싶다면 다음과 같이 하면 됩니다: 

sqlite> delete from Employee where Name = 'Anne Jones';

여러 행이 조건에 부합하면 부합하는 열이 모두 삭제됩니다. SQL은 언제나 질의에 부합하는 모든 열을 연산합니다. 정규 표현식으로 문자열이나 파일을 연속적으로 검색하는 것과는 다릅니다.

전체 테이블과 그의 내용을 삭제하려면 SQL DROP 명령어를 사용합니다. 잠시 후에 작동하는 모습을 보여드리겠습니다. Delete과 Drop 같은 파괴적인 명령어는 아주 조심스럽게 사용해야 합니다!

테이블에 걸쳐서 데이터 연결하기

데이터 제약

앞서 SELECT 섹션에서 테이블에 걸쳐서 데이터를 연결하는 법에 관하여 언급하였습니다. 그렇지만 이는 데이터베이스 이론에서 매우 근본적인 부분이므로 여기에서 더 깊이 연구해 보겠습니다. 테이블 사이의 링크는 데이터 개체(entities) 사이의 관계(relationships)를 나타냅니다. 그러므로 SQLite 같은 데이터베이스가 그의 이름을 관계형 데이터베이스(Relational Database)라고 부르는 것입니다. 데이터베이스는 객체에 관하여 미가공 데이터를 관리할 뿐만 아니라 관계에 관한 정보도 관리합니다.

관계에 관한 정보는 데이터베이스 제약(database constraints)의 형태로 저장됩니다. 제약은 어떤 종류의 데이터가 저장될 수 있는지 그리고 유효한 값의 집합을 지시하는 규칙입니다. 이 제약은 CREATE 서술문을 사용하여 데이터베이스 구조를 정의할 때 적용됩니다.

보통 제약을 필드 단위로 필드에 표현합니다. 그래서 열을 정의 해 둔 CREATE 서술문 안에서 기본 정의를 확장할 수 있습니다: 

CREATE Tablename (Column, Column,...);

다음과 같이 확장합니다: 

CREATE Tablename (
ColumnName Type Constraint,
ColumnName Type Constraint,
...);

그리고 가장 일반적인 제약은 다음과 같습니다: 

NOT NULL 
PRIMARY KEY [AUTOINCREMENT] 
UNIQUE 
DEFAULT value

NOT NULL은 상당히 자기 설명적입니다. 값이 존재해야 하며 NULL이 되면 안된다고 지시합니다! NULL 값은 지정된 값이 없는 값입니다. 그리하여 그 필드에 대하여 값이 주어져야 한다는 NOT NULL은 값이 지정되어야 한다는 것을 뜻합니다. 그렇지 않으면 에러가 일어나고 데이터는 삽입되지 않습니다.

PRIMARY KEY는 그냥 SQLite에게 검색에 메인 키로 이 열을 사용하도록 지시합니다 (실제로 이것은 더 빠른 검색을 위해 최적화된다는 뜻입니다). AUTOINCREMENT는 INTEGER 유형 값이 INSERT 마다 자동으로 할당될 것이라는 뜻입니다. 그리고 자동으로 그 값이 하나씩 증가합니다. 이렇게 하면 프로그래머가 따로 계수기를 관리하는 수고를 많이 덜 수 있습니다. AUTOINCREMENT "키워드"는 실제로 사용되지 않습니다. 그 보다는 INTEGER PRIMARY KEY의 유형/제약 조합으로 묵시적으로 지정됩니다. 이는 SQLite 문서에서 그렇게 확실하게 드러나는 괴벽이 아니기 때문에 SQLite FAQ 리스트 상단에 나타날 만큼 많은 사람들이 함정에 빠집니다!

UNIQUE는 값이 열 안에서 유일해야 한다는 뜻입니다. 값을 열 안에 UNIQUE 제약으로 삽입하려고 하면 에러가 일어나고 그 행은 삽입되지 않습니다. UNIQUE는 종종 비-INTEGER 유형의 PRIMARY KEY 열에 사용됩니다.

DEFAULT는 언제나 값에 따라 다닙니다. 그 값은 사용자가 명시적으로 제공하지 않을 경우 SqlLite가 지정 필드에 삽입하는 값입니다. 이의 효과는 DEFAULT 제약이 있는 열들이 실제로 NULL인 경우가 아주 드물기 때문에 a NULL 값을 만들려면 명시적으로 NULL을 값으로 설정할 필요가 있습니다.

여기에서 기본 사용법을 보여주는 예를 볼 수 있습니다: 

sqlite> create table test
   ...> (id Integer Primary Key,
   ...> Name NOT NULL,
   ...> Value Integer Default 42);
sqlite> insert into test (Name, Value) values ('Alan',24);
sqlite> insert into test (Name) values ('Heather');
sqlite> insert into test (Name,Value) values ('Linda', NULL);
sqlite> select * from test;
1|Alan|24
2|Heather|42
3|Linda|
sqlite>
 

어떻게 Heather에 대한 엔트리에 기본 값이 설정되었는지 눈치채셨습니까? 또한 Linda에 대한 값이 존재하지 않습니다. 즉 NULL입니다. 그것은 NOT NULL과 DEFAULT 사이의 중요한 차이점입니다. NOT NULL은 기본 값이든 명시적이든 NULL 값을 허용하지 않습니다. DEFAULT 제약은 지정되지 않은 NULL을 방지하지만 고의로 NULL 값을 만드는 것은 막지 않습니다.

테이블 그 자체에 적용되는 제약도 있지만 이 자습서에서는 깊이 다루지 않겠습니다.

이미 언급한 바와 같이 적용할 수 있는 다른 종류의 제약은 열 종류를 지정하는 것입니다. 이는 프로그래밍 언어에서 유형의 개념과 정확하게 같은 개념입니다. SQLite에서 유효한 유형 집합은 다음과 같습니다:

· TEXT

· NUMERIC

· INTEGER

· None

정수뿐만 아니라 부동소수점수로 저장을 할 수 있는 NUMERIC만 제외하면 다른 것들은 짐작하신 그대로입니다. None은 실제로는 유형이 아니지만 그냥 위에서 지적한 것처럼 아무 유형도 지정할 필요가 없다는 것을 나타냅니다. 대부분의 데이터베이스는 광범위한 유형 집합이 따라 오는데, 여기에는 중요한 유형인 DATE 유형이 포함됩니다. 곧 보시겠지만, SQLite는 유형에 약간 비관례적으로 접근합니다. 그래서 이런 멋진 유형들에 크게 얽매이지 않습니다.

대부분의 데이터베이스는 엄격하게 지정된 유형을 적용합니다. 그렇지만 SQLite는 좀 더 역동적인 체계를 채용합니다. 지정된 유형이 힌트와 비슷하며 어떤 유형의 데이터도 테이블에 저장할 수 있습니다. 유형이 다른 데이터가 필드로 적재되면 SQLite는 선언된 유형을 사용하여 그 데이터를 변환하려고 시도합니다. 그러나 변환할 수 없다면 원래 형태로 저장됩니다. 그리하여 필드가 INTEGER로 선언되어 있는데 TEXT 값인 '123'이 건네지면, SQLite는 문자열 '123'을 숫자 123으로 변환합니다. 그러나 TEXT 값 'Freddy'가 건네지면 변환은 실패할 것이고 그래서 SQLite는 그냥 그 문자열 'Freddy'를 필드에 적재합니다! 이 약점을 인지하고 있지 않으면 이 때문에 약간 이상한 행위가 야기될 수 있습니다. 대부분의 데이터베이스는 유형 선언을 엄격한 제약으로 취급합니다. 그리고 불법적인 값이 건네지면 실패합니다.

제약으로 관계 모델링하기

그래서 이 제약이 데이터 특히 관계 모델링에 어떻게 도움을 줄까? 간단한 테이블 두 개로 구성된 데이터베이스를 다시 살펴봅시다:

EmpID

Name

HireDate

Grade

ManagerID

1020304

John Brown

20030623

Foreman

1020311

1020305

Fred Smith

20040302

Labourer

1020304

1020307

Anne Jones

19991125

Labourer

1020304

SalaryID

Grade

Amount

000010

Foreman

60000

000011

Labourer

35000

먼저 Employee 테이블을 보면 ID 값이 INTEGER 유형에 속해야 하며 PRIMARY KEY 제약을 가져야 한다는 것을 알 수 있습니다. ManagerID를 제외하고 다른 열들도 NOT NULL이어야 합니다. ManagerID도 역시 INTEGER 유형에 속해야 합니다.

Salary 테이블에 대하여 SalaryID가 PRIMARY KEY를 가진 INTEGER가 되어야 함을 알 수 있습니다. Amount 열도 INTEGER가 되어야 하고 DEFAULT 값으로 10000을 적용하겠습니다. 마지막으로 Grade 컬럼은 Unique으로 제약됩니다. 왜냐하면 직급당 월급이 여러가지이기를 원치 않기 때문입니다! (실제로 이는 나쁜 아이디어입니다. 보통 보수는 직급뿐만 아니라 근무 시간과 같은 것들에 따라 달라지기 때문입니다. 그러나 그런 섬세함은 무시하겠습니다! 사실, 실제 세계에서라면 아마도 이 직급(Grade) 테이블을 호출하지 월급(Salary) 테이블을 호출하지는 않을 것입니다...)

수정된 SQL은 다음과 같이 보입니다: 

sqlite> create table Employee (
   ...> EmpID integer Primary Key,
   ...> Name not null,
   ...> HireDate not null,
   ...> Grade not null,
   ...> ManagerID integer
   ...> );
 
sqlite> create table Salary (
   ...> SalaryID integer primary key,
   ...> Grade  unique,
   ...> Amount integer default 10000
   ...> );
 

이런 제약을 어기는 데이터를 입력함으로써 무슨 일이 일어나는지 시험해 볼 수 있습니다. 아무래도 에러 메시지를 맞이하겠지요!

여기에서 지적할 한 가지는 앞에서 사용한 insert 서술문이 더 이상 적절하지 않다는 것입니다. 앞에서 ID 필드에 값을 삽입했지만 이제 자동생산됩니다. 그래서 삽입된 데이터로부터 그 값들을 생락할 수 있고 (생략해야 합니다!) 그러나 이 때문에 새로운 곤란에 봉착합니다. 관리자의 EmpID가 무엇인지 모르는데 어떻게 managerID 필드를 채울 수 있는가? 그 대답은 nested select 서술문을 사용할 수 있다는 것입니다. 두 단계로 나누어서 처리하기로 했는데 처음에는 NULL 필드를 사용한 다음 모든 행을 만들고 난 후에 update 서술문을 사용하겠습니다.

반복되는 타자수를 줄이기 위해 모든 명령어를 두 개의 파일에 담았는데 테이블 생성 명령어는 employee.sql에 삽입 서술문은 employee.dat에 담았습니다. (이는 .py로 끝나는 파이썬 스크립트 파일을 만들어서 >>> 프롬프트에서 타자하는 것들을 절약하는 것과 똑 같습니다.)

employee.sql 파일의 모습은 다음과 같습니다: 

drop table Employee;
create table Employee (
EmpID integer Primary Key,
Name not null,
HireDate not null,
Grade not null,
ManagerID integer
);
 
drop table Salary;
create table Salary (
SalaryID integer primary key,
Grade unique,
Amount integer default 10000
);

먼저 테이블을 지운(drop) 다음에 테이블을 만들고 있음을 주목하세요. DROP TABLE 명령어는 앞서 언급한 바와 같이 그냥 테이블과 그 안에 든 데이터를 모조리 지웁니다. 이렇게 하면 새로운 테이블을 만들기 전에 데이터베이스가 깨끗한 상태에 있음을 확증할 수 있습니다.

employee.dat 스크립트는 다음과 같습니다: 

insert into Employee (Name, HireDate, Grade, ManagerID)
      values ('John Brown','20030623','Foreman', NULL);
insert into Employee (Name, HireDate, Grade, ManagerID)
      values ('Fred Smith','20040302','Labourer',NULL);
insert into Employee (Name, HireDate, Grade, ManagerID)
      values ('Anne Jones','19991125','Labourer',NULL);
update Employee
set ManagerID = (Select EmpID 
                 from Employee 
              where Name = 'John Brown')
where Name = 'Fred Smith' OR 
      Name = 'Anne Jones';
 
insert into Salary (Grade, Amount)
       values('Foreman','60000');
insert into Salary (Grade, Amount)
       values('Labourer','35000');

내장된 select 서술문을 update 명령에서 사용하고 있음을 주목하세요. 또 단 한 번의 update를 사용하여 불리언 OR 조건으로 employee 행을 수정하였음도 주목하세요. 이 OR 조건을 확장하면 같은 관리자에게 더 많은 사원을 쉽게 추가할 수 있습니다.

이는 처음으로 데이터베이스를 채울 때 봉착할 수 있는 전형적인 문제입니다. 서술문의 순서를 세심하게 계획할 필요가 있습니다. 또다른 테이블을 가리키는 참조 값을 담을 필요가 있는 행마다 이미 그 테이블을 참조하기 위한 데이터를 제공했는지 보증해 줄 필요가 있기 때문입니다! 마치 나무의 잎에서 시작하여 거꾸로 줄기 쪽으로 작업해 가는 것과 닮았습니다. 언제나 처음에는 참조점없이 데이터를 만들고/삽입하세요. 그 다음에 그 데이터를 참조하는 데이터를 만드세요 등등. 최초로 만들고 난 후에 데이터를 추가하려면 질의를 이용하여 데이터가 이미 존재하는지 점검할 필요가 있습니다. 존재하지 않으면 추가합니다. 이 시점에서 파이썬 같은 스크립트 언어가 귀중해 집니다!

마지막으로 다음과 같이 sqlite 프롬프트에서 실행할 수 있습니다: 

sqlite> .read employee.sql
sqlite> .read employee.dat

그렇지만 경로 문제는 잘 처리했는지 확인하세요: sqlite를 sql 스크립트가 있는 곳에서 실행하거나 (앞에서 한 것처럼) 또는 스크립트에 완전한 경로를 제공하세요.

이제 질의를 해서 모든 것이 예상대로 되었는지 시험해 보겠습니다: 

sqlite> select Name from Employee
   ...> where Grade in
   ...> (select Grade from Salary where amount >50000)
   ...> ;
John Brown

작동하는 것 같군요. John Brown이 유일하게 $50000 이상을 버는 사원입니다. IN 조건을 또다른 내장된 SELECT 서술문과 조합해 사용한 것을 주목하세요. 이는 위에서 테이블 간 조인을 사용하여 수행했던 질의를 비슷하게 변형한 것입니다. 두 테크닉 모두 작동하지만 보통 join 접근법이 더 빠릅니다.

다대다 관계

다루지 못한 한 가지 시나리오는 두 테이블이 대 대 다 관계로 결합되는 것입니다. 다시 말해, 한 테이블에서 한 행은 보조 테이블의 여러 행에 연결될 수 있으며 보조 테이블의 한 행이 앞 테이블의 여러 행과 동시에 연결될 수 있습니다.

예제를 생각해 봅시다. 출판사를 지원하는 데이터베이스를 작성할 거라고 상상해 보세요. 저자 목록과 책 목록이 필요합니다. 저자마다 여러 권의 책을 쓸 것입니다. 책마다 여러 명의 저자가 있습니다. 어떻게 그런 관계를 데이터베이스로 표현할까? 해결책은 저자와 책 사이의 관계를 따로 테이블로 표현하는 것입니다. 그런 테이블을 종종 intersection table 또는 mapping table이라고 부릅니다. 이 테이블의 각 행은 책/저자 관계를 표현합니다. 이제 책마다 잠재적으로 많은 책/서적 관계가 있지만 각 관계는 오직 하나의 책과 한 명의 저자만 있습니다. 그래서 다대다 관계를 두 개의 일대다 관계로 변환했습니다. ID를 사용하여 그렇게 하는 법을 이미 알고 있습니다. 실제로 작동하는지 알아봅시다: 

drop table author;
create table author (
ID Integer PRIMARY KEY,
Name String NOT NULL
);
 
drop table book;
create table book (
ID Integer PRIMARY KEY,
Title String NOT NULL
);
 
drop table book_author;
create table book_author (
bookID Integer NOT NULL,
authorID Integer NOT NULL
);
 
insert into author (Name) values ('Jane Austin');
insert into author (Name) values ('Grady Booch');
insert into author (Name) values ('Ivar Jacobson');
insert into author (Name) values ('James Rumbaugh');
 
insert into book (Title) values('Pride & Prejudice');
insert into book (Title) values('Emma');
insert into book (Title) values('Sense & Sensibility');
insert into book (Title) values ('Object Oriented Design with Applications');
insert into book (Title) values ('The UML User Guide');
 
insert into book_author (BookID,AuthorID) values (
(select ID from book where title = 'Pride & Prejudice'),
(select ID from author where Name = 'Jane Austin')
);
 
insert into book_author (BookID,AuthorID) values (
(select ID from book where title = 'Emma'),
(select ID from author where Name = 'Jane Austin')
);
 
insert into book_author (BookID,AuthorID) values (
(select ID from book where title = 'Sense & Sensibility'),
(select ID from author where Name = 'Jane Austin')
);
 
insert into book_author (BookID,AuthorID) values (
(select ID from book where title = 'Object Oriented Design with Applications'),
(select ID from author where Name = 'Grady Booch')
);
 
insert into book_author (BookID,AuthorID) values (
(select ID from book where title = 'The UML User Guide'),
(select ID from author where Name = 'Grady Booch')
);
 
insert into book_author (BookID,AuthorID) values (
(select ID from book where title = 'The UML User Guide'),
(select ID from author where Name = 'Ivar Jacobson')
);
 
insert into book_author (BookID,AuthorID) values (
(select ID from book where title = 'The UML User Guide'),
(select ID from author where Name = 'James Rumbaugh')
);

이제 질의를 해서 어떻게 작동하는지 알아볼 수 있습니다. 제인 어스틴(Jane Austin)의 책이 무엇인지 알아봅시다: 

sqlite> SELECT title from book
   ...> where book_author.bookID = book.ID
   ...> and book_author.authorID = (select ID from author 
   ...>                             where name = "Jane Austin");

약간 복잡해졌지만 가만히 앉아서 연구해 보면 곧 이해하게 될 것입니다. 다른 방식으로 시험해 봅시다. 누가 'The UML User Guide'를 썼는지 볼까요: 

sqlite> SELECT name from author
   ...> where book_author.authorID = author.ID
   ...> and book_author.bookID = (select ID from book 
   ...>                           where title = "The UML User Guide");

자세하게 보면 두 질의의 구조가 동일하다는 사실을 알 수 있습니다. 단지 테이블을 바꾸었고 필드 이름을 약간 바꾸었을 뿐입니다.

예제는 이것으로 충분합니다. 이제 주소록 예제로 다시 돌아가 보겠습니다. 이 예제는 지난 번에 파일 처리 주제에서 연구하던 것입니다. 더 읽기 전에 어떻게 그것을 파일 기반의 저장소에서 완전한 데이터베이스로 바꾸었는지 복습하셔도 좋습니다.

주소록 심층 연구

파일 기반의 주소록에서 이름을 키로 주소를 데이터 항목으로 하여 사전을 이용하였습니다. 언제나 이름을 알면 또는 언제나 완전한 주소를 자세하고 알고 싶으면 잘 작동합니다. 그러나 특정 도시에 사는 사람들을 모두 원하면 어떻게 할까? 또는 'John'이라고 부르는 모든 사람은? 각각의 질의를 위해 특정한 파이썬 코드를 작성할 수도 있겠지만 특별한 질의의 개수가 늘어갈 수록 노력도 심각한 한계에 부딪칩니다. 이곳에서 데이터베이스 접근법이 진가를 발휘합니다. SQL을 사용하여 동적으로 질의를 만들어 낼수 있기 때문입니다.

그래서 주소록은 데이터베이스로 어떤 모습인가? 기본적으로 테이블은 한 개입니다. 데이터를 주소와 개인으로 나누고 그 둘을 연결할 수 있습니다 - 어쨌거나 같은 집에 친구가 여럿 살수도 있지만, 최초의 디자인을 밀고나가 간단한 테이블을 사용하겠습니다.

데이터를 여러 필드로 가를 생각입니다. 간단한 이름과 주소 구조 대신에 이름을 성과 명으로 나누고 주소는 구성요소 별로 나누겠습니다. 가장 최선으로 이를 처리하기 위하여 많은 연구가 있었습니다. 확실한 해답은 없지만 단일 필드 주소는 나쁜 생각이라는데에는 누구나 공감합니다 - 단일 주소 필드는 너무 유연하지 못합니다. 데이터베이스 테이블의 필드를 나열해보고 적용하고 싶은 제약을 나열해 봅시다:

Field Name

Type

Constraint

First Name

String

Primary Key

Last Name

String

Primary Key

House Number

String

NOT NULL

Street

String

NOT NULL

District

String

Town

String

NOT NULL

Post Code

String

NOT NULL

Phone Number

String

NOT NULL

주의해야 할 요점 몇 가지:

1.     기본 키가 두 개인데 이는 허용되지 않는다. 잠시후에 그를 다루어 보겠다.

2.     모든 데이터가 string 유형이다. 집번호 조차도 문자열 유형인데 이는 정수(integer)가 맞다. 불행하게도 집번호는 종종 문자도 포함된다. 그래서 문자열(string)을 사용하지 않을 수 없다.

3.     district는 유일하게 선택적인 필드이다.

4.     우편 번호는 형태가 아주 구체적이다. 그러나 한 나라 안에서만 그렇다. 이는 유형을 string로 남겨두어서 모든 사건을 만족시켜야 한다는 뜻이다.

5.     전화 번호는 UNIQUE 제약의 후보처럼 보이지만, 그렇게 되면 두 친구가 같은 전화번호를 공유하는 것을 허용하지 않을 것이다.

처음 요점으로 되돌아 가서 기본 키가 두 개입니다. 이는 SQL에서 허용되지 않지만 두개의 컬럼을 취해서 조합할 수 있습니다. 이를 합성 키(composite key)라고 부르며 행을 구별하는 것에 관해서는 단일한 값으로 취급할 수 있습니다. 그리하여 create table 서술문 끝에 한 줄을 추가하여 FirstName과 LastName을 하나의 Primary Key로 조합할 수 있습니다. 다음과 같이 보일 것입니다: 

create table address (
FirstName NOT NULL,
LastName NOT NULL,
...
PhoneNumber NOT NULL,
PRIMARY KEY (FirstName,LastName)
);

마지막 줄에서 합성 키로 사용하고 싶은 두 컬럼을 보여주고 있습니다. (이는 실제로 테이블 기반의 제약을 보여주는 한 예입니다.)

그렇지만, 가만히 생각해 보면 실제로는 그렇게 좋은 생각이 아닙니다. 왜냐하면 두 사람이 같은 이름을 사용할 경우 오직 그 중에 하나만 저장할 수 있기 때문입니다. 무엇보다 integer primary key 필드를 정의하여 약속을 유일하게 식별함으로써 이 문제를 다루어 보겠습니다. 물론 질의에서는 거의 사용하지 않겠지만 말입니다.

사원 예제에서 정수 기본 키 제약을 선언하는 법을 배웠습니다.

다음과 같이 그것을 SQLite 데이터 생성 스크립트 안에 곧바로 집어 넣어 보겠습니다:

-- 테이블이 존재하더라도 삭제하고 다시 새로이 생성한다

-- 제약을 이용하여 데이터의 정합성을 개선한다
drop table address;
create table address (
ContactID Integer Primary Key,
First Not Null,
Last Not Null,
House Not Null,
Street Not Null,
District,
Town Not Null,
PostCode Not Null,
Phone Not Null
);

앞의 두 줄은 그냥 주석입니다. 파이썬의 # 심볼과 같아서 옆줄 두 개(--) 다음에 따라오는 것이면 무엇이든 SQL에서 주석으로 간주됩니다.

그 유형을 정의하지 않았음을 주목하세요. String이 SQLite에서 기본 값이기 때문인데, 컴퓨터 언어로 된 이 스키마(schema) 즉, 테이블 레이아웃을 다른 데이터베이스로 변환하거나 이식(port)하고 싶다면 아마도 다시 돌아가 유형 정보를 추가할 필요가 있을 것입니다.

다음 단계는 데이터를 테이블에 적재하여 질의를 수행할 준비를 하는 것입니다. 그 일은 독자에게 연습문제로 남겨 두겠습니다. 그러나 다음 데이터 집합(data set)을 다음 예제에 사용하겠습니다:

First

Last

House

Street

District

Town

PostCode

Phone

Anna

Smith

42

Any Street

SomePlace

MyTown

ABC123

01234 567890

Bob

Builder

17

Any Street

SomePlace

MyTown

ABC234

01234 543129

Clarke

Kennit

9

Crypt Drive

Hotspot

MyTown

ABC345

01234 456459

Dave

Smith

42

Any Street

SomePlace

MyTown

ABC123

01234 567890

Dave

Smith

12A

Double Street

AnyTown

DEF174

01394 784310

Debbie

Smith

12A

Double Street

AnyTown

DEF174

01394 784310

이제 데이터가 몇 개 확보되었으므로 가지고 놀아보고 어떻게 SQL 파워를 이용하면 정보를 추출할 수 있는지 알아봅시다. 파이썬 사전에 기반한 단순한 파일에서는 꿈조차 꿀 수 없었던 방식을 알아 봅시다.

누가 거리에 사는가?

이는 주소를 따로따로 필드로 나누었기 때문에 아주 눈에 보이는 그대로 이해가 되는 SQL 질의입니다. 그렇게 하지 않았다면 훨씬 더 복잡하게 문자열 해석 코드를 작성해서 주소 데이터를 뽑아내야 했을 것입니다. SQL 질의는 다음과 같이 보입니다:

sqlite> SELECT First,Last FROM Address
   ...> WHERE Street = "Any Street";

누가 Smith인가?

이 역시 눈에 보이는 그대로 이해가 되는 select/where SQL 표현식입니다:

sqlite> SELECT First,Last FROM Address
   ...> WHERE Last = "Smith";

Dave의 전화 번호는 무엇인가?

역시 눈에 보이는 그대로의 질의입니다. 다만 여러 결과를 돌려받는 점이 다릅니다. 

sqlite> SELECT First,Last, Phone FROM Address
   ...> WHERE First Like "Dav%";

Like를 where 절에 사용했음을 주목하세요. 이는 와일드 카드 스타일 비교를 사용하고 대소문자를 구별하지 않습니다. (SQL 와일드 카드는 백분률 기호(%)입니다. 더 일반적인 별표(*)가 아닙니다.) 결과적으로 정확하게 일치를 요구하는 동등성보다 더 느슨하게 매치됩니다. 와일드 카드 패턴으로 D%만 사용했다면 Debbie도 선택되었을 것입니다.

중복된 이름이란 무엇인가?

다음은 더 복잡한 질의입니다. 한 번 이상 나타나는 엔트리를 선택할 필요가 있습니다. 다음은 유일 ContactID 키가 역할을 하는 곳입니다: 

sqlite> SELECT DISTINCT A.First, A.Last 
   ...> FROM Address as A, Address as B
   ...> WHERE A.First = B.First
   ...> AND A.Last = B.Last
   ...> AND NOT A.ContactID = B.ContactID;

여기에서 몇 가지 새로운 특징을 사용합니다. 첫 번째 특징은 from 절에서 별명(alias)으로 A와 B를 테이블에 준다는 것입니다. (타자수를 절감하기 위하여 결과 값에도 역시 별명을 줄 수 있습니다.) 또 친숙한 점 연산자를 사용하여 결과 필드를 참조할 때도 그런 별명을 사용합니다. 질의에 별명을 사용할 수 있지만 여기에서는 그렇게 하지 않을 수 없습니다. 왜냐하면 같은 테이블을 즉, Address를 두 번 사용하기 때문입니다 (그리하여 자신을 조인합니다!) 그래서 where 절에서 두 개의 실체를 구별하려면 두 개의 별명이 필요합니다. 또 DISTINCT 키워드를 도입했는데 그 결과로 중복 결과가 제거되었습니다.

요약하면, 이 질의는 성과 이름은 같지만 contactID는 다른 행을 검색합니다. 다음 중복 결과를 제거하고 화면에 보여줍니다.

파이썬 상호대화 프롬프트에서처럼 SqlLite 상호대화 프롬프트는 이와 같이 더 복잡한 질의를 개발할 때 강력한 도구입니다. 간단한 질의로 시작한 다음 진행해 가면서 복잡하게 구축할 수 있습니다. 예를 들어 질의에 마지막으로 추가한 부분은 DISTINCT 키워드였습니다. 물론 DISTINCT 키워드는 최종 질의의 두 번째 단어입니다!

파이썬에서 SQL 접근하기

SQLite는 수 많은 표준 함수로 구성된 API(Application Programmers Interface)를 제공합니다. 프로그래머는 이 API을 이용하면 상호대화 SQL 프롬프트에서 해왔던 것과 똑같이 모두 수행할 수 있습니다. SQLite API는 C로 작성되었지만 파이썬을 비롯하여 다른 언어를 위하여 포장자(wrappers)가 제공됩니다.

커서(Cursors)

프로그램 안에서 데이터베이스에 접근할 때 고려해야 할 중요한 일은 잠재적으로 select 서술문이 돌려줄 여러 행의 데이터에 접근하는 법입니다. 그 대답은 SQL에서 커서(cursor)라고 알려진 것을 사용하는 것입니다. 커서는 파이썬의 연속열과 비슷합니다. 한 번에 한 행씩만 접근할 수 있다는 점에서 말입니다. 그리하여 커서 하나를 선택한 다음 회돌이를 사용하여 그 커서에 접근하면 방대한 데이터 집단을 처리할 수 있습니다.

DBAPI

최신 버전의 파이썬 DB API 문서는 파이썬 웹 사이트의 데이터베이스 주제 가이드에 있습니다. 파이썬을 사용하여 중요한 데이터베이스 프로그램을 할 생각이라면 이를 세심하게 읽어야 합니다.

SQLite 드라이버 설치하기

고지: 파이썬 2.5 버전부터 SQLite 드라이버가 표준 파이썬 라이브러리에 포함되었습니다. 2.5 버전에서 반입은 다음과 같이 합니다: 

   import sqlite3 as sqlite

아직 파이썬 2.5가 설치되어 있지 않으면 다음을 잘 읽어 보세요.

SQLite용 파이썬 DBAPI 드라이버는 여기에서 얻을 수 있습니다. 그냥 파이썬 버전에 상응하는 버전을 내려받아 윈도우즈를 사용하고 있다면 설치기를 실행하고, 다른 OS 사용자라면 웹 사이트에서 지시를 따를 필요가 있습니다! 설치되면 다음과 같이 드라이버 모듈을 반입할 수 있을 것입니다: 

from pysqlite2 import dbapi2 as sqlite

에러가 보고되지 않으면 성공하신 겁니다. 축하합니다. 모듈이 설치되었고 사용가능하게 되었습니다.

기본 DBI 사용법

모든 DBI 특징을 다룰 생각은 없습니다. 단지 데이터베이스에 접근해서 질의를 해보고 그 결과를 처리하는 정도로 만족하겠습니다. 마지막으로 텍스트 파일 대신에 데이터베이스를 사용하여 주소록 프로그램을 재작성해 보고 마치겠습니다. 

>>> db = sqlite.connect('D:/DOC/HomePage/Tutor2/sql/address.db')
>>> cur = db.cursor()
>>> cur.execute('Select * from address')
>>> print cur.fetchall()

그 결과는 다음과 같이 보입니다: 

[(1, u'Anna', u'Smith', u'42', u'Any Street', u'SomePlace', u'MyTown', u'ABC123', u'01234 567890'), 
 (2, u'Bob', u'Builder', u'17', u'Any Street', u'SomePlace', u'MyTown', u'ABC234', u'01234 543129'), 
 (3, u'Clarke', u'Kennit', u'9', u'Crypt Drive', u'Hotspot', u'MyTown', u'ABC345', u'01234 456459'),
 (4, u'Dave', u'Smith', u'42', u'Any Street', u'SomePlace', u'MyTown', u'ABC123', u'01234 567890'), 
 (5, u'Dave', u'Smith', u'12A', u'Double Street', u'', u'AnyTown', u'DEF174', u'01394 784310')]

보시다시피 커서는 터플로 구성된 리스트를 돌려줍니다. 이는 처음 시작할 때 미가공 재료 주제에서 다루었던 것과 아주 비슷합니다! 마치 파일에서 읽은 듯이 이 리스트를 프로그램에서 이용할 수 있습니다. 데이터베이스를 단순히 영속 메커니즘으로 사용해서 말입니다. 그렇지만 데이터베이스의 진정한 힘은 select를 사용하여 섬세한 질의를 수행하는데 있습니다.

주소록 - 마지막 시간

이제 마지막으로 주소록 예제를 보여드리겠습니다. 별로 다듬어지지 못했고 여전히 명령줄 기반입니다. GUI를 붙이고 싶을 수 있습니다. 잊지마시고 코드를 리팩토링하여 기능과 표현을 가르세요.

코드는 자세하게 설명하지 않겠습니다. 지금쯤이면 쭉 읽어 보시기만 해도 대부분 이해가 가시리라 믿습니다. 그렇지만 몇 가지 요점은 끝에 강조하겠습니다. 

 
###############################
# Addressbook.py
#
# Author: A J Gauld
#
# SQLite 데이터베이스와
# 파이썬 DB-API를 이용하여
# 간단한 주소록을 구축한다
###############################
 
# 데이터베이스와 커서를 설정한다.
dbpath = "D:/DOC/Homepage/Tutor2/sql/"
def initDB(path):
    from pysqlite2 import dbapi2 as sqlite
    try: 
        db = sqlite.connect(path)
        cursor = db.cursor()
    except : 
        print "Failed to connect to database:", path
        db,cursor = None,None
    return db,cursor
 
# 드라이버 함수들
def addEntry(book):
    first = raw_input('First name: ') 
    last =  raw_input('Last name: ') 
    house =  raw_input('House number: ') 
    street =  raw_input('Street name: ') 
    district =  raw_input('District name: ') 
    town =  raw_input('City name: ') 
    code =  raw_input('Postal Code: ') 
    phone =  raw_input('Phone Number: ') 
    query = '''INSERT INTO Address 
               (First,Last,House,Street,District,Town,PostCode,Phone)
               Values ("%s","%s","%s","%s","%s","%s","%s","%s")''' %\
               (first, last, house, street, district, town, code, phone)
    try:
       book.execute(query)
    except :  
       print "Insert failed"
       # raise
    return None
 
def removeEntry(book):
    name = raw_input("Enter a name: ")
    names = name.split()
    first = names[0]; last = names[-1]
    try:
       book.execute('''DELETE FROM Address 
                    WHERE First LIKE "%s" 
                  AND Last LIKE "%s"''' % (first,last))
    except : 
       print "Remove failed"
       # raise
    return None
    
def findEntry(book):
    field = raw_input("Enter a search field: ")
    value = raw_input("Enter a search value: ")
    query = '''SELECT first,last,house,street,district,town,postcode,phone 
               FROM Address WHERE %s LIKE "%s"''' % (field,value)
    try:
       book.execute(query)
    except : print "Sorry no matching data found"
    else: 
       for line in book.fetchall():
         print ' : '.join(line)
    return None
 
def testDB(database):
    database.execute("Select * from Address")
    print database.fetchall()
    return None
 
def closeDB(database, cursor):
    try:
       cursor.close()
       database.commit()
       database.close()
    except:
       print "problem closing database..."
       # raise
       
# 사용자 인터페이스 함수들
def getChoice(menu):
    print menu
    choice = raw_input("Select a choice(1-4): ")
    return choice
    
def main():
    theMenu = '''
    1) Add Entry
    2) Remove Entry
    3) Find Entry
    4) Test database connection
    9) Quit and save
    '''
    theDB, theBook = initDB(dbpath + 'address.db')
    choice = getChoice(theMenu)
    while choice != '9' and choice.upper() != 'Q':
        if choice == '1' or choice.upper() == 'A':
            addEntry(theBook)
        elif choice == '2' or choice.upper() == 'R':
            removeEntry(theBook)
        elif choice == '3' or choice.upper() == 'F':
            findEntry(theBook)
      elif choice == '4' or choice.upper() == 'T':
          testDB(theBook)
        else: print "Invalid choice, try again"
        choice = getChoice(theMenu)
    else: closeDB(theDB,theBook)
 
if __name__ == '__main__': main()
 

SQL 질의에서 문자열 주위에 인용부호를 붙인 것에 주목하세요. 그렇지 않으면 SQLite는 질의를 필드 이름으로 이해합니다. 또 closeDB 함수에 commit 호출이 포함되어 있음에 주목하세요. 이 함수는 데이터베이스에 현재 세션에서 변한 것들을 모두 다시 파일에 쓰도록 강제합니다. file.flush 메쏘드와 비슷하다고 생각해도 좋습니다. 마지막으로 insert 서술문에 모든 필드 이름을 포함해야 한다는 것을 주목하세요. 그렇지 않으면 SQLite는 필드 개수가 일치하지 않는다고 불평합니다. ID 필드가 AUTOINCREMENT로 선언되어 있다고 할지라도 말입니다.

마지막 요점은 데이터베이스 사용과 관련이 없지만 그럼에도 유용한 디버깅 트릭입니다. 여러 함수가 try/except 구조가 있으며 except 절에 raise 서술문을 배치했읍니다. 이제 주석 처리가 되어 있습니다. raise는 오직 디버깅을 돕기 위한 것입니다. 이것이 없다면 except 절이 파이썬 에러 보고를 완전히 가려버릴 것입니다. 그렇지만 사용자 친화적인 에러 보고를 복구하려면 이 한 줄을 간단하게 주석처리 하면 됩니다. 이는 디버깅 중에 전체 try/except 구조를 주석처리 하는 것보다 훨씬 더 쉽습니다.

보안에 관하여 한 마디

위의 코드가 작동하지만 그리고 파이썬에서 SQL을 호출하는 법을 보여주지만 심각한 결점이 하나 있습니다. 문자열 형식화를 이용하여 질의를 구축하였기 때문에 악성 사용자가 음험한 SQL 코드를 입력할 수 있습니다. 이 악당 코드는 형식화 문자열을 이용하여 질의에 삽입되고 실행되어, 잠재적으로 중요한 데이터를 지울 수도 있습니다. 그를 피하기 위해 execute() API 호출은 소매에 또다른 트릭을 숨기고 있습니다. execute()는 형식화 문자열에 사용된 값들을 따로 연속열 매개변수로 받아서 질의에 삽입해 줍니다. 만만한 문자열 형식화와 다르게 execute는 똑똑해서 대부분의 악당 코드를 탐지하고 에러를 일으킵니다. 그렇지만 완벽한 것은 아니며 데이터베이스에 제출되는 실제 SQL 질의를 볼 수 없습니다 (디버깅할 때는 유용할 수 있습니다). 그러나 사용자가 악성 데이터를 입력할 가능성이 조금이라도 있다면 이것이 execute를 사용하는 가장 좋은 방법입니다. 예를 들어 다음은 removeEntry() 함수로써 execute로 형식화를 수행합니다: 

def removeEntry(book):
    name = raw_input("Enter a name: ")
    names = name.split()
    first = names[0]; last = names[-1]
    try:
       book.execute('''DELETE FROM Address 
                    WHERE First LIKE ? 
                 AND Last LIKE ?''', (first,last) )
    except : 
       print "Remove failed"
       # raise
    return None

문자열 형식화 % 연산자를 제거하고 쉼표로 질의 문자열 인자와 터플 값 인자를 가르고 있습니다. 또한 형식화 표식은 이제 물음표입니다. 그렇지만 이 값은 데이터베이스 포장자에 종속적으로 사용됩니다. 마지막으로 표식 둘레에 인용부호를 붙일 필요가 없습니다. execute는 똑똑하게도 스스로 알아서 채웁니다.

맺는 말

SQLite를 예제에 사용했습니다. 왜냐하면 무료로 얻을 수 있고 설치하고 사용하기 쉬우며 실수에 상당히 관대하기 때문입니다. 그렇지만, 이런 단순함은 더 강력한 패키지에 포함된 특징들이 많이 모자란다는 뜻입니다. 특히 텍스트 처리 능력과 제약의 범위가 상당히 제한적입니다. Oracle이나 IBM사의 DB2를 데이터베이스를 만난다면 시간을 들여 참조 문서를 읽어보고 최대한의 효과를 얻도록 그 데이터베이스의 특징을 사용해 볼 가치가 충분합니다. 그러면 작성해야 할 맞춤 코드의 양을 상당히 줄일 수 있습니다.

주시해야 할 고급 특징은 아래 박스에 기술하여 두었습니다:

고급 데이터 베이스 특징

외래키

대부분의 데이터베이스는 외래키를 특징으로 합니다. 외래키를 이용하면 테이블 사이의 연결관계를 지정할 수 있습니다. 외래키는 다른 테이블의 기본 키를 구성합니다. 어떤 경우 외래키는 다른 데이터베이스에 있어도 될 뿐만 아니라 현재 데이터베이스의 다른 테이블에서 가져와도 됩니다. 테이블 사이의 조인은 여기에서 상세하게 연구하지 않았습니다. 그러나 이는 어플리케이션이 커질 수록 관계형 테이터베이스에서 가장 흔하게 사용되는 특징 중의 하나입니다.

참조 정합성

참조 정합성은 한 컬럼에 있는 데이터 값이 또다른 위치에 존재한다면 그 값만을 허용하는 능력입니다. 예를 들어 사원 데이터베이스에서 Employee.Grade 필드에 있는 값을 Salary.Grade 테이블에 정의된 값으로 제한할 수 있었습니다. 이는 데이터베이스 사이에 데이터의 일관성을 유지하는데 아주 강력한 도구이며 grade 컬럼에서 처럼 값들을 키로 사용하여 두 테이블을 조인하는 경우에 특히 중요합니다.

저장 프로시저

저장 프로시저는 데이터베이스 판매상이 제공한 독점적 프로그래밍 언어로 작성된 함수로서 데이터베이스에 저장됩니다. 저장 프로시저를 사용하면 좋은 점은 이것이 컴파일되어 있으므로 동등한 SQL 명령어보다 훨씬 더 빠른 경향이 있다는 것입니다. 게다가 클라이언트 프로그램에서 함수 이름과 인자만 건네면 되므로 대역폭이 절약됩니다. 서버 안에 내장되어 있기 때문에 복잡한 비즈니스 구축 같은 일반적인 행위를 데이터베이스 안에 구축해 넣을 수 있습니다. 데이터베이스 안에서 모든 어플리케이션이 일관성 있게 공유할 수 있습니다. 나쁜 점은 독점적이기 대문에 데이터베이스 벤더를 바꾸고 싶다면 저장 프로시저를 모두 재작성할 필요가 있다는 것입니다. 반면에 표준 SQL은 어느 데이터베이스에도 거의 그대로 작동할 것입니다.

뷰(Views)

뷰는 가상 테이블로서 실제 테이블로 구성됩니다. 뷰는 또다른 테이블의 데이터 하위집합일 수 있습니다. 한 테이블에 몇 칼럼과 또다른 테이블에서 몇 칼럼을 어떤 키로 조인하여 간편하게 열람할 수 있습니다. SQL 질의가 끊임없이 실행되고 그 결과가 뷰에 저장된다고 생각해도 좋습니다. 뷰는 아래의 데이터가 변하면 같이 변합니다. 어떤 데이터베이스는 뷰에서 읽기만 허용하지만 대부분은 갱신도 허용합니다. 뷰는 종종 데이터 분할을 구현하는데 사용됩니다. 예를 들어 한 사용자는 데이터에서 오직 자신에게 관련된 부분집합만 볼 수 있습니다.

계단식 삭제

두 데이터 항목 사이에 계단식 삭제가 설정되면 마스터 항목이 삭제될 때 그 아래의 모든 항목도 삭제된다는 뜻입니다. 가장 흔한 예제는 주문입니다. 주문은 보통 주문한 여러 항목과 주문 그 자체로 구성됩니다. 이런 것들은 전형적으로 두 개의 테이블에 따로 저장됩니다. 주문을 취소하면 주문 항목도 취소해야 합니다. 계단식 삭제는 보통 데이터베이스 DDL 서술문으로 환경구성됩니다. DDL은 데이터베이스 스키마 생성에 사용됩니다. 계단식 삭제는 일종의 제약입니다.

고급 데이터 유형

어떤 데이터 유형은 광범위한 데이터 유형을 저장할 수 있습니다. 보통의 숫자와 문자 그리고 날짜와 시간과 더불어, 네트워크 주소 이미지 파일 등등에 대한 이진 덩어리 객체 (BLOB)도 저장할 수 있습니다. 또다른 일반적인 데이터 유형은 고정 정밀도 십진 유형으로서 회계 데이터에 사용되는 유형입니다. 이를 사용하면 전통적인 부동소수점수에서 일어나는 반올림 에러를 피할 수 있습니다.

마지막으로 SQLite의 좀 더 섬세한 사용법을 탐험하고 싶다면 마이크 치리코(Mike Chirico)가 집필한 탁월한 자습서가 있습니다. 이 자습서는 웹 여러 곳에서 얻을 수 있지만 가장 읽기 쉬운 곳은 여기입니다. 위의 기본 지식을 머리에 익히면 아무 문제없이 마이크의 탁월한 자습서를 따라 가실 수 있을 것입니다.

기억해야 할 것

· 데이터베이스는 데이터를 테이블로 조직한다.

· 레코드는 필드로 구성되며 테이블의 행을 구성한다.

· SQL은 데이터를 관리하는데 사용되는 언어이다.

· CREATE, INSERT, SELECT 그리고 UPDATE는 핵심 명령어들이다.

· 프로그래밍 언어는 SQL 포장자를 제공하므로 데이터를 프로그램으로부터 접근할 수 있다.

· 커서는 SQL 질의의 결과를 임시로 그러나 접근 가능한 형태로 저장한다.

 

'Python > 파이썬 프로그래밍 연습' 카테고리의 다른 글

CGI 서버 예제  (0) 2012.05.07
운영 체제와 작업하기  (0) 2012.04.27
실전에 사용되는 파이썬  (0) 2012.04.25
사례 연구  (0) 2012.04.25
기능적 프로그래밍  (0) 2012.04.25

관련글

댓글

티스토리툴바