파이썬을 활용한 PostgreSQL ORM 활용법
- PostgreSQL DB 환경 셋팅은 각자 도메인에 맞게 설정
- 아래와 같이 ORM 모듈 작성
from sqlalchemy import create_engine
import pandas as pd
import json
import os
class sql:
def __init__(self,test=False):
secret = self.load_secret()
self.username = secret['USERNAME']
self.password = secret['PASSWORD']
if test == True:
self.host = secret['TEST_HOST']
else:
self.host = secret['HOST']
self.port = secret['PORT']
self.db_name = secret['DB_NAME']
def load_secret(self):
base_loc = os.getcwd()
secret_src = os.path.join(base_loc,'utils','secret.json')
with open(secret_src,'r') as f:
secret = json.load(f)['DATABASE']
return secret
def connect(self):
engine = create_engine(
f'postgresql://{self.username}:{self.password}@{self.host}:{self.port}/{self.db_name}'
)
return engine
def read(self,query,engine):
df = pd.read_sql(query,engine)
return df
{
"DATABASE": {
"USERNAME": “유저 이름”,
"PASSWORD": “비밀번호”,
"TEST_HOST": “테스트 서버 주소”,
"HOST": “라이브 “서버 주소,
"PORT": “포트 “번호,
"DB_NAME": “DB 이름”
}
}
from postgre import sql
postgre = sql(test=False)
engine = postgre.connect()
userTBL = postgre.read("""
SQL 쿼리문 작성
""",engine)