[SQLite3] sqlite3 - python 연동 CRUD 실습 자료

1.파이썬 데이터베이스(SQLite)¶

1-1.테이블 생성¶

In [ ]:

import sqlite3
import datetime

In [ ]:

print('sqlite3 version : ', sqlite3.version)

sqlite3 version :  2.6.0

In [ ]:

# 데이터 추가 날짜 값 추출하기
now = datetime.datetime.now()
print('now : ', now)
nowDatetime = now.strftime('%Y-%m-%d %H:%M:%S')
print('nowDatetime : ', nowDatetime)

now :  2021-10-25 14:26:04.244614
nowDatetime :  2021-10-25 14:26:04

1-2.DB 연결¶

• ### sqlite3는 db연결시 filedb가 만들어짐(ex:database.db)

In [ ]:

# conn = sqlite3.connect("database.db")
conn = sqlite3.connect("sqldb.db")

# auto commit : 실행하면 db에 바로 반영
# conn = sqlite3.connect("database.db", isolation_level=None)

1-3 db cursor 식별자 생성¶

In [ ]:

cur = conn.cursor()
print('cursor type : ',type(cur))

cursor type :  <class 'sqlite3.Cursor'>

1-4 DB 테이블 생성¶

In [ ]:

cur.execute(
    """
    create table if not exists sqldb(
    id interger primary key,
    username text, 
    email text, 
    phone text,
    website text,
    regdate text)
    """
)

Out[ ]:

<sqlite3.Cursor at 0x7fa01bc89c70>

1-5 데이터 CRUD¶

• ### Create(insert),
• ### Read(select),
• ### Update(update),
• ### Delete(delete)

1-5-1. Create(insert)¶

In [ ]:

# 테이블 생성( Data Type : TEXT, NUMERIC, INTEGER, REAL, BLOB)
cur.execute(
    """
    INSERT INTO sqldb 
    VALUES(
        1,
        'Kim',
        'kim@cozlab.com',
        '010-1234-5678',
        'cozlab.com',
        ?
        )
    """,
    (nowDatetime,))
    # ? 부분에 날짜(nowDatetime,)를 뒤에 값을 튜플 형태로 집어 넣어준다.

Out[ ]:

<sqlite3.Cursor at 0x7fa01bc89c70>

In [ ]:

# 데이터 insert 튜플형식
cur.execute(
    """
    INSERT INTO sqldb(id, 
                      username, 
                      email, 
                      phone, 
                      website, 
                      regdate
                    )
    VALUES (?,?,?,?,?,?)
    """,
    (2,
    'Park',
    'park@naver.com',
    '010-3456-4567',
    'park.com',
    nowDatetime
    )
)

Out[ ]:

<sqlite3.Cursor at 0x7fa01bc89c70>

In [ ]:

# 데이터 insert many 형식(튜플, 리스트)
# 많은 양의 데이터를 한꺼번에 넣는 방법

userList = (
    (3,'Lee','lee@navercom','010-3333-3333','lee.com',nowDatetime),
    (4,'Cho','cho@navercom','010-4444-4444','cho.com',nowDatetime),
    (5,'Yue','yue@navercom','010-5555-5555','yue.com',nowDatetime),
    (6,'Sea','sea@navercom','010-6666-6666','sea.com',nowDatetime),
)

cur.executemany(
    """
    INSERT INTO sqldb(
                      id,
                      username,
                      email,
                      phone,
                      website,
                      regdate
                    )
    VALUES (?,?,?,?,?,?)
    """,
    userList
)

Out[ ]:

<sqlite3.Cursor at 0x7fa01bc89c70>

commit, rollback¶

conn = sqlite3.connect("sqldb.db")¶

• ### conn.commit() 명령어를 실행해야함.
• ### 취소하고 싶을 경우, conn.rollback() 바로 이전 실행 취소함. ## conn = sqlite3.connect("database.db", isolation_level = None)
• ### isolation_level = None 일 경우 자동(오토 커밋)

In [ ]:

conn.commit()

In [ ]:

# DB 접속을 더 이상 하지 않는다면, 접속 해제, 자원 반환하기
cur.close()

1-5-2. Read(select)¶

다양한 데이터 조회¶

• ### select
• ### where
• ### where 조건의 tuple, dict Mapping

In [ ]:

import sqlite3
import datetime

# 삽입 날짜 셋업
now = datetime.datetime.now()
print('now : ', now)
nowDatetime = now.strftime('%Y-%m-%d %H:%M:%S')
print('nowDatetime : ', nowDatetime)

