[이재우] CSV to SQLite Python

# CSV 파일을 데이터 베이스 저장하기
데이터를 다루다 보면, 다양한 `xxx.csv`파일을 다루게 될 것이다. Raw 데이터를 가지고, 다양한 데이터 전처리 과정을 통해서 우리는 필요한 데이터를 구축할 것이다. 우리가 전처리한 데이터는 어디에 저장을 해야 할까? 물론 `Pandas`에서는 `pandas.to_csv`메서드를 통해서 csv파일로 저장을 도와주지만, 데이터가 하나가 아닌 여러 가지 데이터가 존재하고 또한, 데이터 사이의 관계가 존재한다면 매번 수 십개 `xxx.csv` 파일을 `pd.read_csv`를 통해서 데이터를 불러오는 방법은 너무 비효율적이다. 그래서, `SQLite`라는 데이터 베이스에 저장하여 관리를 하는 방법을 소개하고자 한다. SQL은 Structured Query Language의 줄임말로 수 십개 데이터를 하나의 데이터베이스에 저장을 한다. SQLite는 SQL에서 조금 간단한 버전이라고 볼 수 있다. SQL은 말 그대로 구조화된 데이터를 다루기 때문에, 데이터 정합성에 아주 탁월하다. 그리고 복잡한 Query문을 통해서 SQL 만으로도 데이터 분석이 가능하다. 하지만 너무 구조화되어 있으므로 구조의 변화가 있거나 데이터 스케일링에서는 비효율적이다. 그런데도, 현재 다양한 회사에서는 SQL을 통해 데이터를 저장하고 관리를 한다. 그럼 CSV파일을 SQLite 데이터베이스에 저장을 해보자.

# CSV to SQlite
## 1. 라이브러리
그럼 파이썬 파일을 새로 만들어 열고, 본격적으로 시작해보자.
```py
# 라이브러리 Import
import sqlite3
import csv
```
파이썬에서 SQLite와 연결하기 위해서는 `sqlite3`가 필요하다. 파이썬에서는 기본으로 탑재되어 있기 때문에, 따로 설치는 필요하지않아도 된다.
두번째 라이브러리는 csv파일을 읽기 위한 라이브러리 이다. 그럼 이제 우리가 데이터 베이스에 저장할 데이터 파일을 처리해보자.

## 2. CSV 파일
파이썬에서 `xxx.csv`파일을 읽어오기 위해서는 파일 경로를 지정해주어야 한다. 파일 경로를 지정해주는 방법은 다음과 같다.
1. 상대경로
현재 디렉토리를 기준으로 상대적 경로를 활용하는 방법.
>`os.path.dirname(__file__)` : 파이썬 파일의 현재 디렉토리
2. 절대경로
전체 파일경로를 활용하는 방법.
> `/Users/xxxx/Desktop/Blog/xxxx.csv`
3. 같은 디렉토리에 `xxx.csv`파일을 위치시키는 방법.

가장 간단한 방법인 **3**을 활용할 것이다. 이유는 같은 디렉토리에 위치하게 되면 경로를 따로 정해주지 않아도 되기 때문에 간편하다. 바탕화면에 하나의 폴더를 만들고, 현재 작성하고 있는 파이썬 파일과 불러올 csv 파일을 옮겨놓자.

> 위의 사진과 같이, `forestfires.csv`와 `csv_to_sqlite.py`파일이 존재함을 알 수 있다.
## 3. SQLite 연결
`prac.sqlite`라는 데이터 베이스를 생성하고, 파이썬 코드를 통해서 연결해보자.
```py
# SQL connect
connection = sqlite3.connect('pract.sqlite')
```
> `pract.sqlite`라는 데이터 베이스가 없으면, 같은 위치에 새로 생성한다.


지금까지의 코드를 파일로 실행을 하게 되면, 다음과 같이 `prac.sqlite` 데이터 베이스가 폴더에 생성됨을 알 수 있다. 따라서 지금까지 새로운 데이터 베이스를 생성하였고, csv 파일을 읽을 준비가 되었다.

## 4. CSV 파일 불러와서 데이터 베이스에 저장하기
이제 파이썬 코드를 통해서 CSV 파일을 읽어오고, 읽어온 데이터를 데이터 베이스에 저장을 해보자.
### 4.1. CSV 파일 읽기
```py
# csv 파일 읽기
with open('forestfires.csv', 'r') as csv_file:
reader = csv.reader(csv_file)
header = next(reader)
data = reader
```
* `with open('forestfires.csv', 'r') as csv_file:` : `forestfires.csv`파일을 `'r'` 읽기모드로 열고, 이를 csv_file로 부른다.
* `reader = csv.reader(csv_file)` : 읽어온 csv_file을 `csv.reader` 메서드를 통해서 내용 모두를 가져와 `reader`에 저장한다.
* `columns = next(reader)` :
`csv.reader`를 통해서 가져온 데이터는 row 단위로 저장 된 iterator 입니다. 쉽게 말해서 csv 파일의 한 줄의 데이터를 연속으로 담고 있습니다. 여기서 `next(reader)`의 의미는 첫번째 row를 넘어가겠다는 말이 됩니다. 그러면서 첫번째 row를 `header`로 저장합니다. 아래의 csv 파일을 보게되면 첫번째 행은 **column name**을 의미하기 때문에 `header`로 저장합니다.
* `data = reader` : 첫번째 줄인 `header`가 없는 나머지 모든 정보를 `data`라는 변수에 저장하겠다는 의미가 됩니다.

