팀원이 csv 파일을 mysql에 저장하는 코드를 만들어 공유해줬다. 코드를 확인하고 모르는 개념을 정리했다.

pandas로 mysql에 DB만들기

• 참고 링크 : pandas dataframe을 mysql에 저장하기

1. 필요한 라이브러리 설치하기

SQLAlchemy는 파이썬에서 사용할 수 있는 ORM(Object-relational maping)이다. ORM은 객체-관계 매핑으로 객체지향 프로그래밍에서 쓰이는 객체라는 개념을 구현한 클래스와 관계형 데이터베이스(RDB)에서 쓰이는 데이터인 테이블을 자동으로 매핑(연결)하는 것을 의미한다. ORM을 사용하면 SQL 질의어를 쓰지 않고 CRUD 등을 사용할 수 있다.

!pip install pymysql
!pip install sqlalchemy

2. 데이터베이스 연결

from sqlalchemy import create_engine
import pymysql
import pandas as pd

# 먼저 MySQL에서 db를 만들고 실행해야 한다
db_connection_str = 'mysql+pymysql://[db유저이름]:[db password]@[host address]/[db name]'
db_connection = create_engine(db_connection_str)
conn = db_connection.connect()

3. 데이터베이스 저장

데이터프레임(df)를 MySQL에 저장하기.
%%time는 CPU times, Wall time 출력하는 모듈이다.

1) 한 개의 파일 불러오기

%%time
df=pd.read_csv('filename.csv', encoding='cp949')

2) 한 개의 파일 저장하기

%%time
df.to_sql(name='db의 테이블 이름', con=db_connection, if_exists='append',index=False)

• con : con에 연결되는 db_connection은 create_engine()를 담은 변수명을 적는다
• if_exists : [fail, replace, append]의 속성으로 테이블이 이미 존재하는 경우 어떻게 할지에 대한 옵션을 지정한다
  • append : 존재하는 테이블에 값 저장, 기존 테이블에 저장을 하는 것으로 저장할 데이터프레임의 길이나 타입이 맞지 않으면 오류가 난다
  • replace : 테이블 지우고 새로 생성 후 값 저장, 새로 데이터프레임 타입에 맞춰 테이블이 생성되므로 데이터프레임 타입이 object일 땐 MySQL에서는 text 타입으로 생긴다. dtype 옵션으로 타입을 맞출 수도 있다.

dtypesql = {'exclusive':sqlalchemy.types.VARCHAR(10), 
          'cost':sqlalchemy.types.VARCHAR(10), 
          'contractedAt':sqlalchemy.Date(), 
          'createdAt':sqlalchemy.DateTime()}
df.to_sql(name='building', con=db_connection, if_exists='append', index=False,dtype=dtypesql)

3) 여러 개의 파일 저장하기

특정한 패턴이나 확장자를 가진 많은 양의 파일들을 다룰 때 이들의 경로나 이름이 필요할 때가 있다. glob 모듈의 glob() 함수는 파일의 경로명을 이용해 사용자가 제시한 조건에 맞는 파일명을 리스트 형식으로 반환한다. 즉 파일들의 리스트를 뽑을 때 사용한다!
sorted()은 이터러블로부터 새로운 정렬된 리스트를 만드는 내장 함수다. 오름차순이 디폴트!

# glob 모듈 import
from glob import glob

# 현재 디렉토리 data 폴더의 모든(*) csv 파일의 리스트 반환 후 새로운 변수에 담아준다
list = sorted(glob("data/*.csv"))

# 반복문으로 테이블 생성, 생성될 때마다 테이블이 노트북에 출력되도록 print() 한다
%%time
for i in range(0, len(list)):
    df= pd.read_csv(list[i], encoding='cp949')
    table_name='name'+'_'+str((list[i]))
    df.to_sql(name=table_name, con=db_connection, if_exists='append',index=False) 
    print(table_name)