Flask: MySQL Query

Seoyul Kim·2020년 7월 16일
WeCodemysqlpymysql위코드
1

Flask

목록 보기
1/1

MySQL DB 모듈

Python에서 MySQL 데이타베이스를 사용하기 위해 PyMySql 이라는 모듈을 사용한다.

MySQL DB 모듈 중 하나인 PyMySql 모듈을 다음과 같이 설치한다.

$ pip install PyMySQL

MySql 사용 절차

Python에서 MySQL에 있는 데이터를 사용하는 일반적인 절차는 다음과 같다.

  • PyMySql 모듈을 import 한다.
import pymysql
  • pymysql.connect() 메소드를 사용하여 MySQL에 Connect 한다. 호스트명, 로그인, 암호, 접속할 DB 등을 파라미터로 지정한다.
#connections.py
# MySQL Connection 연결

def db_connector():
    connector = pymysql.connect(**MYSQL_CONFIGS)
    return connector
#config.py

MYSQL_CONFIGS = {
     'host': 'localhost',
     'port': 3306,
     'database': 'test',
     'user': 'root',
     'passwd': '12345',
     'charset': 'utf8mb4',
     'autocommit': False
 }
  • DB 접속이 성공하면, Connection 객체로부터 cursor() 메서드를 호출하여 Cursor 객체를 가져온다. DB 커서는 Fetch 동작을 관리하는데 사용되는데, 만약 DB 자체가 커서를 지원하지 않으면, Python DB API에서 이 커서 동작을 Emulation 하게 된다.
#controllers/user.py

from connections import db_connector

db = db_connector()

#models.py

with db.cursor() as cursor:

# Connection 으로부터 Dictoionary Cursor 생성
with db.cursor(pymysql.cursors.DictCursor) as cursor:
  • Cursor 객체의 execute() 메서드를 사용하여 SQL 문장을 DB 서버에 보낸다.
#models.py
# SQL문 실행

affected_row = cursor.execute(query, (user_id, name))

  • SQL 쿼리의 경우 Cursor 객체의 fetchall(), fetchone(), fetchmany() 등의 메서드를 사용하여 데이터를 서버로부터 가져온 후, Fetch 된 데이터를 사용한다.

  • 삽입, 갱신, 삭제 등의 DML(Data Manipulation Language) 문장을 실행하는 경우, INSERT/UPDATE/DELETE 후 Connection 객체의 commit() 메서드를 사용하여 데이터를 확정 갱신한다.

#controllers/user.py

db.begin()
db.commit()
  • Connection 객체의 close() 메서드를 사용하여 DB 연결을 닫는다.
#controllers/user.py
# Connection 닫기

finally:
        if db:
            db.close()

✔️ Check
(1) connect() 메서드에서 종종 클라이언트의 charset이 제대로 설정되지 않으면 한글이 깨지는 경우가 발생할 수 있다.
(2) 커서의 fetchall() 메서드는 모든 데이터를 한꺼번에 클라이언트로 가져올 때 사용되며, fetchone()은 한번 호출에 하나의 Row 만을 가져올 때 사용된다. fetchone()을 여러 번 호출하면, 호출 때 마다 한 Row 씩 데이터를 가져오게 된다. 그리고 fetchmany(n) 메서드는 n개 만큼의 데이터를 한꺼번에 가져올 때 사용된다.
(3) print(rows) 문은 전체 row들을 Tuple의 Tuple로서 출력하게 되고, row[0], row[1]와 같이 인덱스를 지정하면, 첫번째, 두번째 row 등을 가리키게 된다. 각 row는 Tuple로 리턴되며, 컬럼 순서대로 데이터가 표시된다 (이러한 디폴트 Row 데이터 형식을 흔히 Array based cursor라 부르고, 이는 connect() 혹은 cursor() 메서드에서 옵션으로 변경할 수 있다.)

rows = curs.fetchall()
print(rows) 
(1, '김하은', 1, '서울')

📍 주의
만약 Python 문자열에서 사용하는 기본 String Interpolation을 사용하면 데이터에 특수 문자가 있는 경우 SQL문 문법 오류를 발생시킬 수 있다. 기본 String Interpolation을 사용하여 변수 data 안에 단일 인용부호가 있는 경우 SQL Syntax 에러를 유발시키게 된다.
또 이런 String Interpolation 혹은 문자열 결합(Concatenation)을 통해 동적 SQL 문을 만드는 방법은 SQL Injection 공격에 쉽게 노출되는 문제점이 있다.

