[데이터 엔지니어링] 1-4 파이썬으로 DB 불러오기

1. DB API

"Database Application Programming Interface"의 약자로, 데이터베이스와 상호작용하기 위한 프로그래밍 인터페이스를 나타낸다. DB API는 일반적으로 Python과 같은 프로그래밍 언어에서 데이터베이스에 접근하고 SQL 쿼리를 실행하는 데 사용된다.

2. 클라우드

a. 클라우드 VS 온프레미스

- 클라우드 : AWS나 GCP와 같이 회사 외부에 데이터 서버와 서버 관리를 아웃소싱하는 것. 관리가 편리하다.
- 온프레미스 : 회사 내부에 서버를 들여와 직접 구축하고 관리하는 것. 보안에 유리하고 속도가 빠르다.

b. URI

보통 원격으로 데이터베이스를 연결할 때에는 URI 형식 으로 연결을 하게 됩니다. URI는 다음과 같은 구조를 갖는다. 

서비스://유저_이름:유저_비밀번호@호스트:포트번호/경로

c. 클라우드와 DB 관리 프로그램 연결하기

DB 관리 프로그램에서 Connection 버튼을 누르고 요구하는 정보들(유저 이름, 비밀번호, 호스트 등)을 입력하면 된다

3. 파이썬으로 로컬DB 이용하기

로컬 데이터베이스로 SQLite를 이용했다. SQLite는 파이썬과 함께 설치되는 가벼운 파일형-관계형 데이터베이스다. DB API로는 sqlite3 라이브러리를 이용한다.

데이터베이스와 파이썬을 연결하기 위해서는 먼저 해당 데이터베이스와 커넥션을 만들어야 한다. 그리고 커서를 만들어, 그 커서를 통해 SQL 쿼리를 실행한다.

만약 테이블에 값을 추가한다면 꼭 commit을 해야 데이터 베이스에 반영될 수 있다.

결과를 확인할 때는 일반적인 SELECT문으로는 아무것도 조회할 수 없다. 이때는 fetchone, fetchmany, fetchall 등을 활용할 수 있다.

 

import sqlite3

# 커넥션 생성
conn = sqlite3.connect('test.db')

# 커서 생성
cur = conn.cursor()

# 테이블 생성
cur.execute("DROP TABLE IF EXISTS test_table;")
cur.execute("""CREATE TABLE test_table (
name VARCHAR(32),
age INT);
""")

# 테이블에 값 입력
cur.execute("INSERT INTO test_table (name, age) VALUES ('spongebob', 12);")
cur.execute("INSERT INTO test_table (name, age) VALUES ('patrick', 13);")
cur.execute("INSERT INTO test_table (name, age) VALUES ('squidward', 14);")

conn.commit()

# 결과 확인
cur.execute("SELECT * FROM test_table")
results = cur.fetchall()
for row in results:
    print(row)

 

4. 파이썬으로 클라우드 이용하기

SQLite 등의 로컬 데이터베이스는 데이터 규모가 작을 때 적당하다. 하지만 데이터 크기가 커져 클라우드를 활용하고자 한다면 SQLite를 쓸 수 없다. 

우리는 클라우드 연습용으로 Elephant SQL을 이용할 예정인데 이는 Postfre SQL 기반의 클라우드. 그래서 DB API로는 psycopg2 라이브러리를 이용한다

아래와 같이 클라우드와 커넥션을 생성하면 나머지 과정은 로컬DB와 같다. 이번에는 csv 파일을 불러와 클라우드 내 테이블에  넣어보자

 

import psycopg2
import pandas as pd

# 커넥션 생성
host="서버 호스트 주소"
database="데이터베이스 이름"
user="유저 이름"
password="유저 비밀번호"

connection = psycopg2.connect(
    host=host,
    user=user,
    password=password,
    database=database
)

# 커서 생성
cursor = connection.cursor()


cursor.execute("DROP TABLE IF EXISTS passenger;") #테이블 생성을 여러번 시도하기 위해 추가

# 테이블 생성
cursor.execute("""
    CREATE TABLE passenger (
        Id INTEGER PRIMARY KEY,
        Survived INTEGER,
        Pclass INTEGER,
        Name VARCHAR(128),
        Sex VARCHAR(12),
        Age FLOAT,
        Siblings_Spouses_Aboard INTEGER,
        Parents_Children_Aboard INTEGER,
        Fare FLOAT
);
""")

# csv 파일 불러오기
df = pd.read_csv('./titanic.csv')

# csv 파일 속 정보들을 클라우드 내 테이블에 INSERT
for idx, row in df.iterrows():
    cursor.execute(f"""
    INSERT INTO passenger VALUES ({idx}, {row['Survived']}, {row['Pclass']}, '{row['Name']}', '{row['Sex']}',
    {row['Age']}, {row['Siblings/Spouses Aboard']}, {row['Parents/Children Aboard']}, {row['Fare']});""")

connection.commit() # 마지막에 commit을 꼭 해야 반영이 된다. 까먹지 말기!

---

* fetchone, fetchmany, fetchall 이 세가지의 차이는?

fetchone(), fetchmany(), 그리고 fetchall()은 모두 cursor 객체의 메소드로, SQLite 데이터베이스에서 실행된 쿼리의 결과를 가져오는 데 사용됩니다. 각 메소드는 반환하는 행의 수와 관련이 있습니다.

fetchone(): 이 메소드는 실행된 쿼리의 결과에서 한 번에 한 행씩 가져옵니다. 매번 호출할 때마다 커서는 다음 행으로 이동하며, 가져온 행은 튜플 형태로 반환됩니다. 쿼리의 결과 행이 없을 경우 None을 반환합니다.

fetchmany(size): 이 메소드는 실행된 쿼리의 결과에서 지정된 크기(size)만큼의 행을 가져옵니다. size는 정수값으로 지정되며, 가져올 행의 수를 나타냅니다. 메소드를 호출할 때마다 커서는 다음 행으로 이동하며, 가져온 행들은 튜플의 리스트로 반환됩니다.

fetchall(): 이 메소드는 실행된 쿼리의 모든 결과 행을 한 번에 가져옵니다. 커서는 실행된 쿼리의 마지막 행으로 이동하며, 모든 행들은 튜플의 리스트로 반환됩니다. 결과 행이 없을 경우 빈 리스트를 반환합니다.