[SQL] Python과 PostgreSQL 연결하기 / psycopg

 

python에서 데이터베이스에 연결하기 위해서는 connector를 사용해야함.

mysql에서는 pymysql, postgresql에서는 psycopg2가 대표적!

 

설치

# pip을 이용하여 설치
pip install psycopg2-binary

연동방법

• 연결(connection)
• 변경(insert, delete, update)
• 조회(select)
• 해제(close, disconnection)

 

연결 - connect(), cursor()

• db와 연결하기 위해서는 connect() 메서드를 사용
• db와 연결이 되면, cursor()를 통해 연결에 대한 인스턴스를 생성하고 해당 인스턴스로 DB를 조작,
  • cursor() : 실질적으로 DB에 쿼리문을 수행하고 결과를 가져오는 역할

import psycopg2
import pandas as pd

# PostgreSQL 데이터베이스 연결 설정
conn = psycopg2.connect(
    host="your_host",
    database="your_database",
    user="your_user",
    password="your_password"
)

cursor = conn.cursor()

 

 

조회

select문도 execute 메서드를 이용해서 쿼리를 실행하나, fetchone(), fetchmany(), fetchall() 을 이용해 가져온 데이터를 로컬 변수에 저장해줘야 함

• with db.connection() as conn:
      with conn.cursor() as cur:
          cur.execute("SELECT id from content;")
          rows = cur.fetchall()
  

변경

• 생성된 cursor를 통해 DB와 연동 가능함
• cursor.execute(query): 실행시키려는 SQL문을 처리
  • query에 완성된 sql query문을 넣을 수도 있고, cursor.execute('SELECT id VALUES %s;", 50) 처럼 파라미터 지정하는 것도 가능함
• connection.commit() 을 통해 실제 데이터베이스에다가 commit을 진행시켜줌. 해당 트랜잭션이 실제로 수행되려면 commit을 날려줘야 db에 반영이 됨!

cursor.execute(query)
connection.commit() # 실제 데이터베이스에 변경

 

연결 해제

• 트랜잭션이 모두 완료됐으면 db와 연결을 끊어주고, 사용이 완료된 인스턴스들은 해제해주는 것이 좋음.
• conn 인스턴스와 cursor 모두 연결을 해제해줘야 함! -> 연결과 반대 순서로 해제 해줘야 함!

cur.close()
conn.close()

 

• conn.close()를 해주지 않으면, 필요없는 세션이 유지돼서 리소스가 낭비될 수도 있음!
• with문을 사용하면 close()를 사용하지 않아도 됨! 실제로 with구문 많이 사용함

with db.connection() as conn:
    with conn.cursor() as cur:
      query = 'your query;'
      conn.execute(query)