MySQL 찍먹하기 !! (feat.pymysql)

pymysql 라이브러리를 사용해 mysql 데이터베이스에 접속하고, 테이블 생성부터 데이터 업로드, 테이블 삭제하는 방법까지의 과정에 대해 다룬다. 

---

0. MySQL 연결 

pymysql 라이브러리를 사용해 mysql에 접속한다. 연결 전에, MySQL 데이터베이스를 생성하고 ip 접속 허용 설정을 해 주어야 한다. 

1) MySQL 환경파일 기본 설정 

args = dict()
args['host'] = "본인의 ip 주소"
args['user'] = "db에 접속할 사용자 이름"
args['password'] = "db에 접속할 사용자 패스워드"
args['db'] = "db 이름"
args['port'] = 3306    # 기본 포트 번호

2) MySQL 연결 

import pymysql

conn = pymysql.connect(host=args['host'], port=args['port'], user=args['user'], password=args['password'], db=args['db'], charset='utf8')
curs = conn.cursor()

1. Database 조작  

0) SQL 실행 함수 정의 

def execute_sql(sql):
    curs.execute(sql)
    curs.fetchall()

1) 테이블 생성

해당 데이터베이스에 앞으로 사용할 테이블을 생성해준다. 

sql = "CREATE TABLE 테이블명(칼럼명 칼럼 자료형 NOT NULL, \
    칼럼명2 칼럼자료형2, ... \
    칼럼명n, 칼럼자료형n, ... \
    PRIMARY KEY(고유키 칼럼명) \
)"

execute_sql(sql)

테이블 조회

2) 한글 입력 허용

기본적으로 MySQL에서 생성한 테이블에는 한글 데이터 입력 시 오류가 발생한다. 한글 데이터를 적재하기 위해서는 다음과 같이 한글 입력을 허용하는 명령어를 추가로 실행해줘야 한다. 

sql = "ALTER TABLE 테이블명 CHARACTER SET utf8 COLLATE utf8_general_ci";
curs.execute(sql)

3) 데이터 업로드 

생성한 테이블에 내가 가지고 있는 데이터를 업로드한다. 본인은 주로 csv 파일을 데이터베이스 테이블에 업로드하는데, csv 파일 리더기를 사용해서 업로드하면 보다 쉽게 할 수 있다. VALUE와 format에 칼럼 수가 잘 매핑되어있는지 확인하고 데이터 업로드를 진행하자. 

 

* 간혹 데이터에 "'" 기호가 들어있는 경우 업로드가 제대로 되지 않는 경우가 있는데, 이때는 "'" 기호를 다른 문자나 공백으로 바꿔준 후 업로드를 해주어야 한다. 

import csv 

file_name = '업로드할 파일명.csv'
file = open(file_name, 'r', encoding='UTF8')
fReader = csv.reader(file)
err_list = [] 

for idx, line in enumerate(fReader):
    if idx == 0: 
        continue 
    
    sql = f"INSERT INTO 업로드할 테이블명 VALUES('{{0}}', ... , '{{n}}')".format(line[0], ... , line[n])
    curs.execute(sql)

DB에서 데이터를 조회해보면 업로드가 잘 된 것을 볼 수 있다.

SELECT * FROM 테이블명

4) 테이블 수정

간혹가다 데이터를 수정할 일이 생기는데, update 문을 활용해 데이터를 수정할 수 있다. 

sql = "UPDATE 테이블명 SET 컬럼1 = 수정값1, ... , 컬럼n = 수정값n [WHERE 조건];"
execute(sql)

5) 테이블 삭제

sql = "DROP TABLE 테이블명;"
execute_sql(sql)

6) 명령어 반영

간혹가다 commit이나 fetch를 하지 않으면 DB에 수행했던 명령어들이 반영되지 않을 때가 있다. 데이터 삽입, 삭제 같은 테이블 조작 명령어를 수행하고난 후에는 다음 명령어를 수행해주자. 

curs.fetchall()
conn.commit()
curs.close()
conn.close()