[SQL] CSV 파일 SQLite 이용해 DB파일로 바꾸기

개인프로젝트로 키오스크 만드는 과제를 하던 중, 엑셀로 정리한 데이터를 db로 바꾸는 방법을 찾아보았다.

Python에서 CSV 파일을 데이터베이스에 저장하는 방법은 여러가지가 있지만, 

SQLite 데이터 베이스를 사용하는 예시를 들며 적용해 보겠다.

 

1. SQLite3 라이브러리 설치

sqlite3 라이브러리는 내장되어 있어 설치할 필요가 없다.

import sqlite3

코드 상단에 임포트만 해주면 된다.

 

2. SQLite 데이터베이스 파일을 만든다.

예를 들어, "data.db"라는 이름의 데이터베이스 파일을 생성한다.

 

sqlite3 exe파일을 실행시킨다.

이 exe파일은 복사 - 붙여넣기 해서 이 코드를 실행시킬 프로젝트 파일로 가져와도 상관 없다.

db 생성:

.open {db이름}.db

data.db 파일 생성:

.open data.db

 

3. CSV 파일을 데이터베이스에 저장하는 Python 코드를 작성한다.

아래는 코드 전문이다.

import csv
import sqlite3

# 데이터베이스에 연결
con = sqlite3.connect('data.db')
cursor = con.cursor()

# 테이블 생성
cursor.execute('''
                CREATE TABLE menu(
                id int,
                category text, 
                category_num int,
                degree text,
                name text,
                price int,
                info text
                )''')

#csv 파일 읽기 및 데이터베이스에 삽입
with open('data.csv', 'r') as file:
    csv_data = csv.reader(file)
    next(csv_data) # 첫번째 행은 헤더이므로 건너뜀
    for row in csv_data:
        cursor.execute('INSERT INTO menu VALUES (?, ?, ?, ?, ?, ?, ?)', row)

#변경사항 저장 및 연결 종료
con.commit()
con.close()

위 코드에서는 data.csv 라는 이름의 CSV 파일을 읽고,

menu 라는 이름의 테이블에 데이터를 삽입했다.

CSV 파일의 각 행은 INSERT INO 문을 사용해서 데이터베이스에 삽입된다.

CSV 파일과 테이블의 열 구성이 일치해야 한다.

 

발견한 오류1

line 24, in <module>
    cursor.execute('INSERT INTO menu VALUES (?, ?, ?, ?, ?, ?)', row)
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 6, and there are 29 supplied.

주어진 오류 메시지는 "바인딩 수가 잘못되었다"는 오류이다. 즉 쿼리에서 사용되는 바인딩 변수의 수와 제공된 데이터의 수가 일치하지 않음을 나타낸다.

다시 말해 쿼리에 6개의 바인딩 변수가 있지만 제공된 데이터에는 29개의 열이 포함되어 있다는 뜻이다.

구글 스프레드시트에서 작성해서 가져오다보니 빈 열이 들어가 있었나 보다. 빈 열들을 삭제해 주고 해결함

 

발견한 오류2

 

line 9, in <module>
    cursor.execute('''
sqlite3.OperationalError: table menu already exists

이 오류 메시지는 "테이블 menu가 이미 존재한다"는 것을 나타낸다.

즉, 동일한 이름의 테이블을 다시 생성하려고 하여 오류가 발생한 것이었다.

위에서 오류를 만들어낸 코드를 작성했을 때 테이블만 생성되고 값은 들어있지 않은 빈 테이블 menu가 생성된 것이다.

 

메뉴 테이블을 삭제하고 해결완

 

발견한 오류 3

line 9, in <module>
    cursor.execute('''
sqlite3.OperationalError: near "index": syntax error

열 이름으로 index라는 열이름을 작성했었는데,

"index"는 SQLite에서 예약어로 사용되기 때문에 테이블 열의 이름으로 직접 사용할 수 없었다.

예약어를 피하기 위해 열 이름을 다른 것으로 변경해야 했다.

 

index를 id로 변경하고 해결했다.

 

 

---

코드 실행 후 sqlite3에서 확인

데이터가 잘 실행된다.

 

 

사용한 데이터 출처: 메가커피 공식 홈페이지

data.csv

0.02MB

쿼리 참고 사이트: https://stackoverflow.com/questions/2887878/importing-a-csv-file-into-a-sqlite3-database-table-using-python

Importing a CSV file into a sqlite3 database table using Python

이 방법 외에도 수만가지 방법이 있다. 스택오버플로우 방법을 참고하시길

데이터 변형해주는 사이트: https://www.rebasedata.com/

Database conversion - RebaseData

하지만 이 사이트는 한글지원을 안 하는듯.. 데이터에 한글이 있으면 변환을 안해준다.