[PYTHON/PSYCOPG2] execute_values 함수 : BULK INSERT 처리하기

■ execute_values 함수를 사용해 BULK INSERT를 처리하는 방법을 보여준다.

▶ 테스트 테이블 (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
import psycopg2.extras

with psycopg2.connect("host='192.168.29.118' dbname='testdb' user='testuser1' password='test1234'") as connection:
    with connection.cursor() as cursor:

        cursor.execute("DELETE FROM test")

        psycopg2.extras.execute_values(
            cursor, "INSERT INTO test (id, name, age) VALUES %s;",
            [(1, '홍길동', 20), (2, '김철수', 30), (3,'이영희', 25)]
        )

        connection.commit()

        cursor.execute("SELECT * FROM test")

        resultList = cursor.fetchall()

        for result in resultList:
            print(result)

"""
(1, '홍길동', 20)
(2, '김철수', 30)
(3, '이영희', 25)
"""

import psycopg2

import psycopg2.extras

with psycopg2.connect("host='192.168.29.118' dbname='testdb' user='testuser1' password='test1234'") as connection:

with connection.cursor() as cursor:

cursor.execute("DELETE FROM test")

psycopg2.extras.execute_values(

cursor, "INSERT INTO test (id, name, age) VALUES %s;",

[(1, '홍길동', 20), (2, '김철수', 30), (3,'이영희', 25)]

)

connection.commit()

cursor.execute("SELECT * FROM test")

resultList = cursor.fetchall()

for result in resultList:

print(result)

"""

(1, '홍길동', 20)

(2, '김철수', 30)

(3, '이영희', 25)

"""

▶ requirements.txt


psycopg2-binary==2.9.9

psycopg2-binary==2.9.9