데이터 베이스 만들기

sqlite3

• sqlite3는 SQLite 데이터 베이스를 사용하는데 필요한 인터페이스 파이썬 표준 라이브러리
• 파이썬 설치 시 SQLite가 함께 자동으로 설치됨
• SQLite는 주로 개발용이나 소규모 프로젝트에서 사용하는 파일 기반의 가벼운 데이터베이스
• 개발 시에는 SQLite를 사용하여 빠르게 개발하고 실제 운영 시스템에서는 좀 더 규모 있는 데이터베이스를 사용하는 것이 일반적

 

데이터 베이스 생성

import sqlite3

# 버젼 확인
print(sqlite3.version)
# 2.6.0

print(sqlite3.sqlite_version)
# 3.40.1

# 데이터 베이스 만들기
conn = sqlite3.connect('test1.db')

# 커서 생성
c = conn.cursor()

# 쿼리문 작성
query = '''CREATE TABLE test (ID INTEGER PRIMARY KEY, PRODUCT_NAME TEXT, PRICE INTEGER)'''
c.execute(query)

# DB 연결 해제
conn.close()

 

 

데이터 불러오기, 조회하기

sqlite3 데이터 조회

• sqlite3에서 데이터 조회 방법에는 fetchone(), fetchmany(), fetchall() 3 가지 방법을 사용
• seletct 문을 사용한 조회 결과 범위에서 실제 가져오는 row수를 결정

 

메서드	내용
fetchone()	조회 결과에서 1개의 row를 가져옴
fetchmany(size=2)	조회 결과에서 지정한 size 만큼 row를 가져옴
fetchall()	조회 결과에서 모든 row를 가져옴

 

import sqlite3
import pandas as pd

# 연습용 DB 연결
conn = sqlite3.connect("chadwick.db")

# 커서 생성
c = conn.cursor()

# 전체 테이블 현황 조회
c.execute('''SELECT * FROM sqlite_master WHERE type="table"''')
# <sqlite3.Cursor at 0x1ed5a7d3840>

pd.DataFrame(c.fetchall())

 

데이터 조회

# 전체 데이터 조회
c.execute('''SELECT * FROM Parks''')

# 전체 로우 선택
pd.DataFrame(c.fetchall())

 

특정 데이터 조회

# 컬럼(키) 확인
cur = c.execute('''SELECT * FROM Parks''')
cur.description

# (('park.key', None, None, None, None, None, None),
# ('park.name', None, None, None, None, None, None),
# ('park.alias', None, None, None, None, None, None),
# ('city', None, None, None, None, None, None),
# ('state', None, None, None, None, None, None),
# ('country', None, None, None, None, None, None))

 

# 특정 row 만 가져오기
c.execute('''SELECT * FROM Parks WHERE state = ?''', ('NY',))
pd.DataFrame(c.fetchall())

# DB 연결 해제
conn.close()

 

c.execute('''SELECT * FROM Parks WHERE state = ?''', ('NY',))

 

•  ?는 SQL에서 변수처럼 동작하는 플레이스홀더.
• 두 번째 인자로 **튜플 ('NY',)**을 전달하여 ?에 'NY' 값을 대입.
• SELECT * FROM Parks WHERE state = 'NY'와 동일하게 실행됨.

 

 이 방법은 SQL 인젝션 공격을 방지, 쿼리 실행 속도를 최적화할 수 있음

 

• 만약 state 값이 "NY'; DROP TABLE Parks; --" 같은 악의적인 문자열이라면?
• Parks 테이블이 삭제될 수도 있음! 

 

 

데이터 삽입, 수정, 삭제

• SELECT
• INSERT
• UPDATE
• DELETE

 

INSERT 1. 열(키) 항목 순서를 정확히 알고 있는 경우

# 연습용 DB 연결
conn = sqlite3.connect("test1.db")

# 커서 생성
c = conn.cursor()

# 테이블 생성
query = '''CREATE TABLE test (ID INTEGER PRIMARY KEY, PRODUCT_NAME TEXT, PRICE INTEGER)'''
c.execute(query)

