주피터 노트북 파일을 마크다운으로 변환하여 작성한 글입니다.
코드는 깃헙에 정리해두었습니다!
https://github.com/pirate-turtle/SQL
Python에서는 sqlite3 라이브러리를 기본적으로 제공합니다.
import sqlite3연결 생성
sqlite3 모듈을 사용하기 위해서는 먼저 Connection 객체를 생성하여 데이터베이스에 연결해야합니다.
# 원하는 파일명 지정 (존재하지 않는 파일인경우 생성됨)
con = sqlite3.connect('./database/example.db')
# 실제 파일 경로 대신 :memory: 를 넘기면 RAM에 임시로 데이터베이스가 생성됨
# con = sqlite3.connect(':memory:')SQL 실행
Connection 객체를 생성한 다음 Cursor 오브젝트를 생성하여 SQL문을 실행할 수 있도록 합니다.
# Cursor 생성
cur = con.cursor()
# 테이블이 이미 있다면 삭제
cur.execute("DROP TABLE IF EXISTS student")
# 테이블 생성
cur.execute("""
CREATE TABLE student(
first_name text,
last_name text,
age integer
)
""")
# 데이터 삽입
# execute 함수는 한번에 하나의 SQL문만 실행 가능
cur.execute("INSERT INTO student VALUES ('Tomas', 'Train', 10)")
cur.execute("INSERT INTO student VALUES ('Bean', 'Green', 1)")<sqlite3.Cursor at 0x1d459050420># 한번에 여러개를 실행하려고 하면 에러 발생
cur.execute("""
INSERT INTO student VALUES ('Tomas', 'Train', 10);
INSERT INTO student VALUES ('Bean', 'Green', 1);
""")---------------------------------------------------------------------------
Warning Traceback (most recent call last)
~\AppData\Local\Temp/ipykernel_16564/2793874112.py in <module>
3 INSERT INTO student VALUES ('Tomas', 'Train', 10);
4 INSERT INTO student VALUES ('Bean', 'Green', 1);
----> 5 """)
Warning: You can only execute one statement at a time.# 한번에 여러 SQL문을 실행하고 싶은 경우 executescript 사용하기
cur.executescript("""
DROP TABLE IF EXISTS teacher;
DROP TABLE IF EXISTS book;
CREATE TABLE teacher(
firstname,
lastname,
age
);
CREATE TABLE book(
title,
author,
published
);
INSERT INTO book(title, author, published)
VALUES (
'Dirk Gently''s Holistic Detective Agency',
'Douglas Adams',
1987
);
""")<sqlite3.Cursor at 0x1d459050420># 같은 형식의 SQL문에 데이터만 바꿔서 여러번 실행하고 싶은 경우,
# executemany와 제너레이터를 조합하면 좋다
# 메모리에 모두 올라가는 적은 양의 데이터라면 그냥 리스트로 넘겨도 될듯
def data_generator():
datas = [
['Bee', 'Honey', 3],
['Bee', 'Zig', 5],
['Bob', 'Sponge', 7]
]
for data in datas:
yield data
cur.executemany("INSERT INTO student VALUES (?, ?, ?)", data_generator())
cur.execute("SELECT * FROM student")
cur.fetchall()[('Tomas', 'Train', 10),
('Bean', 'Green', 1),
('Bee', 'Honey', 3),
('Bee', 'Zig', 5),
('Bob', 'Sponge', 7)]결과 조회
SQL 결과를 조회하기 위해서는 Cursor 객체를 iterator로 활용하는 방법,
fetchone, fetchmany, fetchall 함수를 이용하는 방법이 있습니다.
# Cursor는 iterator객체이므로 순차적으로 값을 불러올 수 있다
cur.execute("SELECT * FROM student").rowcount
print(next(cur))
print(next(cur))('Tomas', 'Train', 10)
('Bean', 'Green', 1)# SQL 실행하면 동일한 cursor 객체를 리턴해준다
cur.execute("SELECT * FROM student") is curTrue# 그래서 이렇게 바로 for문에 넣는것도 가능!
for row in cur.execute("SELECT * FROM student"):
print(row)('Tomas', 'Train', 10)
('Bean', 'Green', 1)
('Bee', 'Honey', 3)
('Bee', 'Zig', 5)
('Bob', 'Sponge', 7)fetchone, fetchmany, fetchall 함수를 이용하여 결과를 가져올 경우,
이미 조회한 데이터는 다시 함수를 호출해도 조회할 수 없습니다. (다음 데이터부터 가져옴)
다시 조회하고 싶다면 SQL문을 다시 실행해야합니다.
# fetchone은 실행결과 중 하나의 행만 가져온다
cur.execute("SELECT * FROM student")
cur.fetchone()('Tomas', 'Train', 10)# 한번 더 호출해보면 그 다음 행을 가져온다
cur.fetchone()('Bean', 'Green', 1)# fetchmany는 실행 결과 중 원하는 개수의 행을 가져올 수 있다
# 원하는 개수보다 적은 행이 남아있으면 남은 행만큼만 가져온다
cur.fetchmany(5)[('Bee', 'Honey', 3), ('Bee', 'Zig', 5), ('Bob', 'Sponge', 7)]# fetchall은 실행 결과 중 남아있는 모든 행을 가져온다
# 남아있는 행이 없으면 빈 리스트 리턴
cur.fetchall()[]# 다시 조회하고 싶으면 SQL문을 다시 실행해야한다
cur.execute("SELECT * FROM student")
cur.fetchall()[('Tomas', 'Train', 10),
('Bean', 'Green', 1),
('Bee', 'Honey', 3),
('Bee', 'Zig', 5),
('Bob', 'Sponge', 7)]변경사항 저장
Connection객체의 commit함수로 변경사항을 저장할 수 있습니다.
# commit으로 변경사항 저장
con.commit()
# 다시 연결한 다음 확인해보기
con.close()
con = sqlite3.connect('./database/example.db')
cur = con.cursor()
# 변경사항이 저장됐다
for row in cur.execute("SELECT * FROM student"):
print(row)('Tomas', 'Train', 10)
('Bean', 'Green', 1)
('Bee', 'Honey', 3)
('Bee', 'Zig', 5)
('Bob', 'Sponge', 7)테이블 보기 좋게 출력하기
prettytable
이번 프로젝트에서는 prettytable 모듈을 활용했습니다.
pandas를 활용해도 좋겠지만 단순히 SQL 실행 결과만 확인할 것이기 때문에 prettytable로도 충분합니다.
# prettytable 모듈 버전 확인
!pip show prettytable | findstr Version
# 리눅스 환경에서는 findstr 말고 grep 사용하기
# !pip show prettytable | grep Version
# 모듈 없는 경우 주석 해제하고 설치하기
# !pip install prettytableVersion: 3.3.0Python DB-API를 통해 지원되는 DB모듈의 경우 Cursor 객체를 바로 테이블로 만들 수 있습니다
from prettytable import from_db_cursor
cur.execute("SELECT * FROM student")
from_db_cursor(cur)| first_name | last_name | age |
|---|---|---|
| Tomas | Train | 10 |
| Bean | Green | 1 |
| Bee | Honey | 3 |
| Bee | Zig | 5 |
| Bob | Sponge | 7 |
컬럼명 (필드명)
prettytable 모듈을 거치지 않고 특정 테이블의 컬럼명을 알고 싶은 경우 2가지 방법이 있습니다.
- PARAGMA 키워드로 스키마 확인하기
- Conncetion 객체에 Row 객체 연결하기
스키마에서 컬럼명을 확인하는 경우, 특정 인덱스의 열만 가져오는 작업이 필요합니다.
# PRAGMA 키워드로 스키마 확인
cur.execute("PRAGMA table_info('student')")
schema = cur.fetchall()
schema[(0, 'first_name', 'text', 0, None, 0),
(1, 'last_name', 'text', 0, None, 0),
(2, 'age', 'integer', 0, None, 0)]# 컬럼명만 가져오기
col_names = list(map(lambda x: x[1], schema))
col_names['first_name', 'last_name', 'age']Row 객체를 연결하면 더 쉽게 접근이 가능합니다.
SQL 실행 결과가 Row 객체로 오게 되는데, keys() 함수로 편리하게 컬럼명을 얻을 수 있습니다.
# Row 객체 연결
con.row_factory = sqlite3.Row
# ※연결 후 Cursor 객체를 다시 생성해줘야 함
cur = con.cursor()
cur.execute("SELECT * FROM student")
row = cur.fetchone()
# 컬럼명 확인하기
print('col_name: ', row.keys())col_name: ['first_name', 'last_name', 'age']# SQL 실행 결과는 Row 객체가 리턴됨
print(row)<sqlite3.Row object at 0x000001D459163CD0># 컬럼명으로 특정 값에 접근 가능
cur.execute("""SELECT * FROM student""")
row = cur.fetchone()
print(row['last_name'])
# 행 데이터를 확인하기 위해 형변환
# dict나 list 타입도 가능하지만 값 변경 못하게 tuple로 변환해보기
print(tuple(row))Train
('Tomas', 'Train', 10)sqlite_master
DB 파일의 테이블에 관한 정보는 마스터 테이블인 sqlite_master에 저장됩니다.
# sqlite_master 스키마 확인해보기
cur.execute("PRAGMA table_info('sqlite_master')")
from_db_cursor(cur)| cid | name | type | notnull | dflt_value | pk |
|---|---|---|---|---|---|
| 0 | type | text | 0 | None | 0 |
| 1 | name | text | 0 | None | 0 |
| 2 | tbl_name | text | 0 | None | 0 |
| 3 | rootpage | int | 0 | None | 0 |
| 4 | sql | text | 0 | None | 0 |
# 생성된 데이터베이스에 대한 정보가 저장되어있다
cur.execute("SELECT * FROM sqlite_master")
from_db_cursor(cur)| type | name | tbl_name | rootpage | sql |
|---|---|---|---|---|
| table | student | student | 2 | CREATE TABLE student( first_name text, last_name text, age integer ) |
| table | teacher | teacher | 4 | CREATE TABLE teacher( firstname, lastname, age ) |
| table | book | book | 3 | CREATE TABLE book( title, author, published ) |
