PyMySQL로 데이터 적재하기

앞서 Workbench에서 xlsx(csv) 파일을 DB에 넣는 방법을 배웠습니다. 그렇다면, 이번엔 PyMySQL을 사용해서 xlsx(csv) 파일을 바로 DB에 넣는 방법을 알아보겠습니다. csv나 xlsx 형식으로 raw data를 제공하는 경우가 많으니, 이 방법도 알면 보다 수월한 작업을 진행할 수 있을테죠!

방법은 여러가지가 있는데, 본 교안에서는 csv와 xlsx 모두 사용할 수 있는 방법 한가지를 소개하겠습니다. 교안이 참고한 블로그 출처

다른 방법이 궁금하다면 아래 블로그를 참고해보세요!

csv to mysql, mysql to csv (동영상 강의 있음)

mysql for excel 다운받아서 하는 방법

1. pandas 활용해서 xlsx(csv) 가져오기

기존에 Python에서 xlsx(csv)파일을 사용하듯 pandas를 활용해서 데이터를 가져옵니다.

import pandas as pd

data = pd.read_excel("data.xlsx")
data

'''csv 코드 (이외는 xlsx과 똑같음!)
import pandas as pd

data = pd.read_csv("data.csv", encoding='utf-8')
data
'''

2. DB 만들어주기

기존에 있던 DB를 사용한다면 해당 부분은 진행하지 않아도 됩니다.

### DB만들기

#1
import pymysql

#2. connect
db = pymysql.connect(host = 'localhost', port = 3306, user='root', passwd = '********', charset='utf8')

#3. curosr 사용하기
cursor = db.cursor() 

#4. sql 구문 (DB만들어주기) 만들기
sql = "CREATE DATABASE new_database" 

#5 cursor execute
cursor.execute(sql) 

#6. commit (최종 변경)
db.commit() 

#7. DB 닫아주기 
db.close()

3. TABLE 만들기

DB와 같이 기존에 있는 DB의 TABLE을 사용한다면 해당 부분은 진행하지 않아도 됩니다. TABLE을 새로 만든다면 넣어줄 데이터와 똑같은 형식 (컬럼, 데이터 타입)으로 지정하여 만들어주어야 합니다.

#1
import pymysql

#2. connect
db = pymysql.connect(host='localhost',  port = 3306, user='root', password='********', db='new_database',charset='utf8') 

#3. curosr 사용하기
cursor = db.cursor() 

#4. sql 구문 (DB만들어주기) 만들기
my_table= "CREATE TABLE IF NOT EXISTS new_table(column1 VARCHAR(10) primary key, column2 VARCHAR(10), column3 varchar(10))" 

#5 cursor execute
cursor.execute(my_table)

#6. commit (최종 변경)
db.commit() 

#7. DB 닫아주기 
db.close()

4. 데이터 가져와서 DB에 넣어주기

DB에 데이터를 넣어주는 INSERT를 진행합니다. 이때 for문을 사용해서 전체 데이터를 한번에 넣어줄 수 있습니다. (PyMySQL을 쓰는 보람이 느껴지는 부분!) 추가로, INSERT할 때 VALUE의 형식을 잘 지정해주어야 합니다!

#1
import pymysql

#2. connect
db = pymysql.connect(host='localhost',  port = 3306, user='root', password='********', db='new_database',charset='utf8') 

#3. curosr 사용하기
cursor = db.cursor() 

#4. sql 구문 (DB만들어주기) 만들기 + cursor execute
sql='INSERT INTO new_table(column1, column2, column3) VALUES(%s, %s, %s)'
for idx in range(len(data)):
    cursor.execute(sql, tuple(data.values[idx]))

#6. commit (최종 변경)
db.commit() 

#7. DB 닫아주기 
db.close()