[python/DB] python으로 SQL문 작성하여 DB 다루기(sqlite3)

오늘 포스팅은

python으로 SQL문을 작성하여  sqlite3 데이터베이스(DB)를 다루는 방법을 간단하게 알아보도록 하겠습니다.

sqllite3는 파이썬에 기본 내장되어 있기때문에 바로 불러와서 사용하면 됩니다.

 

샘플 DB 준비

이번에 사용할 샘플 DB를 다운받겠습니다.

SQLite에서 제공하는 샘플데이터를 사용해 보도록 하겠습니다.

아래의 링크를 통해 데이터를 다운받아 주세요.

https://www.sqlitetutorial.net/sqlite-sample-database/

SQLite Sample Database And Its Diagram (in PDF format)

위의 링크 접속 후 스크롤을 좀 내려서 하이라이트 표시해 놓은 부분을 클릭하면 다운 받을 수 있습니다.

압축을 풀고 db파일을 준비해 주세요.

샘플 db의 구조는 아래의 사진과 같이 구성되어 있습니다.

출처: https://www.sqlitetutorial.net/wp-content/uploads/2018/03/sqlite-sample-database-diagram-color.pdf

 

Python에서 SQLite3 사용하기

파이썬에서 sqlite3를 사용하여 db를 조작하는 방법은 크게 6단계로 나뉩니다.

1. sqlite3 모듈 import

2. 데이터베이스 파일 불러오기(connect)

3. 커서(cursor: 명령어를 db에 전달하여 정보를 가져오는 역할) 생성(cursor)

4. SQL 명령어 작성(CREATE, INSERT, SELECT, DELETE 등등)

5. SQL 명령어 실행(execute, cexutemany)

6. 데이터 베이스 닫기(close)

 

SQL 명령어 - 테이블 생성(CREATE)

CREATE는 테이블을 만드는 명령어 입니다.

불러온 DB에 Item이라는 테이블이 존재하지 않으면(NOT EXISTS) Item 테이블을 생성합니다.

Item 테이블에는 id, code, artist, price 4개의 컬럼이 있습니다.

id는 기본 키로 설정하여 식별이 가능하게 하고 자동으로 숫자가 부여되도록 설정하겠습니다.

나머지 컬럼은 빈 값(null)이 없도록 not null 설정을 해줍니다.

그후 select문을 이용해서 위에서 생성한 테이블이 제대로 만들어졌는지 확인해 줍니다.

## create table################################################

# 1.모듈 import
import sqlite3

# 2.DB파일 열기
conn = sqlite3.connect('chinook.db')

# 3. cursor(커서) 생성
cursor = conn.cursor()
###############################################################

# 4. SQL 명령어 작성
CREATE_SQL = """
    CREATE TABLE IF NOT EXISTS Item(
        id integer primary key autoincrement,
        code text not null,
        artist text not null,
        price integer not null
    );"""


# 5. SQL 명령 실행
cursor.execute(CREATE_SQL)

# 잘 실행되었는지 Item Table 출력하여 확인
cursor.execute('SELECT * FROM sqlite_master WHERE type="table" AND name="Item";')
table_list = cursor.fetchall()
for i in table_list:
    for j in i:
        print(j)

# 6. DB 닫기
conn.close()

그럼 터미널 창에 각 컬럼에 설정한 내용이 각각 잘 지정되어 Item 테이블이 생성된 것을 확인할 수 있습니다.

 

SQL 명령어 - 테이블에 정보 입력하기(INSERT)

Item이라는 테이블이 생성 되었지만 아직 아무 내용이 없는 빈 테이블 입니다.

그래서 테이블에 들어갈 정보를 INSERT문을 사용하여 몇 가지 입력해 줍니다.

INSERT INTO 테이블이름(컬럼1, 컬럼2, 컬럼3...) VALUES(?,?,?);

이렇게 위의 SQL 명령을 입력하면 이후 명령어 실행시 간편하게 내용 입력이 가능합니다.

여러 개의 정보를 한꺼번에 입력하고 싶으면 executemany()를 사용하여 다수의 정보를 테이블에 입력할 수 있습니다.

그리고 INSERT, UPDATE, DELETE 등을 사용할 때 

commit()을 꼭 실행하여 DB에 반영이 될 수 있도록 해야 합니다.

################################################################
## insert
# insert sql 작성
INSERT_SQL = 'INSERT INTO Item(code,artist,price) VALUES (?,?,?);'
## 데이터 여러개 한번에 추가하기
data = (
    ('ABC111','마룬5', 35000),
    ('DFG222','아리아나그란데', 45000),
    ('HIJ333','NCT', 30000)
)
# insert로 값 추가

# 여러개 값 추가
cursor.executemany(INSERT_SQL,data)
# 한개의 값만 추가
#cursor.execute(INSERT_SQL, ('KLM444','아이유', 38000))
# 커밋(insertm update delete 는 commit을 꼭 해주어야 데이터베이스에 반영 된다.)
conn.commit()
# 데이터 확인
cursor.execute('SELECT * FROM Item;')
item_list = cursor.fetchall()
for i in item_list:
    print(i)

# 닫기
conn.close()

내용이 잘 들어갔음을 확인할 수 있습니다.

SQL 명령어 - 테이블에 있는 정보 가져오기(SELECT)

이제 테이블에 있는 정보를 SELECT를 사용하여 불러오도록 하겠습니다.

customer라는 고객 테이블의 모든 정보를 가져오려면

SELECT * FROM customers;

이렇게 입력을 합니다. 그 후 정보를 가져올 때 fetchall()을 사용하여 불러와 주고 

