[파이썬] Python Sqlite3 모듈을 사용하여 Database를 생성하고 데이터를 관리해보자

 

'Python Sqlite3'

 

이번 포스팅에서는 파이썬 Sqlite3 모듈을 사용하여 자체 데이터베이스를 생성하고 데이터를 관리하는 방법에 대해 알아보도록 할 예정이다.

Sqlite3는 파이썬에서 제공하는 모듈로써, 텍스트나 숫자와 같이 간단한 데이터를 저장하는 심플한 데이터베이스를 구축할 수 있다.

마치 MariaDB와 MYSQL의 축소형이라고 생각할 수 있다.

 

 

Sqlite3에서 지원하는 자료형

---

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

 

 

예제에서 사용할 데이터

---

id (INTEGER)	name (TEXT)	age (INTEGER)	number (TEXT)	address (TEXT)
1	김상욱	22	010-4545-6767	서울특별시 종로구 세종대로 종로 1가
2	최지훈	20	010-7896-1234	전라북도 전주시 덕진구 석소로 77, 101동 101호(인후동1가, 대우아파트)
3	Dr.Bae	67	010-8452-5678	전라북도 전주시 덕진구 석소2길 21-1(우아동2가)
4	강서혁	27	010-1414-6767	경상남도 의령군 화정면 화정로 41-6
5	유민규	21	010-6497-6497	서울특별시 동작구 흑석한강로 2(흑석동)

 

 

---

 

상황 (Situation)

 

5명의 지인이 있는 개발자 BuNa는 친구들의 정보를 전산화하여 편리하게 관리하고 싶어 졌다.

BuNa는 마침 파이썬에 대해 공부하고 있었고, 파이썬의 Sqlite3 모듈을 사용하여 데이터베이스를 구축하고 해당 DB에 친구들의 정보를 입력하면 좋겠다고 생각했다.

그럼 지금 바로 BuNa의 상황을 가정하여 친구들의 데이터를 관리해보도록 하자.

 

 

import sqlite3

 

sqlite는 파이썬 내장 모듈이므로 따로 설치할 필요 없이 바로 import 해주면 된다.

'import sqlite3' 라고 작성하면 간단히 sqlite 모듈을 사용할 수 있다.

 

 

friend_data = list(tuple())
friend_data.append(('김상욱', 22, '010-4545-6767', '서울특별시 종로구 세종대로 종로 1가'))
friend_data.append(('최지훈', 20, '010-7896-1234', '​전라북도 전주시 덕진구 석소로 77, 101동 101호(인후동1가, 대우아파트)'))
friend_data.append(('Dr.Bae', 67, '010-8452-5678', '​전라북도 전주시 덕진구 석소2길 21-1(우아동2가)'))
friend_data.append(('강서혁', 27, '010-1414-6767', '경상남도 의령군 화정면 화정로 41-6'))
friend_data.append(('유민규', 21, '010-6497-6497', '서울특별시 동작구 흑석한강로 2(흑석동)'))

 

친구들의 데이터를 DB에 한 번에 삽입(Insert)할 수 있도록 배열 friend_data 변수를 선언해준다.

friend_data 변수는 리스트 안에 여러 개의 튜플 데이터가 들어 있는 형태이다.

선언한 배열에 위에서 소개한 데이터를 튜플 형태로 배열에 삽입해주면 된다..

 

 

테이블 생성 및 데이터 삽입

---

#테이블 생성 및 데이터 삽입

conn = sqlite3.connect('friend.db')

# db 쿼리를 조작하기 위한 커서 객체 생성
cur = conn.cursor()

# 테이블 생성
cur.execute("""CREATE TABLE IF NOT EXISTS friend_data(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT,
  age INTEGER,
  number TEXT,
  address TEXT
  )""")

cur.executemany('INSERT INTO friend_data (name, age, number, address) VALUES (?, ?, ?, ?)', friend_data)
conn.commit()

# 마지막엔 무조건 close() 메소드로 db연결을 해제해야 한다.
conn.close()

 

우선, sqlite3에 포함되어 있는 connect() 메소드에 연결할 DB를 전달해주고 connection 객체를 전달받는다.

connect() 메소드에 입력한 DB가 이미 존재한다면 그대로 사용할 것이고, 존재하지 않는다면 새롭게 friend.db라는 DB를 생성할 것이다.

그러고 나서 DB를 조작하기 위한 커서(Cursor)를 가져온다.

커서를 통해 테이블을 생성(CREATE)하거나, 데이터를 삽입(INSERT), 삭제(DELETE)하는 등 쿼리문을 입력/전달할 수 있다.

 

 

지금은 코드를 최초로 실행하는 것이기 때문에 테이블이 존재하지 않는다.

따라서 위 코드를 입력하여 friend_data라는 테이블이 존재하지 않는다면, 테이블을 생성하도록 해준다.

테이블 컬럼으로는 name(이름), age(나이), number(전화번호), address(집주소)가 존재한다.

 

 

테이블을 생성했다면, 이제 데이터를 입력할 차례이다.

executemany() 메소드를 사용하면 여러개의 데이터를 배열로 전달하여 한 번에 삽입할 수 있다.

마지막으로 쿼리를 삽입(INSERT) / 삭제(DELETE) / 수정(UPDATE) 할 때는 commit() 메소드를 호출하여 변경사항을 인지시켜주어야 한다.

 

 

데이터 조회

---

#데이터 조회

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

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

friends = cur.fetchall()

for friend in friends:
  print(friend)

conn.close()

 

데이터를 삽입했으니, 정상적으로 데이터가 들어갔는지 확인해볼 필요가 있다.

이번에는 SELECT 쿼리문을 사용하여 friend_data 테이블에 있는 데이터를 모두 가져와보도록 하겠다.

