[postgreSQL] 한글 TXT 또는 CSV 데이터 import하기

Ubuntu 20.04의 터미널 환경에서 직접 실행하는 경우

DB서버 내 경로를 인식할 수 있음

postgres@ubuntu:~$ export PGCLIENTENCODING='uhc'

postgres@ubuntu:~$ psql test_db

test_db=> set client_encoding='UHC'; #일회용 세션
# 우분투 서버 내의 파일경로 입력
test_db=> \copy test FROM '/home/data/test.txt' CSV DELIMITER '|'; # 헤더 없는 경우, 헤더 있을 시에는 `HEADER` 추가 
>> COPY 193765 # 결과

 

set client_encoding 영구 설정

postgreSQL의 configration 수정해야함

우분투에서 apt로 설치한 경우, /etc/postgresql/버전/main에 위치한 postgresql.conf 수정

한글 인코딩을 위해 UHC로 설정함

client_encoding='UHC'

 

로컬에서 파이썬(python)으로 업로드할 경우

DB서버 내 경로 인식하지 못함(COPY, \copy 명령어 둘 다 에러남)

로컬 경로에 있는 데이터를 업로드

 

 

# 서버 연결
import psycopg2 #psycog2 패키지 임포트

connection = psycopg2.connect(
    host='ip주소',
    port = 포트번호,
    database = 'DB명',
    user = 'postgres아이디',
    password = '비밀번호')
cursor = connection.cursor()

# 각 테이블의 스키마를 정의하는 SQL문 작성 (chatGPT 활용하면 편함)
table_schema ='''
CREATE TABLE test (
    a varchar(5),
    b varchar(10),
    d varchar(5),
    e varchar(5),
    f varchar(12),
    PRIMARY KEY (a, b)
);
'''

# 테이블 스키마 생성
try:
    cursor.execute(table_schema)
    connection.commit()
except Exception as e:
    print('Error:', e)
    connection.rollback()

import time

# 파일이 여러개라면 경로와 테이블 이름 설정하는 부분만 for문으로 묶어서 실행하면 됨
file_path = r'C:\data\test.txt'
table_name = '테이블명'

try:
    start_time = time.time()  # 코드 블록 시작 시간 기록
    
    with open(file_path, 'r', encoding='cp949') as f: #한글 인코딩
        # 파일 포인터의 현재 위치 저장
        original_position = f.tell()
        
        cursor.copy_from(f, table_name, sep='|')
        
        # 파일 포인터를 원래 위치로 되돌림
        f.seek(original_position)
        
        # 파일을 읽고 행 수 계산
        line_count = sum(1 for _ in f)

        #테이블의 총 행수를 확인
        cursor.execute('SELECT COUNT(*) FROM basic_number')
        table_row_count = cursor.fetchone()[0]
    end_time = time.time()  # 코드 블록 종료 시간 기록
    
    # 실행 시간을 소수점 2자리까지 출력
    elapsed_time = round(end_time - start_time, 2)
    print(f'파일경로: {file_path} 파일 행 수: {line_count}, 테이블 행 수: {table_row_count}  실행 시간: {elapsed_time} 초', )

    connection.commit()
except Exception as e:
    print('Error:', e)
    connection.rollback()

 

 

참고) 

PostgreSQL DB 데이터 export & import

[postgresql] ERROR: invalid byte sequence for encoding "UTF8": 0xbc

[PostgreSQL] COPY 사용 불가 (SQL Error 42501)

[psycopg2] Loading and Extracting Data with Tables

https://goguri.tistory.com/391