[GPDB, PostgreSQL] Python DataFrame을 Sqlalchemy engine을 이용해 DB Table에 직접 쓰기 : df.to_sql()

지난 포스팅에서는 Python pandas DataFrame을 csv 파일로 다운로드 한 후에 로컬에서  PostgreSQL, Greenplum DB에 Copy해서 넣는 방법 (https://rfriend.tistory.com/457) 을 소개하였습니다. 

 이번 포스팅에서는 Python pandas DataFrame을 csv 파일로 다운로드 하는 절차 없이, sqlalchemy engine과 to_sql() 함수를 이용하여 바로 직접 PostgreSQL, Greenplum DB에 쓰는 방법을 소개하겠습니다. 이렇게 하면 다운로드하는 절차가 필요없기 때문에 좀더 간편하고 workflow가 간소화되는 장점은 있는데요, csv로 내려서 copy 하는 것 대비 속도고 조금 더 느리다는 단점이 있습니다. 

먼저 예제로 사용할 간단한 DataFrame을 만들어보겠습니다. 

# make a sample DataFrame import pandas as pd score = pd.DataFrame({         'date': ['2019-07-28']*4,          'name': ['kim', 'lee', 'choi', 'park'],          'age': [19, 20, 19, 20],          'math_score': [91, 95, 92, 70],          'pass_yn': [True, True, True, False]},           columns=['date', 'name', 'age', 'math_score', 'pass_yn'])

이제 'score' 라는 이름의 pandas DataFrame을 Postgresql, Greenplum DB에 'score' 라는 Table 이름으로 public schema에 생성해서 써보겠습니다. 

이때 DB connection을 하기 위해 SQLAlchemy로 DB engine 을 생성해줘야 하는데요,
engine = sqlalchemy.create_engine("postgresql://user:password@host:port/database") 
의 순서대로 자신의 DB 설정값을 입력해주면 됩니다. (port 가 5432 디폴트 값이면 생략 가능)

import sqlalchemy from sqlalchemy import create_engine # engine = sqlalchemy.create_engine("postgresql://user:password@host:port/database") engine = create_engine("postgresql://postgres:postgres@localhost:5432/postgres") # set yours engine.execute("DROP TABLE IF EXISTS public.score;") # drop table if exists score.to_sql(name = 'score',               con = engine,               schema = 'public',               if_exists = 'fail', # {'fail', 'replace', 'append'), default 'fail'              index = True,               index_label = 'id',               chunksize = 2,               dtype = {                      'id': sqlalchemy.types.INTEGER(),                       'date': sqlalchemy.DateTime(),                       'name': sqlalchemy.types.VARCHAR(100),                       'age': sqlalchemy.types.INTEGER(),                       'math_score': sqlalchemy.types.Float(precision=3),                       'pass_yn': sqlalchemy.types.Boolean()                      })

'if_exists' 옵션에는 {'fail', 'replace', 'append'}의 3개가 존재하고, 디폴트는 'fail' 옵션입니다. 

• if_exists = 'fail' : 같은 이름의 Table이 존재할 경우 ValueError 가 남
• if_exists = 'replace': 같은 이름의 Table이 존재할 경우 기존 Table을 Drop하고 새로운 값을 Insert함
• if_exists = 'append': 같은 이름의 Table이 존재할 경우 기존 Table에 추가로 새로운 값을 Insert함

index = True 로 설정해주면 pandas DataFrame의 Index도 DB Table에 insert 해주며, index_label = 'xxx'로 index의 칼럼 이름을 부여해줄 수 있습니다. 

chunksize = xx 를 설정해주면 pandas DataFrame 데이터를 xx row 개수 만큼 DB table 에 insert를 해줍니다. 설정해주지 않으면 pandas DataFrame을 통째로 한꺼번에 insert를 합니다. 

dtype 은 pandas DataFrame의 각 변수별로 DB table에 넣어줄 Data Type을 사전형(Dictionary)으로 {'column': data_type} 형식으로 설정해줄 수 있습니다. 위의 예시에서 INTEGER, DateTime(), VARCHAR(), Float(), Boolean 데이터 형태 지정하는 것을 보여주었는데요, 대/소문자, 괄호() 여부를 위의 예시처럼 똑같이 사용해야 합니다. (괄호를 빼먹거나, 대/소문자가 틀리면 에러가 납니다)

참고로, to_sql() 에서 dtype 을 칼럼 별로 설정하지 않으면 전부 'text' 데이터 형태로 해서 DB table에 입력됩니다. 

확인차, DBeaver로 PostgreSQL에 score table을 조회해보겠습니다. Python pandas의 'score' DataFrame이 PostgreSQL의 score table로 데이터가 잘 들어갔네요! 

많은 도움이 되었기를 바랍니다.

이번 포스팅이 도움이 되었다면 아래의 '공감~'를 꾹 눌러주세요.