SQLite3 사용법 [Python]

import sqlite3

#DB생성
conn = sqlite3.connect("test.db", isolation_level=None)

#커서 획득
c=conn.cursor( )
c.execute("CREATE TABLE IF NOT EXISTS table1 (id integer PRIMARY KEY, name text, birthday text);")

1. INSERT 구문

# 데이터 삽입 방법 1 (필드명과 순서 정확히 알고 있을 때)
 c.execute("INSERT INTO table1 VALUES(1, 'KIM','1994-02-05');")

#데이터 삽입 방법 2 (튜플이나 리스트로 넣어줄 때)
c.execute("INSERT INTO table1(id, name, birthday) VALUES(?, ?, ?)",(2, 'LAUL','2018-02-02')) 
tuple=((3, 'HAN', '1993-01-15'), (4, 'LEE','1993-10-05'), (5, 'CHO','1991-06-01'))
c.executemany("INSERT INTO table1(id, name, birthday) VALUES(?, ?, ?);", tuple)

ID	NAME	BIRTHDAY
1	KIM	1994-02-05
2	LAUL	2018-02-02
3	HAN	1993-01-15
4	LEE	1993-10-05
5	CHO	1991-06-01

위와 같은 테이블로 삽입

2.  SELECT 구문

c.execute("SELECT * FROM table1")
print(c.fetchone())
print(c.fetchone())
print(c.fetchall())

< 결과값 > 
(1, 'KIM', '1994-02-05')
(2, 'LAUL', '2018-02-02')
[(3, 'HAN', '1993-01-15'), (4, 'LEE', '1993-10-05'), (5, 'CHO', '1991-06-01')]

c.fetchone() : 한 줄씩 출력 한번 더 사용하면 커서가 이동하기 때문에 다음 row를 출력한다. 

c.fetchall() : 커서로부터 전체 row들을 출력한다. 만약 앞에 실행 하고 전체를 가져오려면 아래와 같이 다시 전체를 읽어오고 해야한다. 

c.execute("SELECT * FROM table1")
print(c.fetchone())
print(c.fetchone())
c.execute("SELECT * FROM table1")
print(c.fetchall())

< 결과값 >
(1, 'KIM', '1994-02-05')
(2, 'LAUL', '2018-02-02')
[(1, 'KIM', '1994-02-05'), (2, 'LAUL', '2018-02-02'), (3, 'HAN', '1993-01-15'), (4, 'LEE', '1993-10-05'), (5, 'CHO', '1991-06-01')]

 

3. WHERE 구문

원하는 데이터만 찾아서 가져올 수 있는 구문이다. SQL에서 WHERE문을 사용 해야한다.

# 방법1
param1 = (1, )
c.execute("SELECT * FROM table1 WHERE id =?', param1)
print('param1', c.fetchone())
print('param1', c.fetchall())
# 방법2
param2 = 1c.execute("SELECT * FROM table1 WHERE id='%s'" % param2)  # %s %d %f
print('param2', c.fetchone())
print('param2', c.fetchall())
# 방법 3
c.execute("SELECT * FROM table1 WHERE id=:Id", {"Id": 1})
print('param3', c.fetchone())
print('param3', c.fetchall())
# 방법 4
param4 = (1, 4) 
c.execute('SELECT * FROM table1 WHERE id IN(?,?)', param4)
print('param4', c.fetchall())
# 방법 5
c.execute("SELECT * FROM table1 WHERE id In('%d','%d')" % (1, 4))
print('param5', c.fetchall())
# 방법 6
c.execute("SELECT * FROM table1 WHERE id=:id1 OR id=:id2", {"id1": 1, "id2": 4})
print('param6', c.fetchall())

< 결과값 >
param1 (1, 'KIM', '1994-02-05')
param1 []
param2 (1, 'KIM', '1994-02-05')
param2 []
param3 (1, 'KIM', '1994-02-05')
param3 []
param4 [(1, 'KIM', '1994-02-05'), (4, 'LEE', '1993-10-05')]
param5 [(1, 'KIM', '1994-02-05'), (4, 'LEE', '1993-10-05')]
param6 [(1, 'KIM', '1994-02-05'), (4, 'LEE', '1993-10-05')]

4. DELETE 구문

특정데이터를 지울려면 DELETE와 WHERE 절을 합치면 된다.

# 방법 1
c.execute("DELETE FROM table1 WHERE id=?", (1,))

# 방법 2
c.execute("DELETE FROM table1 WHERE id=:id", {'id': 3})

# 방법 3
c.execute("DELETE FROM table1 WHERE id='%s'" % 5)

# 확인
for row in c.execute('SELECT * FROM table1'):
print(row)

< 결과값 >
(2, 'LAUL', '2018-02-02')
(4, 'LEE', '1993-10-05')

만약 전체를 지우려면

# 방법 1
conn.execute("DELETE FROM table1")

# 방법 2
print(conn.execute("DELETE FROM table1").rowcount)

#결과값 : 2 --> 위에서 3개 지워서 나머지 2개 지워짐 

rowcount를 붙여주면 지운 행 개수를 돌려준다.

 

5. DB Browser for SQLite로 확인하기

5. DB백업하기 (dump)

데이터 베이스는 항상 dump를 통해 백업을 해놓는게 중요하다. 그래야 나중에 다른 pc에서도 재구성할 수 있다.

with conn:
       with open('dump.sql', 'w') as f:
            for line in conn.iterdump():
                f.write('%s\n' % line)
            print('Completed.')

이렇게 하고 DB Brewser for SQLite확인하면 아래와 같이 나온다.