Note 314 DB API, Cloud Database, Python & SQL

SQL을 꼭 MySQL, SQLite과 같은 DB 프로그램에서만 실행시킬 필요는 없다. 파이썬에서도 SQL을 작성하여 원하는 데이터를 조작할 수 있는데 이때 사용하는 것이 DB API이다. 또한, 자체적으로 DB를 관리하지 않고 AWS나 Google Cloud Database를 활용하면 여러 costs를 줄여서 DB를 사용할 수 있기 때문에 편리하다. 이번 포스팅에서는 파이썬과 DB를 연결하는 방법에 대해 알아보자.

PEP(Python Enhancement Proposal)

PEP는 Python에 관한 정보를 전달하고, 새로운 기능을 설명하는 문서이다. 즉, 파이썬의 성능을 향상을 위한 정보로 사용자에게 도움을 준다. 특히, PEP 249에는 DB API에 대한 정보가 담겨있다.

DB API란 프로그래밍 언어와 데이터베이스를 연결시켜주는 툴이다. 예를 들어, 파이썬에서는 SQLite에 접근하기 위해 SQLite3 패키지를 사용하는데 이 SQLite3가 DB API다.

SQLite와 파이썬

SQLite는 파일형 관계형 데이터베이스이다. 이름에 Lite가 있는 것처럼 가벼운 데이터베이스로 MySQL처럼 서버를 사용하는 DB에 비해 기능이 제한적이다. 파일형 데이터베이스이기 때문에 실행 중인 프로그램 메모리에 의존할 수 있어서 파일을 삭제하거나 프로세스를 종료하여 발생하는 데이터 손실에 유의해야 한다. 하지만, 간단하고 빠르게 데이터베이스를 구축하고, 단순한 실험을 위한 용도로는 적합하다.

데이터 베이스 접근순서

1) connect

파이썬에서 데이터베이스에 접근하기 위해서는 connect method를 사용해서 DB와 파이썬을 연결한다. 연결 후에는 데이터베이스와 연결된 하나의 세션이 conn에 보관된다.

ex) SQLite

import sqlite3
conn = sqlite3.connect('test.db')

2) cursor

connect method로 DB와 연결된 세션이 있으면 이 세션을 통해 DB와 소통하기 위한 cursor method를 사용한다. 마우스의 커서처럼 DB를 가리켜서 특정구문을 실행시킬 준비를 한다.

cur = conn.cursor()

3) execute

커서까지 완료가 되었으면 이제 CRUD(Create, Read, Update, Delete)를 수행할 수 있다. execute method를 사용하면 원하는 쿼리를 실행할 수 있다.

table_create = """CREATE TABLE IF NOT EXISTS players(
  playerId INTEGER NOT NULL PRIMARY KEY,
  goals INTEGER;"""

cur.execute(db_create)

insert_val = "INSERT INTO players (playerId, goals) VALUES (?,?);" # sqlite에서는 ?을 사용하지만, sql에서는 %s를 사용한다.

val_list = [["1", "15"], ["2", "20"]]
for val in val_list:
  cur.execute(insert_val, val)

4) commit

실행을 시켰다고 DB의 상태가 변화되는 것이 아니다. commit을 시켜야지만 이전에 실행시켰던 쿼리를 확정지으면서 DB에 적용된다.

conn.commit() # commit은 cursor가 아니라 세션을 보관하고 있는 conn을 사용해야함.
cur.close()
conn.close() # cursor와 connect개체를 닫는 것을 습관화하자.

5) fetch

DB를 조회하고 싶을 때는 어떻게 할까. DB와 연결되어서 아직 close되지 않았다면 fetch method들을 사용할 수 있다.

cur.execute("SELECT * FROM players;")
print(cur.fetchall()) # 위 실행구문의 모든 내용을 다 보여준다.
print(cur.fetchone()) # 위 실행구문의 한 행을 보여준다. 처음에는 첫번째 행이고, 실행되고 나면 커서가 다음 행으로 이동해서 다음행을 보여준다.
# 연속적으로 fetchone을 실행하면 커서가 이동되는 거에 따라서 결과를 보여줌.
print(cur.fetchmany(3) # 커서위치에서 3개의 행을 보여준다.

Cloud Database

클라우드 데이터베이스는 원격으로 관리되는 데이터베이스다. 로컬로 데이터베이스를 관리하는 온프레미스(on-premise)와는 반대로 인터넷을 사용하여 데이터베이스를 관리한다. 따라서 접근 정보가 있어야지만 데이터베이스에 접근할 수 있다.

이미지출처: https://hanamon.kr/%EB%84%A4%ED%8A%B8%EC%9B%8C%ED%81%AC-%EA%B8%B0%EB%B3%B8-url-uri-urn-%EC%B0%A8%EC%9D%B4%EC%A0%90/

• URI(Unirorm Resource Identifier)

보통 원격으로 데이터베이스에 접근할 때는 URI 형식을 사용한다. URI는 URL, URN의 상위 개념이다. 이름처럼 인터넷의 자원을 식별하기 위해 사용된다. 즉, 네트워크의 정보를 찾기 위함이다.

'서비스://유저_이름:유저_비밀번호@호스트:포트번호/경로'
#서비스: postgres, 유저이름: kim, 비밀번호: 123, 호스트: database.com, 포트번호: 5432, 경로(DB 이름): test_db
'postgres://kim:123@database.com:5432/test_db'

• URL(Uniform Resource Locator)

URL은 네트워크 상에서 웹, 이미지 등의 파일이 위치한 정보를 나타낸다. 쉽게 말하면, URL은 리소스를 어떻게 얻을지에 대한 정보고, URI는 어디서 가져올지에 대한 정보이다.

• URN(Uniform Resource Nace)

URN은 이름으로 리소스를 특정하는 URI이다. http와 같은 프로토콜을 제외하고 리소스의 name을 가리킨다. URN은 리소스에 부여된 영구적이고 유일한 이름이고 변하지 않는다. 하지만 위치 정보가 없기 때문에 실제 자원을 찾기 위해서는 URN을 URL로 변환하여 이용한다.

간단히 말하면, URN은 리소스에 어떻게 접근할지는 명시하지 않고(프로토콜 명시 X), 경로와 리소스 자체를 특정하는 것을 목표로 하는 URI이다.

• 포트번호

포트번호는 컴퓨터가 데이터 통신을 할 때 통신하고자 하는 컴퓨터의 프로그램을 지정하기 위한 번호이다. 포트 하나당 하나의 프로그램을 돌릴 수 있기 때문에 만약 SQL을 두개로 돌리고 싶으면 포트번호를 각각 다르게 해주어야 한다.

ex) 집주소와 받는 사람 이름까지 적어야지만 정확한 배송이 완료된다. 집주소는 IP(원하는 데이터의 위치가 어딘지), 받는 사람 이름이 Port 번호(데이터와 통신할 때 사용할 프로그램은 뭔지).