fetchall() 메소드를 사용하면 execute() 메소드로 탐색한 데이터를 배열로 가져올 수 있다.

그리고 for문을 통해 결과를 출력해보자.

 

'SELECT 쿼리문을 통해 DB에 저장된 데이터를 출력한 결과'

 

다행히 BuNa는 지인들의 데이터가 정상적으로 DB에 저장되었음을 확인할 수 있었다.

그런데 여기서 문제가 생겼다.

'최지훈'이라는 친구의 휴대전화 번호를 잘못 입력했다는 사실이다.

이럴 경우에는 어떻게 해야 하는지 글을 계속 읽어보며 알아보자.

 

 

테이블 수정

---

#데이터 수정

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

cur = conn.cursor()
cur.execute("UPDATE friend_data SET number=? WHERE name=?", ('010-1234-5678', '최지훈'))

cur.execute("SELECT * FROM friend_data WHERE name=?", ('최지훈', ))
friend = cur.fetchone()

print(friend)

conn.commit()
conn.close()

 

위에서 '최지훈'이라는 친구의 번호를 잘못 저장했다고 언급했다.

그렇다면 번호를 어떻게 수정할 수 있을까?  바로 UPDATE문이다.

UPDATE문의 SET에는 변경할 컬럼과 값을 입력해주고, WHERE절에는 수정할 데이터의 name이 최지훈이라는 사실을 알려준다.

그리고 다시 SELECT문을 통해 최지훈의 데이터를 출력해보자.

 

'UPDATE 쿼리문을 통해 '최지훈'의 전화 번호를 수정한 결과'

 

다행히 수정하고자 하는 번호로 변경할 수 있었다.

이처럼 데이터를 수정하고 싶다면 UPDATE문의 WHERE 절로 조건을 걸어 특정 데이터의 값을 변경해주면 된다.

 

 

테이블 삭제

---

#데이터 수정

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

cur = conn.cursor()
cur.execute("DELETE FROM friend_data WHERE name=?", ('Dr.Bae', ))

cur.execute("SELECT * FROM friend_data")
friends = cur.fetchall()

for friend in friends:
  print(friend)

conn.commit()
conn.close()

 

BuNa는 또다시 고민하기 시작했다.

바로 대학교 교수님인 Dr.Bae와 손절하여 더 이상 필요 없는 데이터를 지우고 싶었기 때문이다.

이런 경우에는 DELETE 문을 통해 지우고 싶은 데이터의 컬럼명과 구분할 수 있는 값을 전달해주어 삭제하면 된다.

위 코드를 보면 알 수 있다시피 이름이 'Dr.Bae'인 데이터를 찾아서 삭제해달라고 요청하고 있다.

데이터가 정상적으로 제거되었는지 SELECT문을 통해 모든 데이터를 불러오고 출력해보자.

 

'DELETE 쿼리문을 통해 이름이 Dr.Bae인 데이터를 삭제한 결과'

 

출력된 결과를 통해 Dr.Bae라는 이름의 데이터가 정상적으로 삭제되었음을 확인하였다.

마지막으로 이 데이터들을 보기 좋게 나타낼 수 있는 방법을 알아보고 마무리하겠다.

 

 

import pandas as pd

conn = sqlite3.connect('friend.db')
cur = conn.cursor()

cur.execute("SELECT * FROM friend_data")

rows = cur.fetchall()

# 테이블 컬럼명 가져오기
columns = [column[0] for column in cur.description]

frame = pd.DataFrame.from_records(data=rows, columns=columns)

conn.close()

frame

 

Pandas 라이브러리를 사용하면 데이터를 테이블 형태로 보기좋게 출력할 수 있다.

기존과 동일하게 SELECT문을 통해 'rows' 배열에 모든 데이터를 가져온다.

그리고 테이블의 컬럼명을 가져오기 위해 for문을 통해 description의 첫 번째 요소를 모두 가져온 후, 'columns'이라는 이름의 배열로 만들어준다.

이 두 배열들을 row와 column의 값으로 Pandas에 전달하고 데이터 프레임을 출력해보자.

 

'Pandas 라이브러리를 사용하여 데이터를 테이블 형식으로 출력한 결과'

 

출력된 결과를 보면, 데이터베이스를 사용하고 있다는 느낌이 훨씬 잘 들도록 깔끔하게 데이터를 출력해주고 있다.

맨 위에 테이블의 컬럼명과 그 아래로는 테이블의 데이터를 보여주고 있다.

 

오늘은 Sqlite3 모듈을 통해 간단히 데이터를 DB에 저장하는 방법에 대해 알아보았다.

해당 포스팅에서는 지인의 정보를 저장한다는 상황으로 가정하여 적은 양의 데이터를 관리해보았지만,

실제로는 더 많은 데이터를 관리할 수 있다.

 

다만, Sqlite는 말 그대로 데이터베이스를 맛보기로 체험해보는 수준이기 때문에,

데이터를 정의하는 종류도 적고, 명령어도 현저히 적다.

만약 방대한 양의 데이터를 관리하고 싶다면 맨 처음 설명한 MariaDB, MySQL 등을 사용하면 된다.

이에 대한 내용은 다음 포스팅에 구체적인 예제와 함께 설명하는 시간을 갖도록 하겠다.

 

---

 

 

전체 소스 코드 : 

https://colab.research.google.com/drive/1dKE64A0Ar2RrS2RFi_0qQ5HXr7DXIolP#scrollTo=DsPIyjbuoEGD

 

 

👍클릭으로 구독하기👍

 

 

(이해가 다소 힘들거나, 틀린 부분이 있다면 댓글 부탁드리겠습니다! 😊)

💖도움이 되셨다면 '구독'과 '공감' 부탁드립니다!💖