psycopg

PostgreSQL 연동

• Python에서 PostgreSQL 데이터베이스를 연동하기 위해 사용하는 드라이버는 PostgreSQL 공식 페이지에 의하면 2가지가 존재
  • psycopg
  • SQL-relay

• SQL-relay는 여러 가지 언어를 같이 지원
• psycopg가 Python 전용 드라이버이자 정식 드라이버
  • 버전은 현재 2이고 3이 활발하게 개발 중

psycopg 사용법

설치

• pip 이용해서 설치
  • pip install psycopg2-binary

연동 방법

• 데이터베이스와 연동하는 방법을 크게 4가지 방법을 먼저 파악하고 추가적으로는 세부적인 내용들을 찾아보면 됨
  • 연결 (connection)
  • 변경 (insert, delete, update)
  • 조회 (select)
  • 해제 (close, disconnection)

연결

• PostgreSQL과 연결하기 위해서는 psycopg2 패키지의 connect 메서드를 이용하면 됨

# 예제 1
connection = psycopg2.connect(
    "host=192.168.0.1 dbname=postgres user=postgres password=1234 port=5432"
) 

# 예제 2
connection = psycopg2.connect(
    host="192.168.0.1",
    dbname="postgres",
    user="postgres",
    password="1234",
    port=5432
)

• 정상적으로 생성이 완료되면, 추가적으로 cursor라는 인스턴스를 만들어서 그 인스턴스를 통해 데이터 조작을 진행
  • cursor = connection.cursor()

변경

• 생성한 cursor를 통해 데이터베이스와 연동 가능
  • 먼저 데이터를 입력 • 조회하기 위한 간단한 테이블을 생성

cursor.execute(
    "CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);"
)
connection.commit()

• CREATE TABLE 명령어를 이용하여 테이블 생성을 시키고 commit() 메서드를 호출하여 실제로 데이터베이스에 변경을 일으킴
  • 만약 기존에 만든 변경 내용을 취소하고 싶으면 rollback() 메서드를 호출

cursor.execute(
    "INSERT INTO test (id, num, data) VALUES (%s, %s, %s);",
    (1, 100, "data01")
)

• execute 메서드의 첫 번째 파라미터는 실행시키려는 SQL문을 지정
  • 하나의 완벽한 (데이터를 포함한) SQL를 사용해도 가능하고 위의 예제처럼 파라미터를 따로 지정하는 것도 가능
  • 여기서 %s는 placeholder라고 함
  • 위의 예제에도 있지만 string을 넘길 경우 %s를 '%s'로 지정하지 않도록 주의

• delete나 update문도 마찬가지로 %s placeholder를 이용해서 쿼리문을 작성해서 실행시키면 됨

# update 예제 
cursor.execute(
    "UPDATE test SET num=num+%s where id = %s", 
    (10, 1)
)
connection.commit()

# delete 예제 
cursor.execute(
    "DELETE FROM test where id = %s', 
    (1)
)
connection.commit()

조회

• select문도 execute 메서드를 이용하여 쿼리를 실행
  • 한 가지 다른 점은 Fetch***() 메서드를 이용하여 가져온 데이터를 로컬 변수에 저장하는 부분이 추가됨

cursor.execute("SELECT * FROM test")
(id, num, data) = cursor.fetchone()

print(f"{id}, {num}, {data}")

• fetchone(), fetchmany(), fetchall() 3개의 메서드가 있는데 이름에서 알 수 있듯이 한 개 또는 여러 개 또는 전부 가져오는 메소 드
  • fetchall() 같은 경우 아주 큰 테이블에 실행할 경우 문제가 생길 수 있으니 잘 고려해서 사용해야 함
  • 결과값은 tuple이 기본이고 -many나 -all류의 메서드는 array of tuple 타입이 됨

해제

• 사용이 완료된 인스턴스들은 해제하는 것이 좋음

cursor.close()
connection.close()

• cursor의 경우 Python의 with 키워드를 통해 자동 리소스 해제도 가능

conn = psycopg2.connect('.....') 

# 예제 1
with conn: 
    with conn.cursor() as cursor: 
    cursor.execute(sql_query)
    conn.commit()

conn.close()

# 예제 2
from contextlib import closing

with closing(conn.cursor()) as cursor:
    cursor.execute(sql_query)
    conn.commit()

Tip! 추가 내용

• 명령을 처리할때 사용하는 cursor 함수
• SQL 명령을 처리하기 위한 execute 함수
• 데이터베이스에 트랜잭션 변화가 있을때 커밋을 해주는 commit 함수

# cusor
cursor = db.cursor()

# execute
def execute(self,query,args={}):
    self.cursor.execute(query, args)
    row = self.cursor.fetchall()
    return row

# commit
def commit(self):
    self.cursor.commit()