sqlite3 -DB의 가장 기초적인 단계

SQLite3 - DB의 가장 기초적인 단계¶

이항의 주가데이터를 매일 수집하기 위한 프로젝트를 위해 세번째 블로깅을 한다.

우선, SQLite3을 알아보려한다.

DB의 가장 기초적인 단계를 시작하려고 한다.

SQLite는 다른 여러종류의 DB에 비해 가볍고 설치도 쉽고 무료이기 때문에 접근성도 좋고 DB의개념을 알아가는데 도움이 될 수 있다.별도의 서버 프로세스가 필요 없다. 또한, SQL 질의 언어의 비표준 변형을 사용하여 데이터베이스에 엑세스할 수 있는 경량 디스크 기반 데이터베이스를 제공하는 C라이브러리이다. 데이터 베이스는 .db 확장자를 가지는 단일 파일로 저장한다.

SQLite는 무엇인지, 위키백과에서는 아래와 같이 설명해준다.

SQLite는 MySQL나 PostgreSQL와 같은 데이터베이스 관리 시스템이지만, 서버가 아니라 응용 프로그램에 넣어 사용하는 비교적 가벼운 데이터베이스이다.

일반적인 RDBMS에 비해 대규모 작업에는 적합하지 않지만, 중소 규모라면 속도에 손색이 없다. 또 API는 단순히 라이브러리를 호출하는 것만 있으며, 데이터를 저장하는 데 하나의 파일만을 사용하는 것이 특징이다. 버전 3.3.8에서는 풀텍스트 검색 기능을 가진 FTS1 모듈이 지원된다. 컬럼을 삭제하거나 변경하는 것 등이 제한된다.

구글 안드로이드 운영 체제에 기본 탑재된 데이터베이스이기도 하다.

• 출처:위키백과

요약 :

• 데이터 베이스는 .db 확장자를 가지는 단일 파일로 저장 됨.

• SQLite란 핸드폰 수준에서 사용할 수 있는 간단한 데이터베이스다.

• 이 DB에 데이터를 저장하고 조회하는 등의 작업을 하려면 SQL문(쿼리문)이라는 문법 익히고,

• SQLiteDatabase라는 클래스의 객체를 얻어야 사용이 가능하다.

예제로 시작하기¶

사용 예제¶

1) employee.db¶

• 먼저 sqlite3 불러오기

In [2]:

import sqlite3

In [3]:

sqlite3.connect("employee.db")

Out[3]:

<sqlite3.Connection at 0x7fb00c74cab0>

In [4]:

# 해당 empolyee.db로 연결한다.
conn = sqlite3.connect("employee.db") 


# conn.cursor를 통해 Cursor 하나를 생성한다.
cur = conn.cursor() 


# conn.executemany를 통해 데이터를 insert 한다.
cur.executemany( 'INSERT INTO employee_data VALUES (?, ?, ?, ?, ?)', 
                [(1001, 'Donghyun', 'SOMJANG', 'Development', '2020-04-01 00:00:00.000'), 
                 (2001, 'Sol', 'Fairy', 'Marketing', '2020-04-01 00:00:00.000'), 
                 (2002, 'Jiyoung', 'Magician', 'Marketing', '2020-04-01 00:00:00.000'), 
                 (1002, 'Hyeona', 'Theif', 'Development', '2020-04-01 00:00:00.000'), 
                 (1003, 'Soyoung', 'Chief', 'Development', '2020-04-01 00:00:00.000') ] )

# conn.close를 통해 연결을 해제한다.
conn.commit()
conn.close()

데이터 가져와서 출력하기¶

In [5]:

# 만약 employee.db가 없을 경우 새로 생성하면서 연결하게되고 employee.db가 이미 있을 경우 해당 db로 연결하게 된다.
conn = sqlite3.connect("employee.db")

# cursor 하나를 생성한다.
cur = conn.cursor()

# cur.execute를 통해 SELECT 쿼리를 실행한다.
cur.execute("SELECT * FROM employee_data")

# cur.execute를 통해 가져온 모든 결과의 행을 가져와 리스트로 가져온다.
rows = cur.fetchall()

# 가져온 데이터를 반복문을 통해 하나의 row씩 출력한다.
for row in rows: 
    print(row)
    
# 연결을 해제한다.
conn.close()

