[DE] csv파일 데이터베이스에 인입하기 (feat. psycopg)

니나노래방 2024. 1. 22. 03:27

csv 파일을 받아서 데이터베이스에 인입하는 연습을 해보았습니다.

멋지게 데이터베이스에 인입하고 SQL 과제를 하고 싶었는데.. 세상에 왜 안되는 것이었을까요? 🤣🤣🤣

파이썬을 이용하면 DB에 접근하고, 데이터베이스에 csv파일을 넣을 수 있습니다! DataGrip에서는 사실 더 쉽게 파일 불러오기가 가능합니다. 하지만 파이썬을 이용했을 때는 원하는 컬럼이나 조건을 설정해서 DB에 넣을 수 있다는 장점이 있지요! 회사에서 사용하는 DB IDE가 다를 수 있다는 점도 고려할 필요가 있었습니다.

나중에 한꺼번에 정리하려고 했으나, 데이터 넣는 과정에서 실수한 부분을 복기하기 위해서 글을 작성해 놓습니다!

1. 파일 불러오기

파일은 US E-commerce records 2020 입니다 (캐글 링크)

python에서 파일 스트림을 이용해서 파일을 불러옵니다. 여기서 1건의 오류가 발생했습니다.

import csv
with open("US  E-commerce records 2020.csv", "r", encoding='latin-1') as f:
    csv_file = csv.reader(f, delimiter=',')
    header = False

    data = []

    for row in csv_file:
        if not header:
            header = True
            continue
        data.append(row)

print(data)

csv 라이브러리는 기본 내장되어 있습니다.
파일 경로를 불러옵니다. 읽기 전용(read only) 상태로 가져옵니다. UTF-8 에서 자꾸 오류가 납니다. GPT가 인코더를 latin-1로 써보라고 권해주었습니다. 그대로 따라봅니다.
csv 라이브러리의 reader 함수를 가져와서 파일을 가져오고, ','로 분리되어 있으니 잘 판별해달라고 합니다.
csv 파일에 header가 들어있는데 이를 제거하는 작업을 해줬습니다. (첫 행은 떼어내고 나머지 row들만 data 리스트에 넣어달라고 부탁합니다)
data 리스트에 차곡차곡 데이터들이 쌓였습니다.
data 리스트를 프린트하니 잘 불러와졌습니다.

첫 번째 오류 포인트 : 문자에 따라 인코딩 파라미터를 넣어야 할 수 있습니다.

2. psycopg 임포트하기

psycopg를 가지고 오면 DB를 연결할 수 있고, 여기에서 원하는 DB 처리를 할 수 있습니다.

PyCharmCE를 이용하고 있었는데, 'requirements.txt' 에서 pyscopg를 설치하려 하였으나 원인 모를 문제가 발생했습니다. 일단 PyCharm 설정에서 패키지를 설치할 수 있으므로 이걸로 대체했습니다.

두 번째 오류 포인트 : 패키지가 정확히 설치되었는지 확인해야 합니다.

3. psycopg 에 DB 연결하기

psycopg에서 이제 DB를 연결해야 합니다.

이때 with문을 사용할 수 있습니다. with문 안에서 파일이나 데이터 베이스 연결 등을 깔끔하게 처리할 수 있습니다. 추후에 한번 더 정리해 보겠습니다.

with psycopg.connect("dbname=디비명 host=호스트명 user=유저명 password=패스워드 port=포트주소") as conn:
    with conn.cursor() as cur:

위의 with문에서는 psycopg의 connect 메서드를 이용해 connection이라는 객체를 불러옵니다. 이 connection 객체를 통해 DB를 원격으로 연결하는 과정을 보여줍니다. conn이라는 변수 안에 DB 연결 정보가 들어있다고 보면 됩니다.
아래 with는 이제 원하는 쿼리를 실행하기 위한 with문입니다.
여기서 오류가 발생했는데요. 위의 DB를 연결할 때 connect 정보를 확인하지 않았습니다.
다른 포트주소를 설정했는데, 이걸 생각하지 않고 포트 주소를 입력하지 않아서 연결 오류가 발생했습니다.

세 번째 오류 포인트 : 연결 정보는 꼭 한번 확인해 봅니다.

4. DB에 테이블 만들기

이제 DB가 연결되었습니다. 이제 쿼리 작업이 가능합니다.

테이블을 만들고, 그 안에 데이터를 집어 넣고, 조회하는 작업들을 할 수 있다는 것이지요!

일단 테이블을 만들어 봅니다.

with psycopg.connect(생략) as conn:
    with conn.cursor() as cur:
        cur.execute("""
            create table records_2020 (
                order_date varchar(20) not null,
                row_id int not null,
				-- 중략 -- 
                product_name varchar(100) not null,
                sales float not null,
                profit float not null
            );
        """)
        conn.commit()

cursor()라는 객체는 연결된 DB 안에서 다양한 작업들을 수행해 줍니다.
하나의 예로 cursor를 이용하면 SQL 쿼리를 실행할 수 있습니다. DDL(Data Definition Language) 또는 DML(Data Manipulation Language) 관련 명령을 처리해주기도 하고, SELECT문으로 추출한 SQL 결과물을 가져와주기도 합니다.
product_name이 설마 100자가 넘어갈까 했지만 100자가 넘어갔습니다..