이 내용을 for문을 써서 한 행의 정보를 하나씩 출력하여 깔끔하게 볼 수 있도록 합니다.

여기서는 LIMIT 을 사용하여 customer 10명의 정보만 불러와 보겠습니다.

################################################################
##select

SELECT_SQL = 'SELECT * FROM customers LIMIT 10;' #Where Country = "Austria";'#특정 국적의 사람만 정보 가져오기

#select로 선택된 행들 가져와서 출력하기
cursor.execute(SELECT_SQL)
rows = cursor.fetchall()
for row in rows:
    print(row)

conn.close()

만약 특정 국적의 고객 정보만 불러오고 싶으면 아래와 같이 WHERE을 사용하여 SQL을 작성해 줍니다.

SELECT * FROM customers Where Country = "Austria";

 

SQL 명령어 - 기존 정보 업데이트하기(UPDATE)

이번에는 기존에 존재하는 정보가 바뀌었을 때 변경하는 방법을 알아보도록 하겠습니다.

예를 들어, 직원 테이블 employees를 확인해 봅시다.

8 명의 직원 중 Jane이라는 직원이 IT 매니져로 직책이 바뀌었다고 했을 때,

직원 테이블에서는 이 직원의 정보를 기존의 Sales Support Agent에서 IT MAnager로 업데이트 하여야 합니다.

이 때, UPDATE와 SET, WHERE을 사용하여 직원 정보를 업데이트 할 수 있습니다.

또한 여기서도 commit()을 꼭 실행하여 반영이 완료되도록 해줍니다.

################################################################
##update

UPDATE_SQL = 'UPDATE employees set Title = "IT Manager" WHERE FirstName="Jane";'
cursor.execute(UPDATE_SQL)

conn.commit()
# 확인
cursor.execute('SELECT * FROM employees WHERE FirstName="Jane";')
employee = cursor.fetchall()
print(employee)
# db닫기
conn.close()

 

SQL 명령어 - 테이블 정보 삭제하기(DELETE)

이번에는 테이블에 있는 내용을 삭제해 보겠습니다.

처음에 생성했던 Item에서 마룬5의 정보를 삭제한다고 할 때 

DELETE를 사용하여 삭제가 가능합니다.

commit()도 실행하여 반영이 될 수 있도록 해 줍니다.

###############################################################
##delete

DELETE_SQL = 'DELETE FROM Item WHERE artist="마룬5";'
cursor.execute(DELETE_SQL)

conn.commit()
# 확인
cursor.execute('SELECT * FROM Item;')
rows = cursor.fetchall()
for row in rows:
    print(row)

conn.close()

 

전체 코드

## create table################################################

# 1.모듈 import
import sqlite3

# 2.DB파일 열기
conn = sqlite3.connect('chinook.db')

# 3. cursor(커서) 생성
cursor = conn.cursor()
###############################################################

# 4. SQL 명령어 작성
CREATE_SQL = """
    CREATE TABLE IF NOT EXISTS Item(
        id integer primary key autoincrement,
        code text not null,
        artist text not null,
        price integer not null
    );"""


# 5. SQL 명령 실행
cursor.execute(CREATE_SQL)

# 잘 실행되었는지 Item Table 출력하여 확인
cursor.execute('SELECT * FROM sqlite_master WHERE type="table" AND name="Item";')
table_list = cursor.fetchall()
for i in table_list:
    for j in i:
        print(j)

# 6. DB 닫기
conn.close()

################################################################
## insert
# insert sql 작성
INSERT_SQL = 'INSERT INTO Item(code,artist,price) VALUES (?,?,?);'
## 데이터 여러개 한번에 추가하기
data = (
    ('ABC111','마룬5', 35000),
    ('DFG222','아리아나그란데', 45000),
    ('HIJ333','NCT', 30000)
)
# insert로 값 추가

# 여러개 값 추가
cursor.executemany(INSERT_SQL,data)
# 한개의 값만 추가
cursor.execute(INSERT_SQL, ('KLM444','아이유', 38000))
# 커밋(insertm update delete 는 commit을 꼭 해주어야 데이터베이스에 반영 된다.)
conn.commit()
# 데이터 확인
cursor.execute('SELECT * FROM Item;')
item_list = cursor.fetchall()
for i in item_list:
    print(i)

# 닫기
conn.close()
################################################################
##select

SELECT_SQL = 'SELECT * FROM customers;' #LIMIT 10; #Where Country = "Austria";'

#select로 선택된 행들 가져와서 출력하기
cursor.execute(SELECT_SQL)
rows = cursor.fetchall()
for row in rows:
    print(row)

conn.close()
################################################################
##update

UPDATE_SQL = 'UPDATE employees set Title = "IT Manager" WHERE FirstName="Jane";'
cursor.execute(UPDATE_SQL)

conn.commit()
# 확인
cursor.execute('SELECT * FROM employees WHERE FirstName="Jane";')
employee = cursor.fetchall()
print(employee)
# db닫기
conn.close()

###############################################################
##delete

DELETE_SQL = 'DELETE FROM Item WHERE artist="마룬5";'
cursor.execute(DELETE_SQL)

conn.commit()
# 확인
cursor.execute('SELECT * FROM Item;')
rows = cursor.fetchall()
for row in rows:
    print(row)

conn.close()

 

코드 파일

sqlite3_ex.py

0.00MB

참고 자료

https://www.sqlitetutorial.net/

SQLite Tutorial - An Easy Way to Master SQLite Fast

 

마무리

오늘 간단하게 파이썬으로 SQL작성하는 방법을 알아보았습니다 ㅎㅎ

항상 포스팅 잘 봐주셔서 감사합니다! 이번주도 좋은 한 주 되세요~