(1001, 'Donghyun', 'SOMJANG', 'Development', '2020-04-01 00:00:00.000')
(2001, 'Sol', 'Fairy', 'Marketing', '2020-04-01 00:00:00.000')
(2002, 'Jiyoung', 'Magician', 'Marketing', '2020-04-01 00:00:00.000')
(1002, 'Hyeona', 'Theif', 'Development', '2020-04-01 00:00:00.000')
(1003, 'Soyoung', 'Chief', 'Development', '2020-04-01 00:00:00.000')
(1001, 'Donghyun', 'SOMJANG', 'Development', '2020-04-01 00:00:00.000')
(2001, 'Sol', 'Fairy', 'Marketing', '2020-04-01 00:00:00.000')
(2002, 'Jiyoung', 'Magician', 'Marketing', '2020-04-01 00:00:00.000')
(1002, 'Hyeona', 'Theif', 'Development', '2020-04-01 00:00:00.000')
(1003, 'Soyoung', 'Chief', 'Development', '2020-04-01 00:00:00.000')
(1001, 'Donghyun', 'SOMJANG', 'Development', '2020-04-01 00:00:00.000')
(2001, 'Sol', 'Fairy', 'Marketing', '2020-04-01 00:00:00.000')
(2002, 'Jiyoung', 'Magician', 'Marketing', '2020-04-01 00:00:00.000')
(1002, 'Hyeona', 'Theif', 'Development', '2020-04-01 00:00:00.000')
(1003, 'Soyoung', 'Chief', 'Development', '2020-04-01 00:00:00.000')
(1001, 'Donghyun', 'SOMJANG', 'Development', '2020-04-01 00:00:00.000')
(2001, 'Sol', 'Fairy', 'Marketing', '2020-04-01 00:00:00.000')
(2002, 'Jiyoung', 'Magician', 'Marketing', '2020-04-01 00:00:00.000')
(1002, 'Hyeona', 'Theif', 'Development', '2020-04-01 00:00:00.000')
(1003, 'Soyoung', 'Chief', 'Development', '2020-04-01 00:00:00.000')
(1001, 'Donghyun', 'SOMJANG', 'Development', '2020-04-01 00:00:00.000')
(2001, 'Sol', 'Fairy', 'Marketing', '2020-04-01 00:00:00.000')
(2002, 'Jiyoung', 'Magician', 'Marketing', '2020-04-01 00:00:00.000')
(1002, 'Hyeona', 'Theif', 'Development', '2020-04-01 00:00:00.000')
(1003, 'Soyoung', 'Chief', 'Development', '2020-04-01 00:00:00.000')
(1001, 'Donghyun', 'SOMJANG', 'Development', '2020-04-01 00:00:00.000')
(2001, 'Sol', 'Fairy', 'Marketing', '2020-04-01 00:00:00.000')
(2002, 'Jiyoung', 'Magician', 'Marketing', '2020-04-01 00:00:00.000')
(1002, 'Hyeona', 'Theif', 'Development', '2020-04-01 00:00:00.000')
(1003, 'Soyoung', 'Chief', 'Development', '2020-04-01 00:00:00.000')
(1001, 'Donghyun', 'SOMJANG', 'Development', '2020-04-01 00:00:00.000')
(2001, 'Sol', 'Fairy', 'Marketing', '2020-04-01 00:00:00.000')
(2002, 'Jiyoung', 'Magician', 'Marketing', '2020-04-01 00:00:00.000')
(1002, 'Hyeona', 'Theif', 'Development', '2020-04-01 00:00:00.000')
(1003, 'Soyoung', 'Chief', 'Development', '2020-04-01 00:00:00.000')
(1001, 'Donghyun', 'SOMJANG', 'Development', '2020-04-01 00:00:00.000')
(2001, 'Sol', 'Fairy', 'Marketing', '2020-04-01 00:00:00.000')
(2002, 'Jiyoung', 'Magician', 'Marketing', '2020-04-01 00:00:00.000')
(1002, 'Hyeona', 'Theif', 'Development', '2020-04-01 00:00:00.000')
(1003, 'Soyoung', 'Chief', 'Development', '2020-04-01 00:00:00.000')

여기서 원하는 값만 골라 출력하려면 cur.excute("SELET * FROM employee_data") 안에 들어가는 쿼리만 변경하여 사용하면 된다.

• 예를 들어보자. 'employeed_data 테이블에서 id가 2000보다 큰 사람의 이름과 부서만 가져오기'

In [6]:

conn = sqlite3.connect("employee.db")
cur = conn.cursor()
cur.execute("SELECT name, department FROM employee_data WHERE employee_data.id > 2000")
rows = cur.fetchall()
for row in rows: 
    print(row)

