Jongya

Jonghyuk Park

Intro

pymysql은 python에서 mysql 을 사용할 수 있게끔 해주는 라이브러리입니다.
사용법이 쉽고 직관적이며, MySQL의 쿼리 구문을 그대로 사용할 수 있다는 점에서 사용성이 좋은 라이브러리입니다.

더불어 이 라이브러리를 통해 python과 DB간 직접 연결을 할 수 있기 때문에
별도의 중간 통신 방법을 마련하지 않아도 됩니다.



Flow

설치

pip를 통해 pymysql을 설치할 수 있습니다.

1

$ pip install pymysql

기본 사용법

(1) pymysql import

1

import pymysql

(2) DB 연결 객체 생성

1
2
3
4
5
6
7
8

conn = pymysql.connect(
    host = "MySQL 서비스의 IP",
    port = MySQL 서비스의 포트번호,    # int로 입력
    user = "DB 계정",
    passwd = "계정 비밀번호",
    db = "연결할 DataBase",
    charset = "DB에서 사용할 인코딩"
)

(3) Cursor 객체 생성

CLI 환경에서 MySQL을 사용한다고 생각해봅시다.
명령어를 작성할 때 깜빡이는 작은 커서가 보이죠?
Cursor 객체는 바로 이 커서를 의미합니다.

1

cursor = conn.cursor()

(4) SQL문 작성 및 실행

cursor.execute() 라는 메서드로 SQL문을 실행시킵니다.
execute : 실행하다.

쉬운 이해를 위해, SELECT로 조회하는 SQL문을 실행해보겠습니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

cursor.execute("SELECT * FROM test_table;")


# 쿼리가 길어질 경우 아래와 같이 쿼리문 변수 선언을 추천
sql = '''
INSERT INTO table_a (col1, col2, col3)
VALUES (val1, val2, val3)
ON DUPLICATE KEY UPDATE
    col1 = val1_b,
    col2 = val2_b,
    col2 = val3_b
'''
cursor.execute(sql)

(5) SQL문 실행 결과 받아오기

cursor.fetch() 메서드를 통해 받아올 수 있습니다.
해당 메서드는 2중 튜플 형태 ((출력행1-열1값,),(출력행1-열2값,)...(출력행n-열m값)) 로 결과값을 받아옵니다.

  • fetchall() : 모든 실행결과를 가져온다.
  • fetchone() : 실행결과를 한 번에 한 줄씩 가져온다.
  • fetchmany(n) : 실행결과를 n줄씩 가져온다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

result = cursor.fetchall()

for i in range(result):           # 열 for
    for j in range(result[i]):    # 행 for
        print(result)

>>> 001
>>> 테스트행1
>>> 테스트결과값1
>>> 002
>>> 테스트행2
>>> 테스트결과값2
...
>>> n
>>> 테스트행n
>>> 테스트결과값n

위 코드의 대상인 table은 아래와 같이 생겼습니다.

col1 col2 col3
001 테스트행1 테스트결과값1
002 테스트행2 테스트결과값2
… … …
n 테스트행n 테스트결과값n

(6) commit 혹은 rollback

cursor.execute() 를 통해 실행된 내용을 DB에 적용(commit) 하거나 이전 상태로 되돌릴(rollback) 수 있습니다.
commit 이나 rollback은 연결 객체(conn) 단에서 실행합니다.

1
2
3
4
5

# execute 한 명령을 DB에 적용
conn.commit()

# execute 이전 상태로 되돌리기
conn.rollback()

단, 자동 커밋(autocommit) 옵션이 켜져있는 경우엔 rollback이 불가능하니 execute 전 꼭 확인하기를 바랍니다.

1
2
3
4
5
6
7
8

# 자동 커밋 여부 확인
cursor.execute("SHOW VARIABLES LIKE 'AUTOCOMMIT';")
print(cursor.fetchall()[0][0])
>>> 자동커밋 활성화일 경우 : ('autocommit', 'ON')
>>> 자동커밋 비활성화일 경우 : ('autocommit', 'OFF')

# 자동 커밋 변경
cursor.execute("SET autocommit = FALSE;")

(7) 연결 해제

모든 작업을 완료했다면 DB와의 연결을 끊어줍니다.
close 를 하지 않은 연결이 쌓이다 보면 DB의 connection 자원이 고갈되어 연결이 불가능해질 수 있습니다.

