[AI 서비스 개발] python과 mysql 연동하기(비밀번호 특수문자 주의)

1. MySQL이란?

- 관계형 데이터베이스로서 데이터를 구조적으로 저장하고 관리하기 위해 사용(속도, 신뢰성)

- SQL: Structured Query Language

- 관계형 데이터베이스 관리 시스템(RDBMS) 비교

Oracle	MySQL	PostgreSQL
- 유료 - 대규모 트랜잭션에 최적화 - 높은 학습 곡선 - 금융, 대규모 ERP, 복잡한 트랜잭션 등	- 무료(커뮤니티 버전) - 읽기 작업에 특화, 중소규모에 적합 - 상대적으로 쉬움 - 중소규모 웹앱, 전자상거래, 블로그 등	- 무료 - 대규모 데이터 및 트랜잭션에 강점 - 중간 수준 - 데이터 분석 플랫폼, 복잡한 쿼리, JSON/NoSQL과 관계형 데이터를 함께 사용

 

2. Python과 MySQL의 연동하면 좋은 점

- python을 통해 데이터를 가져와 복잡한 데이터 분석이나 변환 작업을 쉽게 수행할 수 있다.

- 동적 웹 애플리케이션을 구축할 수 있다.(예.사용자 인증 시스템, 쇼핑몰, 블로그 등)

- API와 연동: MySQL데이터를 RESTful API로 제공하거나, 외부 API 데이터를 MySQL에 저장할 수 있다.

- 스케쥴링 및 백업: python을 사용해 주기적으로 데이터를 MySQL에 업로드하거나 백업할 수 있다.

 

3. 라이브러리 비교

pymysql	sqlalchemy
- 가볍기 때문에 단순한 삽입/조회 작업에서 빠르다 - SQL를 직접 작성하여 DB구조나 쿼리에 유연하게 대처 가능 - 대량 데이터 처리시 직접 반복문과 커밋 관리 필요 - 의존성 최소화가 중요할 때	- 대규모 데이터에 적합하며 배치 처리, 자동 커밋 기능 - SQL 작성 없이도 테이블 생성 및 관리 기능 - Pandas와 데이터프레임을 to_sql()로 저장 가능 - ORM 지원으로 객체 지향적으로 DB 모델링, 조작 가능

1) ORM이란? 객체(Object)와 테이블(Table)의 매핑

- 프로그래밍 언어의 클래스를 데이터베이스의 테이블과 연결(클래스의 속성은 테이블의 열(Column)과 매핑)

- 클래스의 인스턴스는 데이터베이스 테이블의 행에 해당

- 데이터를 객체 형태로 다루고, 저장/조회 시 SQL대신 ORM이 이를 처리

- 사용하는이유: SQL 쿼리 작성 없이 메서드 호출만으로 DB와 상호작용 가능하여 코드량이 감소하고 유지보수가 쉽다.

- 단점: 복잡한 쿼리나 고도로 최적화된 쿼리는 ORM이 비효율적일 수 있고, ORM이 SQL 쿼리를 생성하는 과정에서 직접 작성한 SQL보다 비효율적일 수 있다.

 

2) SQLAlchemy는 데이터베이스와의 연결을 관리하고 고수준 인터페이스를 제공하지만, MySQL과의 실제 연결을 위해서는 pymysql 같은 드라이버가 필요다. pymysql을 import하지 않아도 sqlalchemy가 내부적으로 pymysql을 호출하지만, 라이브러리를 설치해야 한다.

※ 주의!! password에 특수문자가 있으면 에러가 발생하므로 비밀번호를 변환해줘야한다.

- 판다스 데이터 프레임으로 mysql에 테이블 생성하고 적재, 적재한거 조회하기

import pandas as pd
import pymysql
from sqlalchemy import create_engine
from urllib.parse import quote_plus

# 적재 할 데이터 준비
df = pd.read_csv('my_data.csv')

#  MySQL 연결 정보
db_username = 'root'
db_password = '비밀번호'
db_host = 'localhost'
db_port = 3306
db_name = 'db이름'
db_charset = 'utf8mb4'

# 비밀번호 URL 인코딩(비밀번호에 특수문자 있는 경우)
encoded_password = quote_plus(db_password)

# SQLAlchemy 엔진 생성
engine = create_engine(f"mysql+pymysql://{db_username}:{encoded_password}@{db_host}:{db_port}/{db_name}")

# 데이터프레임을 테이블로 생성 및 적재
df.to_sql(name="테이블이름", con=engine, if_exists='replace', index=False)

# 잘 생성되었는지 조회해보기 pymysql 
my_db = pymysql.connect(
    host=db_host,
    port=db_port,
    user=db_username,
    password=db_password,
    database= db_name,
    charset=db_charset)

# 딕셔너리 형태로 결과 반환(데이터 프레임 쓸거라서)
cursor = my_db.cursor(pymysql.cursors.DictCursor)

# 조회 쿼리문 작성
sql = """
        SELECT *
        FROM 테이블이름
"""
# 쿼리 보내기
cursor.execute(sql)

# 데이터 가져오기
data = cursor.fetchall()

# 가져온 데이터 조회
print(data)

my_db.close()