('Sol', 'Marketing')
('Jiyoung', 'Marketing')
('Sol', 'Marketing')
('Jiyoung', 'Marketing')
('Sol', 'Marketing')
('Jiyoung', 'Marketing')
('Sol', 'Marketing')
('Jiyoung', 'Marketing')
('Sol', 'Marketing')
('Jiyoung', 'Marketing')
('Sol', 'Marketing')
('Jiyoung', 'Marketing')
('Sol', 'Marketing')
('Jiyoung', 'Marketing')
('Sol', 'Marketing')
('Jiyoung', 'Marketing')

• 예를들어보자. 'employeed_data 테이블에서 이름이 Donghyun인 사람의 닉네임만 가져오기'

In [7]:

conn = sqlite3.connect("employee.db")
cur = conn.cursor()
cur.execute("SELECT nickname FROM employee_data WHERE employee_data.name == 'Donghyun'")
rows = cur.fetchall()
for row in rows: 
    print(row)

('SOMJANG',)
('SOMJANG',)
('SOMJANG',)
('SOMJANG',)
('SOMJANG',)
('SOMJANG',)
('SOMJANG',)
('SOMJANG',)

모든 데이터를 가져와서 DataFrame 형태로 출력하기¶

In [8]:

# 먼저 DataFrame 사용하기 위해 pandas를 import 한다.
import pandas as pd

# employeed.db에 연결한다.
conn = sqlite3.connect("employee.db")

# cursor를 생성한다.
cur = conn.cursor()

# cur.execute 를 통해 SELECT 쿼리를 실행한다.
cur.execute("SELECT * FROM employee_data")

# 행 데이터를 가져온다.
rows = cur.fetchall()

# coulumns 이름을 가져온다.
cols = [column[0] for column in cur.description]

# 위의 코드를 활용해 DataFrame을 만들어준다.
data_df = pd.DataFrame.from_records(data=rows, columns=cols)

# 연결을 해제한다.
conn.close()

# 만든 DataFrame을 확인하자.
data_df

Out[8]:

	id	name	nickname	department	employment_date
0	1001	Donghyun	SOMJANG	Development	2020-04-01 00:00:00.000
1	2001	Sol	Fairy	Marketing	2020-04-01 00:00:00.000
2	2002	Jiyoung	Magician	Marketing	2020-04-01 00:00:00.000
3	1002	Hyeona	Theif	Development	2020-04-01 00:00:00.000
4	1003	Soyoung	Chief	Development	2020-04-01 00:00:00.000
5	1001	Donghyun	SOMJANG	Development	2020-04-01 00:00:00.000
6	2001	Sol	Fairy	Marketing	2020-04-01 00:00:00.000
7	2002	Jiyoung	Magician	Marketing	2020-04-01 00:00:00.000
8	1002	Hyeona	Theif	Development	2020-04-01 00:00:00.000
9	1003	Soyoung	Chief	Development	2020-04-01 00:00:00.000
10	1001	Donghyun	SOMJANG	Development	2020-04-01 00:00:00.000
11	2001	Sol	Fairy	Marketing	2020-04-01 00:00:00.000
12	2002	Jiyoung	Magician	Marketing	2020-04-01 00:00:00.000
13	1002	Hyeona	Theif	Development	2020-04-01 00:00:00.000
14	1003	Soyoung	Chief	Development	2020-04-01 00:00:00.000
15	1001	Donghyun	SOMJANG	Development	2020-04-01 00:00:00.000
16	2001	Sol	Fairy	Marketing	2020-04-01 00:00:00.000
17	2002	Jiyoung	Magician	Marketing	2020-04-01 00:00:00.000
18	1002	Hyeona	Theif	Development	2020-04-01 00:00:00.000
19	1003	Soyoung	Chief	Development	2020-04-01 00:00:00.000
20	1001	Donghyun	SOMJANG	Development	2020-04-01 00:00:00.000
21	2001	Sol	Fairy	Marketing	2020-04-01 00:00:00.000
22	2002	Jiyoung	Magician	Marketing	2020-04-01 00:00:00.000
23	1002	Hyeona	Theif	Development	2020-04-01 00:00:00.000
24	1003	Soyoung	Chief	Development	2020-04-01 00:00:00.000
25	1001	Donghyun	SOMJANG	Development	2020-04-01 00:00:00.000
26	2001	Sol	Fairy	Marketing	2020-04-01 00:00:00.000
27	2002	Jiyoung	Magician	Marketing	2020-04-01 00:00:00.000
28	1002	Hyeona	Theif	Development	2020-04-01 00:00:00.000
29	1003	Soyoung	Chief	Development	2020-04-01 00:00:00.000
30	1001	Donghyun	SOMJANG	Development	2020-04-01 00:00:00.000
31	2001	Sol	Fairy	Marketing	2020-04-01 00:00:00.000
32	2002	Jiyoung	Magician	Marketing	2020-04-01 00:00:00.000
33	1002	Hyeona	Theif	Development	2020-04-01 00:00:00.000
34	1003	Soyoung	Chief	Development	2020-04-01 00:00:00.000
35	1001	Donghyun	SOMJANG	Development	2020-04-01 00:00:00.000
36	2001	Sol	Fairy	Marketing	2020-04-01 00:00:00.000
37	2002	Jiyoung	Magician	Marketing	2020-04-01 00:00:00.000
38	1002	Hyeona	Theif	Development	2020-04-01 00:00:00.000
39	1003	Soyoung	Chief	Development	2020-04-01 00:00:00.000

