Flask 블로그 제작기 (3) - PostgreSQL 연결

플라스크로 서비스되던 구버전 블로그에서 옮겨왔습니다.

---

왜 PostgreSQL?

Python에서 기본적으로 사용하고 있는 DB 미들웨어는 SQLite3이며, Flask 튜토리얼에서도 DB는 SQLite3를 사용하고 있습니다. 다만, 이 포스트 시리즈에서도 볼 수 있듯이, SQLite3는 서버가 아닌 파일 기반의 시스템이라, DB를 한꺼번에 여러 사용자가 사용하는 경우에 알맞지 않습니다.

따라서 보통 MySQL이나 PostgreSQL을 사용하게 되는데, MySQL이 더 오래되고 개발이력이 오래되어 튼튼한 감은 있지만, PostgreSQL은 오픈소스로서의 잠재력이 높아 요즘 사람들이 많이 사용하는 추세입니다. 이 웹사이트, Dwarf in the Flask 또한 PostgreSQL을 사용하고 있습니다. 사용에 참조한 튜토리얼은 이것과 이것입니다.

Postgres 명령어들

PostgreSQL 설치는 공식 다운로드 또는 sudo apt-get install postgresql 명령어를 통해 그냥 설치하면 되지만, 파일 기반으로 소통하는 SQLite와 달리 PostgreSQL은 서버를 실행하는 형식으로 DB와 소통하는 데서 어려움이 찾아옵니다.

psql -U postgres

처음 설치 후 postgres라는 계정이 자동 생성되며, postgres라는 database와 자동 연결되어 접속할 수 있게 됩니다. 비밀번호가 있다면 아마도 postgres일 것입니다. 접속되면 help를 입력해보도록 합시다.

postgres=# help
You are using psql, the command-line interface to PostgreSQL.
Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

\h는 SQL 명령어들에 대한 도움말이니, 아마도 저보다 SQL에 익숙하실 여러분들께 필요한건 \?를 통해 psql 특수 명령어들에 대한 도움말일 것입니다. MySQL을 통해 show desc use 등의 명령어에 익숙하셨던 여러분은 이제 \d \l \q등에 익숙해지셔야 합니다.

웹사이트에서 사용할 유저 이름과 비밀번호를 설정해보도록 합시다.

CREATE USER username WITH PASSWORD 'password';

\q 명령어를 통해 서버에서 나간 다음, psql -U username으로 접속하려고하면 접속되지 않는 것을 알 수 있습니다. 이는 PostgreSQL은 접속 시에 username과 일치하는 database로 자동접속하기 때문이므로, 다시 psql -U postgres로 접속한 다음, createdb username으로 username과 일치하는 database를 만들어주면 접속가능해집니다.

참고로, PostgreSQL database 백업을 위한 pg_dump 명령어 예시는 다음과 같습니다.

sudo pg_dump -d db_name -h 127.0.0.1 -U username -p port > backup.sql

Psycopg2 어댑터

PostgreSQL 서버와 소통하려면, python에서 서버와 소통하는 Psycopg2 데이터베이스 어댑터를 사용해야합니다.

pip install psycopg2

설치가 완료되면, DB와 소통하는 파일, 예를 들면 db.py같은 파일로 들어가서 SQLite가 점유하고 있던 코드를 모두 Psycopg2를 사용하는 코드로 바꿔주도록 합시다.

from flask import current_app, g
import psycopg2, psycopg2.extras

def get_conn():
    if "conn" not in g:
        db_config = current_app.config["DATABASE"]
        g.conn = psycopg2.connect(
            host=db_config["HOST"],
            user=db_config["USER"],
            password=db_config["PASSWORD"],
            port=db_config["PORT"],
        )
    return g.conn

def get_cur():
    if "cur" not in g:
        g.cur = get_conn().cursor(cursor_factory=psycopg2.extras.DictCursor)
    return g.cur

...

SQLite와 PostgreSQL의 코드를 자세히 살펴보면, 3가지 정도의 차이점이 있습니다.

1. 파일을 통해 DB를 여는 SQLite와 달리, 서버를 통해 DB를 여는 PostgreSQL은 host와 port를 설정해줘야 합니다.
2. 동일한 이유로, PostgreSQL은 connection과 cursor 객체를 따로 만들어 DB에 commit해야합니다.
3. SQL query의 formatting 시에 문법 차이가 다소 있습니다. 특히, prepared statement를 만들 때 SQLite는 ? 물음표에 변수를 할당할 수 있지만, PostgreSQL은 할 수 없기 때문에 SQL injection 보안 공격에 취약해질 수 있습니다.