[Python] Python과 PostgreSQL 연동

 

 이번 글에서는 Python과 PostgreSQL을 연동하여 DB에서 테이블을 불러오고 데이터 프레임 형태로 바꾸는 과정을 살펴보고자 합니다. 우선 terminal이나 conda를 이용하여 'psycopg2' 패키지를 설치해줍니다. 이후 DB 정보를 입력하고, 쿼리를 통해 테이블을 가져온 후 데이터 프레임으로 바꾸는 일반적인 코드는 다음과 같습니다.

 

>>> import psycopg2
>>> import pandas as pd
>>> 
>>> pgdb = psycopg2.connect(
>>>     host={'localhost' or ip address},
>>>     dbname={DB name},
>>>     user={User name},
>>>     password={Password},
>>>     port={Port number}
>>> )
>>> 
>>> cursor = pgdb.cursor()
>>> 
>>> cursor.execute("select * from test.table")
>>> data = cursor.fetchall()
>>> df = pd.DataFrame(data)
>>> df.head()
    0   1
0   A   a
1   B   b
2   C   c
3   D   d
4   E   e

 

 이러한 과정을 통해 테이블을 불러올 때 값들은 온전하지만, DB에 있는 테이블의 컬럼명들이 누락되어 0, 1과 같은 숫자로 나타나는 것을 확인할 수 있습니다. 이럴 경우 추가적으로 컬럼명을 입력해주는 과정을 진행해주어야 하기 때문에 번거로울 수 있습니다.

 이러한 문제는 Pandas의 read_sql 함수를 활용하면 해결할 수 있습니다. 다음의 코드와 결과를 보겠습니다. 코드가 더 간결해졌을 뿐만 아니라, 테이블의 컬럼명까지 제대로 불러온 것을 확인할 수 있습니다.

 

>>> import psycopg2
>>> import pandas as pd
>>> 
>>> pgdb = psycopg2.connect(
>>>     host={'localhost' or ip address},
>>>     dbname={DB name},
>>>     user={User name},
>>>     password={Password},
>>>     port={Port number}
>>> )
>>> 
>>> df = pd.read_sql("select * from test.table", pgdb)
>>> df.head()
   first  second
0      A       a
1      B       b
2      C       c
3      D       d
4      E       e

 

 이 코드를 local에서 실행했을 때는 문제가 없었지만, AWS EC2 서버에서 실행했을 때는 다음과 같은 Warning message가 출력되었습니다.

 

"UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connection other DBAPI2 objects are not tested, please consider using SQLAlchemy"

 

 Waring message의 말대로 SQLAlchemy의 engine을 사용해보도록 하겠습니다. 우선 create_engine 함수부터 살펴보면, 다음과 같은 인자들이 들어갑니다. postgreSQL의 경우, 'dialect+driver' 부분에 'postgresql+psycopg2'로 사용할 DB 종류와 패지키를 입력하면 됩니다.

 

engine = create_engine("dialect+driver://username:password@hostname:portnumber/databasename")

#postgreSQL
engine = create_engine("postgresql+psycopg2://username:password@hostname:portnumber/databasename")

 

 따라서 postgreSQL과 연동하여 테이블을 불러오고 데이터 프레임으로 변환하는 최종적인 코드는 다음과 같습니다.

 

#!/usr/bin/python3
import psycopg2
import pandas as pd
from sqlalchemy import create_engine

engine = create_engine("postgresql+psycopg2://{User name}:{Password}@{ip address}:{Port number}/{DB name}")

df = pd.read_sql("select * from test.table", engine)

 

 

<참고>

http://sanghun.xyz/2016/11/python%EC%97%90%EC%84%9C-postgres-db-%EC%97%B0%EA%B2%B0%ED%95%B4%EC%84%9C-%EC%BF%BC%EB%A6%AC-%EC%A1%B0%ED%9A%8C%ED%95%98%EA%B8%B0/

python에서 postgres DB 연결해서 쿼리 조회하기

 

https://greendreamtrre.tistory.com/196

Python (파이썬) SqlAlchemy 모듈 (engine)