잔재미코딩

데이터베이스 심화 (MySQL+AWS) - pandas DataFrame to_sql 메서드 사용 예제

2. pandas DataFrame to_sql 메서드 사용 예제

  • sqlalchemy 라이브러리 활용해서 DataFrame을 바로 Mysql에 저장하는 방법
    • pip install sqlalchemy
  • 미리 데이터베이스 만들 때 다음 utf-8 설정을 써줘야, 한글아 들어 있는 table 자동생성이 to_sql 로 가능하다.
    • CREATE DATABASE dave_db_utfset DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

2.1 테이블에 따라 컬럼명 맞춰주기

In [ ]:
estate_data
In [61]:
columns = ['price', 'year', 'dong', 'apartname', 'month', 'day', 'space', 'address', 'addresscode', 'floor']
df = pd.DataFrame(estate_data, columns=columns)

2.2 create_engine (SQL DB 연결 라이브러리) 임포트

In [20]:
from sqlalchemy import create_engine

2.3 create_engine 으로 SQL DB 연결



  • engine = create_engine("mysql+pymysql://아이디:"+"암호"+"@mysql주소:포트/데이터베이스이름?charset=utf8", encoding='utf-8')
In [ ]:
db.close() ### 이전 DB 커넥션을 끊어주세요
In [63]:
engine = create_engine("mysql+pymysql://davelee:"+"korea356"+"@funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com:3306/estate_db?charset=utf8", encoding='utf-8')
In [64]:
conn = engine.connect()

2.4 to_sql 사용하기

  • df.to_sql(name=테이블이름, con=engine, if_exists='append', index=False)
  • Dataframe은 항상 index가 있기 때문에, 이 부분이 테이블 구조와 안맞을 수 있음, 그래서 index=False 로 작성
  • 각 column name을 테이블의 컬럼명과 동일하게 하면 해당 컬럼에 데이터 입력
  • estate_id 는 PRIMARY KEY로 AUTO_INCREMENT 옵션을 넣었으므로, 데이터 입력을 하지 않음
In [ ]:
df
  • name='테이블명' 이름으로 기존 테이블이 있으면 해당 테이블의 컬럼명에 맞게 데이터를 넣을 수 있음
    • if_exists='append' 옵션이 있으면, 기존 테이블에 데이터를 추가로 넣음
    • if_exists='fail' 옵션이 있으면, 기존 테이블이 있을 경우, 아무일도 하지 않음
    • if_exists='replace' 옵션이 있으면, 기존 테이블이 있을 경우, 기존 테이블을 삭제하고, 다시 테이블을 만들어서, 새로 데이터를 넣음
  • 이미 만들어진 테이블이 없으면, name='테이블명' 이름으로 테이블을 자동으로 만들고, 데이터를 넣을 수 있음
    • 테이블이 자동으로 만들어지므로, 테이블 구조가 최적화되지 않아 자동으로 테이블 만드는 것은 추천하지 않음


In [66]:
df.to_sql(name='estate_new', con=engine, if_exists='append', index=False)
  • conn.close() 로 DB 연결을 닫음
In [67]:
conn.close()
In [68]:
import pymysql
import pandas as pd
In [69]:
host_name = "funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com"
username = ""
password = ""
database_name = "estate_db"    # 데이터베이스 이름을 sakila 로 바꿔줘야 합니다.
In [70]:
db = pymysql.connect(
    host=host_name,  # DATABASE_HOST
    port=3306,
    user=username,  # DATABASE_USERNAME
    passwd=password,  # DATABASE_PASSWORD
    db=database_name,  # DATABASE_NAME
    charset='utf8'
)
In [40]:
SQL = "SELECT * FROM estate_new LIMIT 1"
df = pd.read_sql(SQL, db)
df
Out[40]:
price year dong apartname month day space address addresscode floor
0 33700 2017 수색동 대림한숲 8 1~10 59.9 415-1 11380 1
In [41]:
SQL = "DESC estate_new"
df = pd.read_sql(SQL, db)
df
Out[41]:
Field Type Null Key Default Extra
0 price bigint(20) YES None
1 year text YES None
2 dong text YES None
3 apartname text YES None
4 month text YES None
5 day text YES None
6 space text YES None
7 address text YES None
8 addresscode text YES None
9 floor text YES None


In [71]:
SQL = "SELECT COUNT(*) FROM estate_new"
df = pd.read_sql(SQL, db)
df
Out[71]:
COUNT(*)
0 392
In [43]:
db.close() ### 이전 DB 커넥션을 끊어주세요

2.5. sqlalchemy 라이브러리에서의 인코딩 문제 정리

  • sqlalchemy 라이브러리에서는 가능한 모든 곳에 utf8설정을 해야 함
    • engine = create_engine("mysql+pymysql://davelee:"+"korea123"+"@funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com:3306/dave_db?charset=utf8", encoding='utf-8')