[파이썬] 파이썬에서 SQL 실행하기 (sqlite3)

sqlite3라는 라이브러리를 사용하여 파이썬에서 sql을 실행해보려고 합니다. sqllite3는 파이썬에 기본적으로 내장되어 있기 때문에 바로 import 해서 사용하실 수 있습니다.

 

실행하기 전에 connect() 라는 함수로 데이터베이스랑 연결을 하고, cursor()를 활용해 데이터베이스에서 이것저것 실행할 수 있게 명령어를 db에 전달하여 정보를 가지고 오도록 하고, 중간중간에 연결된 DB파일에 저장을 해준다거나(commit) 저장시점으로 되돌아가거나 (rollback) 연결을 끊어줄 수도 있습니다(close).

 

기본적인 예제를 실행해보도록 하겠습니다. 실행해볼 기본적인 단계는 아래와 같습니다.

  1. 파이썬에서 데이터베이스에 연결하기. 

  2. 테이블을 만들고 데이터 삽입

  3. 테이블 확인

  4. 데이터 추가 삽입

  5. 삽입 잘 되었나 다시 확인

  6. 파이썬 데이터프레임으로 만들기 ( 파이썬으로 이것저것 처리하는게 더 편하다면! )

  7. 데이터베이스에 테이블 저장한 후 데이터베이스와 연결 해제. 

 

시작해보겠습니다!!

 

1. 파이썬에서 데이터베이스에 연결하기. 

import sqlite3
# 만드려는 Database 파일의 이름을 지정
dbpath = "maindb.db" # "maindb.sqlite"으로 지정해도 상관없음

conn = sqlite3.connect(dbpath) # Database 파일에 '연결'합니다.
cur = conn.cursor() # 데이터베이스 안에서 일하는 심부름꾼 같은 cursor 만들기

 

2. 테이블을 만들고 데이터 삽입

script = """
-- 1) employees 테이블이 이미 있다면 제거
DROP TABLE IF EXISTS employees;

-- 2) employees 테이블 생성 
CREATE TABLE employees( 
id INTEGER PRIMARY KEY AUTOINCREMENT, --숫자를 저장, 이 테이블의 주요 Key(기본 키)로 지정, 값을 따로 입력하지 않으면 자동 증가 숫자 부여
name TEXT NOT NULL, --빈 값이 저장되는 것을 허용하지 않음
ReportsTo INT --소수점이 들어간 자료형
);

-- 3) employees 테이블에 데이터 삽입
INSERT INTO employees(name) VALUES('Andrew');
INSERT INTO employees(name, ReportsTo) VALUES('Nancy', 1);
INSERT INTO employees(name, ReportsTo) VALUES('Jane', 2);
INSERT INTO employees(name, ReportsTo) VALUES('Margaret', 2);
INSERT INTO employees(id, name, ReportsTo) VALUES(5,'Steve', 2);

-- 전체 필드에 빠짐없이 값을 넣을 시 필드명 생략 가능 
INSERT INTO employees VALUES(6, 'Michael', 1); 
"""

cur.executescript(script) # 여러개의 SQL 명령어를 한 장의 "Script"처럼 한번에 실행합니다.
conn.commit() # 실제로 DB에 위 Table & Data를 저장합니다. 즉, 실행

 

3. 테이블 확인

cur.execute('select * from employees;')
cur.fetchall()

# Result
[(1, 'Andrew', None),
 (2, 'Nancy', 1),
 (3, 'Jane', 2),
 (4, 'Margaret', 2),
 (5, 'Steve', 2),
 (6, 'Michael', 1)]

 

4. 데이터 추가 삽입

data = [(7, 'Robert', 6), 
        (8, 'Laura', 6)]

# executemany : 구조가 동일한 SQL문을 여러번 바꿔가며 실행
cur.executemany("INSERT INTO employees(id, name, ReportsTo) VALUES(?, ?, ?);", data)
conn.commit()

 

5. 삽입 잘 되었나 다시 확인

cur.execute('select * from employees;')
cur.fetchall()

# Result
[(1, 'Andrew', None),
 (2, 'Nancy', 1),
 (3, 'Jane', 2),
 (4, 'Margaret', 2),
 (5, 'Steve', 2),
 (6, 'Michael', 1),
 (7, 'Robert', 6),
 (8, 'Laura', 6)]

 

6. 파이썬 데이터프레임으로 만들기 ( 파이썬으로 이것저것 처리하는게 더 편하다면! )

script = """
            SELECT *
            FROM 
                employees
            ;
        """ 

df = pd.read_sql_query(script, conn)
df

# Result
	id	name		ReportsTo
0	1	Andrew		NaN
1	2	Nancy		1.0
2	3	Jane		2.0
3	4	Margaret	2.0
4	5	Steve		2.0
5	6	Michael		1.0
6	7	Robert		6.0
7	8	Laura		6.0

 

7. 데이터베이스에 테이블 저장한 후 데이터베이스와 연결 해제. 

conn.commit() # 저장
conn.close() # 연결 해제