# auto commit(그때 그때 DB에 반영),
# rollback(되돌림)
conn = sqlite3.connect("sqldb.db")
cur = conn.cursor() # cursor binding

now :  2021-10-25 15:08:17.416134
nowDatetime :  2021-10-25 15:08:17

In [ ]:

cur = conn.cursor()
cur.execute(
    """
    SELECT *
    FROM sqldb
    """
)

Out[ ]:

<sqlite3.Cursor at 0x7fa01bdb4ab0>

In [ ]:

# 커서 위치 변경
# 1개 로우 선택
print('one -> \n', cur.fetchone())

one -> 
 (1, 'Kim', 'kim@cozlab.com', '010-1234-5678', 'cozlab.com', '2021-10-25 14:26:04')

In [ ]:

print('one -> \n', cur.fetchone())

one -> 
 (2, 'Park', 'park@naver.com', '010-3456-4567', 'park.com', '2021-10-25 14:26:04')

In [ ]:

# 현재에서 3개 가져오기
print('Three -> \n',cur.fetchmany(size=3))

Three -> 
 [(3, 'Lee', 'lee@navercom', '010-3333-3333', 'lee.com', '2021-10-25 14:26:04'), (4, 'Cho', 'cho@navercom', '010-4444-4444', 'cho.com', '2021-10-25 14:26:04'), (5, 'Yue', 'yue@navercom', '010-5555-5555', 'yue.com', '2021-10-25 14:26:04')]

In [ ]:

# 현재 cur가 있는 위치 이후, 남은 것 모두
print('All -> \n',cur.fetchall())

All -> 
 [(6, 'Sea', 'sea@navercom', '010-6666-6666', 'sea.com', '2021-10-25 14:26:04')]

select ~ 순회(retriveve) 방법¶

DB 테이블을 돌며서 데이터를 읽어오는 것.¶

In [ ]:

cur.execute(
    """
    SELECT *
    FROM sqldb
    """
)

Out[ ]:

<sqlite3.Cursor at 0x7fa01bdb4ab0>

In [ ]:

# 순회 1:
rows = cur.fetchall()
for row in rows:
    print('retrivel ->', row)

retrivel -> (1, 'Kim', 'kim@cozlab.com', '010-1234-5678', 'cozlab.com', '2021-10-25 14:26:04')
retrivel -> (2, 'Park', 'park@naver.com', '010-3456-4567', 'park.com', '2021-10-25 14:26:04')
retrivel -> (3, 'Lee', 'lee@navercom', '010-3333-3333', 'lee.com', '2021-10-25 14:26:04')
retrivel -> (4, 'Cho', 'cho@navercom', '010-4444-4444', 'cho.com', '2021-10-25 14:26:04')
retrivel -> (5, 'Yue', 'yue@navercom', '010-5555-5555', 'yue.com', '2021-10-25 14:26:04')
retrivel -> (6, 'Sea', 'sea@navercom', '010-6666-6666', 'sea.com', '2021-10-25 14:26:04')

In [ ]:

# 순회2 : 커서가 제일 끝에 있기 때문에 실행을 하려면 select부터 다시 해야함.
for row in cur.fetchall():
    print('retrivel ->',row)

In [ ]:

# 순회3
for row in cur.execute("SELECT * FROM sqldb ORDER BY id DESC"):
    print('retrivel ->',row)

retrivel -> (6, 'Sea', 'sea@navercom', '010-6666-6666', 'sea.com', '2021-10-25 14:26:04')
retrivel -> (5, 'Yue', 'yue@navercom', '010-5555-5555', 'yue.com', '2021-10-25 14:26:04')
retrivel -> (4, 'Cho', 'cho@navercom', '010-4444-4444', 'cho.com', '2021-10-25 14:26:04')
retrivel -> (3, 'Lee', 'lee@navercom', '010-3333-3333', 'lee.com', '2021-10-25 14:26:04')
retrivel -> (2, 'Park', 'park@naver.com', '010-3456-4567', 'park.com', '2021-10-25 14:26:04')
retrivel -> (1, 'Kim', 'kim@cozlab.com', '010-1234-5678', 'cozlab.com', '2021-10-25 14:26:04')

select ~ where retriever( 튜플형, format문, dict형)¶

In [ ]:

# WHERE 조건1 튜플형
param1 = (3,)
cur.execute("SELECT * FROM sqldb WHERE id=?", param1)
print('param1',cur.fetchone()) # 1개
print('param1',cur.fetchall()) # 데이터 없음

