python + postgresql 사용하기

 

찾아보니 대부분의 한글 예제가 mysql 중심으로 작성되어 있어서 스터디한 것에 대한 기록용으로 작성함

새로운 프로젝트에 사용할 오픈소스 DB에 대해 고민하다가

아래의 내용을 발견하게됨

https://medium.com/dbconvert/mysql-vs-postgres-in-2024-ba44cc07234a

MySQL vs Postgres in 2024.

 

간단하게 말하면 최근 postgres가 객관적인 평가도 좋고 개발자들 사이에 인기가 많다는 이야기였음

여러 장점 중에서 표준을 더 잘 따른 다는 점도 중요하게 다가왔음

 

전부터 한번 써보고 싶었던 터라 이번 프로젝트는 Postgres로 진행하기로 결정 탬플릿을 작성하려고 하니 생각보다 자료가 많지 않아서 조금 놀람

 

궁극적으로 fastapi와 연동하는 것이 목적이지만 이 포스트에선 DB 핸들링만 정리하도록 하겠음

 

파이썬에서 postgre를 사용하기 위해 기본적으로 SQLAlchemy 부터 설치한다.

 

pip install sqlalchemy

 

https://docs.sqlalchemy.org/en/20/dialects/postgresql.html

PostgreSQL — SQLAlchemy 2.0 Documentation

 

위 링크 설명에 따르면 postgresql DBAPI는 5종류를 지원하는데

• psycopg2
• psycopg (a.k.a. psycopg 3)
• pg8000
• asyncpg
• psycopg2cffi

https://www.psycopg.org/

PostgreSQL driver for Python — Psycopg

https://www.psycopg.org/psycopg3/docs/basic/usage.html

Basic module usage - psycopg 3.2.0.dev1 documentation

 

psycopg가 사실상 psycopg3로 최신 버전이므로 나는 psycopg를 설치하였다.

pip install psycopg[binary]

 

 

PostgreSQL은  최신 버전인 16.3으로 설치했다.

원래는 도커로 설치해서 사용할까 싶었는데 물리영역을 지정해야 하는 것도 그렇고 처음이니 안정성을 고려해 윈도우 환경에 바로 설치했다.

 

https://diary-developer.tistory.com/20

[Docker] PostgreSQL 도커로 실행하기

 

설치파일은 공식사이트에서 다운로드가능하다.

 

https://www.postgresql.org/

PostgreSQL

 

설치하면 쉘과 몇가지 프로그램이 보이는데 pgAdmin 4 를 이용해 데이터베이스 관리를 할 수 있다.

기본 아이디는 postgres 이고 설치시 입력한 비밀번호로 접속이 가능하다.

 

나는 별도로 test 라는 아이디와 test 라는 데이터베이스를 만들어 사용했다.

 

간단한 사용자 테이블을 하나 만들어 insert, update, delete, select 문을 작성해보겠다.

 

먼저 데이터베이스 접속은

 

<db.py>

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine(
    "postgresql+psycopg://test:******@localhost/test",
    echo=False
)
SessionLocal = sessionmaker(autocommit=False, autoflush=True, bind=engine)

Base = declarative_base()

 

URL 부분만 세분화하면 아래처럼 표현 될 수 있고 필요에 따라 포트를 지정할 수 있다.

# Database connection
username = 'test'
password = '********'
host = 'localhost'
database = 'your_database'

SQLALCHEMY_DATABASE_URL = f'postgresql+psycopg://{username}:{password}@{host}/{database}'

engine = create_engine(SQLALCHEMY_DATABASE_URL)

 

이제 모델(테이블)을 만들어보자.

 

<models.py>

from sqlalchemy import Column, Integer, String, Text, DateTime, sql
from sqlalchemy.dialects import postgresql
from db import Base

class UserTBL(Base):
    __tablename__ = "user"

    id = Column(Integer, primary_key=True, index=True)
    email = Column(String, nullable=False, index=True)
    nick = Column(String, nullable=False)
    name = Column(String, nullable=False)
    year = Column(String, nullable=True)
    pw = Column(String, nullable=False)
    comment = Column(Text)
    enroll_time = Column(DateTime(timezone=True), server_default=sql.func.now())
    use_yn = Column(String, default='Y')

 

