파이썬 기초 강좌 – SQLite 데이터베이스의 기본적인 사용법과 데이터 모델링에 대해 배웁니다. 테이블 생성, 데이터 삽입, CRUD 작업, 여러 테이블 간 관계 설정 등 SQL의 핵심 개념을 Python 코드 예제와 함께 자세히 설명합니다. 또한 데이터베이스 정규화와 성능 최적화를 위한 인덱스 사용법도 다룹니다.
1. 파이썬 기초 강좌 – 데이터베이스란 무엇인가?
데이터베이스는 데이터를 저장하기 위해 조직된 파일입니다. 대부분의 데이터베이스는 키와 값을 매핑하는 사전과 유사하게 구성됩니다. 하지만 데이터베이스는 디스크와 같은 영구 저장소에 저장되므로 프로그램이 종료된 후에도 데이터를 보존할 수 있습니다. 이는 컴퓨터 메모리에 제한된 사전보다 훨씬 더 많은 데이터를 저장할 수 있게 합니다.
데이터베이스 소프트웨어는 대량의 데이터에서도 빠른 데이터 삽입 및 접근을 가능하게 합니다. 이는 데이터가 추가될 때 인덱스를 생성하여 특정 항목으로 빠르게 이동할 수 있도록 유지하기 때문입니다. 대표적인 데이터베이스 시스템으로는 Oracle, MySQL, Microsoft SQL Server, PostgreSQL, 그리고 SQLite가 있습니다.
이 글에서는 SQLite에 집중합니다. SQLite는 매우 일반적인 데이터베이스로, 파이썬에 내장되어 있어 사용이 편리합니다. SQLite는 애플리케이션 내에 임베디드되어 데이터베이스 지원을 제공하도록 설계되었습니다. 예를 들어, Firefox 브라우저를 포함한 많은 제품들이 내부적으로 SQLite를 사용합니다.
SQLite는 정보학에서 자주 접하는 데이터 조작 문제를 해결하는 데 매우 적합합니다. SQLite에 대한 자세한 정보는 SQLite 공식 웹사이트에서 확인할 수 있습니다.
.
2. 데이터베이스 개념
데이터베이스는 처음 보면 여러 시트가 있는 스프레드시트처럼 보입니다. 데이터베이스의 주요 데이터 구조는 테이블, 행, 그리고 열입니다. 관계형 데이터베이스의 기술적 설명에서는 테이블, 행, 열의 개념을 각각 관계, 튜플, 속성이라고 더 공식적으로 표현합니다. 이 장에서는 비공식적인 용어를 사용하겠습니다.
.
3. SQLite용 데이터베이스 브라우저
이 글에서는 Python을 사용하여 SQLite 데이터베이스 파일을 다루는 방법에 초점을 맞추겠지만, 많은 작업은 SQLite용 데이터베이스 브라우저(Database Browser for SQLite)를 사용하면 더 편리하게 수행할 수 있습니다. 이 소프트웨어는 무료로 제공되며, 여기서 다운로드할 수 있습니다.
데이터베이스 브라우저를 사용하면 테이블을 쉽게 생성하고, 데이터를 삽입하거나 편집하고, 데이터베이스의 데이터를 대상으로 간단한 SQL 쿼리를 실행할 수 있습니다.
데이터베이스 브라우저는 텍스트 파일을 작업할 때 텍스트 편집기와 유사합니다. 텍스트 파일에서 한두 개의 작업만 수행할 때는 텍스트 편집기를 열어 원하는 변경을 합니다. 하지만 텍스트 파일에 많은 변경을 해야 할 경우 간단한 Python 프로그램을 작성하는 것이 더 효율적입니다. 데이터베이스 작업에서도 마찬가지로, 간단한 작업은 데이터베이스 관리자를 통해 수행하고, 더 복잡한 작업은 Python을 사용하여 수행하는 것이 가장 편리합니다.
.
4. 데이터베이스 테이블 생성하기
Python을 사용하여 데이터베이스 테이블을 만드는 방법을 알아보겠습니다. 데이터베이스는 Python 리스트나 딕셔너리보다 더 정의된 구조를 필요로 합니다. 따라서 데이터베이스 테이블을 생성할 때는 테이블의 각 열(column) 이름과 각 열에 저장할 데이터 유형을 미리 알려줘야 합니다. 이렇게 하면 데이터베이스 소프트웨어는 데이터 유형에 따라 가장 효율적인 방법으로 데이터를 저장하고 조회할 수 있습니다.
SQLite에서 지원하는 다양한 데이터 유형을 확인하려면 여기를 방문하세요.
4.1 데이터베이스 테이블 생성
데이터베이스 파일과 데이터베이스에 ‘Track’이라는 이름의 테이블을 생성하는 코드 예제는 다음과 같습니다:
import sqlite3
conn = sqlite3.connect('music.sqlite')
cur = conn.cursor()
cur.execute('DROP TABLE IF EXISTS Track')
cur.execute('CREATE TABLE Track (title TEXT, plays INTEGER)')
conn.close()sqlite3.connect('music.sqlite'): 현재 디렉토리에 ‘music.sqlite’라는 이름의 데이터베이스 파일을 생성합니다. 파일이 없으면 새로 생성됩니다.conn.cursor(): 데이터베이스와의 상호작용을 위해 커서를 생성합니다.cur.execute('DROP TABLE IF EXISTS Track'): 기존의 ‘Track’ 테이블이 있으면 삭제합니다.cur.execute('CREATE TABLE Track (title TEXT, plays INTEGER)'): ‘Track’ 테이블을 생성하고 ‘title’과 ‘plays’라는 두 개의 열을 정의합니다.
4.2 데이터 삽입 및 조회
이제 ‘Track’ 테이블에 데이터를 삽입하고 조회하는 방법을 알아보겠습니다:
import sqlite3
conn = sqlite3.connect('music.sqlite')
cur = conn.cursor()
cur.execute('INSERT INTO Track (title, plays) VALUES (?, ?)', ('Thunderstruck', 20))
cur.execute('INSERT INTO Track (title, plays) VALUES (?, ?)', ('My Way', 15))
conn.commit()
print('Track:')
cur.execute('SELECT title, plays FROM Track')
for row in cur:
print(row)
cur.execute('DELETE FROM Track WHERE plays < 100')
conn.commit()
cur.close()cur.execute('INSERT INTO Track (title, plays) VALUES (?, ?)', ('Thunderstruck', 20)): ‘Track’ 테이블에 ‘Thunderstruck’이라는 제목과 20의 플레이 횟수를 가진 행을 삽입합니다.cur.execute('INSERT INTO Track (title, plays) VALUES (?, ?)', ('My Way', 15)): ‘Track’ 테이블에 ‘My Way’라는 제목과 15의 플레이 횟수를 가진 행을 삽입합니다.conn.commit(): 삽입한 데이터를 데이터베이스 파일에 저장합니다.cur.execute('SELECT title, plays FROM Track'): ‘Track’ 테이블에서 ‘title’과 ‘plays’ 열의 데이터를 조회합니다.for row in cur: 조회한 데이터를 반복문을 통해 출력합니다.cur.execute('DELETE FROM Track WHERE plays < 100'): 플레이 횟수가 100 미만인 행을 삭제합니다.conn.commit(): 삭제한 데이터를 데이터베이스 파일에서 제거합니다.
위 코드의 출력 결과는 다음과 같습니다:
Track:
('Thunderstruck', 20)
('My Way', 15)이 예제에서는 두 개의 행이 조회되었으며, 각 행은 제목과 플레이 횟수를 포함하는 Python 튜플로 표시됩니다.
마지막으로, DELETE 명령어를 사용하여 삽입한 데이터를 삭제하고, commit()을 호출하여 데이터베이스에서 데이터를 제거합니다. 이를 통해 프로그램을 여러 번 실행해도 같은 결과를 얻을 수 있습니다.
이처럼 Python과 SQLite를 사용하면 간단하고 효율적으로 데이터베이스를 관리할 수 있습니다. SQL 명령어를 통해 데이터베이스를 조작하고, 데이터를 효율적으로 저장하고 조회할 수 있습니다.
.
5. SQL 요약: 기본 명령어와 사용법
Python 예제에서 Structured Query Language(SQL)를 사용하여 데이터베이스와 상호작용하는 방법을 배웠습니다. 이번 섹션에서는 SQL 언어의 개요와 기본 구문을 살펴보겠습니다. SQL은 여러 데이터베이스 벤더 간에 호환성을 유지하기 위해 표준화되었습니다.
5.1 SQL의 기본 구성 요소
관계형 데이터베이스는 테이블, 행, 열로 구성됩니다. 열은 일반적으로 텍스트, 숫자 또는 날짜 데이터 유형을 가집니다. 테이블을 생성할 때 각 열의 이름과 유형을 지정해야 합니다.
CREATE TABLE Track (title TEXT, plays INTEGER)5.2 데이터 삽입
테이블에 행을 삽입하려면 SQL INSERT 명령을 사용합니다:
INSERT INTO Track (title, plays) VALUES ('My Way', 15)INSERT 문은 테이블 이름과 새 행에서 설정하려는 필드/열 목록을 지정한 후, VALUES 키워드와 각 필드에 해당하는 값을 나열합니다.
5.3 데이터 조회
SQL SELECT 명령은 데이터베이스에서 행과 열을 조회하는 데 사용됩니다. SELECT 문을 통해 조회하고자 하는 열과 WHERE 절을 사용하여 조회할 행을 지정할 수 있습니다. 또한, ORDER BY 절을 통해 반환된 행의 정렬을 제어할 수 있습니다.
SELECT * FROM Track WHERE title = 'My Way'*를 사용하면 WHERE 절과 일치하는 각 행의 모든 열을 반환합니다.
SELECT title, plays FROM Track ORDER BY title이 명령은 title 열을 기준으로 행을 정렬하여 반환합니다.
5.4 데이터 업데이트
테이블의 하나 이상의 행에서 열을 업데이트하려면 SQL UPDATE 명령을 사용합니다:
UPDATE Track SET plays = 16 WHERE title = 'My Way'UPDATE 문은 테이블 이름과 변경할 필드 및 값을 SET 키워드 뒤에 지정하고, WHERE 절을 통해 업데이트할 행을 선택합니다. WHERE 절을 지정하지 않으면 테이블의 모든 행이 업데이트됩니다.
5.5 데이터 삭제
SQL DELETE 명령은 특정 행을 삭제하는 데 사용됩니다. WHERE 절을 사용하여 삭제할 행을 지정합니다:
DELETE FROM Track WHERE title = 'My Way'5.6 CRUD 개념
이 네 가지 기본 SQL 명령어(INSERT, SELECT, UPDATE, DELETE)는 데이터를 생성하고 유지하는 데 필요한 네 가지 기본 작업을 수행합니다. 이를 종합하여 “CRUD”(Create, Read, Update, Delete)라고 합니다.
이로써 SQL의 기본 명령어와 사용법을 이해하게 되었습니다. SQL을 통해 효율적으로 데이터베이스를 관리하고, 데이터를 생성, 조회, 업데이트 및 삭제할 수 있습니다. SQL은 데이터베이스와의 상호작용을 표준화된 방법으로 제공하여 여러 벤더 간에 호환성을 유지하는 데 큰 도움이 됩니다.
.
6. 다중 테이블과 기본 데이터 모델링
관계형 데이터베이스의 진정한 강점은 다중 테이블을 생성하고 이러한 테이블 간의 연결을 만드는 데 있습니다. 애플리케이션 데이터를 여러 테이블로 분할하고 테이블 간의 관계를 설정하는 작업을 데이터 모델링이라고 합니다. 테이블과 그 관계를 보여주는 설계 문서를 데이터 모델이라고 합니다. 이번 섹션에서는 관계형 데이터 모델링의 기본 개념을 소개합니다. 자세한 내용은 여기를 참조하세요.
6.1 기본 데이터 모델링
예를 들어, 각 트랙의 제목과 재생 횟수 외에도 아티스트의 이름을 추적하고 싶다고 가정해 보겠습니다. 간단한 접근 방식은 데이터베이스에 ‘artist’라는 열을 추가하는 것입니다:
DROP TABLE IF EXISTS Track;
CREATE TABLE Track (title TEXT, plays INTEGER, artist TEXT);그리고 몇 개의 트랙을 테이블에 삽입할 수 있습니다:
INSERT INTO Track (title, plays, artist)
VALUES ('My Way', 15, 'Frank Sinatra');
INSERT INTO Track (title, plays, artist)
VALUES ('New York', 25, 'Frank Sinatra');데이터를 조회하면 잘 작동하는 것처럼 보입니다:
SELECT * FROM Track;결과:
My Way|15|Frank Sinatra
New York|25|Frank Sinatra하지만 이는 데이터베이스 정규화를 위반한 것입니다. 정규화에 대해 자세히 알고 싶다면 여기를 참조하세요. 간단히 말해, 동일한 문자열 데이터를 여러 번 열에 넣어서는 안 됩니다. 필요한 경우, 숫자 키를 생성하여 실제 데이터를 참조합니다.
6.2 데이터 모델링의 문제 해결
아티스트의 눈 색깔을 추적하고 싶다면 어떻게 해야 할까요?
DROP TABLE IF EXISTS Track;
CREATE TABLE Track (title TEXT, plays INTEGER, artist TEXT, eyes TEXT);위 접근 방식은 잘못된 것입니다. Frank Sinatra가 1200곡 이상을 녹음했다면, ‘Blue’라는 문자열을 1200행에 넣어야 합니다. ‘Light Blue’로 변경하려면 어떻게 해야 할까요? 이런 방식은 비효율적입니다.
올바른 해결책은 각 아티스트를 위한 별도의 테이블을 생성하고, 아티스트에 대한 모든 데이터를 그 테이블에 저장하는 것입니다. 그런 다음, Track 테이블의 행과 Artist 테이블의 행을 연결해야 합니다. 이 연결을 ‘관계’라고 합니다.
DROP TABLE IF EXISTS Artist;
CREATE TABLE Artist (name TEXT, eyes TEXT);
INSERT INTO Artist (name, eyes)
VALUES ('Frank Sinatra', 'blue');이제 Artist 테이블과 Track 테이블을 연결하기 위해 키를 사용해야 합니다. 각 테이블에 기본 키(primary key)를 추가합니다:
DROP TABLE IF EXISTS Artist;
CREATE TABLE Artist (id INTEGER, name TEXT, eyes TEXT);
INSERT INTO Artist (id, name, eyes)
VALUES (42, 'Frank Sinatra', 'blue');이제 ‘Frank Sinatra’에 대한 행과 기본 키 ’42’가 있습니다. Track 테이블을 다음과 같이 변경합니다:
DROP TABLE IF EXISTS Track;
CREATE TABLE Track (title TEXT, plays INTEGER, artist_id INTEGER);
INSERT INTO Track (title, plays, artist_id)
VALUES ('My Way', 15, 42);
INSERT INTO Track (title, plays, artist_id)
VALUES ('New York', 25, 42);artist_id 열은 정수이며, 다른 테이블의 기본 키를 가리키는 외래 키(foreign key)입니다. 이제 데이터베이스 정규화를 준수하고 있습니다. 하지만 데이터 조회 시 숫자 대신 이름과 눈 색깔을 보고 싶다면, JOIN 키워드를 사용합니다:
SELECT title, plays, name, eyes
FROM Track JOIN Artist
ON Track.artist_id = Artist.id;결과:
My Way|15|Frank Sinatra|blue
New York|25|Frank Sinatra|blue이 방식은 처음에는 번거로워 보일 수 있지만, 실제로는 데이터베이스 성능의 한계를 극복하는 데 도움이 됩니다. 문자열보다 정수 비교가 훨씬 빠르기 때문입니다.
.
7. 데이터 모델 다이어그램
데이터베이스 설계가 단순할 때는 이해하기 쉬울 수 있지만, 복잡한 데이터 모델을 다룰 때는 그래픽 표현이 훨씬 유용합니다. 이번 섹션에서는 “Crow’s Foot 다이어그램”이라는 고전적인 방법을 사용하여 데이터 모델을 시각화하는 방법을 설명하겠습니다.
7.1 데이터 모델 시각화
우리의 Track과 Artist 데이터베이스 설계는 두 개의 테이블과 하나의 일대다 관계만을 포함하는 단순한 예입니다. 그러나 이러한 데이터 모델은 금방 복잡해질 수 있으며, 시각적 표현을 통해 더 쉽게 이해할 수 있습니다.
Crow’s Foot 다이어그램
Crow’s Foot 다이어그램에서는 각 테이블이 상자(box)로 표시되고, 테이블의 이름과 열(column)이 포함됩니다. 두 테이블 간의 관계는 선으로 연결되며, 선 끝에는 관계의 특성을 나타내는 표기법이 추가됩니다.
다수의 트랙이 각 아티스트와 연관될 수 있습니다. 트랙 쪽 끝은 “많음”을 나타내는 까마귀 발 모양으로 표시되며, 아티스트 쪽 끝은 “하나”를 나타내는 수직선으로 표시됩니다. 각 아티스트는 여러 트랙과 연관될 수 있으며, 각 트랙은 특정 아티스트와 연관됩니다.
외래 키(foreign key)인 artist_id는 “많음” 끝에 있고, 기본 키(primary key)는 “하나” 끝에 있습니다. 외래 키와 기본 키 배치의 패턴은 매우 일관성이 있어, 데이터 모델 다이어그램에서 외래 키와 기본 키 열을 생략할 수 있습니다. 이러한 열들은 관계의 세부 사항을 포착하는 “구현 세부 사항”으로 간주되며, 모델링되는 데이터의 본질적인 부분으로 간주되지 않습니다.
7.2 예제: Track과 Artist 테이블
다음은 Track과 Artist 테이블 간의 관계를 나타내는 간단한 Crow’s Foot 다이어그램입니다:
+------------+ +--------------+
| Artist | | Track |
+------------+ +--------------+
| id |<---------| artist_id |
| name | | title |
| eyes | | plays |
+------------+ +--------------+이 다이어그램은 다음을 나타냅니다:
Artist테이블은id,name,eyes열을 포함합니다.Track테이블은artist_id,title,plays열을 포함합니다.artist_id는Artist테이블의id를 참조하는 외래 키입니다.- 각 아티스트는 여러 트랙과 연결될 수 있으며, 각 트랙은 하나의 아티스트와 연결됩니다.
이러한 그래픽 표현은 데이터 모델링을 더 직관적으로 이해할 수 있게 해줍니다. 관계형 데이터베이스에서의 다중 테이블과 그 관계를 시각적으로 표현하는 것은 데이터베이스 설계와 유지 관리에 큰 도움이 됩니다.
Crow’s Foot 다이어그램을 사용하면 복잡한 데이터 모델도 명확하고 간단하게 표현할 수 있습니다. 데이터베이스 설계의 본질적인 부분을 포착하여 효과적으로 데이터 모델을 설명할 수 있습니다.
.
8. 자동으로 기본 키 생성 및 논리 키를 통한 빠른 조회
데이터베이스에서 수백만 개의 행을 삽입할 때, 기본 키를 수동으로 할당하는 대신 데이터베이스가 자동으로 기본 키 값을 생성하도록 하는 것이 좋습니다. 이를 통해 고유한 값을 자동으로 할당받을 수 있습니다.
8.1 기본 키 자동 생성
다음은 Artist 테이블을 생성하고, id 열을 PRIMARY KEY로 설정하여 자동으로 고유 값을 생성하는 예제입니다:
DROP TABLE IF EXISTS Artist;
CREATE TABLE Artist (id INTEGER PRIMARY KEY, name TEXT, eyes TEXT);
INSERT INTO Artist (name, eyes) VALUES ('Frank Sinatra', 'blue');이제 데이터베이스가 Frank Sinatra 행에 대해 고유 값을 자동으로 할당합니다. 최근 삽입된 행의 id 값을 얻기 위해 SQLite의 last_insert_rowid() 함수를 사용할 수 있습니다:
sqlite> DROP TABLE IF EXISTS Artist;
sqlite> CREATE TABLE Artist (id INTEGER PRIMARY KEY, name TEXT, eyes TEXT);
sqlite> INSERT INTO Artist (name, eyes) VALUES ('Frank Sinatra', 'blue');
sqlite> SELECT last_insert_rowid();
1
sqlite> SELECT * FROM Artist;
1|Frank Sinatra|blue이제 Frank Sinatra 행의 id 값을 알게 되었으므로, 이를 사용하여 Track 테이블에 트랙을 삽입할 수 있습니다. 모든 테이블에 id 열을 추가하는 일반적인 전략을 사용합니다:
DROP TABLE IF EXISTS Track;
CREATE TABLE Track (id INTEGER PRIMARY KEY, title TEXT, plays INTEGER, artist_id INTEGER);artist_id 값은 Artist 테이블의 새로 할당된 행을 참조합니다. Track 테이블에 행을 삽입할 때 id 열을 포함하지 않으면 데이터베이스가 고유 값을 자동으로 선택합니다:
sqlite> INSERT INTO Track (title, plays, artist_id) VALUES ('My Way', 15, 1);
sqlite> SELECT last_insert_rowid();
1
sqlite> INSERT INTO Track (title, plays, artist_id) VALUES ('New York', 25, 1);
sqlite> SELECT last_insert_rowid();
2각 삽입 후 SELECT last_insert_rowid()를 호출하여 데이터베이스가 새로 생성된 행의 id 값을 할당했음을 알 수 있습니다. Python 코드에서 이 값을 변수에 저장하여 나중에 사용할 수 있습니다.
8.2 논리 키를 통한 빠른 조회
아티스트 테이블과 트랙 테이블이 각각 외래 키로 연결되어 있을 때, 특정 아티스트의 트랙을 조회하고 싶을 수 있습니다. 예를 들어, ‘Frank Sinatra’가 부른 모든 트랙을 조회하는 SQL 문은 다음과 같습니다:
SELECT title, plays, name, eyes
FROM Track JOIN Artist
ON Track.artist_id = Artist.id
WHERE Artist.name = 'Frank Sinatra';두 테이블이 외래 키로 연결되어 있어 데이터 모델링이 잘 되었지만, 아티스트 이름으로 많은 조회를 해야 한다면, 이름 열에 인덱스를 추가하여 조회 성능을 향상시킬 수 있습니다:
CREATE INDEX artist_name ON Artist(name);데이터베이스가 특정 열에 인덱스가 필요하다는 것을 알게 되면, 인덱싱된 필드를 사용하여 행을 더 빠르게 조회할 수 있도록 추가 정보를 저장합니다. 인덱스를 생성한 후, 테이블에 접근하는 SQL에는 특별한 작업이 필요 없습니다. 데이터베이스는 데이터가 삽입, 삭제, 업데이트될 때 인덱스를 자동으로 유지하고, 데이터베이스 쿼리 성능이 향상될 경우 자동으로 인덱스를 사용합니다.
이처럼 실제 세계의 정보(예: 아티스트 이름)를 기반으로 행을 찾는 데 사용되는 텍스트 열을 논리 키라고 합니다. 논리 키를 통해 데이터베이스의 성능을 최적화하고 더 빠르게 데이터를 조회할 수 있습니다.
.
9. 데이터베이스 제약 조건 추가하기
데이터베이스에서 제약 조건(constraints)을 사용하면 데이터의 무결성을 유지하고 특정 규칙을 강제할 수 있습니다. 이번 섹션에서는 인덱스를 사용하여 데이터베이스 제약 조건을 추가하는 방법에 대해 설명하겠습니다. 특히, 유일성 제약 조건(uniqueness constraint)을 살펴보겠습니다.
9.1 유일성 제약 조건
유일성 제약 조건은 특정 열의 모든 값이 고유해야 함을 보장합니다. CREATE INDEX 문에 UNIQUE 키워드를 추가하여 데이터베이스가 해당 열에 유일성 제약 조건을 적용하도록 할 수 있습니다.
유일성 제약 조건 적용
먼저 기존의 인덱스를 삭제하고, 유일성 제약 조건이 있는 인덱스를 다시 생성합니다:
DROP INDEX artist_name;
CREATE UNIQUE INDEX artist_name ON Artist(name);이제 ‘Frank Sinatra’를 두 번 삽입하려고 하면 오류가 발생합니다:
sqlite> SELECT * FROM Artist;
1|Frank Sinatra|blue
sqlite> INSERT INTO Artist (name, eyes) VALUES ('Frank Sinatra', 'blue');
Runtime error: UNIQUE constraint failed: Artist.name (19)
sqlite>중복 키 오류를 무시하려면 INSERT 문에 IGNORE 키워드를 추가합니다:
sqlite> INSERT OR IGNORE INTO Artist (name, eyes) VALUES ('Frank Sinatra', 'blue');
sqlite> SELECT id FROM Artist WHERE name='Frank Sinatra';
1
sqlite>9.2 INSERT OR IGNORE와 SELECT 결합
INSERT OR IGNORE와 SELECT를 결합하여 이름이 이미 존재하지 않는 경우 새 레코드를 삽입하고, 레코드가 이미 존재하는 경우 해당 기본 키를 조회할 수 있습니다:
sqlite> INSERT OR IGNORE INTO Artist (name, eyes) VALUES ('Elvis', 'blue');
sqlite> SELECT id FROM Artist WHERE name='Elvis';
2
sqlite> SELECT * FROM Artist;
1|Frank Sinatra|blue
2|Elvis|blue
sqlite>예제 코드
다음은 전체적인 예제 코드입니다:
DROP TABLE IF EXISTS Artist;
CREATE TABLE Artist (id INTEGER PRIMARY KEY, name TEXT, eyes TEXT);
-- 유일성 제약 조건이 있는 인덱스 생성
CREATE UNIQUE INDEX artist_name ON Artist(name);
-- 데이터 삽입
INSERT INTO Artist (name, eyes) VALUES ('Frank Sinatra', 'blue');
-- 중복 삽입 시도 (오류 발생)
INSERT INTO Artist (name, eyes) VALUES ('Frank Sinatra', 'blue');
-- 중복 삽입 무시
INSERT OR IGNORE INTO Artist (name, eyes) VALUES ('Frank Sinatra', 'blue');
-- 새로운 데이터 삽입
INSERT OR IGNORE INTO Artist (name, eyes) VALUES ('Elvis', 'blue');
-- 데이터 조회
SELECT id FROM Artist WHERE name='Elvis';
SELECT * FROM Artist;9.3 결과
이 예제에서는 ‘Frank Sinatra’와 ‘Elvis’ 두 명의 아티스트가 Artist 테이블에 추가되었습니다. name 열에는 유일성 제약 조건이 적용되었기 때문에, 동일한 이름을 두 번 삽입하려고 하면 오류가 발생합니다. 그러나 INSERT OR IGNORE를 사용하면 중복 오류를 무시하고, 필요한 경우 기존 레코드를 조회할 수 있습니다.
이처럼 유일성 제약 조건과 IGNORE 키워드를 활용하여 데이터의 무결성을 유지하면서 효율적으로 데이터를 관리할 수 있습니다.
.
10. 다중 테이블을 활용한 샘플 애플리케이션
이 섹션에서는 tracks_csv.py라는 샘플 애플리케이션을 통해 다중 테이블과 데이터 모델링 개념을 결합하여 데이터를 삽입하고 관리하는 방법을 소개합니다. 이 애플리케이션은 tracks.csv라는 쉼표로 구분된 파일을 읽고, 파싱한 데이터를 여러 테이블에 적절히 삽입합니다.
10.1 데이터 모델
데이터 모델은 다음과 같이 정의됩니다:
DROP TABLE IF EXISTS Artist;
DROP TABLE IF EXISTS Album;
DROP TABLE IF EXISTS Track;
CREATE TABLE Artist (
id INTEGER PRIMARY KEY,
name TEXT UNIQUE
);
CREATE TABLE Album (
id INTEGER PRIMARY KEY,
artist_id INTEGER,
title TEXT UNIQUE
);
CREATE TABLE Track (
id INTEGER PRIMARY KEY,
title TEXT UNIQUE,
album_id INTEGER,
len INTEGER,
rating INTEGER,
count INTEGER
);각 테이블에는 유일성 제약 조건이 있는 UNIQUE 키워드가 사용됩니다. 이를 통해 INSERT IGNORE 문을 사용할 때 중복 오류를 방지할 수 있습니다.
10.2 샘플 애플리케이션 코드
다음은 tracks_csv.py 코드입니다:
import sqlite3
conn = sqlite3.connect('trackdb.sqlite')
cur = conn.cursor()
handle = open('tracks.csv')
for line in handle:
line = line.strip()
pieces = line.split(',')
if len(pieces) != 6:
continue
name = pieces[0]
artist = pieces[1]
album = pieces[2]
count = pieces[3]
rating = pieces[4]
length = pieces[5]
print(name, artist, album, count, rating, length)
cur.execute('''INSERT OR IGNORE INTO Artist (name)
VALUES ( ? )''', (artist, ))
cur.execute('SELECT id FROM Artist WHERE name = ? ', (artist, ))
artist_id = cur.fetchone()[0]
cur.execute('''INSERT OR IGNORE INTO Album (title, artist_id)
VALUES ( ?, ? )''', (album, artist_id))
cur.execute('SELECT id FROM Album WHERE title = ? ', (album, ))
album_id = cur.fetchone()[0]
cur.execute('''INSERT OR REPLACE INTO Track
(title, album_id, len, rating, count)
VALUES ( ?, ?, ?, ?, ? )''',
(name, album_id, length, rating, count))
conn.commit()이 코드는 다음과 같은 작업을 수행합니다:
tracks.csv파일을 열고 각 줄을 읽습니다.- 쉼표로 구분된 각 줄을 파싱하여 트랙 정보(제목, 아티스트, 앨범, 재생 횟수, 평점, 길이)를 추출합니다.
INSERT OR IGNORE문을 사용하여 아티스트와 앨범 정보를 삽입하고, 각 삽입 후SELECT문을 통해artist_id와album_id를 조회합니다.INSERT OR REPLACE문을 사용하여 트랙 정보를 삽입합니다.- 데이터베이스 변경 사항을 커밋합니다.
10.3 데이터 조회
삽입된 데이터를 조회하기 위해 다음 SQL 명령어를 사용할 수 있습니다:
SELECT * FROM Album LIMIT 5;결과는 다음과 같습니다:
id artist_id title
-- --------- -----------------
1 1 Greatest Hits
2 2 Herzeleid
3 3 Grease
4 4 IV
5 5 The Wall [Disc 2]또한, JOIN을 사용하여 관계형 데이터를 조회할 수 있습니다:
SELECT * FROM Track
JOIN Album ON Track.album_id = Album.id
JOIN Artist ON Album.artist_id = Artist.id
LIMIT 2;결과는 다음과 같습니다:
id = 1
title = Another One Bites The Dust
album_id = 1
len = 217103
rating = 100
count = 55
id = 1
artist_id = 1
title = Greatest Hits
id = 1
name = Queen
id = 2
title = Asche Zu Asche
album_id = 2
len = 231810
rating = 100
count = 79
id = 2
artist_id = 2
title = Herzeleid
id = 2
name = Rammstein이 예제는 세 개의 테이블과 두 개의 일대다 관계를 보여줍니다. 인덱스와 유일성 제약 조건을 사용하여 테이블과 관계를 프로그래밍 방식으로 구성하는 방법도 보여줍니다. 다음 섹션에서는 다대다 관계를 다루는 방법을 알아보겠습니다.
.
11. 데이터베이스에서 다대다 관계 설정
다대다 관계는 데이터베이스에서 자주 등장하며, 이를 올바르게 모델링하기 위해 중간 테이블을 사용하는 방법을 알아보겠습니다. 이번 섹션에서는 코스 관리 시스템을 예로 들어 다대다 관계를 구현하는 방법을 설명하겠습니다.
11.1 다대다 관계 설명
예를 들어, 코스 관리 시스템에서 코스(Course)와 사용자(User)가 있다고 가정합니다. 한 사용자는 여러 코스에 등록될 수 있으며, 하나의 코스에는 여러 사용자가 등록될 수 있습니다. 이를 다대다 관계로 모델링하기 위해 중간 테이블(Member)을 사용합니다.
테이블 구조
세 개의 테이블을 사용하여 다대다 관계를 모델링합니다:
CREATE TABLE User (
id INTEGER PRIMARY KEY,
name TEXT UNIQUE
);
CREATE TABLE Course (
id INTEGER PRIMARY KEY,
title TEXT UNIQUE
);
CREATE TABLE Member (
user_id INTEGER,
course_id INTEGER,
PRIMARY KEY (user_id, course_id)
);User테이블은 사용자 정보를 저장합니다.Course테이블은 코스 정보를 저장합니다.Member테이블은User와Course간의 관계를 저장하며,user_id와course_id의 조합을 기본 키로 설정하여 고유성을 보장합니다.
데이터 삽입 및 조회
JSON 형식의 데이터를 읽어 각 테이블에 데이터를 삽입하는 Python 코드 예제입니다:
import json
import sqlite3
conn = sqlite3.connect('rosterdb.sqlite')
cur = conn.cursor()
str_data = open('roster_data_sample.json').read()
json_data = json.loads(str_data)
for entry in json_data:
name = entry[0]
title = entry[1]
print((name, title))
cur.execute('''INSERT OR IGNORE INTO User (name) VALUES ( ? )''', (name,))
cur.execute('SELECT id FROM User WHERE name = ? ', (name,))
user_id = cur.fetchone()[0]
cur.execute('''INSERT OR IGNORE INTO Course (title) VALUES ( ? )''', (title,))
cur.execute('SELECT id FROM Course WHERE title = ? ', (title,))
course_id = cur.fetchone()[0]
cur.execute('''INSERT OR REPLACE INTO Member (user_id, course_id) VALUES ( ?, ? )''', (user_id, course_id))
conn.commit()이 코드는 다음 작업을 수행합니다:
roster_data_sample.json파일을 열고 데이터를 읽습니다.- JSON 데이터를 파싱하여 사용자 이름과 코스 제목을 추출합니다.
INSERT OR IGNORE문을 사용하여User및Course테이블에 데이터를 삽입하고, 각 삽입 후SELECT문을 통해user_id와course_id를 조회합니다.INSERT OR REPLACE문을 사용하여Member테이블에 사용자와 코스 간의 관계를 삽입합니다.- 데이터베이스 변경 사항을 커밋합니다.
데이터 조회
삽입된 데이터를 조회하기 위해 다음 SQL 명령어를 사용할 수 있습니다:
SELECT * FROM Course
JOIN Member ON Course.id = Member.course_id
JOIN User ON Member.user_id = User.id;이 명령어는 세 개의 테이블을 조인하여 관계 데이터를 조회합니다. 결과는 다음과 같습니다:
+----+-------+---------+-----------+----+---------+
| id | title | user_id | course_id | id | name |
+----+-------+---------+-----------+----+---------+
| 1 | si110 | 1 | 1 | 1 | Charley |
| 1 | si110 | 2 | 1 | 2 | Mea |
| 1 | si110 | 3 | 1 | 3 | Hattie |
| 1 | si110 | 4 | 1 | 4 | Lyena |
| 1 | si110 | 5 | 1 | 5 | Keziah |
| 1 | si110 | 6 | 1 | 6 | Ellyce |
| 1 | si110 | 7 | 1 | 7 | Thalia |
| 1 | si110 | 8 | 1 | 8 | Meabh |
| 2 | si106 | 2 | 2 | 2 | Mea |
| 2 | si106 | 10 | 2 | 10 | Mairin |
| 2 | si106 | 11 | 2 | 11 | Zendel |
| 2 | si106 | 12 | 2 | 12 | Honie |
| 2 | si106 | 9 | 2 | 9 | Rosa |
+----+-------+---------+-----------+----+---------+위 결과에서는 Course, Member, User 테이블을 조인하여 각 코스와 사용자 간의 관계를 보여줍니다.
이와 같이 다대다 관계를 중간 테이블을 사용하여 모델링함으로써, 데이터베이스의 구조를 효율적으로 관리하고 확장할 수 있습니다. 다음 섹션에서는 다대다 관계의 더 복잡한 예제를 다룰 예정입니다.
.
12. 다대다 연결에서 데이터 모델링
다대다 관계를 모델링할 때, 단순히 두 개의 외래 키로 연결하는 것을 넘어서 그 연결 자체에 추가적인 데이터를 포함하고 싶을 때가 많습니다. 예를 들어, 학습 관리 시스템에서 사용자, 코스, 역할 정보를 관리하려면 사용자와 코스 간의 관계를 나타내는 Member 테이블에 역할 정보를 추가할 수 있습니다.
12.1 역할 정보 추가
먼저, Member 테이블에 역할 정보를 추가하는 방법을 살펴보겠습니다. 각 사용자는 코스에서 다양한 역할을 가질 수 있으며, 이를 role 열로 나타낼 수 있습니다.
테이블 구조 수정
Member 테이블에 role 열을 추가합니다:
DROP TABLE IF EXISTS Member;
CREATE TABLE Member (
user_id INTEGER,
course_id INTEGER,
role INTEGER,
PRIMARY KEY (user_id, course_id)
);역할 정보를 포함하는 JSON 데이터를 사용하여 Member 테이블에 데이터를 삽입하는 예제입니다:
import json
import sqlite3
conn = sqlite3.connect('rosterdb.sqlite')
cur = conn.cursor()
str_data = open('roster_data_sample.json').read()
json_data = json.loads(str_data)
for entry in json_data:
name = entry[0]
title = entry[1]
role = entry[2]
print((name, title, role))
cur.execute('''INSERT OR IGNORE INTO User (name) VALUES ( ? )''', (name,))
cur.execute('SELECT id FROM User WHERE name = ? ', (name,))
user_id = cur.fetchone()[0]
cur.execute('''INSERT OR IGNORE INTO Course (title) VALUES ( ? )''', (title,))
cur.execute('SELECT id FROM Course WHERE title = ? ', (title,))
course_id = cur.fetchone()[0]
cur.execute('''INSERT OR REPLACE INTO Member (user_id, course_id, role) VALUES ( ?, ?, ? )''', (user_id, course_id, role))
conn.commit()역할 테이블 추가
더 복잡한 시스템에서는 역할 정보를 별도의 Role 테이블로 분리하고, Member 테이블의 role 열을 외래 키로 설정할 수 있습니다:
DROP TABLE IF EXISTS Member;
CREATE TABLE Member (
user_id INTEGER,
course_id INTEGER,
role_id INTEGER,
PRIMARY KEY (user_id, course_id, role_id)
);
CREATE TABLE Role (
id INTEGER PRIMARY KEY,
name TEXT UNIQUE
);
INSERT INTO Role (id, name) VALUES (0, 'Student');
INSERT INTO Role (id, name) VALUES (1, 'Instructor');이 경우, 역할 데이터를 삽입할 때 role_id를 참조하도록 코드를 수정합니다:
import json
import sqlite3
conn = sqlite3.connect('rosterdb.sqlite')
cur = conn.cursor()
str_data = open('roster_data_sample.json').read()
json_data = json.loads(str_data)
for entry in json_data:
name = entry[0]
title = entry[1]
role = entry[2]
print((name, title, role))
cur.execute('''INSERT OR IGNORE INTO User (name) VALUES ( ? )''', (name,))
cur.execute('SELECT id FROM User WHERE name = ? ', (name,))
user_id = cur.fetchone()[0]
cur.execute('''INSERT OR IGNORE INTO Course (title) VALUES ( ? )''', (title,))
cur.execute('SELECT id FROM Course WHERE title = ? ', (title,))
course_id = cur.fetchone()[0]
cur.execute('''INSERT OR IGNORE INTO Role (id, name) VALUES (?, ?)''', (role, 'Student' if role == 0 else 'Instructor'))
cur.execute('SELECT id FROM Role WHERE id = ? ', (role,))
role_id = cur.fetchone()[0]
cur.execute('''INSERT OR REPLACE INTO Member (user_id, course_id, role_id) VALUES ( ?, ?, ? )''', (user_id, course_id, role_id))
conn.commit()12.2 데이터 조회
추가된 역할 정보를 포함하여 데이터를 조회하려면 다음과 같은 SQL 명령어를 사용할 수 있습니다:
SELECT User.name, Course.title, Role.name
FROM Member
JOIN User ON Member.user_id = User.id
JOIN Course ON Member.course_id = Course.id
JOIN Role ON Member.role_id = Role.id;이 명령어는 각 사용자가 각 코스에서 어떤 역할을 하는지 보여줍니다. 결과는 다음과 같습니다:
+---------+-------+-----------+
| name | title | role |
+---------+-------+-----------+
| Charley | si110 | Instructor|
| Mea | si110 | Student |
| Hattie | si110 | Student |
| Keziah | si110 | Student |
| Rosa | si106 | Student |
| Mea | si106 | Instructor|
| Mairin | si106 | Student |
| Zendel | si106 | Student |
| Honie | si106 | Student |
| Rosa | si106 | Student |
+---------+-------+-----------+이와 같이 다대다 관계에서 연결 자체에 추가 정보를 포함시켜 데이터 모델을 확장할 수 있습니다. 이는 더 복잡한 데이터 관계를 효율적으로 모델링하고 관리하는 데 매우 유용합니다.
.
13. 데이터베이스 사용 요약
이번 장에서는 Python에서 데이터베이스를 사용하는 기본 개념을 다루었습니다. 데이터베이스를 사용하여 데이터를 저장하는 코드를 작성하는 것은 Python 딕셔너리나 평면 파일을 사용하는 것보다 더 복잡하지만, 애플리케이션이 데이터베이스의 기능을 진정으로 필요로 할 때는 데이터베이스 사용이 매우 유용할 수 있습니다.
13.1 데이터베이스가 유용한 상황
데이터베이스는 다음과 같은 상황에서 매우 유용합니다:
- 애플리케이션이 대규모 데이터 세트에서 많은 작은 무작위 업데이트를 수행해야 할 때
- 데이터가 너무 커서 딕셔너리에 저장할 수 없고 반복적으로 정보를 조회해야 할 때
- 장기 실행 프로세스를 중지하고 다시 시작해야 하며, 각 실행 사이에 데이터를 유지해야 할 때
13.2 단일 테이블과 다중 테이블
간단한 애플리케이션의 경우 단일 테이블로 구성된 간단한 데이터베이스를 구축할 수 있지만, 대부분의 문제는 여러 테이블과 테이블 간의 링크/관계를 필요로 합니다. 테이블 간에 링크를 만들기 시작할 때는 데이터베이스의 기능을 최대한 활용하기 위해 신중한 설계와 데이터베이스 정규화 규칙을 따르는 것이 중요합니다.
13.3 데이터 모델링의 중요성
데이터베이스를 사용하는 주요 동기는 대량의 데이터를 효율적으로 처리하기 위함입니다. 따라서 데이터를 효율적으로 모델링하여 프로그램이 가능한 한 빠르게 실행되도록 하는 것이 중요합니다. 이를 위해 다중 테이블과 정규화를 통한 관계형 데이터베이스 설계를 고려해야 합니다.
13.4 데이터베이스 사용의 장점
- 작은 무작위 업데이트: 데이터베이스는 많은 작은 업데이트를 효과적으로 처리할 수 있습니다.
- 큰 데이터 처리: 데이터베이스는 대량의 데이터를 처리하고 효율적으로 검색할 수 있습니다.
- 데이터 지속성: 장기 실행 프로세스에서 데이터를 지속적으로 저장하고, 프로세스 중단 후 다시 시작할 수 있습니다.
13.5 정리
데이터베이스는 복잡한 데이터 저장 및 검색 작업을 효율적으로 처리할 수 있도록 설계된 강력한 도구입니다. Python에서 데이터베이스를 사용하는 것은 초기 학습 곡선이 있지만, 애플리케이션의 데이터 요구 사항이 증가함에 따라 데이터베이스의 강력한 기능을 활용하는 것이 매우 유익할 수 있습니다. 데이터를 효율적으로 모델링하고 정규화 규칙을 따름으로써, 데이터베이스의 성능을 극대화하고 애플리케이션의 효율성을 높일 수 있습니다.