2024-01-12

Suhyeon Lee·2025년 1월 12일

자기주도학습

목록 보기

77/83

최종 프로젝트

어제 계획한 방법대로 코드를 짜다 보니 1일 최대 한도(864,000)를 넘을 것 같아서 조금 방법을 바꿨음
1) rank/top에서 1등부터 1000등까지 userNum 획득
2) 해당 데이터 DB에 저장 → user_info 테이블
3) user_info 테이블에 있는 userNum 정보로 user/games 접근
4) user/games에서 unique한 gameId 및 startDtm(서버의 게임 시작 시간), expireDtm(해당 게임 데이터 호출 만료 기간) 받기
우리가 중점적으로 볼 내용은 시즌 6(seasonId 29) 정보
- 시즌 6: 2024.12.12 - 2025.03.20
  - 오늘(2025.01.12) 기준으로 32일째 진행 중
90일이 지난 게임 데이터는 더 이상 API로 불러올 수 없음
- 즉, 시즌 5의 정보만 하루치씩 사라지고 있는 거니까 굳이 게임 데이터를 적재할 필요 없이 gameId와 api에서 불러올 수 있는지 여부만 알면 될 것이라는 판단
  - 시즌 5: 2024.09.05 - 2024.12.05
- 오늘(2025.01.12) 기준으로 90일 전은 2024년 10월 15일
  - 필요하다면 2024년 10월 17일부터 12월 5일까지의 게임 데이터 적재할 것
    → 50일 분량
  - 프로젝트 발표일 기준(2025년 2월 11일)으로 시즌 6이 62일차니까 얼추 맞을 듯
expireDtm의 경우 유저마다 시간이 다르기 때문에(게임 종료 시간이 팀마다 다름) 그냥 '년-월-일'로 변환시켜서 처리하면 될 듯

https://github.com/jmpop97/ER_statistics/blob/main/read_txt.py

코드 작성 관련

DB 테이블을 DataFrame으로 읽어오는 방법

참고 1, 2, 3

sqlalchemy의 커넥터만 변경해 주면, MySQL뿐만 아니라 모든 데이터베이스에 적용 가능
sqlalchemy, pymysql이 설치되어 있어야 함

sqlalchemy를 통해 DB 연결
- 주소에서 root, password, table을 DB에 맞게 변경해야 함

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine(
    'mysql://root:password@localhost/table'
    , convert_unicode=True
)
conn = engine.connect()

pandas를 통해 table 읽기
- pandas의 read_sql()
  - 0.19 버전부터 지원
  - sqlalchemy를 필수로 사용하도록 되어 있음

# 방법 1: read_sql_table()
data = pd.read_sql_table('table_name', conn)
data.head()

# 방법 2: read_sql_query() → 추천
query = "select * from table_name;"
data = pd.read_sql_query(sql=query, con=conn)

1. MySQL 데이터 불러오기

1) pymysql 모듈

mysql을 python에서 사용할 수 있게 하는 라이브러리

import pymysql
db = pymysql.connect(
		host='',            # 접속할 mysql server의 주소
		port='3306',        # 접속할 mysql server의 포트 번호
		user='your_id',     
		passwd='your_pw',
		db='dbname'         # 접속할 database명
		charset=''          # 'utf8' 등 문자 인코딩 설정 (한글 데이터가 깨지지 않도록)
)

2) Mysql 데이터 python으로 받기

연결한 DB와 상호작용하기 위해 cursor 객체 생성

cursor = db.cursor()

pymysql의 기본적인 데이터 조회 쿼리 실행
- execute() → fetch(all, one, many)

메서드	설명
fetchall()	쿼리 결과의 모든 데이터를 가져오기
fetchone()	쿼리 결과의 1개 행만 가져오기
fetchmany()	쿼리 결과의 데이터 n개 가져오기

아래와 같이 실행하면 tuple 형태로 쿼리 결과를 리턴

ql = 'SELECT * FROM table_name'
cursor.execute(sql)
result = cursor.fetchall()

3) MySQL 데이터 pandas DataFrame으로 바로 받기

pandas DataFrame으로 MySQL의 데이터를 바로 주고받을 수 있다면 더욱 편리하게 작업 가능
- MySQL에서 표 데이터를 읽어오는 경우는 pandas의 read_sql() 메서드로 간편하게 불러올 수 있음

sql = 'SELECT * FROM table_name'
df = pd.read_sql(sql, db)

※ 작업이 끝났으면 DB 연결 닫기

db.close()

2. MySQL로 데이터 보내기/수정하기

데이터 분석의 결과 데이터를 MySQL 테이블로 내보내거나, 테이블을 수정/삭제 하는 등의 작업을 하는 방법 알아보기

1) INSERT/UPDATE/DELETE 쿼리 실행

INSERT/UPDATE/DELETE 등의 DML 쿼리를 실행하는 방법
- SELECT문 쿼리를 실행할 때와 비슷하지만 fetch()로 쿼리의 데이터를 가져오는 대신 commit 메서드로 db에 쿼리 내용을 반영
  - commit 해주지 않으면 db로 보내진 쿼리는 반영되지 않음
- DML 쿼리의 실행은 cursor.execute() → db.commit() 메서드로 이루어짐

sql = '''
{INSERT/UPDATE/DELETE 쿼리}
'''

cursor.execute(sql)
db.commit()

2) DataFrame → MySQL 내보내기

INSERT 문의 경우에는 쿼리를 작성하는 대신 DataFrame의 데이터를 바로 MySQL에 저장할 수 있으면 훨씬 편리할 것
- sqlalchemy 라이브러리를 사용하면 됨!
  - sqlalchemy create_engine(sql db 연결 라이브러리)을 import하고, db를 연결
  - to_sql() 메서드로 DataFrame을 데이터베이스로 내보냄

