[PYTHON/PSYCOPG2] Connecton 클래스 : commit/rollback 메소드를 사용해 트랜잭션 처리하기

■ Connecton 클래스의 commit/rollback 메소드를 사용해 트랜잭션을 처리하는 방법을 보여준다.

▶ 테스트 테이블 (SQL)


CREATE TABLE test
(
    ID   INTEGER PRIMARY KEY,
    NAME VARCHAR,
    AGE  INTEGER
);

CREATE TABLE test

(

ID INTEGER PRIMARY KEY,

NAME VARCHAR,

AGE INTEGER

);

▶ 예제 코드 (PY)


import psycopg2

from psycopg2 import Error

try:
    connection = psycopg2.connect(host = "192.168.29.118", port = "5432", database = "testdb", user = "testuser1", password = "test1234")

    cursor = connection.cursor()

    cursor.execute("DELETE FROM test")

    cursor.execute("INSERT INTO test (id, name, age) VALUES (1, '홍길동', 20)")
    cursor.execute("INSERT INTO test (id, name, age) VALUES (2, '김철수', 30)")
    cursor.execute("INSERT INTO test (id, name, age) VALUES (3, '이영희', 25)")

    connection.commit()

    print("트랜잭션이 완료되었습니다.")

except (Exception, Error) as error:
    connection.rollback()

    print("트랜잭션이 취소되었습니다.")
    print(f"PostgreSQL 연결시 에러가 발생했습니다 : {error}")

finally:
    if connection:
        cursor.close()
        connection.close()

        print("PostgreSQL 연결이 종료되었습니다.")

import psycopg2

from psycopg2 import Error

try:

connection = psycopg2.connect(host = "192.168.29.118", port = "5432", database = "testdb", user = "testuser1", password = "test1234")

cursor = connection.cursor()

cursor.execute("DELETE FROM test")

cursor.execute("INSERT INTO test (id, name, age) VALUES (1, '홍길동', 20)")

cursor.execute("INSERT INTO test (id, name, age) VALUES (2, '김철수', 30)")

cursor.execute("INSERT INTO test (id, name, age) VALUES (3, '이영희', 25)")

connection.commit()

print("트랜잭션이 완료되었습니다.")

except (Exception, Error) as error:

connection.rollback()

print("트랜잭션이 취소되었습니다.")

print(f"PostgreSQL 연결시 에러가 발생했습니다 : {error}")

finally:

if connection:

cursor.close()

connection.close()

print("PostgreSQL 연결이 종료되었습니다.")

▶ requirements.txt


psycopg2-binary==2.9.9

psycopg2-binary==2.9.9