[PostgreSQL] 파이썬에서 PostgreSQL 연결하기 (ft. Psycopg)

Psycopg는 파이썬에서 PostgreSQL 데이터베이스에 연결하고 사용하기 위해 사용하는 PostgreSQL 데이터베이스 드라이버입니다. 파이썬 애플리케이션에서 PostgreSQL 데이터베이스에 쿼리를 보내고, 데이터를 읽거나 쓰는 작업을 할 수 있게 해주는 역할을 합니다. (마치 자바의 JDBC처럼..)

💋 Psycopg의 주요 기능

1. PostgreSQL 데이터베이스 연결

Psycopg는 파이썬 애플리케이션이 PostgreSQL 데이터베이스에 연결할 수 있도록 해줍니다.

사용자는 데이터베이스에 대한 연결을 설정하고, 그 위에서 쿼리를 실행할 수 있습니다.

import psycopg2

# 데이터베이스 연결 설정
connection = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="myuser",
    password="mypassword"
)

2. SQL 쿼리 실행

PostgreSQL 데이터베이스에서 SQL 쿼리를 실행할 수 있습니다.

SELECT, INSERT, UPDATE, DELETE 같은 SQL 명령을 사용하여 데이터를 조회하거나 수정할 수 있습니다.

cursor = connection.cursor()

# SELECT 쿼리 실행
cursor.execute("SELECT * FROM users")

# 결과 가져오기
rows = cursor.fetchall()
for row in rows:
    print(row)

3. 트랜잭션 관리

트랜잭션은 작업이 성공적으로 완료되면 commit(), 실패하면 rollback()으로 처리할 수 있습니다.

try:
    # 데이터 삽입
    cursor.execute("INSERT INTO users (name, email) VALUES (%s, %s)", ("Alice", "alice@example.com"))

    # 변경 사항 저장
    connection.commit()
except Exception as e:
    # 오류 발생 시 롤백
    connection.rollback()
    print("Failed to insert data:", e)

4. Prepared Statement 및 Parametarized Query

Psycopg는 파라미터화된 쿼리를 통해 SQL 인젝션 공격을 방지하고, 안전하게 사용자 입력을 처리할 수 있습니다.

파라미터를 명시하여 쿼리를 실행하면 쿼리와 데이터를 분리해서 다루기 때문에 보안이 강화됩니다.

cursor.execute("SELECT * FROM users WHERE email = %s", ('user@example.com',))

5. 연결 풀링 (Psycopg2 Pool)

Psycopg는 많은 연결을 효율적으로 관리하기 위해 연결 풀링을 지원하며, 데이터베이스 커넥션을 효과적으로 재사용할 수 있습니다. 성능 향상과 리소스 관리를 위해 유용합니다.

💋 Psycopg의 사용 방법

PostgreSQL에서 자주 사용하는 SQL 명령들을 psycopg에서 어떻게 활용하는지에 대해 알아보겠습니당

1. Drop Tables

테이블을 삭제하는 기능입니다.

cursor.execute("DROP TABLE IF EXISTS users")
connection.commit()

2. Create Tables

새로운 테이블을 생성하는 기능입니다.

cursor.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id SERIAL PRIMARY KEY,
        name VARCHAR(100),
        email VARCHAR(100)
    )
""")
connection.commit()

3. Insert Records

데이터베이스에 레코드를 삽입하는 기능입니다.

cursor.execute("INSERT INTO users (name, email) VALUES (%s, %s)", ('Alice', 'alice@example.com'))
connection.commit()

4. Cartesian Product

두 테이블 간의 카르테시안 곱을 구하는 SQL 조인 연산입니다.

cursor.execute("SELECT * FROM users, orders")
rows = cursor.fetchall()

5. String Operations

문자열과 관련된 다양한 연산을 수행할 수 있습니다.

cursor.execute("SELECT * FROM users WHERE name LIKE %s", ('A%',))
rows = cursor.fetchall()

6. Where Clause Predicates

특정 조건을 사용해 데이터를 필터링하는 기능입니다.

cursor.execute("SELECT * FROM users WHERE id = %s", (1,))
row = cursor.fetchone()

7. Set Operations

집합 연산을 통해 여러 쿼리의 결과를 결합합니다.

cursor.execute("""
    SELECT name FROM users
    UNION
    SELECT name FROM employees
""")
rows = cursor.fetchall()

8. Aggregate Functions

데이터의 합계, 평균 등을 계산하는 기능입니다.

cursor.execute("SELECT COUNT(*) FROM users")
count = cursor.fetchone()[0]

9. Nested Subqueries

서브쿼리를 사용하여 복잡한 쿼리를 처리하는 기능입니다.

cursor.execute("""
    SELECT * FROM users WHERE id IN (
        SELECT user_id FROM orders WHERE total > %s
    )
""", (100,))
rows = cursor.fetchall()

10. Delete Records

레코드를 삭제하는 기능입니다.

cursor.execute("DELETE FROM users WHERE id = %s", (1,))
connection.commit()

11. Update Records

데이터베이스에서 레코드를 수정하는 기능입니다.

cursor.execute("UPDATE users SET email = %s WHERE id = %s", ('newemail@example.com', 1)) connection.commit()

 

 

도움이 되었다면, 공감/댓글을 달아주면 깃짱에게 큰 힘이 됩니다!🌟
비밀댓글과 메일을 통해 오는 개인적인 질문은 받지 않고 있습니다. 꼭 공개댓글로 남겨주세요!