데이터베이스 심화 (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]:
In [41]:
SQL = "DESC estate_new"
df = pd.read_sql(SQL, db)
df
Out[41]:
In [71]:
SQL = "SELECT COUNT(*) FROM estate_new"
df = pd.read_sql(SQL, db)
df
Out[71]:
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')