sql = "select * from customer where category=%s and region=%s"
curs.execute(sql, (1, '서울'))
# 잘못된 표현
data = '서\'울'
sql = "select * from customer where category=%s and region=%s" % (1, data)
curs.execute(sql)

MySQL DML

기본 절차

  • PyMySql 모듈을 import 한다.

  • pymysql.connect() 메소드를 사용하여 MySQL에 Connect 한다. 호스트명, 로그인, 암호, 접속할 DB 등을 파라미터로 지정한다.

  • DB 접속이 성공하면, Connection 객체로부터 cursor() 메서드를 호출하여 Cursor 객체를 가져온다.

  • Cursor 객체의 execute() 메서드를 사용하여 INSERT, UPDATE 혹은 DELETE 문장을 DB 서버에 보낸다.

  • 삽입, 갱신, 삭제 등이 모두 끝났으면 Connection 객체의 commit() 메서드를 사용하여 데이타를 Commit 한다. 디폴트로 Autocommit 이 아니므로 commit()을 명시적으로 호출한다. 자동커밋을 원하는 경우, connect() 메서드 호출시 autocommit=True 를 지정한다.

  • Connection 객체의 close() 메서드를 사용하여 DB 연결을 닫는다.

INSERT

  • 데이타를 테이블에 추가하기 위해 INSERT문을 사용한다. INSERT문에 사용하는 각 컬럼값들은 직접 값을 지정하거나 Parameter Placeholder를 사용할 수 있다.

  • 데이터를 INSERT만 실행하고 Commit하지 않으면, 테이블의 데이타는 변경되지 않는다.

import pymysql

#connections.py
def db_connector():
    connector = pymysql.connect(**MYSQL_CONFIGS)
    return connector
 
#controllers/user.py
from connections import db_connector
db = db_connector()

#models.py
with db.cursor() as cursor:
query = """
	insert into customer(name,category,region)
    values (%s, %s, %s)
    """
cursor.execute(query, ('김하은', 1, '서울'))
db.commit()
 
db.close()
  • execute() 메서드는 하나의 Row를 치환하여 실행하는 반면, executemany() 메소드는 복수개의 Tuple 데이터를 하나의 DML 문에 적용하게 된다.
data = (
    ('김하은', 1, '서울'),
    ('김수지', 2, '부산'),
    ('김희은', 1, '서울'),
)
query= """
	insert into customer(name,category,region)
    values (%s, %s, %s)
  	"""
cursor.executemany(sql, data)
db.commit()

UPDATE, DELETE

기존의 데이터를 수정하기 위해 UPDATE 문을 사용하고, 삭제하기 위해서 DELETE 문을 사용한다. 

#update
query = """
	update customer
    set region = '서울특별시'
    where region = '서울'
    """
cursor.execute(sql)
 
#delete
query = "delete from customer where id=%s"
curs.execute(sql, 6)
 
conn.commit()
conn.close()

try와 with 문의 사용

SQL Connection을 열고 프로그램 중간에서 에러가 발생하면, Connection은 그대로 열려 있는 상태로 있을 수 있다. 이렇게 오픈되어 있는 Connection이 증가하면, 나중에 새로운 Connection을 오픈할 수 없게 되는데, 이를 Connection Leak 이라 부른다. 이러한 Connection Leak을 막기 위하여 try...finally를 사용하여 finally에서 항상 Conneciton을 Close해 주는 것이 좋다.

try:
    # INSERT
    with db.cursor() as cursor:
        query = "
        	insert into customer(name, category, region) 
            values (%s, %s, %s)
            "
        cursor.execute(sql, ('김하은', 1, '서울'))
 
    conn.commit()
 
    # SELECT
    with db.cursor() as cursor:
        query = "select * FROM customer"
        cursor.execute(sql)
        
finally:
    db.close()

INSERT와 SELECT 문을 각기 다른 커서에서 사용하고 있다. 첫번째 INSERT 실행 시 with 문으로 커서를 만들어 자동으로 커서 리소스가 해제되도록 하였고, 두번째 SELECT 시에도 with 문으로 해당 커서가 자동 해제되도록 하였다.
SQL 객체들을 다룰 때 try...finally 나 with 문을 적절히 사용하여 리소스를 해제해 주는 것이 좋다.

Transaction

복수 개의 SQL 문을 하나의 트랜잭션으로 묶기 위하여 sql.DB의 Begin() 메서드를 사용한다. 트랜잭션은 복수 개의 SQL 문을 실행하다 중간에 어떤 한 SQL문에서라도 에러가 발생하면 전체 SQL문을 취소하게 되고(rollback), 모두 성공적으로 실행되어야 전체를 commit하게 된다.

profile
Seoyul Kim

0개의 댓글