4. 데이터베이스 응용

데이터베이스

데이터베이스 (Database) 소개

• (관계형) 데이터베이스

• DBMS (DataBase Management System) 이라고도 함

• 대용량의 데이터를 매우 효율적으로 처리하고 저장하는 기술

• SQLite, MYSQL, 오라클 등이 있음

• 데이터 베이스 용어
  필드, 열: 가장 작은 단위의 의미있는 데이터 표현
  레코드, 행: 서로 관련있는 필드들의 집합
  테이블:레코드들의 집합으로 개체와 관계가 모두 테이블로 표현
  데이터베이스: 서로 유기적인 관계가 있는 테이블의 집합체

데이터베이스 구성

• 데이터 베이스의 계층
  데이터베이스 > 테이블 > 레코드 > 데이터
• 테이블
  열과 행으로 구성
  주 키(primary key): 각 레코드를 구분하는 필드

SQLite 데이터베이스

SQL

• Structured Query Language

• 데이터베이스 표준 질의어

• ANSI 와 ISO 표준

• SQL 기본 명령어
  테이블 생성, create
  데이터 입력, insert
  데이터 조회, select
  데이터 삭제, delete
  데이터 수정, update

SQLite 데이터베이스

• 2000년 Richard Hipp 이 개발한 데이터베이스
• 경량 데이터베이스 (400KB 미만)
• 서버가 아닌 오프라인에서 응용 프로그램과 연동하는 임베디드 데이터베이스
• 안드로이드, 아이폰, 심비안 등의 모바일 환경에서 많이 사용
• API는 단순히 라이브러리를 호출
• 데이터를 저장하는 데 하나의 파일만을 사용하는 것이 특징
• 데이터베이스 당 1개의 파일
• www.sqlite.org

SQL 문

SQLite3

• 파이썬에는 SQLite3 모듈이 기본 설치
• 아나콘다에는 이미 설치되어 있음
• 윈도우 명령행 프로그램 (command-line program, CLP) 다운로드

데이터베이스 실전

데이터 구성 조사

• c:> sqlite3 surfersDB.db <- surfersDB.db 실행
• sqlite> .database <- 데이터베이스 이름과 파일 조회
• sqlite> .tables <- 테이블 목록
• sqlite> .schema surfers <- 테이블 생성에 대한 SQL 기술

데이터 조회 (1)

• sqlite> select * from surfers; <- 테이블의 모든 행 조회
• sqlite> .mode column <- 열 형식 출력
• sqlite> .headers on <- 열의 헤더 표시
• sqlite> select * from surfers;

데이터 조회 (2)

• 데이터 조회 (검색) SQL 문, SELECT
  SELECT column1, column2, ... column FROM table_name [WHERE condition];

행

행 삽입

• 행 삽입 SQL 문, INSERT
  INSERT INTO TABLE_NAME (column1, column2, column3, ... , columnN) VALUES (value1, value2, value3, ...valueN);

행 수정

• 행 수정 SQL 문, UPDATE
  UPDATE table_name SET column1 = value1, column2 = value2..., columnN = valueN WHERE conditon;

행 삭제

• 행 삭제 SQL 문, DELTE
  DELETE FROM table_name WHERE condition;

테이블

테이블 생성 (1)

• 테이블 생성 SQL 문, CREATE
  CREATE TABLE table_name(
  column1 datatype PRIMARY KEY,
  column2 datatype,
  column3 datatype,
  ....
  columnN datatype, );

• 열에 주 키(primary key)를 지정할 수 있음

• 주 키는 각 행이 서로 다른 값 (unique value)을 가져야 함

테이블 생성 (2)

• meminfo 테이블 생성 예
• sqlite create table meminfo (no integer primary key, name text, score real, country text, gender text); <- 지정된 필드의 테이블 생성
• sqlite> . tables <- 테이블 목록
• sqlite> . schema meminfo <- 테이블에 대한 SQL 정의

데이터 내보내기

• 데이터베이스 테이블의 데이터를 내보내기
• CSV (comma separated values) 파일 형식으로 내보내기
• 이전에 설정된 .mode .headers 를 변경
  현재 설정은 .show 명령으로 확인
• mode csv명령으로 csv 출력 설정
• .headers off 명령으로 헤더 제거
• .ouput 명령으로 저장 파일 지정 후 데이터 조회

SQLite 브라우저

데이터베이스, 테이블 생성

• SQLite 브라우저를 사용하여 데이터베이스, 테이블 생성

• 데이터베이스, SoonSurf.db

• 테이블, surfinfo
  name text
  score real
  gender text
  country text

• SQLite 브라우저 실행

• 새 데이터베이스(New Database) -> 필드 추가 (Add field)

• 열 이름과 타입 지정

파이썬과 데이터베이스 연결

파이썬과 SQLite3 연결

• sqlite3 모듈을 사용하여 파이썬과 SQLite3 데이터베이스 연동 프로그램 작성

• sqlite3.connect (filename)

filename: 데이터베이스 파일
데이터베이스 파일이 없으면 새로운 파일을 생성
연결 오픈이 성공하면 연결객체 (connection object)를 리턴

• connection.close()
  데이터베이스 연결 종료

• connection.commit()
  데이터베이스로 내보내기 (commit)
  이를 실행하지 않으면 수정된 내용이 데이터베이스에 반영되지 않음

import sqlite3
# 데이터베이스 연결
conn = sqlite3.connect('member.db')
print("데이터베이스 연결 성공")

이런식으로 연결이 됐는지 확인할 수 있음

SQL 실행

• SQL문을 실행하기 위해서는 연결 객체를 사용하여 커서 객체 (cursor object)를 생성

• 커서 객체 생성

• connection.cursor()
  커서 객체를 리턴

• SQL문 실행

• cursor.execute (sql)
  sql: SQL문

회원 가입 데이터베이스 예

데이터베이스 이름, 테이블 이름, 필드 (계정 이름, 패스워드, 이름, 가입시간) 을 기본적으로 설정해야 함

# 커서 객체 생성
cursor = conn.cursor()
# 테이블 생성 SQL 실행
cursor.execute('''
create table meminfo (id text primary key, passwd text, name text, time text);
''')
print("테이블 생성 성공");

이런식으로 테이블 생성을 할 수 있음

SQL 실행 - 데이터 조회 예

import sqlite3
conn = sqlite3.connect('member.db') # 데이터베이스 연결
cursor = conn.cursor() # 커서 객체 생성

# 데이터 조회 SQL문 실행
cursor.execute("select * from meminfo")
rows = cursor.fetchall() # 모든 행(레코드) 가져오기

# 각 행의 열의 값들을 출력
for row in rows:
  print("ID: %s, password: %s, name: %s, time: %s" % (row[0], row[1], row[2], row[3]))
conn.close()

SQL 실행 - 열 이름으로 조회

import sqlite3
conn = sqlite3.connect('member.db') # 데이터베이스 연결

# 연결 객체의 row_factory 속성을 sqlite3.Row 클래스로 지정
conn.row_factory = sqlite3.Row
cursor = conn.cursor() # 커서 객체 생성
cursor.execute('select * from meminfo') # 데이터 조회 SQL문 실행
rows = cursor.fetchall() # 모든 행(레코드) 가져오기

# 각 행의 열의 값들을 출력
for row in rows:
  print("ID: %s, password: %s, name: %s, time: %s" % (row['id'], row['passwd'], 
  row['name'], row['time']))
conn.close()