사용예제 2)¶

파이썬에 기본적으로 내장되어 있는 데이터베이스인 sqlite3 모듈를 사용하는 방법 이다.

데이터베이스 접속¶

sqlite3 내장 모듈을 import 하고, connect 메서드를 통해 커넥션 객체를 생성하기

In [54]:

# 메모리 DB 접속 (일회성)
import sqlite3
con = sqlite3.connect(':memorry:')

In [73]:

# 파일 DB 접속
import sqlite3
con = sqlite3.connect('./test_1.db')

• 테이블 생성

커서 객체를 받아와서 execute 메서드로 CREATE TABLE 쿼리를 전송 한다. 참고적으로 한번 run을 하고 재 실행을 하니 중복된다는 오류코드가 뜸 해결방법은 (DB네임을 바꿔서 새로 만들던가, DB를 지우던가 둘중 하나)를 해야 한다.

In [74]:

cur = con.cursor()
cur.execute("CREATE TABLE PhoneBook(Name text, PhoneNum text);")

Out[74]:

<sqlite3.Cursor at 0x7fafde9712d0>

데이터 삽입¶

마찬가지로 커서 객체로 작업한다.

• 기본 String Query 사용

In [75]:

cur = con.cursor()
cur.execute("INSERT INTO PhoneBook Values('Derick', '010-1234-5678');")

Out[75]:

<sqlite3.Cursor at 0x7fafde9713b0>

• Parameter: Tuple 사용

In [76]:

name = 'SangJung'
phoneNumber = '010-5670-2343'
cur = con.cursor()
cur.execute('INSERT INTO PhoneBook VALUES(?, ?);', (name, phoneNumber))

Out[76]:

<sqlite3.Cursor at 0x7fafde971340>

• Named Parameter: Dictionary 사용

In [78]:

name = 'SangJung'
phoneNumber = '010-5670-2343'
cur = con.cursor()
cur.execute('INSERT INTO PhoneBook VALUES(:name, :phoneNumber);', {"name":name, "phoneNumber":phoneNumber})

Out[78]:

<sqlite3.Cursor at 0x7fafde971260>

• List 사용

In [79]:

dataList = (('Tom', '010-543-5432'), ('DSP', '010-123-1234'))
cur = con.cursor()
cur.executemany("INSERT INTO PhoneBook VALUES(?, ?);", dataList)

Out[79]:

<sqlite3.Cursor at 0x7fafde9711f0>

데이터 조회¶

커서 객체는 파일 포인터처럼 한 번 읽은 데이터는 다시 읽을 수 없으므로 다시 SELECT 쿼리를 전송해야 함을 주의합니다.

• 순회 조회

In [80]:

cur.execute('SELECT * FROM PhoneBook')
for row in cur:
    print(row)

('Derick', '010-1234-5678')
('SangJung', '010-5670-2343')
('SangJung', '010-5670-2343')
('Tom', '010-543-5432')
('DSP', '010-123-1234')

• 단건 조회

In [81]:

cur.execute('SELECT * FROM PhoneBook')
cur.fetchone()

Out[81]:

('Derick', '010-1234-5678')

• 다건 조회

In [83]:

cur.fetchmany(2)

Out[83]:

[('SangJung', '010-5670-2343'), ('SangJung', '010-5670-2343')]

• 모두 조회

In [84]:

cur.fetchall()

Out[84]:

[('Tom', '010-543-5432'), ('DSP', '010-123-1234')]

SQLite3 모듈 사용법을 예제로 하면서 생성된 .db이다. ( 예제 2번을 할때, CREATE문을 사용하고, 두번째 러닝할때는 DB에 이미 있어서 중복에러가 발생했다. 그래서 db네임을 바꿔서 했더니 git status 에서 db를 눈으로 확인 할 수 있었다.)

• 출처
  • 솜씨좋은 장씨님 tistory
  • 예제로 배우는 파이썬 프로그래밍
  • [파이썬] sqlite3 모듈 사용법