# 테이블 생성확인
c.execute('''SELECT * FROM sqlite_master WHERE type="table"''')
print(c.fetchall())
# [('table', 'test', 'test', 2, 'CREATE TABLE test (ID INTEGER PRIMARY KEY, PRODUCT_NAME TEXT, PRICE INTEGER)')]

# 데이터 삽입
c.execute("INSERT INTO test VALUES(1,'모자',150000)")
conn.commit()

# 데이터 삽입
c.execute("INSERT INTO test VALUES(2,'코트',200000)")
conn.commit()

 

 

INSERT 2. 열(키) 항목 순서를 정확히 모르는 경우

# 데이터 삽입
c.execute("INSERT INTO test(PRODUCT_NAME, PRICE, ID) VALUES(?,?,?)", ('티셔츠', 20000, 3))
conn.commit()

# 데이터 삽입
c.execute("INSERT INTO test(ID, PRICE, PRODUCT_NAME) VALUES(?,?,?)", (4, 55000, '블라우스'))
conn.commit()

 

 

 

INSERT 3. 여러 데이터를 한번에 삽입하고 싶은 경우

# 테이블 내용 삭제
c.execute("DELETE FROM test")
conn.commit()

# 추가할 상품 리스트
product_list = [[1, '모자', 15000],
                [2, '코트', 200000],
                [3, '티셔츠', 20000],
                [4, '블라우스', 55000],
                [5, '가디건', 45000],
                [6, '청바지', 50000],
                [7, '구두', 150000],
                [8, '가방', 170000]]
                
# 데이터 여러줄 삽입
c.executemany("INSERT INTO test(ID, PRODUCT_NAME, PRICE) VALUES(?,?,?)", product_list)
conn.commit()

 

 

UPDATE 1. 튜플 형태로 수정

c.execute("UPDATE test SET PRODUCT_NAME = ? WHERE ID = ?", ('슬랙스', 6))
conn.commit()

UPDATE 2. 딕셔너리 형태로 수정

c.execute("UPDATE test SET PRICE = :price WHERE ID = :id", {"price":55000, "id":6})
conn.commit()

 

UPDATE 3. %s 표시자 사용

c.execute("UPDATE test SET PRODUCT_NAME = '%s' WHERE ID = '%s'" % ('트랜치코트', 2))
conn.commit()

 

 

DELETE 1. 튜플 형태로 삭제

c.execute("DELETE FROM test WHERE ID =?", (8,))
conn.commit()

 

 

DELETE 2. 딕셔너리 형태로 삭제

c.execute("DELETE FROM test WHERE PRODUCT_NAME = :product_name", {'product_name':'슬랙스'})
conn.commit()

 

 

DELETE 3. 전체 삭제

c.execute("DELETE FROM test")
conn.commit()
# DB 연결 해제
conn.close()

 

 

데이터 백업

iterdump

• 데이터 베이스를 백업할 때 사용하는 모듈
• .sql파일 확장자로 테이블을 다시 복원할 수 있는 쿼리문을 저장

 

test

import sqlite3

# 연습용 DB 연결
conn = sqlite3.connect("test.db")
# 커서 생성
c = conn.cursor()

# 추가할 상품 리스트
product_list = [[1, '모자', 15000],
                [2, '코트', 200000],
                [3, '티셔츠', 20000],
                [4, '블라우스', 55000],
                [5, '가디건', 45000],
                [6, '청바지', 50000],
                [7, '구두', 150000],
                [8, '가방', 170000]]
                
# 데이터 여러줄 삽입
c.executemany("INSERT OR REPLACE INTO test(ID, PRODUCT_NAME, PRICE) VALUES(?,?,?)", product_list)
conn.commit()

# 데이터 베이스 백업 파일 생성
with conn:
    with open('backup.sql', 'w') as f:
        for line in conn.iterdump():
            f.write('%s\n' % line)
        print('Completed.')

 

# 테이블 삭제
c.execute("DROP TABLE test")
conn.commit()

# 백업 SQL 파일 로딩
with open('backup.sql', 'r') as sql_file:
    sql_script = sql_file.read()
    
# SQL 스크립트 실행
c.executescript(sql_script)
conn.commit()