[Python] sqlite3 모듈

python에서 기본적으로 제공되는 sqlite3 모듈에 대해 설명한다.

여기에 있는 모든 내용은 python 공식문서에서 가져온 것입니다. 

링크: https://docs.python.org/3/library/sqlite3.html#module-sqlite3

---

SQLite는 별도의 서버 프로세스가 필요 없고 db에 접근할 수 있는 경량 디스크 기반 데이터베이스를 제공하는 C 라이브러리이다. 일부 응용 프로그램은 내부 데이터 저장을 위해 SQLite를 사용할 수 있다. SQLite를 사용하여 응용 프로그램을 프로토타입 한 다음 PostgreSQL이나 Oracle과 같은 더 큰 데이터베이스로 코드를 옮길 수 있다.

 

관련 사이트: 
https://www.sqlite.org
SQLite 웹 페이지; 설명서는 지원되는 SQL 언어에 대한 문법과 사용 가능한 데이터형을 설명합니다.
https://www.w3schools.com/sql/
SQL 문법 학습을 위한 자습서, 레퍼런스 및 예제
PEP 249 - 데이터베이스 API 명세 2.0
Marc-André Lemburg가 작성한 PEP.

 

DB 만들기

전체 코드

import sqlite3
con = sqlite3.connect('tutorial.db')

cur = con.cursor()

cur.execute('CREATE TABLE movie(title, year, score)')

res = cur.execute("SELECT name FROM sqlite_master")
res.fetchone()

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

res = cur.execute("SELECT score FROM movie")
print(res.fetchall())

data = [
    ("Monty Python Live at the Hollywood Bowl", 1982, 7.9),
    ("Monty Python's The Meaning of Life", 1983, 7.5),
    ("Monty Python's Life of Brian", 1979, 8.0),
]
cur.executemany("INSERT INTO movie VALUES(?, ?, ?)", data)
con.commit()  # Remember to commit the transaction after executing INSERT.

for row in cur.execute("SELECT year, title FROM movie ORDER BY year"):
    print(row)

con.close()
new_con = sqlite3.connect("tutorial.db")
new_cur = new_con.cursor()
res = new_cur.execute("SELECT title, year FROM movie ORDER BY score DESC")
title, year = res.fetchone()
print(f'The highest scoring Monty Python movie is {title!r}, released in {year}')

---

db생성

첫번째로, 데이터베이스를 만들고 sqlite3 로 연결한다.

sqlite3.connect() 명령어를 사용해 db와 연동한다.

→ 이 명령어는 존재하면 가져오고, 존재하지 않는다면 새로 db를 만든다.

import sqlite3
con = sqlite3.connect("tutorial.db")

con 변수에 데이터가 반환된다.

SQL 문을 실행하고 SQL 질의에서 결과를 가져오기 위해서는 데이터베이스 커서를 사용해야 한다. 커서를 생성하려면 con.cursor()를 호출한다.

cur = con.cursor()

이제 우리는 데이터베이스 연결과 커서를 사용하여, 열 이름을 지정하여 데이터베이스를 생성할 수 있다.

테이블 생성

예를 들어 영화의 제목과, 출시일, 평점을 열로 지정하여 만들어 보겠다. 테이블 생성에 필요한 명령어는 cur.execute(…) 이다.

cur.execute("CREATE TABLE movie(title, year, score)")

새로운 테이블이 만들어졌는지 확인하기 위해 SQLite 내장 sqlite_master 테이블을 쿼리하는 것으로 확인할 수 있다. 이제 cur.execute(...)명령을 호출하여 쿼리를 실행하고 결과를 res 에 할당하고 res.fetchone() 를 호출하여 결과 행을 가져온다.

res = cur.execute("SELECT name FROM sqlite_master")
res.fetchone() -- 이 부분을 프린트 문 찍으면 ('movie',) 출력됨.

테이블이 생성되고, 테이블 이름이 튜플로 반환되는 모습을 확인할 수 있다. 만약 존재하지 않은 테이블(예를 들어 spam)을 불러온다면, res.fetchone()는 None값을 반환한다.

res = cur.execute("SELECT name FROM sqlite_master WHERE name='spam'")
res.fetchone() is None -- 이 부분을 프린트 문 찍으면 True 출력됨

