[Python] Jupyter notebook사용해서 MY SQL에 csv파일 Import하는 방법

CSV파일을 python을 이용해서 MY SQL에 Import하는 방법입니다.
주피터 노트북을 열어서 실행해주시면 됩니다. 

---

#설치 안한 경우만 설치
#!pip install pymysql
#!pip install sqlalchemy

Requirement already satisfied: sqlalchemy in c:\users\user\anaconda3\lib\site-packages (1.4.32)
Requirement already satisfied: greenlet!=0.4.17 in c:\users\user\anaconda3\lib\site-packages (from sqlalchemy) (1.1.1)

 

from sqlalchemy import create_engine
import pymysql
import pandas as pd

#유저이름, 비밀번호, host address, dbname만 원하는대로 변경 후 진행하면 된다. 
#db_connection_str = 'mysql+pymysql://[db유저이름]:[db비밀번호]@[host address]/[db name]'
db_connection_str = 'mysql+pymysql://root:root@localhost:3306/mydata'
db_connection = create_engine(db_connection_str)
conn = db_connection.connect()

#Working Directory 확인
#os라는 모듈 필요, 모듈안의 getcwd함수를 사용해서 Working Directory경로 확인
import os

current_path = os.getcwd()
current_path

'C:\\Users\\USER'

#Working Directory에 csv data 넣은 경우
#다른 경로에 넣으면, '경로\Womens Clothing E-Commerce Reviews.csv'해줘야함
df_reviews = pd.read_csv('Womens Clothing E-Commerce Reviews.csv')
df_reviews

	Clothing ID	Age	Title	Review Text	Rating	Recommended IND	Positive Feedback Count	Division Name	Department Name	Class Name
0	767	33	NaN	Absolutely wonderful - silky and sexy and comf...	4	1	0	Initmates	Intimate	Intimates
1	1080	34	NaN	Love this dress! it's sooo pretty. i happene...	5	1	4	General	Dresses	Dresses
2	1077	60	Some major design flaws	I had such high hopes for this dress and reall...	3	0	0	General	Dresses	Dresses
3	1049	50	My favorite buy!	I love love love this jumpsuit. it's fun fl...	5	1	0	General Petite	Bottoms	Pants
4	847	47	Flattering shirt	This shirt is very flattering to all due to th...	5	1	6	General	Tops	Blouses
...	...	...	...	...	...	...	...	...	...	...
23481	1104	34	Great dress for many occasions	I was very happy to snag this dress at such a ...	5	1	0	General Petite	Dresses	Dresses
23482	862	48	Wish it was made of cotton	It reminds me of maternity clothes. soft stre...	3	1	0	General Petite	Tops	Knits
23483	1104	31	Cute but see through	This fit well but the top was very see throug...	3	0	1	General Petite	Dresses	Dresses
23484	1084	28	Very cute dress perfect for summer parties an...	I bought this dress for a wedding i have this ...	3	1	2	General	Dresses	Dresses
23485	1104	52	Please make more like this one!	This dress in a lovely platinum is feminine an...	5	1	22	General Petite	Dresses	Dresses

23486 rows × 10 columns

#name은 테이블 이름 의미, 테이블 이름을 dataset1으로 정하겠다.
#connect는 위에서 만들어준 db_connectrion으로 쓰겠다.
#if_existis = 'replace': 이미 있으면 덮어주겠다.
#index = FASLE는 테이블 맨 좌측에 자동으로 생성된 인덱스 번호는 안넣겠다. 
df_reviews.to_sql(name='dataset1', con=db_connection, if_exists='replace', index=False)

23486