파이썬 sqlite3 사용하기 -1 : 데이터타입, 기본 사용법

sqlite3 — SQLite 데이터베이스용 DB-API 2.0 인터페이스 — Python 3.8.4 문서

SQLite 란?

• SQLite는 별도의 서버 프로세스가 필요 없고 SQL 질의 언어의 비표준 변형을 사용하여 데이터베이스에 액세스할 수 있는 경량 디스크 기반 데이터베이스를 제공하는 C 라이브러리이다.

• SQLite를 사용하여 응용 프로그램을 프로토타입 한 다음 PostgreSQL 이나 Oracle과 같은 더 큰 데이터베이스로 코드를 이식할 수도 있다.

SQLite는 서버가 필요없어 작은 규모의 응용 프로그램 등에 적합합니다.

 

데이터 타입

사용할 수 있는 데이터 타입은 NULL, INTEGER, REAL, TEXT, BLOB 이 있습니다.

보통 text에 문자열을 저장하고,  binary 데이터는 blob에 저장할 수 있습니다.

 

SQLite type	Python type
NULL	None
INTEGER	int or long
REAL	float
TEXT	depends on text_factory, unicode by default
BLOB	buffer

 

테이블 생성/삭제

connet를 통해 DB 생성 및 연결을 하고, 그 객체를 이용해서 cursor 객체를 만듭니다.

또 그 cursor 객체를 이용해서 SQL 명령을 수행하게 됩니다.

 

컬럼 속성 정의

import sqlite3

conn = sqlite3.connect('blog.db')
cur = conn.cursor()

sql2 = '''CREATE TABLE color (id integer not null primary key autoincrement,
                         	  name text not null, 
                         	  rgb text)'''
cur.execute(sql2)
conn.commit()
conn.close()

 

PRIMARY KEY 가 설정된 컬럼에 중복된 값을 삽입하면 다음 에러가 발생합니다.

아래 코드에서는 중복되지 않은 첫 번째 데이터만 들어갔습니다.

import sqlite3

conn = sqlite3.connect('blog.db')
cur = conn.cursor()

cname = ['red', 'green' , 'blue']
for c in cname:
    sql = f'INSERT INTO color(id, name) VALUES (1, "{c}")'
    try:
        cur.execute(sql)
    except Exception as e:
        print(str(e))
        
conn.commit()

cur.execute('SELECT * from color')
print(cur.fetchall())

conn.close()

UNIQUE constraint failed: color.id
UNIQUE constraint failed: color.id
[(1, 'red', None)]

 

NOT NULL 이 설정된 컬럼에 빈 값으로 삽입하면 다음 에러가 발생.

import sqlite3

conn = sqlite3.connect('blog.db')
cur = conn.cursor()

sql = '''INSERT INTO color(rgb) VALUES ('(0, 0, 255)')'''
cur.execute(sql)
conn.commit()

conn.close()

sqlite3.IntegrityError: NOT NULL constraint failed: color.name

 

AUTOINCREMENT 는 설정된 컬럼은 생략해도 다음과 같이 카운트되어 들어갑니다.

import sqlite3

conn = sqlite3.connect('blog.db')
cur = conn.cursor()

cname = ['red', 'green' , 'blue']
for c in cname:
    sql = f'INSERT INTO color(name) VALUES ("{c}")'
    cur.execute(sql)
conn.commit()

cur.execute('SELECT * from color')
print(cur.fetchall())

[(1, 'red', None), (2, 'green', None), (3, 'blue', None)]

 

삭제는 다음과 같이 진행.

import sqlite3

conn = sqlite3.connect('blog.db')
cur = conn.cursor()

cur.execute('SELECT * from color')
print(cur.fetchall())

sql = 'DROP TABLE color'
r = cur.execute(sql)
print(r.rowcount)
conn.commit()

conn.close()

[(1, 'red', None), (2, 'red', None), (3, 'green', None), (4, 'blue', None)]
-1

 

레코드 삽입

다음 예제 코드처럼 다양하게 방법으로 SQL문을 사용할 수 있습니다.

import sqlite3

conn = sqlite3.connect('blog.db')
cur = conn.cursor()

sql = '''INSERT INTO color VALUES (1, 'red', '(255, 0, 0)')'''
cur.execute(sql)

sql2 = '''INSERT INTO color VALUES (?, ?, ?)'''
cur.execute(sql2, (2, 'green', '(0, 255, 0)'))

sql3 = '''INSERT INTO color(id, rgb) VALUES (?, ?)'''
cur.execute(sql3, (3, '(0, 0, 255)'))

conn.commit()

conn.close()

 

또한 executemany 를 사용하면 여러개의 레코드를 삽입할 수 있습니다.

import sqlite3

conn = sqlite3.connect('blog.db')
cur = conn.cursor()

#1
# sql = '''INSERT INTO color VALUES (1, 'red', '(255, 0, 0)')'''
# cur.execute(sql)

# sql2 = '''INSERT INTO color VALUES (?, ?, ?)'''
# cur.execute(sql2, (2, 'green', '(0, 255, 0)'))

# sql3 = '''INSERT INTO color(id, rgb) VALUES (?, ?)'''
# cur.execute(sql3, (3, '(0, 0, 255)'))

#2
sql4 = '''INSERT INTO color VALUES (?, ?, ?)'''
colors = [(1, 'red', '(255, 0, 0)'),
          (2, 'green', '(0, 255, 0)'),
          (3, 'blue', '(0, 0, 255)')
]
cur.executemany(sql4, colors)

conn.commit()

conn.close()

 

삽입 결과 확인은 rowcount 로 합니다.

import sqlite3

conn = sqlite3.connect('blog.db')
cur = conn.cursor()

sql = '''INSERT INTO color VALUES (1, 'red', '(255, 0, 0)')'''

# if cur.execute(sql).rowcount:
# 	 print('ok')
# else:
#	 print('no')

e = cur.execute(sql)
if e.rowcount:
    print(f'{e.rowcount} inserted')
else:
    print('No')

conn.commit()

conn.close()

1 inserted

 

레코드 조회

fetchone은 하나의 행, fetchall은 행 전체를 가져옵니다.

주의합니다. SQL문 안의 데이터를 지정할 때, ' 또는 "로 묶어야 합니다.

import sqlite3

conn = sqlite3.connect('blog.db')
cur = conn.cursor()

#1
sql = '''SELECT * from color'''
cur.execute(sql)
rows = cur.fetchall()
for row in rows:
    print(row)

#2
sql2 = '''SELECT * from color'''
cur.execute(sql2)
row = cur.fetchone()
print(row)

#3
sql3 = "SELECT * from color WHERE name='red'"
cur.execute(sql3)
rows = cur.fetchall()
for row in rows:
    print(row)

conn.close()

(1, 'red', '(255, 0, 0)')
(2, 'green', '(0, 255, 0)')
(3, None, '(0, 0, 255)')
(1, 'red', '(255, 0, 0)')
(1, 'red', '(255, 0, 0)')

 

레코드 삭제

삽입과 삭제는 commit을 하지 않으면 반영되지 않습니다.

rowcount를 이용하여 결과를 확인합니다.

import sqlite3

conn = sqlite3.connect('blog.db')
cur = conn.cursor()

sql = '''DELETE from color'''
e = cur.execute(sql)
print(f'{e.rowcount} deleted')

conn.commit()
conn.close()

3 deleted