SQLite 학습 가이드

데이터베이스 생성

SQLite는 파일 기반 데이터베이스로, 별도의 서버 프로세스 없이 파일로 직접 데이터베이스를 생성하고 관리할 수 있습니다.

1.1 SQLite 명령줄에서 데이터베이스 생성

# SQLite 명령줄 도구 실행
sqlite3 my_database.db

# 이제 SQLite 프롬프트 내에 있습니다
sqlite> .databases
# 현재 연결된 데이터베이스 목록을 표시합니다

1.2 데이터베이스 설정 및 메타 명령어

-- SQLite 프롬프트 내에서 실행합니다
.mode column  -- 열 형식으로 출력 설정
.headers on   -- 열 헤더 표시
.tables       -- 데이터베이스의 모든 테이블 목록 표시

1.3 테이블 생성

CREATE TABLE students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    grade REAL,
    enrollment_date TEXT
);

 

기본 SQL 쿼리

SQLite는 표준 SQL 문법을 대부분 지원합니다. 다음은 기본적인 SQL 쿼리들입니다.

2.1 데이터 삽입 (INSERT)

-- 단일 레코드 삽입
INSERT INTO students (name, age, grade, enrollment_date) 
VALUES ('김철수', 20, 3.5, '2023-03-01');

-- 여러 레코드 한 번에 삽입
INSERT INTO students (name, age, grade, enrollment_date) 
VALUES 
    ('이영희', 19, 4.2, '2023-03-01'),
    ('박민수', 21, 3.8, '2023-03-02'),
    ('정지훈', 20, 4.0, '2023-03-01');

2.2 데이터 조회 (SELECT)

-- 모든 학생 정보 조회
SELECT * FROM students;

-- 특정 열만 조회
SELECT name, age FROM students;

-- 조건부 조회 (WHERE 절 사용)
SELECT * FROM students WHERE age > 19;

-- 정렬 (ORDER BY 절 사용)
SELECT * FROM students ORDER BY grade DESC;

-- 특정 개수만 조회 (LIMIT 절 사용)
SELECT * FROM students LIMIT 2;

-- GROUP BY 및 집계 함수 사용
SELECT age, COUNT(*) as count, AVG(grade) as avg_grade 
FROM students 
GROUP BY age;

2.3 데이터 업데이트 (UPDATE)

-- 특정 학생의 성적 업데이트
UPDATE students SET grade = 4.5 WHERE name = '이영희';

-- 여러 필드 동시 업데이트
UPDATE students 
SET grade = 4.0, age = 22 
WHERE id = 3;

2.4 데이터 삭제 (DELETE)

-- 특정 조건에 맞는 레코드 삭제
DELETE FROM students WHERE grade < 3.0;

-- 모든 레코드 삭제 (테이블 구조는 유지)
DELETE FROM students;

2.5 테이블 구조 변경 (ALTER TABLE)

-- 새 열 추가
ALTER TABLE students ADD COLUMN address TEXT;

-- 테이블 이름 변경
ALTER TABLE students RENAME TO university_students;

2.6 JOIN 연산 (테이블 연결)

-- 학생과 과목 테이블을 연결하는 예시
CREATE TABLE courses (
    course_id INTEGER PRIMARY KEY AUTOINCREMENT,
    course_name TEXT NOT NULL,
    professor TEXT
);