from sqlalchemy import create_engine 

engine = create_engine("mysql+pymysql://[id]:[pw]@[mysql주소]:[port]/[db_name]?charset=utf8", encoding='utf-8')
conn = engine.connect()

df.to_sql(name="[table_name]", con=engine, if_exists='append', index=False)

to_sql() 메서드
- DataFrame의 컬럼명과 테이블의 컬럼명을 동일하게 맞춰두면 해당 컬럼에 저장
- 테이블을 만들수도, 기존 테이블에 row를 추가하거나 지우고 새 데이터를 넣을 수도 있지만, to_sql 메서드로 새 테이블을 만드는 방법은 사용하지 않는 것이 좋고, 기존 테이블에 대해 사용하는 것을 추천
  - [table_name]이 없으면 새 테이블 생성 (테이블 구조가 최적화되지 않음)
  - [table_name]이 존재할 경우 → if_exists = ‘fail’, ‘append’, ‘replace’ 사용 가능

3. Summary

1) mysql에서 데이터를 불러오기

간단한 데이터를 불러올 경우
- pymysql. 쿼리 execute() → fetch(one, many, all)
표 데이터를 불러올 경우
- pymysql. pd.read_sql(sql, db)

2) mysql로 데이터 보내기/수정하기

INSERT/UPDATE/DELETE DML 쿼리를 실행할 때
- pymysql. 쿼리 execute() → commit()
표 데이터를 DB 테이블에 INSERT할 때
- sqlalchemy. df.to_sql("table_name", con=engine)

3) pymysql 사용법
1. pymysql 모듈 import
2. MySQL db 연결: pymysql.connect() 메서드로 Connection 객체 생성
3. Connection 객체로부터 cursor() 메서드로 Cursor 객체 생성
4. Cursor 객체의 execute() 메서드로 sql 쿼리를 mysql db에 전송
5. SELECT 쿼리의 경우 fetchall(), fetchone(), fetchmany() 메서드를 사용하여 데이터를 가져옴

pd.read_sql()을 사용하면 데이터를 바로 DataFrame으로 불러올 수 있음

INSERT/UPDATE/DELETE DML(Data Manipulation Language) 쿼리의 경우 Connection 개체의 commit() 메서드를 사용하여 데이터 확정
종료: Connection 객체의 close() 메서드를 사용해 db 연결을 종료

4) sqlalchemy 사용법
1. sqlalchemy create_engine(sql db 연결 라이브러리)을 import
2. MySQL db 연결: create_engine() 메서드로 engine 객체 생성. connect() 메서드로 db 연결
3. DataFrame → MySQL 내보내기: df.to_sql() 메서드 사용
4. 종료: close() 메서드로 db 연결 종료

파이썬에서 MySQL 데이터베이스 연동하기

참고

1. MySQL Connector/Python 사용

MySQL Connector/Python은 MySQL과 파이썬을 연결해주는 공식 드라이버
이 드라이버를 사용하면 파이썬에서 MySQL 데이터베이스에 접속하고, 데이터를 읽고 쓰는 작업을 쉽게 수행할 수 있음

import mysql.connector

# MySQL 데이터베이스에 접속
cnx = mysql.connector.connect(user='사용자명', password='비밀번호',
                              host='호스트명',
                              database='데이터베이스명')

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

# SQL 쿼리 실행
query = "SELECT * FROM 테이블명"
cursor.execute(query)

# 결과 가져오기
result = cursor.fetchall()

# 결과 출력
for row in result:
    print(row)

# 연결 종료
cursor.close()
cnx.close()

2. SQLAlchemy 사용하기

SQLAlchemy는 파이썬에서 데이터베이스를 다루기 위한 인기있는 ORM(Object-Relational Mapping) 라이브러리
ORM은 객체와 관계형 데이터베이스 간의 매핑을 담당하여, SQL 쿼리를 직접 작성하지 않고도 데이터베이스를 조작할 수 있게 해줌

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

# MySQL 데이터베이스에 접속
engine = create_engine('mysql+mysqlconnector://사용자명:비밀번호@호스트명/데이터베이스명')

# 세션 생성
Session = sessionmaker(bind=engine)
session = Session()

# 모델 정의
Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    email = Column(String(50))

# 데이터 조회
users = session.query(User).all()
for user in users:
    print(user.name, user.email)

# 데이터 추가
new_user = User(name='John Doe', email='johndoe@example.com')
session.add(new_user)
session.commit()

# 연결 종료
session.close()

결론

MySQL Connector/Python과 SQLAlchemy를 사용하여 파이썬에서 MySQL 데이터베이스에 접속하고, 데이터를 읽고 쓰는 작업을 수행할 수 있습니다. 이를 통해 데이터 처리 및 분석 작업을 더욱 효율적으로 수행할 수 있으며, 데이터베이스와의 상호작용을 간편하게 할 수 있습니다.
MySQL 데이터베이스 연동은 파이썬을 활용한 다양한 프로젝트에서 필수적인 요소입니다. 따라서 소개한 방법을 활용하여 파이썬과 MySQL을 연동하는 작업에 익숙해지는 것이 중요합니다. 더불어 다른 데이터베이스와의 연동 방법과 비교하여 장단점을 파악하고, 최적의 방법을 선택하는 것도 중요한 과제입니다.

컬럼명 수정

df_selected = df_selected.rename(  
    columns={
        "name": "city",
        "main.temp": "temperature",
        "main.humidity": "humidity",
        "wind.speed": "wind_speed",
    }
)