csv파일을 읽은 결과를 같이 한 번 보기전에, `forestfires.csv`는 아래와 같은 데이터를 담고 있다.

> 첫번째 열은 column name을 담고, 그 다음부터 데이터가 한 줄씩 나와있다.

다음으로는, 우리가 파이썬 코드를 통해서 읽어온 데이터와 실제 `forestfires.csv`파일과 비교를 해보자. 먼저 `header`의 정보가 잘 들어가있는지 확인해보자.
```py
# Header check
print(header)
```
> `forestfires.csv` 파일과 동일하다.

다음으로는, 처음부터 5번째까지 열의 데이터를 `print`하여, 원본 파일과 비교해보자.

```py
# Top 5 Data Print
with open ('forestfires.csv', 'r') as csv_file:
reader = csv.reader(csv_file)
header = next(reader)
data = reader
for idx, row in enumerate(data):
if idx == 5:
break
else:
print(row)
```
> 원본 csv 파일과 동일하며, 데이터들이 문자열의 리스트로 저장되어 있음을 알 수 있습니다.

### 4.2 SQL 쿼리 작업
파이썬에서는 파이썬 코드를 통해 소통하듯이, SQL과 소통하기 위해서는 Query를 작성할 수 있어야 한다. 먼저, 파이썬에서 Query를 실행하기 위해서 이 두가지는 꼭 명심해야한다.

- `prac.sqlite`는 아무 데이터(테이블)이 존재하지 않는 빈 데이터 베이스이다. 그래서 우리는 `prac.sqlite`에 데이터를 넣기 위해서는 테이블을 추가해주고, 데이터를 저장해야한다. 따라서 데이터 베이스는 데이터의 집합체의 느낌이고, table이 데이터인 느낌이다.

- Query를 실행하는 방법은 다음과 같다.
1. `cursor = connection.cursor()` : 실행하기전 커서를 생성한다.
2. `query = " xxx "` : 실행할 쿼리를 문자열로 저장한다.
3. `cursor.excute(query)` : 생선된 커서가 쿼리를 실행하도록 한다.
4. `connection.commit()` : 쿼리 실행 결과를 연결된 데이터베이스에 저장한다.

#### 4.2.1 CREATE TABLE
테이블은 하나의 데이터를 의미한다, 따라서 우리가 `forestfires.csv`를 저장할 새로운 테이블을 만들어야 한다.
```py
# SQL 연결
connection = sqlite3.connect('pract.sqlite')

# create passengers table
cursor = connection.cursor()
query = """
CREATE TABLE IF NOT EXISTS forestfires
(
X INTEGER,
Y INTEGER,
month VARCHAR(5),
day VARCHAR(10),
FFMC FLOAT,
DMC FLOAT,
DC FLOAT,
ISI FLOAT,
temp FLOAT,
RH INTEGER,
wind FLOAT,
rain FLOAT,
area FLOAT
);
"""
cursor.execute(query)
connection.commit()
```
> 앞에서 확인한 Header를 참조하여 다음과 같이 구성하였다. 예를들면, X는 INT 타입의 데이터를, month는 문자형 데이터를, temp는 FLOAT형 데이터 타입을 정해주었다.

> Tableplus를 사용하여, 만들어진 데이터 베이스를 연결하고 그 결과를 확인해보았다.

CSV파일의 데이터를 저장할 테이블을 만들었다. 그럼, 이 테이블에 `forestfires.csv`파일을 저장해보자.

#### 4.2.2 DATA
지금까지 우리가 확인한 결과는 다음과 같다. `data`에는 문자열의 리스트로 값이 담겨져 있다. 총 13개의 열로 구성되어 있다.

```py
# CSV file read
with open ('forestfires.csv', 'r') as csv_file:
reader = csv.reader(csv_file)
header = next(reader)
data = reader
cursor = connection.cursor()
query = 'INSERT INTO forestfires({0}) VALUES ({1})'.format(','.join(header), ','.join('?' * len(header)))
for data in data:
cursor.execute(query, data)
connection.commit()
```
전반적인 과정은 위에서 설명한 순서 그대로이다. 더 중요한 부분은 쿼리부분이다. 그러기 위해서는 `format` 메서드를 이해해야 한다.


위의 그림을 보면, `{0}`은 `','.join(header)`의 값이 들어가고, `{1}`은 `','.join('?' * len(header))`가 들어간다. `','.join(header)` : `header`의 element들을 하나의 스트링으로 `,`을 통해서 구분하겠다. 말이 어려우니 예를 통해 확인해보자.

