MySQL + Python

pymysql을 활용하면 python을 이용해 MySQL의 데이터베이스에 접근할 수 있다.

> pip install pymysql

DB 접근

import pymysql

db = pymysql.connect(
    host="localhost",
    port=3306,
    user="root",
    passwd="1234",  # 비밀번호
    db="testdb",  # DB 이름
    charset="utf8",  # 인코딩 방식
)

cursor = db.cursor()
# cursor = db.cursor(pymysql.cursors.DictCursor)
# Unbuffered: SSCursor, SSDictCursor

connect 함수를 이용해 DB에 접근하려면, 위 내용을 인자로 전달해 주어야 한다. 로컬 DB일 경우, host와 port는 위와 같이 설정하면 된다. 세부적인 파라미터는 해당 문서에 작성되어 있다.

cursor 함수는 SQL 객체를 다룰 때 사용한다. 기본으로 설정하면 tuple 형태로 값을 가져오지만 DictCursor를 사용하면 dictonary 형태로 가져올 수 있다. SSCursor와 SSDictCursor는 Unbuffered 커서이다. 일반적인 커서는 데이터를 모두 가져온다. 반면, unbuffered 커서는 필요한 만큼 데이터를 가져와 사용하기 때문에 대용량 데이터를 다루거나 속도 향상에 유리하다.

* DB 이름을 모를 땐, 아래 SQL 명령을 통해 확인할 수 있다.

-- MySQL
SELECT DATABASE();

연결 상태

db.open
>>> True / False

open은 연결상태에 따라 True 또는 False를 반환한다.

데이터 조회

# SQL Query 작성
sql = " ... "
# 실행
cursor.execute(sql)
result = cursor.fetchall()

execute 안에 SQL 쿼리를 작성하면 실행할 수 있다. fetchall은 해당하는 모든 데이터를 가져온다.

아래 예시는 데이터를 조회하고 Pandas를 이용해 정리하는 과정이다.

import pymysql
import pandas as pd

db = pymysql.connect( ... ) # 생략
cursor = db.cursor(pymysql.cursors.DictCursor)

sql = "SELECT * FROM people;"
cursor.execute(sql)
result = cursor.fetchall()

df = pd.DataFrame(result)
df.set_index("id", inplace=True)
print(df.head())

     name
id
1   James
2    John
3     Amy
4   Daivd
5    Mark

Workbench를 통해 조회한 결과와 동일한 값을 가져온 곳을 확인했다.

cursor.fetchone()
cursor.fetchmany(n) # n: int

fetchall 대신 fetchone을 이용하면 호출할 때마다 한 행씩 가져온다. fetchmany는 호출할 때마다 n개의 데이터를 가져온다. (앞에서 가져온 데이터에 이어 새로운 데이터를 가져온다.)

DB 수정

sql = " ... "
cursor.execute(sql)
db.commit()
db.close()

DB에 정보를 수정하는 쿼리를 작성한 후, execute로 실행한다. 이후 commit을 통해 DB에 수정된 정보를 업데이트하고, close로 종료한다. (commit을 실행하지 않으면 정보가 DB에 반영되지 않는다.)

아래 예시는 DB에 정보를 추가하는 과정을 나타낸 것이다.

import pymysql

db = pymysql.connect(...)  # 생략
cursor = db.cursor()

sql = """INSERT INTO people (name)
	VALUES ('Tom'), ('Andrew'), ('Peter');"""
cursor.execute(sql)

db.commit()
db.close()

결과:

SELECT * FROM people;

전에 생성된 데이터(1~6)에 추가로 데이터(7~9)가 생성된 것을 확인했다.

Execute 응용

sql = " ... %s ... "
data = ( ... )
cursor.execute(sql, data)

쿼리문에 %s를 작성해 변수가 들어갈 공간을 생성한다. (python의 % 문자열 포맷팅과 다르다. 자료형에 관계없이 %s를 사용한다.) 이후 execute에 쿼리문(str)과 데이터(list, tuple)를 넣어 실행한다.

아래 예시는 조건에 따라 데이터를 가져오는 작업이다.

# ... 생략 ...

sql = """SELECT * FROM people WHERE id = 3 || id > 6"""
cursor.execute(sql)
result = cursor.fetchall()

위 코드를 %s를 활용해 작성하면 아래와 같다.

# ... 생략 ...

sql = """SELECT * FROM people WHERE id = %s || id > %s"""
data = [3, 6]
cursor.execute(sql, data)
result = cursor.fetchall()
>>> ((3, 'Amy'), (7, 'Tom'), (8, 'Andrew'), (9, 'Peter'))

Executemany

# execute
sql = """INSERT INTO people (id, name) 
        VALUES (4, 'David'), (5, 'Tom'), (6, 'Peter');"""
cursor.execute(sql)

# executemany
sql = """INSERT INTO people (id, name) VALUES (%s, %s);"""
data = [[4, "David"], [5, "Tom"], [6, "Peter"]] # 반복
cursor.executemany(sql, data)

executemany는 INSERT와 같이 일정한 패턴으로 반복되는 데이터를 삽입할 때 유용하다. 데이터는 list 또는 tuple로 작성할 수 있다.