Python 으로 Postgresql 데이타베이스 연동 간단 리뷰
파이썬에서 Postgresql디비로 연동하여 사용하기 위해서 사용하는 드라이버는 공식 postgresql페이지에 의하면 2가지가 나옵니다.
- psycopg
- SQL-relay
SQL-relay는 여러 가지 언어를 같이 지원하고 psycopg가 파이썬 전용 드라이버로 보이는군요.(+ 정식 드라이버이기도 합니다.) 버전은 현재 2이고 3이 활발하게 개발 중이라고 합니다. (2021년 1월 기준)
간단하게 psycopg를 사용하는 방법을 정리해볼께요.
설치
파이썬 설치는 역시 pip, 오직 pip
pip install psycopg2-binary
psycopg2-binary와 psycopg2, 두 가지 패키지가 있는데, 일반적인 End-user라면 -binary패키지를 사용하는 것이 좋습니다. 단, 해당 패키지에 의존성을 갖는 다른 패키지를 담당하고 배포하는 개발자이거나 자체로 빌드를 해야 할 필요가 있다면, 바이너리 패키지를 이용하면 안 됩니다.
연동 방법
데이터베이스와 연동하는 방법을 크게 4가지 방법을 먼저 파악하고, 추가적으로는 세부적인 내용들을 찾아가 보면 됩니다.
- 연결(connection)
- 변경(insert, delete, update)
- 조회(select)
- 해제(close, disconnection)
연결
Postgres와 연결하기 위해서는 psycopg2 패키지의 connect메서드를 이용하면 됩니다.
connection = psycopg2.connect("host=192.168.0.1 dbname=postgres user=postgres password=1234 port=5432")
# 또는
connection = psycopg2.connect(host="192.168.0.1", dbname="postgres", user="postgres", password="1234", port=5432)
정상적으로 생성이 완료되면, 추가적으로 cursor라는 인스턴스를 만들어서 그 인스턴스를 통해 데이터 조작을 진행합니다. cursor의 생성은 간단합니다.
cur = connection.cursor()
변경
이제 생성한 cursor를 통해 데이터베이스와 연동을 해봅시다. 먼저 데이터를 입력/조회하기 위한 간단한 테이블을 생성합니다.
cur.execute("CREATE TABLE test (id serial PRIMARY KEY, num integer, data varchar);")
connection.commit()CREATE TABLE명령어를 이용하여 테이블 생성을 시키고 commit() 메서드를 호출하여 실제로 데이터베이스에 변경을 일으킵니다. 만약 기존에 만든 변경 내용을 취소하고 싶으면 rollback() 메서드를 호출합니다.
간단하게 한 개의 Row를 입력합니다.
cur.execute("INSERT INTO test (id, num, data) VALUES (%s, %s, %s);",
(1, 100, "data01")
)execute 메서드의 첫 번째 파라미터는 실행시키려는 SQL문을 지정합니다. 하나의 완벽한(데이터를 포함한) SQL를 사용해도 가능하고 위의 예제처럼 파라미터를 따로 지정하는 것도 가능합니다. 여기서 %s는 placeholder라고 합니다.
(♣ 주의: 위의 예제에도 있지만 스트링을 넘길 경우 %s를 '%s'로 지정하지 않도록 합니다. %s는 단지 placeholder일 뿐입니다.)
delete나 update문도 마찬가지로 %s placeholder를 이용해서 쿼리문을 작성해서 실행시키면 됩니다.
#update 예제
cur.execute("UPDATE test SET num=num+%s where id = %s", (10, 1))
#delete 예제
cur.execute("DELETE FROM test where id = %s', (1))
조회
select문도 execute메서드를 이용하여 쿼리를 실행시킵니다. 한 가지 다른 점은 Fetch***() 메서드를 이용하여 가져온 데이터를 로컬 변수에 저장하는 부분의 추가입니다.
cur.execute("SELECT * FROM test")
(id, num, data) = cur.fetchone()
print(f"{id}, {num}, {data}")fetchone(), fetchmany(), fetchall() 3개의 메서드가 있는데 이름에서 알 수 있듯이 한 개 또는 여러 개 또는 전부 가져오는 메소 드립니다. fetchall() 같은 경우 아주 큰 테이블에 실행할 경우 문제가 생길 수 있으니 잘 고려해서 사용해야겠죠?
결괏값은 tuple이 기본이고 ~many나 ~all류의 메서드는 array of tuple 타입이 되겠죠.
해제
사용이 완료된 인스턴스들은 해제하는 것이 좋습니다.
cur.close()
connection.close()참고로 cursor의 경우 Python의 with키워드를 통해 자동 리소스 해제도 가능합니다. 아래와 같이 사용 가능합니다.
conn = psycopg2.connect('.....')
with conn:
with conn.cursor() as cur:
cur.execute(SQL1)
conn.close()