param1 (3, 'Lee', 'lee@navercom', '010-3333-3333', 'lee.com', '2021-10-25 14:26:04')
param1 []

In [ ]:

# WHERE 조건2 데이터 타입의 FORMAT 문으로
param2 = 4
cur.execute("SELECT * FROM sqldb WHERE id='%s'" % param2) # %s %f %d
print('param2',cur.fetchone()) # 1개

param2 (4, 'Cho', 'cho@navercom', '010-4444-4444', 'cho.com', '2021-10-25 14:26:04')

In [ ]:

# WHERE 조건3 dict 형
cur.execute("SELECT * FROM sqldb WHERE id=:id",{"id":5}) # WHERE id=:id <= dict형 조건
print('param3', cur.fetchone()) # 1개
print('param3', cur.fetchall()) # 1개

param3 (5, 'Yue', 'yue@navercom', '010-5555-5555', 'yue.com', '2021-10-25 14:26:04')
param3 []

In [ ]:

# WHERE 조건4
param4 = (3,5)
cur.execute("SELECT * FROM sqldb WHERE id IN (?,?)", param4)
print('param4 : ', cur.fetchall())

param4 :  [(3, 'Lee', 'lee@navercom', '010-3333-3333', 'lee.com', '2021-10-25 14:26:04'), (5, 'Yue', 'yue@navercom', '010-5555-5555', 'yue.com', '2021-10-25 14:26:04')]

In [ ]:

# WHERE 조건5
cur.execute("SELECT * FROM sqldb WHERE id IN ('%d','%d')" % (3,4))
print('param5 : ', cur.fetchall())

param5 :  [(3, 'Lee', 'lee@navercom', '010-3333-3333', 'lee.com', '2021-10-25 14:26:04'), (4, 'Cho', 'cho@navercom', '010-4444-4444', 'cho.com', '2021-10-25 14:26:04')]

In [ ]:

# WHERE 조건6 WHERE OR
# WHERE id=:id < dict형 조건
cur.execute("SELECT * FROM sqldb WHERE id=:id1 OR id=:id2",{"id1":2,"id2":5})
print('param6',cur.fetchall()) # 1개

param6 [(2, 'Park', 'park@naver.com', '010-3456-4567', 'park.com', '2021-10-25 14:26:04'), (5, 'Yue', 'yue@navercom', '010-5555-5555', 'yue.com', '2021-10-25 14:26:04')]

Dump 출력¶

• ### dump : db를 백업받는 것, 다른 시스템으로 db를 재구성할때 사용.

In [ ]:

with conn: # db 연결, file로 저장
    with open('./dump.sql', 'w') as f:
        for line in conn.iterdump():
            f.write('%s\n' % line)
        print('Dump print complete')

Dump print complete

In [ ]:

# db 연결 종료
conn.close()

1-5-3. DB Update¶

• ### DB 테이블 내용을 수정하는 것.

In [ ]:

conn = sqlite3.connect("sqldb.db")
cur = conn.cursor()

In [ ]:

# kim -> joy를 바꾸기, 튜플
cur.execute("UPDATE sqldb SET username = ? WHERE id = ?", ('joy',1))

Out[ ]:

<sqlite3.Cursor at 0x7fa01bdc4730>

In [ ]:

# 2 park -> 2 joy로 바꾸기, dict
cur.execute("UPDATE sqldb SET username= :name WHERE id= :id",{'name':'good','id':2})

Out[ ]:

<sqlite3.Cursor at 0x7fa01bdc4730>

In [ ]:

conn.commit()

1-5-4 DB Delete¶

• ### DB 테이블 데이터 삭제하기

In [ ]:

import sqlite3
import datetime
now = datetime.datetime.now()
print('now : ', now)

nowDatetime = now.strftime('%Y-%m-%d %H:%M:%S')
print('nowDatetime : ', nowDatetime)

conn = sqlite3.connect("sqldb.db")
cur = conn.cursor()

now :  2021-10-25 16:19:18.756890
nowDatetime :  2021-10-25 16:19:18

In [ ]:

# 테이블데이터모두삭제
# #cur.execute DELETE FROM users
# 삭제 한것 카운트해서 반환함 : cur.execute('DELETE FROM usersdb').rowcount
print("sqldb db delete : ", cur.execute("DELETE FROM sqldb").rowcount)

sqldb db delete :  6

In [ ]:

# 데이터 insertmany형식튜플리스트
userList=(
    (1,'kim','kim@naver.com','010-3456-4567','kim.com',nowDatetime),
    (2,'Park','park@naver.com','010-3456-4567','park.com',nowDatetime),
    (3,'Lee','lee@naver.com','010-3333-3333','lee.com',nowDatetime),
    (4,'Cho','cho@naver.com','010-4444-4444','cho.com',nowDatetime),
    (5,'Yue','yue@naver.com','010-5555-5555','yue.com',nowDatetime),
    (6,'Sea','sea@naver.com','010-6666-6666','sea.com',nowDatetime),
)
cur.executemany("INSERT INTO sqldb(id,username,email,phone,website,regdate) VALUES (?,?,?,?,?,?)", 
userList)

Out[ ]:

<sqlite3.Cursor at 0x7fa01bd5cce0>

In [ ]:

# 삭제 : 튜플형으로
cur.execute("DELETE FROM sqldb WHERE id = ? ",(2,))

Out[ ]:

<sqlite3.Cursor at 0x7fa01bd5cce0>

In [ ]:

# 확인하기
for user in cur.execute("SELECT * FROM sqldb"):
    print(user)

(1, 'kim', 'kim@naver.com', '010-3456-4567', 'kim.com', '2021-10-25 16:19:18')
(3, 'Lee', 'lee@naver.com', '010-3333-3333', 'lee.com', '2021-10-25 16:19:18')
(4, 'Cho', 'cho@naver.com', '010-4444-4444', 'cho.com', '2021-10-25 16:19:18')
(5, 'Yue', 'yue@naver.com', '010-5555-5555', 'yue.com', '2021-10-25 16:19:18')
(6, 'Sea', 'sea@naver.com', '010-6666-6666', 'sea.com', '2021-10-25 16:19:18')

In [ ]:

# 삭제 : dict 형으로
cur.execute("DELETE FROM sqldb WHERE id =:id",{"id":4})

Out[ ]:

<sqlite3.Cursor at 0x7fa01bd5cce0>

In [ ]:

# 확인하기
for user in cur.execute("SELECT * FROM sqldb"):
    print(user)

(1, 'kim', 'kim@naver.com', '010-3456-4567', 'kim.com', '2021-10-25 16:19:18')
(3, 'Lee', 'lee@naver.com', '010-3333-3333', 'lee.com', '2021-10-25 16:19:18')
(5, 'Yue', 'yue@naver.com', '010-5555-5555', 'yue.com', '2021-10-25 16:19:18')
(6, 'Sea', 'sea@naver.com', '010-6666-6666', 'sea.com', '2021-10-25 16:19:18')

In [ ]:

# 삭제 : 스트링 format형으로
cur.execute("DELETE FROM sqldb WHERE id = %d" % 1)

Out[ ]:

<sqlite3.Cursor at 0x7fa01bd5cce0>

In [ ]:

# 확인하기
for user in cur.execute("SELECT * FROM sqldb"):
    print(user)

(3, 'Lee', 'lee@naver.com', '010-3333-3333', 'lee.com', '2021-10-25 16:19:18')
(5, 'Yue', 'yue@naver.com', '010-5555-5555', 'yue.com', '2021-10-25 16:19:18')
(6, 'Sea', 'sea@naver.com', '010-6666-6666', 'sea.com', '2021-10-25 16:19:18')

In [ ]:

# 삭제 : WHERE 조건으로
cur.execute("DELETE FROM sqldb WHERE id=:id AND username=:name", {"id":3,"name":"Lee"})

Out[ ]:

<sqlite3.Cursor at 0x7fa01bd5cce0>

In [ ]:

# 확인하기
for user in cur.execute("SELECT * FROM sqldb"):
    print(user)

(5, 'Yue', 'yue@naver.com', '010-5555-5555', 'yue.com', '2021-10-25 16:19:18')
(6, 'Sea', 'sea@naver.com', '010-6666-6666', 'sea.com', '2021-10-25 16:19:18')

In [ ]:

# 데이터 전체 삭제 : 삭제된 행 카운트 출력
print("sqldb db deleted : ", conn.execute("DELETE FROM sqldb").rowcount," rows")

sqldb db deleted :  2  rows

In [ ]:

conn.commit()
conn.close()

DB 사용 권장 이유¶

1. ### 최신 데이터를 통합관리를 할 수 있기 때문
2. ### 요즘은 데이터가 자산인 시대
3. ### 새로운 서비스 창출 할 수 있음.
4. ### 데이터는 4차 산업혁명 시대에 원유와 같음.