• Cloud Database vs On-premise Databse

클라우드 데이터베이스는 원격에서 데이터베이스가 관리되기 때문에 사용자가 직접 DB를 관리하기 위한 공간, 시간, 비용들이 감소한다. AWS나 Google Cloud Database를 사용하는 것이 그 예시이다. 하지만, 사용료를 지불해야 하고, 직접 관리하는 것보다는 보안이 취약하다. 또, 만약 해당 플랫폼의 서버가 다운되면 플랫폼에서 오류를 수정할 때까지 DB에 접근할 수가 없어서 언제 다시 사용할 수 있는지 알기가 힘들다. 그러나 DB가 커짐에 따라 변화해야 하는 Computing 속도, 메모리 공간 등의 제약으로부터 자유롭기 때문에 많이 사용된다.

반면, On-premise Database는 사용료가 들지 않고, 로컬에서 직접 관리하기 때문에 클라우드 데이터베이스에 비해 보안에 좋다. 하지만, DB 관리를 위한 공간, 시간, 비용이 발생한다.

Postgresql과 파이썬 연결(by using Elephant SQL)

Elephant SQL에서 무료로 클라우드 데이터베이스를 활용할 수 있다.(하지만 무료인만큼 연결을 많이 하지 못한다던가의 기능적 제한이 존재)

DB를 생성하면 URI가 제공되어 host 주소, db 이름, user 이름, 비밀번호 등을 알 수 있고 이를 사용해서 DB에 접근할 수 있다.

import psycopg2

conn = psycopg2.connect(
    host="서버 호스트 주소",
    database="데이터베이스 이름",
    user="유저 이름",
    password="유저 비밀번호")

그 후의 작업은 sqlite와 동일. connect -> cursor -> execute -> commit -> close

DataFrame과 DB 연결

for문을 사용하여 한 행씩 INSERT하지 않고 DB에 DataFrame을 한 번에 넣고 싶으면 to_sql 함수를 사용하면 된다.

cur.close()
connection.close() # 만약 위에서 다른 작업으로 DB를 연결시켜 놓았다면 해당 연결을 종료시키고 새로 열어서 작업해야 한다.

import pandas as pd
import os
import sqlalchemy
from sqlalchemy import create_engine

DATA_PATH = os.getcwd()
df = pd.read_csv(f'{DATA_PATH}/titanic.csv')
df = df.reset_index().rename(columns = {'index' : 'id'})
db_connection = create_engine('postgresql://user이름:비번@호스트이름/db이름')
df.columns = ['id', 'survived', # DB를 미리 생성해두었다면 DB와 똑같은 이름을 가진 변수를 만들어야한다.
    'pclass',
    'name',
    'sex',
    'age',
    'siblings_spouses_aboard',
    'parents_children_aboard',
    'fare']
conn = db_connection.connect()

dtypesql = {'id':sqlalchemy.types.Integer(),  # 미리 생성된 DB가 있다면 DB에 있는 변수의 타입과 똑같이 맞춰야한다.
            'Survived':sqlalchemy.types.INTEGER(),
            'Pclass':sqlalchemy.types.INTEGER(),
            'Name':sqlalchemy.types.VARCHAR(128),
            'Sex':sqlalchemy.types.VARCHAR(12),
            'Age':sqlalchemy.types.FLOAT(),
            'Siblings_Spouses_Aboard':sqlalchemy.types.INTEGER(),
            'Parents_Children_Aboard':sqlalchemy.types.INTEGER(),
            'Fare':sqlalchemy.types.FLOAT()
}

df.to_sql(name='passenger', con=conn, if_exists='append', index=False,dtype=dtypesql) #append를 사용하면 기존에 있는 DB에 새로운 행을 추가한다.
#자세한 하이퍼 파라미터는 공식문서 참고.
conn.close()