```py
print(header)
print(','.join(header))
print('-'.join(header))
```
```
['X', 'Y', 'month', 'day', 'FFMC', 'DMC', 'DC', 'ISI', 'temp', 'RH', 'wind', 'rain', 'area']
X,Y,month,day,FFMC,DMC,DC,ISI,temp,RH,wind,rain,area
X-Y-month-day-FFMC-DMC-DC-ISI-temp-RH-wind-rain-area
```
위의 결과를 적용 하면 `query`는 다음과 같이 되게 된다.
* `INSERT INTO forestfires (X,Y,month,day,FFMC,DMC,DC,ISI,temp,RH,wind,rain,area)`

다음으로, ','.join('?' * len(header)) : `'?'`를 header의 갯수만큼 만들어서 하나의 문자열로 `,`를 통해서 만들것이다.
결과를 같이 보면 다음과 같다. header에 갯수가 13개이기 때문에 13개의 물음표로 만들어졌다.
```py
print(','.join('?' * len(header)))
```
```
?,?,?,?,?,?,?,?,?,?,?,?,?
```
최종적으로, 두가지의 결과를 합하면 다음과 같다.
* `'INSERT INTO forestfires({0}) VALUES ({1})'.format(','.join(header), ','.join('?' * len(header)))`
* `INSERT INTO forestfires (X,Y,month,day,FFMC,DMC,DC,ISI,temp,RH,wind,rain,area) VALUSE (?,?,?,?,?,?,?,?,?,?,?,?,?)`
이 두개의 문자열이 같은 의미가 되는 것이다.

그럼 여기서 물음표의 의미는 무엇이냐면, 우리가 이제 `data`를 넣을건데 총 13개의 값을 넣겠다는 뜻이 되고, 첫번째 물음표는 column 이름이 X인 값을, 두번째 물음표는 column 이름이 Y인 값을 넣게 되는것이라고 생각 할 수 있고, 또는 총 13가지의 값들을 넣을것임을 말한다.

그리고 이 과정을 모든 행의 정보를 각각 실행 시켜주면, 최종적으로 모든 `forestfires.csv`의 정보를 담게 된다.
```py
for data in data:
cursor.execute(query, data)
connection.commit()
```
>`data`안에 있는 모든 정보들을 넣을 수 있게 되는것이다.

좀 더 이해를 돕기 위해 한 개를 예시를 들어보겠습니다. `data`의 첫번째 열만 있다고 가정을 하면,

```py
row = ['7', '5', 'mar', 'fri', '86.2', '26.2', '94.3', '5.1', '8.2', '51', '6.7', '0', '0']
cursor = connection.cursor()
query = 'INSERT INTO forestfires({0}) VALUES ({1})'.format(','.join(header), ','.join('?' * len(header)))
cursor.execute(query, row)
connection.commit()
```

``` py
cursor = connection.cursor()
cursor.execute("""
INSERT INTO forestfires (X,Y,month,day,FFMC,DMC,DC,ISI,temp,RH,wind,rain,area)
VALUSE ('7', '5', 'mar', 'fri', '86.2', '26.2', '94.3', '5.1', '8.2', '51', '6.7', '0', '0')
);
connection.commit()
"""
```
위의 두 코드가 같은 것을 의미하게 된다. 번거롭게 모든 열을 확인하여 값을 하나하나 칠 필요없이 한 번에 해결해주는 아주 편리한 방법이다.

### 5 결과
지금 까지 작성한 코드를 하나로 합쳐보자.
```py
import sqlite3
import csv

# make connection to sqlite
connection = sqlite3.connect('pract.sqlite')

# create passengers table
cursor = connection.cursor()
query = """
CREATE TABLE IF NOT EXISTS forestfires
(
X INTEGER,
Y INTEGER,
month VARCHAR(5),
day VARCHAR(10),
FFMC FLOAT,
DMC FLOAT,
DC FLOAT,
ISI FLOAT,
temp FLOAT,
RH INTEGER,
wind FLOAT,
rain FLOAT,
area FLOAT
);
"""
cursor.execute(query)
connection.commit()

# CSV file read
with open ('forestfires.csv', 'r') as csv_file:
reader = csv.reader(csv_file)
header = next(reader)
data = reader
query = 'INSERT INTO MyTable({0}) VALUES ({1})'.format(','.join(header), ','.join('?' * len(header)))
cursor = connection.cursor()
for data in data:
cursor.execute(query, data)
connection.commit()

```
`prac.sqlite`라는 데이터 베이스를 생성하고, `forestfires`라는 테이블을 데이터 베이스 안에 생성하고, `forestfires.csv`파일을 읽어, `forestfires` 테이블에 저장을 하였다.

> 우리가 예상한 결과를 확인할 수 있다.

우리가 원하는 최종 목표인 `forestfires.csv` 파일을, `prac.sqlite`라는 데이터 베이스 안의 `forestfires`라는 테이블에 저장을 해보았다. 내가 쓴 query가 정답은 아니다. 다양한 방법이 존재하기 때문에, 이렇게 하는 사람도 있구나 정도로 이해해주면 좋겠다. 이 방법을 이해하고, 다른 방법들을 보게 된다면 훨씬 쉽게 이해가 가능할것이다. 그럼 이만!