간단한 사용자 계정 테이블이다.

모델 생성을 위해 클래스를 구성해서 컬럼을 정의하면 된다.

직접 코드로 핸들하는 것도 가능하지만 사용편의성을 위해 클래스로 정의하도록 하겠다.

 

속성에 대한 정의는 아래 링크를 참조하면 되며

기본적으로 String은 크기가 제한된 문자열 Text는 크기에 제한을 두지 않은 문자열이라고 설명하고 있다.

 

컬럼 속성은 일반적인 속성을 사용할 수도 있고 

https://docs.sqlalchemy.org/en/13/core/type_basics.html#generic-types

Column and Data Types — SQLAlchemy 1.3 Documentation

 

밴더에서 지원하는 속성을 사용하는 것도 가능하다.

https://docs.sqlalchemy.org/en/13/core/type_basics.html#sql-standard-and-multiple-vendor-types

Column and Data Types — SQLAlchemy 1.3 Documentation

 

alembic 설치

 

https://alembic.sqlalchemy.org/en/latest/

Welcome to Alembic’s documentation! — Alembic 1.13.1 documentation

 

 

이제 alembic을 설치하고 초기화 한다.

pip install alembic

alembic init migrations

 

초기화를 마치면 alembic.ini 파일과 migrations 폴더가 생성된다.

 

먼저 alembic.ini 파일에서 sqlalchemy.url 항목을 수정한다.

(... 생략 ...)
sqlalchemy.url = postgresql+psycopg://test:testtest@localhost/test
(... 생략 ...)

 

그 다음 migrations 폴더 안에 있는 env.py 파일에서 아래와 같이 모델을 지정해줘야 한다.

(... 생략 ...)
import models
(... 생략 ...)

target_metadata = models.Base.metadata
(... 생략 ...)

 

파일 수정을 마치고  다음과 같이 실행하면 리비전 파일이 생성되면서 데이터베이스에 alembic_version 테이블이 생성된다.

alembic revision --autogenerate

 

이후에 아래와 같이 실행하면 모델로 정의한 user 테이블이 생성된다.

alembic upgrade head

 

여기까지 실행하면 데이터베이스와 테이블 설정은 종료된다.

-----

 

이제 테이블 CRUD를 실행해보자

별도로 스키마를 정의해 사용하는 방법도 가능하지만 여기서는 간단한 샘플로만 보이도록 하겠다.

 

지정된 객체를 생성하여 insert에 해당하는 db.add()를 수행하면 default 설정으로 되어있던 필드들이 자동으로 채워져 있는 것을 확인할 수 있다.

from models import UserTBL
from database import SessionLocal

db = SessionLocal()

u = UserTBL(email='abc@example.com', nick='abc', name='myname', pw='1')

db.add(u)
db.commit()

 

insert / update / delete 모두 commit()이 필요하다.

(autocommit 설정에 따라 달라질 것 같다.)

for u in db.query(UserTBL).all():
    print(u.email)
    if u.nick == 'abc' :
        u.nick = 'aaa'
        db.commit()

 

필터를 이용해 like 검색이 가능하다.

좀더 복잡한 쿼리에 대해서도 예제를 만들어봐야겠다.

 

SQLAlchemy Query API

https://docs.sqlalchemy.org/en/14/orm/query.html

Query API — SQLAlchemy 1.4 Documentation

 

ret = db.query(UserTBL).filter(UserTBL.nick.like('%aaa%')).all()

rn = len(ret)

for i in range(rn) :
    db.delete(ret[i])
    db.commit()

 

생성, 업데이트, 검색, 삭제에 대한 간단 예제 완성~

 

 

※ 별도로 언급되지 않은 내용 중 많은 부분을 아래 참조 링크를 보고 연습하면서 일부 수정하거나 그대로 인용하였음

 

<참조>

https://wikidocs.net/book/8531

점프 투 FastAPI