[Python] DB 연결 (sqlalchemy 사용방법 - to_sql, read_sql, connect, commit, rollback)

 

 

 

필요 패키지

import sqlalchemy
from sqlalchemy import create_engine

 

DB 정보

p_conn = {
     "host": "ip번호",
     "port": 포트번호,
     "database": "데이터베이스명",
     "user": "유저명",
     "password": "비밀번호"
 }
 
 
p_engine = "postgresql://%s:%s@%s:%s/%s" % (
    p_conn["user"],
    p_conn["password"],
    p_conn["host"],
    p_conn["port"],
    p_conn["database"],
)

참고) 예시로, 123.456.78.912:1234 ip 번호가 있으면 ':' 뒤에 있는 1234 는 포트번호이다.

 

"mysql+pymysql"을 사용하여 MySQL 데이터베이스에 연결.

연결할 MySQL 데이터베이스의 정보(유저 이름, 비밀번호, 호스트 이름, db 이름)를 입력.

 

 

DataBase 연결

engine = create_engine(p_engine)

 

 

 

DataBase 연결 및 시작

 with engine.connect() as connection:
        # Start an explicit control over transaction
        tran = connection.begin()

 

---

pd.read_sql_table

DataBase 테이블을 데이터 프레임으로 불러올 때 사용

pd.read_sql_table("DB 테이블명", engine, "스키마명")

 

to_sql

DataFrame에 저장된 레코드를 SQL 데이터베이스에 넣을 수 있다.

새로운 테이블을 생성하거나 덮어씌울 수 있다.

데이터프레임.to_sql(
            "DB 테이블명" ,
            con=engine,
            schema="스키마명",
            if_exists="append",
            index=False,
            dtype={
                "column_1": sqlalchemy.types.VARCHAR(8),
                "column_2": sqlalchemy.types.VARCHAR(),
                "column_3": sqlalchemy.types.INTEGER()
                }

if_exists 옵션: 테이블이 이미 존재하는 경우 

• fail: ValueError를 발생시킵니다.
• replace: 새 값을 삽입하기 전에 테이블을 삭제합니다.
• append: 기존 테이블에 새 값을 삽입합니다.

---

 

commit

 

tran.commit()

 

• 모든 작업을 정상적으로 처리하겠다고 확정하는 명령어.
• 변경된 내용을 모두 영구 저장한다.
• COMMIT 수행하면, 하나의 트랜젝션 과정을 종료하게 된다.
• update , insert, delete 등 작업내용을 실제 DB에 적용

 

rollback

tran.rollback()

 

• 작업 중 문제가 발생했을 때, 트랜젝션의 처리 과정에서 발생한 변경 사항을 취소하고, 트랜젝션 과정을 종료시킨다.
• commit 수행 전, 하나의 묶음 처리가 시작되기 이전의 상태로 되돌린다. 

 

DataBase 연결 끊기

connection.close()
engine.dispose()

• connection.close() 후에 engine.dispose() 를 실행하여 연결을 종료해야 한다.
• 그렇지 않으면, Python Garbage 수집기가 Snowflake와의 통신에 필요한 리소스를 제거하여 Python 커넥터가 세션을 올바르게 종료할 수 없음

 

전체 코드 

engine = create_engine(p_engine)

try:
    with engine.connect() as connection:
        # Start an explicit control over transaction
        tran = connection.begin()
        
       	'''
        db 반영할 가공 코드
        '''
        데이터프레임.to_sql(
            "DB 테이블명" ,
            con=engine,
            schema="스키마명",
            if_exists="append",
            index=False,
            dtype={
                "column_1": sqlalchemy.types.VARCHAR(8),
                "column_2": sqlalchemy.types.VARCHAR(4),
                "column_3": sqlalchemy.types.VARCHAR()
                }
        
        # Commit the transaction explicitly if above insert statements work fine
        tran.commit()
except Exception as e:
	tran.rollback()
    print("Transaction rolled back due to an error:", e)
finally:
	connection.close()
    engine.dispose()
    print('lid_rtate_vmtc: Transaction closed successfully')

 

주의)

이러한 db연결, commit 및 연결 끊기 작업을 하지않아도 db에 데이터가 넣어지긴 한다.

하지만 자동화를 시킬 시에는 log에 에러메세지가 남지않는 물리는 현상이 있을 수 있으므로 연결, 확정, 끊음을 확실히 하는게 좋다.

 

---

 

참고 페이지

 

Connect 관련 

https://docs.snowflake.com/ko/developer-guide/python-connector/sqlalchemy

Python 커넥터와 함께 Snowflake SQLAlchemy 툴킷 사용하기 | Snowflake Documentation

 

COMMIT, ROLLBACK 개념 정의

https://wikidocs.net/4096

1) COMMIT과 ROLLBACK