[postgresql] python으로 SHP 데이터 삽입하기(psycopg3 / sqlalchemy / geopandas)

필요한 라이브러리를 설치해주기

pip install--upgrade sqlalchemy 
pip install --upgrade geopandas
pip install --upgrade psycopg2 
pip install --upgrade GeoAlchemy2
pip install "psycopg[binary,pool]"

 

테스트 데이터는 주소정보누리집의 도로도형의 SHP 파일을 사용했다.

필요한 라이브러리를 불러온 뒤, 테이블을 생성해준다.

import psycopg # psycopg3으로 쿼리나 스키마 만드는 작업
import psycopg2 # sqlalchemy로 연결할 때 사용. sqlalchemy는 psycopg2를 사용함
import os
from dotenv import load_dotenv, set_key
import geopandas as gpd
from sqlalchemy import create_engine


create_table_query = """
CREATE TABLE IF NOT EXISTS road (
    ALWNC_DE VARCHAR(255),
    ALWNC_RESN VARCHAR(255),
    BSI_INT VARCHAR(255),
    ENG_RN VARCHAR(255),
    MVMN_DE DATE,
    MVMN_RESN VARCHAR(255),
    MVM_RES_CD VARCHAR(255),
    NTFC_DE VARCHAR(255),
    OPERT_DE VARCHAR(255),
    RBP_CN VARCHAR(255),
    RDS_DPN_SE VARCHAR(255),
    RDS_MAN_NO VARCHAR(255),
    REP_CN VARCHAR(255),
    RN VARCHAR(255),
    RN_CD VARCHAR(255),
    ROAD_BT VARCHAR(255),
    ROAD_LT VARCHAR(255),
    ROA_CLS_SE VARCHAR(255),
    SIG_CD VARCHAR(255),
    WDR_RD_CD VARCHAR(255),
    geometry GEOMETRY # 실제 shp은 geometry라는 속성이 명시적으로 없지만, postresql에 삽입하면서 생기므로 스키마 단계에서 생성해준다.
);
"""
# 데이터베이스 연결 및 쿼리 실행
try:
    with psycopg.connect("host = {} dbname = {} user = {} password = {}".format(
    os.getenv('PSQL_DATABASE_HOST'),
    os.getenv('PSQL_DATABASE'),
    os.getenv('PSQL_DATABASE_USERNAME'),
    os.getenv('PSQL_DATABASE_PASSWORD')
)) as conn:
        with conn.cursor() as cur:
            cur.execute(create_table_query)
        print("테이블이 성공적으로 생성되었습니다.")
except psycopg.Error as e:
    print(f"데이터베이스 오류 발생: {e}")
except Exception as e:
    print(f"예상치 못한 오류 발생: {e}")

 

생성한 스키마 쿼리는 데이터베이스 연결 뒤, cur.execute로 명령어를 실행시킨다.

 

 

• geopandas로 shp 파일 불러오기
  • 1. TL_SPRD_MANAGE.shp 데이터를 geopandas(gpd)의 데이터 프레임으로 불러온다
  • 2. create_engine은 sqlalchemy의 함수이며 해당 함수를 통해 데이터베이스 엔진과 연결할 수 있다. geopandas에서 지원하는 연결방법이 현재는 sqlalchemy만 지원한다.
  • 3. to_postgis는 geopandas 의 함수로, 1에서 만든 gpd 데이터프레임을 postgis로 변환하기 위해 사용하고, 연결할 데이터베이스(engine)을 지정한다. 이때, 데이터는 테이블 단위로 들어가기 때문에 if_exists가 'replace'이면 기존 데이터를 아예 대체하게 된다. 만약 축적되는 것을 원한다면 'append'로 바꿔주어야 한다.
  • 참고: https://geopandas.org/en/stable/docs/reference/api/geopandas.GeoDataFrame.to_postgis.html

test = gpd.read_file(r'TL_SPRD_MANAGE.shp', encoding='cp949')

engine = create_engine(f'postgresql+psycopg2://{os.getenv("PSQL_DATABASE_USERNAME")}:{os.getenv("PSQL_DATABASE_PASSWORD")}@{os.getenv("PSQL_DATABASE_HOST")}/{os.getenv("PSQL_DATABASE")}')

test.to_postgis('road', engine, if_exists='replace')

 

해당 코드를 실행 후, pdAdmin이라는 GUI 도구를 써서 조회한 결과이다.

잘 들어간 것을 확인할 수 있고, 도형정보는 geometry로 들어갔음을 볼 수 있다.