CREATE TABLE enrollments (
    student_id INTEGER,
    course_id INTEGER,
    semester TEXT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

-- JOIN 사용 예시
SELECT s.name, c.course_name, e.semester
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.course_id
WHERE s.age > 19;

 

Python과 SQLite 연동

Python은 sqlite3 모듈을 통해 SQLite 데이터베이스와 쉽게 연동할 수 있습니다.

3.1 데이터베이스 연결 및 커서 생성

import sqlite3

# 데이터베이스 연결 (파일이 없으면 새로 생성)
conn = sqlite3.connect('my_database.db')

# 커서 객체 생성
cursor = conn.cursor()

# 작업 후 반드시 연결을 닫아주어야 합니다
# conn.close()

3.2 테이블 생성 및 데이터 삽입

# 테이블 생성
cursor.execute('''
CREATE TABLE IF NOT EXISTS employees (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    department TEXT,
    salary REAL,
    hire_date TEXT
)
''')

# 단일 레코드 삽입
cursor.execute('''
INSERT INTO employees (name, department, salary, hire_date)
VALUES (?, ?, ?, ?)
''', ('홍길동', '개발팀', 5000.0, '2022-01-15'))

# 여러 레코드 삽입
employees_data = [
    ('김영수', '마케팅', 4500.0, '2022-02-20'),
    ('이지은', '인사팀', 4800.0, '2022-03-10'),
    ('박준호', '개발팀', 5200.0, '2022-01-05')
]

cursor.executemany('''
INSERT INTO employees (name, department, salary, hire_date)
VALUES (?, ?, ?, ?)
''', employees_data)

# 변경사항 커밋 (중요!)
conn.commit()

3.3 데이터 조회

# 모든 직원 정보 조회
cursor.execute('SELECT * FROM employees')
all_employees = cursor.fetchall()

for employee in all_employees:
    print(employee)  # 튜플 형태로 출력

# 특정 조건으로 조회
cursor.execute('SELECT name, salary FROM employees WHERE department = ?', ('개발팀',))
dev_team = cursor.fetchall()

print("\n개발팀 직원:")
for employee in dev_team:
    print(f"이름: {employee[0]}, 급여: {employee[1]}")

# 단일 레코드 조회
cursor.execute('SELECT * FROM employees WHERE id = ?', (1,))
first_employee = cursor.fetchone()
print("\n첫 번째 직원:", first_employee)

3.4 데이터 업데이트 및 삭제

# 급여 인상
cursor.execute('''
UPDATE employees 
SET salary = salary * 1.1 
WHERE department = ?
''', ('개발팀',))

# 특정 직원 삭제
cursor.execute('DELETE FROM employees WHERE name = ?', ('이지은',))

# 변경사항 커밋
conn.commit()

3.5 트랜잭션 관리

try:
    # 트랜잭션 시작
    cursor.execute('UPDATE employees SET salary = salary + 1000 WHERE id = 1')
    cursor.execute('UPDATE employees SET department = "경영지원팀" WHERE id = 2')
    
    # 의도적 오류 발생 (테스트용)
    # cursor.execute('UPDATE non_existent_table SET value = 0')
    
    # 모든 작업이 성공하면 커밋
    conn.commit()
    print("트랜잭션 성공")
    
except sqlite3.Error as e:
    # 오류 발생 시 롤백
    conn.rollback()
    print(f"트랜잭션 실패: {e}")

3.6 Pandas와 함께 사용

import pandas as pd

# 쿼리 결과를 DataFrame으로 변환
df = pd.read_sql_query("SELECT * FROM employees", conn)
print("\nPandas DataFrame:")
print(df)

# DataFrame을 SQLite 테이블로 저장
new_employees = pd.DataFrame({
    'name': ['최동철', '강민지', '서지수'],
    'department': ['영업팀', '영업팀', '개발팀'],
    'salary': [4300.0, 4400.0, 5100.0],
    'hire_date': ['2022-04-01', '2022-04-15', '2022-04-10']
})

new_employees.to_sql('new_employees', conn, if_exists='replace', index=False)

3.7 프로그램 종료 시 연결 닫기

# 항상 연결을 닫아주어야 합니다
conn.close()

3.8 Context Manager 사용 (권장 방식)

import sqlite3

def get_employee_info(employee_id):
    with sqlite3.connect('my_database.db') as conn:
        cursor = conn.cursor()
        cursor.execute('SELECT * FROM employees WHERE id = ?', (employee_id,))
        return cursor.fetchone()

# 함수 호출
employee = get_employee_info(1)
print("직원 정보:", employee)

이제 SQLite의 기본을 익히셨습니다. 실습을 통해 더 많은 기능을 탐색해보세요!