sqlite에서도 확인해 보니

테이블이 잘 생성된 모습을 확인할 수 있다.

 

자료 삽입

이제 두 줄의 자료를 INSERT문을 사용하여 삽입해 보자. 다시 cur.execute(…) 명령어를 사용한다.

이때 위의 create table 명령어는 주석처리 한다.(전체코드)

cur.execute("""
    INSERT INTO movie VALUES
        ('Monty Python and the Holy Grail', 1975, 8.2),
        ('And Now for Something Completely Different', 1971, 7.5)
""")

 

여기서 INSERT 명령어는 트랜젝션을 연다는 것을 암시한다.

• 트랜잭션이란 — 데이터베이스의 상태를 변화시키기 위해 수행하는 작업의 단위를 의미한다. 데이터베이스의 상태를 변화시킨다는 것은 질의어(INSERT, SELECT, DELETE, UPDATE)를 사용하여 데이터베이에 접근하는 것을 의미한다.
• ▶️ 트랜잭션 관련 참고 링크: https://mommoo.tistory.com/62

con.commit() 을 호출하여 객체를 트랜잭션에 커밋한다.

con.commit()

우리는 데이터가 select 쿼리에 맞게 삽입되었다는 것을 select 문으로 확인할 수 있다.

이제는 익숙한 cur.execute(…)를 다시 사용해 res(result의 줄임인) 변수에 결과를 넣고, res.fetchall()을 사용하여 열들을 반환한다.

res = cur.execute("SELECT score FROM movie")
res.fetchall() -- 이 부분을 프린트문으로 찍어 보면 [(8.2,), (7.5,)] 이 출력된다.

결과는 두개의 튜플을 리스트로 담은 형식으로 반환된다. 각각의 값은 score(영화의 평점)의 값을 가지고 있다.

이제, 세개의 열들을 cur.executemany(…)를 사용하여 삽입해 보자.

data = [
    ("Monty Python Live at the Hollywood Bowl", 1982, 7.9),
    ("Monty Python's The Meaning of Life", 1983, 7.5),
    ("Monty Python's Life of Brian", 1979, 8.0),
]
cur.executemany("INSERT INTO movie VALUES(?, ?, ?)", data)
con.commit()  # Remember to commit the transaction after executing INSERT.

데이터를 쿼리에 바인딩할 때 ? 플레이스홀더를 사용한다. Python 값을 SQL 문에 바인딩하려면 항상 문자열 서식 대신 플레이스홀더를 사용하여 SQL 인젝션 공격을 방지해야 한다

**플레이스홀더: 빠져 있는 다른 것을 대신하는 기호나 텍스트의 일부.

**sql 인젝션 공격(sql 삽입 공격): 악의적인 사용자가 보안상의 취약점을 이용하여, 임의의 SQL 문을 주입하고 실행되게 하여 데이터베이스가 비정상적인 동작을 하도록 조작하는 행위(참조 링크: https://portswigger.net/web-security/sql-injection)

 

삽입된 데이터 확인하기

우리는 select 와 for문을 사용하여 추가된 새로운 열들을 확인할 수 있다.

for row in cur.execute("SELECT year, title FROM movie ORDER BY year"):
    print(row)

실행결과:

(1971, 'And Now for Something Completely Different')
(1975, 'Monty Python and the Holy Grail')
(1979, "Monty Python's Life of Brian")
(1982, 'Monty Python Live at the Hollywood Bowl')
(1983, "Monty Python's The Meaning of Life")

각각의 값 (year, title)은 튜플의 형태로 매치되어 반환된다.

 

데이터베이스 저장

마지막으로 데이터베이스가 디스크에 쓰여졌는지 확인하려면 con.close()를 호출하여 기존 연결을 닫고 새 연결을 열고 새 커서를 만든 다음 데이터베이스를 쿼리한다.

con.close()
new_con = sqlite3.connect("tutorial.db")
new_cur = new_con.cursor()
res = new_cur.execute("SELECT title, year FROM movie ORDER BY score DESC")
title, year = res.fetchone()
print(f'The highest scoring Monty Python movie is {title!r}, released in {year}')

-- 실행결과: The highest scoring Monty Python movie is 'Monty Python and the Holy Grail', released in 1975