1

conn.close()



응용

함께 응용하면 좋을 만한 내용을 몇 가지 적어보겠습니다.

f포매팅

쿼리문의 내용이 항상 고정될 수는 없겠죠?
예를 들어, 여러 행이 있는 엑셀을 DB에 넣는다거나 변화가 많은 변수조건을 적용해야 할 경우 등이 있습니다.

이 경우엔 f포매팅을 이용하면 편합니다.

예를 들어 아래와 같은 표 데이터(pandas)를 DB에 넣는다고 생각해봅시다.

이름 최고점 과목 점수
김철수 영어 85
이철수 수학 95
남궁철수 물리 100
베르나르철수 문학 120

그러면 아래와 같이 작성이 가능하죠.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

print(df)
>>> |이름|최고점 과목|점수|
>>> |---|---|---|
>>> |김철수|영어|85|
>>> |이철수|수학|95|
>>> |남궁철수|물리|100|
>>> |베르나르철수|문학|120|

for i in range(len(df)):
    name = df["이름"].iloc(i)
    subj = df["최고점 과목"].iloc(i)
    scor = df["점수"].iloc(u)

    sql = f'''
    INSERT INTO stud_score (name_col, sub_col, sco_col)
    VALUES ("{name}", "{subj}", {scor})
    ON DUPLICATE KEY UPDATE
        sub_col = "{subj}",
        sco_col = {scor};
    '''

    cursor.execute(sql)

% 를 이용한 포매팅도 가능합니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14

for i in range(len(df)):
    name = df["이름"].iloc(i)
    subj = df["최고점 과목"].iloc(i)
    scor = df["점수"].iloc(u)

    sql = f'''
    INSERT INTO stud_score (name_col, sub_col, sco_col)
    VALUES (%s, %s, %s)
    ON DUPLICATE KEY UPDATE
        sub_col = %s,
        sco_col = %s;
    '''

    cursor.execute(sql, (name, subj, scor, subj, scor))

f포매팅 주의할 점

그런데 %를 이용한 위 예시에서 이상한 점이 있습니다.
int 형인 “점수” 항목을 넘길 때 숫자를 뜻하는 %d 가 아닌, 문자를 뜻하는 %s로 표현했습니다.

이는 pymysql 에서 주의할 점인데요,
% 포매팅을 할 경우 모든 변수값은 스트링 형태(%s) 로 넘겨야 합니다.
문자든 숫자든 상관 없이요. 그러면 SQL 쪽에서 알맞게 해당 형태를 변환해 입력합니다.

그렇게 하지 않는다면 아래와 같은 오류를 볼 수 있습니다.

1

>>> %d format: a number is required, not str

pymysql에서 %포매팅을 할 때에는 무조건 문자열 형태(%s) 로 넘기자!

null 값 넣기

특정 부분에 값이 없는 경우, SQL에서는 (null) 로 표시가 됩니다.
DB에 null 값을 pymysql을 통해 넣으려면?

None 값을 이용하면 됩니다.

1
2
3
4
5
6
7
8
9
10
11
12

if isinstance(df["val_a"].iloc[i]) == True:
    val_a = df["val_a"].iloc[i]
else:
    val_a = None 

sql = f'''
INSERT INTO (col1)
VALUES %s
'''

cursor.execute(sql, (val_a))

가능한 쿼리문

위 예시에서는 SELECT, INSERT 와 같은 간단한 구문만을 예시로 들었습니다.
하지만 CREATE, GRANT 등의 명령어 또한 실행이 가능합니다.

python에서 MySQL을 사용한다고 생각하고 쓰시면 됩니다.



python에서 편리하게 MySQL을 사용할 수 있게 해주는 pymysql.
데이터를 다루는 입장에서는 꼭 알아야 할 필요가 있습니다.

여기까지 pymysql에 대한 설명이었습니다.
감사합니다.



Reference

  • pymysql 기본 사용법 : https://pymysql.readthedocs.io/en/latest/
  • pymysql 기본 사용법 : https://www.fun-coding.org
  • pymysql 기본 사용법 : https://velog.io/@c_hyun403/SQL-PyMySql
  • %포매팅 오류 : https://timmer.tistory.com/135
  • autocommit : https://loklee9.tistory.com/153
  • autocommit : https://zetawiki.com
  • fetch : http://pythonstudy.xyz

You May Also Enjoy