관리 메뉴

솜씨좋은장씨

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

Programming/Python

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

솜씨좋은장씨 2020. 4. 6. 19:37
728x90
반응형

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

 

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

바로 가기 메서드 사용하기 Connection 객체의 비표준 execute(), executemany() 및 executescript() 메서드를 사용하면, (종종 불필요한) Cursor 객체를 명시적으로 만들 필요가 없으므로, 코드를 더 간결하게 작성할 수 있습니다. 대신, Cursor 객체가 묵시적으로 만들어지며 이러한 바로 가기 메서드는 커서 객체를 반환합니다. 이런 방법으로, Connection 객체에 대한 단일 호출만 사용하여 SELECT 문을

docs.python.org

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 쿼리를 넣어 실행하면 됩니다.

읽어주셔서 감사합니다.

Comments