[Python] Python에서 Sqlite3 사용하기 (feat. Pandas)

Python에서 사용할 수 있는 데이터베이스 라이브러리 중 Sqlite3 의 사용법에 대해서 정리해보고자 합니다.

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

Sqlite3

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

데이터 베이스를 .db 확장자를 가지는 단일 파일로 저장합니다.

 

지원하는 자료형

파이썬 형	SQLite형
None	NULL
int	INTEGER
float	REAL
str	TEXT
bytes	BLOB

 

사용 예제

employee.db

 

employee_data

id	name	nickname	department	employment_date
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

먼저 코드를 통해 위와 같은 테이블을 하나 생성하려합니다.

 

각각의 column에 어떠한 데이터가 들어가는지 보고 

다음과 같이 테이블의 각 column을 어떤 자료형으로 저장할 지 선택합니다.

column 명	자료형
id	INTEGER
name	TEXT
nickname	TEXT
department	TEXT
employment_date	TEXT

여기서 emploment_date는 datetime자료형이 들어가므로 TEXT로 설정합니다.

 

코드로 구현하기

 

CREATE TABLE & INSERT DATA

import sqlite3

먼저 sqlite3를 import 합니다.

conn = sqlite3.connect("employee.db")

cur = conn.cursor()

conn.execute('CREATE TABLE employee_data(id INTEGER, name TEXT, nickname TEXT, department TEXT, employment_date TEXT)')

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.commit()
conn.close()

 

한 줄씩 살펴보기

sqlite3.connect("employee.db")

만약 employee.db가 없을 경우 새로 생성하면서 연결하게되고

employee.db가 이미 있을 경우 해당 db로 연결하게 됩니다.

cur = conn.cursor()

conn.cursor를 통해 Cursor를 하나 생성합니다.

conn.execute('CREATE TABLE employee_data(id INTEGER, name TEXT, nickname TEXT, department TEXT, employment_date TEXT)') 

conn.execute를 통해 employee_data 테이블 하나를 생성합니다.

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')
    ]
)

cur.executemany를 통해서 데이터를 INSERT합니다.

conn.commit()

conn.commit()을 통해 변경사항을 저장합니다.

conn.close()

conn.close()를 통해 db와의 연결을 해제합니다.

 

SELECT & PRINT DATA FROM DB

 

모든 데이터 가져와서 출력하기

먼저 전체 데이터를 SELECT해온 뒤 출력하는 코드를 구현해보았습니다.

conn = sqlite3.connect("employee.db")

cur = conn.cursor()

cur.execute("SELECT * FROM employee_data")

rows = cur.fetchall()

for row in rows:
    print(row)
    
conn.close()

한 줄씩 살펴보기

conn = sqlite3.connect("employee.db")

employee.db에 연결합니다.

cur = conn.cursor()

cursor하나를 생성합니다.

cur.execute("SELECT * FROM employee_data")

cur.execute를 통해 SELECT 쿼리를 실행합니다.

rows = cur.fetchall()

cur.execute를 통해 가져온 모든 결과의 행을 가져와 리스트로 가져옵니다.

for row in rows:
    print(row)

가져온 데이터를 반복문을 통해 하나의 row씩 출력합니다.

 

출력결과

(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.close()를 통해 db와의 연결을 해제합니다.

 

여기서 원하는 값만 골라서 출력하고 싶다고 한다면

cur.execute("SELECT * FROM employee_data")

cur.execute 안에 들어가는 쿼리만 변경하여 사용하면됩니다.

 

example 1) employee_data 테이블에서 id가 2000보다 큰 사람의 이름과 부서만 가져오기

cur.execute("SELECT name, department FROM employee_data WHERE employee_data.id > 2000")

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

example 2) employee_data 테이블에서 이름이 Donghyun인 사람의 닉네임만 가져오기

cur.execute("SELECT nickname FROM employee_data WHERE employee_data.name == 'Donghyun'")

('SOMJANG',)

 

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

import pandas as pd

conn = sqlite3.connect("employee.db")

cur = conn.cursor()

cur.execute("SELECT * FROM employee_data")

rows = cur.fetchall()

cols = [column[0] for column in cur.description]

data_df = pd.DataFrame.from_records(data=rows, columns=cols)

conn.close()

data_df

이번에는 데이터를 가져와서 pandas의 DataFrame형식으로 출력하도록 해보았습니다.

 

한 줄씩 살펴보기

import pandas as pd

먼저 DataFrame을 사용하기 위해서 pandas를 import 합니다.

conn = sqlite3.connect("employee.db")

employee.db에 연결합니다.

cur = conn.cursor()

cursor를 생성합니다.

cur.execute("SELECT * FROM employee_data")

cur.execute를 통해 SELECT 쿼리를 실행합니다.

rows = cur.fetchall()

행 데이터를 가져옵니다.

cols = [column[0] for column in cur.description]

columns(열) 이름을 가져옵니다.

data_df = pd.DataFrame.from_records(data=rows, columns=cols)

위의 코드를 활용하여 DataFrame을 만들어줍니다.

conn.close()

연결을 해제하고

data_df

만든 DataFrame을 확인해보면

잘 만들어진 것을 볼 수 있습니다.

 

Table 삭제하기

conn = sqlite3.connect("employee.db")

cur = conn.cursor()

conn.execute('DROP TABLE employee_data2')

conn.close()

conn.execute에 DROP 쿼리를 넣어 실행하면 됩니다.

읽어주셔서 감사합니다.