[Python] pymysql을 활용하여, mysql에서 mysqldump파일(.sql) 복원 및 csv파일 만들기

서론 : 머신러닝에 활용하기 위한 데이터를 만들기 위해 데이터 전처리를 해야하는 과정에서, .sql 파일을 통해 데이터베이스를 전달받아서 이를 python / jupyter notebook에 활용하기 위한 일련의 과정을 정리하였다.

 

- [input : .sql 확장자 파일, output : .csv 확장자 파일]

- 전제조건 : mysql 의 정상적인 설치

 

---

얼마전에 모 스타트업에(기업명은 향후에 이해관계가 없어지면.. 공개할지도?) ML 엔지니어에 서류합격이 되어, 1/31 까지 과제평가 대상자라는 통보를 받게 되었다. 과제가 뭘까 하고 첨부파일을 확인하는 순간, 이걸 할 수 있을까 ? 그래도 2주정도의 시간이 주어졌는데, 어떻게든 해보자. 라는 마음으로 현재도 진행중이다. 그 진행 과정의 일부를 포스팅하려고 한다. 첨부파일에서는 .sql로 제공되었다. 

 

sql이라, 너무나 오랜만에 다뤄보는 파일이다. 반년 전 즈음에 2주정도 맛보기를 하고 그 이후에는 건드려본 적이 없던 것이 자신감을 떨어뜨렸다. 하지만, sql 자체의 절대적인 학습분량은 많지 않고, 기본적인 구조자체는 잊지 않았으니 금방 리마인드 할 수 있을거라고 생각하고 차근차근 진행해보았다. 

 

[진행 환경 : m1 imac(Ventura 13.2), iterms2, mysql 8.0.x ver, vsc with jupyter notebook]

 

진행 절차는 크게 2가지로 나누어진다. 

1. .sql 파일을 mysql 상의 스키마 /데이터베이스로 복원(올리기)

2. pymysql를 활용하여, 스키마 상의 복원된 데이터베이스를 전달받아 csv로 추출하기

 

---

1. .sql 파일을 mysql 상의 스키마 /데이터베이스로 복원(올리기)

- 나는 mysql을 사용하면서, 데이터베이스를 내보내본 적은 없었다. 그렇기때문에 처음에는 .sql파일을 열어보고 난감하였다. 하지만, 검색을 통해 어렵지않게 답을 찾아나갈 수 있었으며, 결정적으로 아래와 같은 코드를 활용하였다.

(iterms의 진행경로와, .sql파일의 저장경로는 통일시켰다.)

 

brew services start mysql

mysql -uroot -p12345678

mysql> SHOW DATABASES;
mysql> CREATE SCHEMA `test_schema`;

mysql> USE test_schema;

mysql> SOURCE ./database.sql



mysql> SHOW tables;
mysql> SELECT * from books;
mysql> SELECT * from users;
mysql> SELECT * from log;

가독성을 위해, 기능별 개행을 해놨으며, 개략적으로 설명하자면, iterms를 통해 mysql로 진입(계정과 패스워드가 맞지않으면 골치아프다.)하여, sql을 불러와줄 스키마를 새로 생성해서(CREATE), 그 스키마를 사용하여(USE), 데이터베이스를 불러오면(SOURCE) 끝난다.

(나머지 코드의 경우, 제대로 복원이 되었는지 확인하기 위함이다.)

  * 스키마가 선택되어있지 않으면, 데이터베이스가 정상적으로 불러와지지 않는다.

---

반응형

---

2. pymysql를 활용하여, 스키마 상의 복원된 데이터베이스를 전달받아 csv로 추출하기

앞선 과정을 통해, 스키마 상에 데이터베이스/테이블이 제대로 올라간 것을 확인했으면, python 패키지를 통해 넘겨받아 저장을 진행할 수 있다. pandas를 활용하면 좀더 저장속도가 빠르다고 하여, 같이 사용하였다.

2-1. pymysql, pandas 설치

 

!pip install pymysql
!pip install pandas

 

2-2. 설치된 패키지들을 활용, 3개의 데이터베이스의 값들을 불러와서 csv로 내보내기

# extract database information to csv
import pandas as pd
import pymysql

conn=pymysql.connect(host='localhost',port=3306,user='root',password='12345678',db='test_schema')
queries = ["log", "products", "users"]

for query in queries : 
    sql=f"select * from {query}"

    result = pd.read_sql_query(sql,conn)
    result.to_csv(f'{query}.csv',index=False)

conn.close()

코드에 대해서는 크게 설명할 부분이 없고, 주로 오류가 난다면, pymysql.connect할 때, host, port, user, password, db등의 정보가 잘 맞아야 된다. 본인의 경우는 Port가 처음에 맞지 않아서, 아래와 같은 메시지가 발생했으며, iterms 에서 포트정보를 확인(결과 내 mysqld의 오른쪽 포트확인-3306, 33060) 하여 수정반영해줬다.(아래 코드 참조)

 

발생 에러메시지

OperationalError: (2003, "Can't connect to MySQL server on 'localhost' ([Errno 61] Connection refused)") 에러 메시지

해결 코드

lsof -n -P -i TCP -s TCP:LISTEN

결과

에러가 나지않는다면, csv파일 3개가 정상적으로 생성된다. 이상이다.

csv 파일이 생성되었다.

---

감사합니다. 오늘도 좋은하루 되세요.