이럴 때는 ALTER TABLE 명령어를 이용해서 테이블 컬럼 속성을 변경해줍니다.

(MySQL의 경우 "ALTER TALBE 테이블명 MODIFY 컬럼명 컬럼 속성" 으로 속성을 변경할 수 있는데요.

PostgreSQL은 "ALTER TABLE 테이블명 ALTER COLUMN 컬럼명 컬럼 속성" 을 사용해야 합니다. 차이가 있었네요!)

with psycopg.connect("생략") as conn:
    with conn.cursor() as cur:
        cur.execute("""
            ALTER TABLE records_2020 -- 테이블명 
            ALTER COLUMN product_name TYPE VARCHAR(300); -- 컬럼 속성 바꿔주기 
        """)
    conn.commit()

오류 포인트 : 문자열 길이를 잘 확인합시다.

5. 테이블에 데이터 넣기

이제 만든 'records_2020' 테이블에 데이터를 넣어줍니다.

with psycopg.connect("생략") as conn:
    with conn.cursor() as cur:
        cur.executemany("""
            INSERT INTO records_2020
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
        """, data)
    conn.commit()

이때 data 리스트를 한꺼번에 넣어줄 수 있는 방법으로 executemany를 사용했습니다
executemany는 성능 이슈가 있어서 psycopg2의 execute_batch를 사용하는 등 다른 방법을 고려하는 것이 좋습니다. 일단 여기선 이걸로 써봤습니다.
%s 처리를 해주는 이유는 보안상의 이유 때문이 가장 큽니다 (인젝션 공격 방지용!)
대신 %s는 갯수를 잘 세줘야 합니다. 오류도 거기서 발생...

print(len(data[1])) -- 사람은 머리를 써야합니다.. 저걸 일일이 세다니요

오류 포인트 : 컬럼 갯수를 잘 세어봅니다.

6. 드디어 DB에 데이터를 넣었습니다 🥹

그래도 엔딩만 행복하면 되었지요

7. 파이썬에서 출력하기

with psycopg.connect("생략") as conn:
    with conn.cursor() as cur:
        cur.execute("""
            SELECT customer_id
                 , COUNT(DISTINCT order_id) as cus_order_cnt
            FROM records_2020
            GROUP BY customer_id
            ORDER BY cus_order_cnt DESC;
        """)

        results = cur.fetchall()

print(results)

-- results : 한 줄에 다 나옴
[('EP-13915', 8), ('MH-18115', 8), ... , ('AC-10660', 1), ('DL-13330', 1)]

출력시에는 다시 cursor() 객체를 불러와야 합니다. SELECT는 조회만 할 수 있습니다. 따라서 커밋을 했을 때 바로 DB에 반영되는 일이 없으므로, 파이썬에서 출력 결과를 볼 수 있게 해야 합니다.
이때 fetch 매서드를 활용해서 결과물을 불러오는데요. fetchall, fetchone, fetchmany가 있습니다. 각각의 차이는 한 줄에 몇 개의 데이터를 불러오냐입니다.
- fetchall은 한 줄에 다 불러온다
- fetchone은 한 줄에 한 개씩 불러온다
- fetchmany는 한 줄에 여러 개씩 불러온다
불러오는 데이터가 적을 때는 fetchall로 간편하게 해결할 수 있습니다. 하지만 출력해야 하는 데이터가 많은 경우에는 한번에 불러올 수 있는 데이터를 끊어 오는 것이 좋습니다.

with psycopg.connect("생략") as conn:
    with conn.cursor() as cur:
        cur.execute("""
            SELECT customer_id
                 , COUNT(DISTINCt order_id) as cus_order_cnt
            FROM records_2020
            GROUP BY customer_id
            ORDER BY cus_order_cnt DESC;
        """)

        results = cur.fetchone()

        while results: -- results에 row가 남지 않을 때까지 반복해서 돌면서 결과를 출력
            print(results)
            results = cur.fetchone()
            
            
--- results : 한 줄에 하나씩 
('EP-13915', 8)
('MH-18115', 8)
('SJ-20125', 7)
('CV-12805', 7)
('JL-15835', 6)
('ML-17755', 6)
('SH-20395', 6)
('Dp-13240', 6)
('DJ-13510', 6)
('DO-13645', 6)
('CS-12355', 6)
...

results = cur.fetchmany(3)

while results:
    print(results)
    results = cur.fetchmany(3)


-- results : 한 줄에 3개씩 끊어옴
[('EP-13915', 8), ('MH-18115', 8), ('SJ-20125', 7)]
[('CV-12805', 7), ('JL-15835', 6), ('ML-17755', 6)]
[('SH-20395', 6), ('Dp-13240', 6), ('DJ-13510', 6)]
[('DO-13645', 6), ('CS-12355', 6), ('TD-20995', 6)]
[('FH-14275', 6), ('SS-20140', 5), ('AH-10075', 5)]
...

마치며

오류를 많이 발생 시킨 것은 아쉽지만, 이렇게 하나씩 해보면서 오류를 해결해나가는 과정도 정말 재미있었습니다. 또 새로운 것을 알아가거나 이미 배운 것도 단단히 정리할 수 있어 의미 있었습니다. 귀찮은 질문을 하나하나 받아준 챗집사에게 감사드리며 글을 마치겠습니다.

저작자표시 비영리 변경금지 (새창열림)