sqlite3를 이용하여 db 만들어보기

sqlite3를 사용하여 python에서 db 접근하기

 

sqlite3 패키지 임포트

import sqlite3

파이썬에서 sql문을 이용하여 dbms가 db에 접근

 

 

서버에 접근하기 -> connection을 이용하여

sqlite는 혼자 쓰는 파일 서버이기 때문에 파일 경로만 있으면 된다

conn = sqlite3.connect('파일 경로')
type(con) 	#sqlite3.Connection

 

cursor 객체 받기

cursor(dbms가 관리)가 작업을 한다

cur = conn.cursor()
type(cur) 	# sqlite3.Cursor

 

 

가장 쉬운 테이블을 만들어보겠습니다

 

 

cursor로 작업할 때 sql문 실행 방법

1. 동일 구문 한 번 실행

cur.execute()

2. 동일 구문 n번 실행

cur.executemany()

3. 여러 sql문 동시 실행(사용하지 않는 것을 권장)

cur.executescript()

 

 

스키마 확인

cur.executescript('''
    CREATE TABLE STUDENT(
        `학번` INTEGER PRIMARY KEY,
        `이름` TEXT,
        `교수번호` INTEGER
    );
    CREATE TABLE PROFESSOR(
        `교수번호` INTEGER PRIMARY KEY,
        `이름` TEXT
    );
''')

 

 

파이썬의 object를 db로 전달하는 법

튜플이나 리스트로 묶어서 전달

1. qmark 스타일

?(question mark)를 사용하고 뒤에 값을 넣어준다

# qmark style
# 한 번만 실행
student = (202401, '가나다' ,1)
cur.execute('''
    INSERT INTO STUDENT VALUES (?, ?, ?);
''', student)

# 여러 번 실행
cur.executemany('''
    INSERT INTO STUDENT VALUES (?, ?, ?);
''', ((202402, '라마바' ,2), (202403, '사아자' ,2)))

 

2. named 스타일

key와 value쌍을 사용

# named style
cur.executemany('''
    INSERT INTO STUDENT VALUES (:pk, :name, :fk);
''', ({'pk':202404, 'name':'차카타' ,'fk':1}, {'pk':202405, 'name':'파하' ,'fk':3}))

 

 

db의 변경 사항을 저장하고 싶을 때

connection을 저장해야 한다 -> conn.commit()

저장하지 않으면 connection이 close될 때 데이터가 저장되지 않는다.

conn.commit()

 

 

db의 데이터를 파이썬으로 전달하는 법

select 구문을 execute하고

커서가 select구문을 가리키고 있을 때 fetch 해야 python으로 데이터를 가져올 수 있다.

 

1. 다 가져오기. 리스트로 리턴
cur.fetchall
2. 몇 개 가져오기(파라미터 필요)
cur.fetchmany
3. 하나만 가져오기
cur.fetchone

 

 

# cur이 SELECT 구문을 가리키고 있음
cur.execute('SELECT * FROM STUDENT WHERE 조건;')

print(cur.fetchone()) # 한 행 리턴

print(cur.fetchone()) # 그 다음 행 리턴

print(cur.fetchall()) # 그 다음 행부터 모두 리턴

print(cur.fetchall()) # 빈 리스트([]) 리턴

 

데이터를 계속 사용한다면 fetch 시 변수에 저장하는 것이 좋다

# cur이 SELECT 구문을 가리키고 있음
cur.execute('SELECT * FROM STUDENT WHERE 조건;')

# 새로운 변수에 저장
data = cur.fetchall()

 

 

반복되는 sql문은 함수로

1. 테이블 조회를 함수로 만들어보자

def show_table(table_name):
    cur.execute('SELECT * FROM ' + table_name)
    print(cur.fetchall())

 

2. 데이터 삽입을 함수로 만들어보자

# 교수 테이블 INSERT
def add_professor(name):
    '''학번은 auto increment'''
    
    # PK auto increment를 위한 마지막 교수 번호를 얻어 온다.
    cur.execute('SELECT * FROM PROFESSOR ORDER BY 교수번호 DESC;')
    present = cur.fetchone()
    # 마지막 학번이 없으면 0으로 할당
    last_number = present[0] if present else 0
    
    # 데이터 삽입
    cur.execute('INSERT INTO PROFESSOR VALUES (?, ?)', (last_number+1, name))
    conn.commit()
    print(f'[{last_number+1}, {name}]이 PROFESSOR 테이블에 저장되었습니다')

 

# 학생 테이블 INSERT
def add_student(name, p_num=None):
    '''교수번호는 auto increment'''
    
    # PK auto increment를 위해 마지막 학번을 얻어 온다
    cur.execute('SELECT * FROM STUDENT ORDER BY 학번 DESC;')
    present = cur.fetchone()
    # 마지막 학번이 없으면 202400으로 할당
    last_number = present[0] if present else 202400
    
    # 담당 교수가 배정되지 않았다면 None으로 삽입
    if p_num == None:
        cur.execute('INSERT INTO STUDENT(학번, 이름) VALUES (?, ?)', (last_number+1, name))
    # 담당 교수가 있다면
    else:
        # 교수 번호가 존재하는지 확인
        cur.execute('SELECT 교수번호 FROM PROFESSOR')
        prof = cur.fetchall()
        # 없다면
        if (p_num,) not in prof:
            print("교수 번호가 존재하지 않습니다. 다시 시도해주세요")
            return
        # 교수 번호가 존재하면 삽입
        else:
            cur.execute('INSERT INTO STUDENT VALUES (?, ?, ?)', (last_number+1, name, p_num))
    conn.commit()
    print(f'[{last_number+1} {name} {p_num}]이 STUDENT 테이블에 저장되었습니다')

 

connection 해제

conn.close()

commit()을 했는지 확인하고 close()하기!

 

 

자세한 건 github에!

https://github.com/stonegyoung/sqlite_study/blob/main/sqlite3%20%ED%99%9C%EC%9A%A9%ED%95%98%EA%B8%B0.ipynb

sqlite_study/sqlite3 활용하기.ipynb at main · stonegyoung/sqlite_study