Python (24) - 파이썬으로 MySQL 다루기 (PyMySQL)

우리가 다양한 프로젝트를 할 때 처음에는 엑셀 파일 및 CSV 파일등으로 데이터를 다룹니다. 하지만 프로젝트의 규모가 커지면 자연스럽게 DB를 사용합니다. 이번에는 pymysql을 이용하여 파이썬과 MySQL을 연결하여 활용한 방법을 알아보도록 하겠습니다.

Database - Pixabay

 

파이썬과 MySQL 연동

파이썬과 MySQL의 연동하는 것은 DB 관리 및 분석에 있어 중요한 도구입니다. 파이썬은 MySQL은 활용하기 위해  mysql-connector-python, PyMySQL, SQLAlchemy와 같은 다양한 라이브러리를 제공합니다. 이를 이용하여 DB에 저장된 데이터를 쉽게 다룰 수 있습니다. 두 프로그램의 연동을 통해 웹 애플리케이션 개발, 데이터 분석, 자동화 스크립트 작성 등 다양한 프로젝트에서 시너지를 낼 수 있습니다. 안정적이고 신뢰할 수 있는 데이터 처리를 위해 파이썬과 MySQL의 연동을 할 수 있습니다.

 

PyMySQL 설치 

pip install pymysql

터미널에 라이브러리를 설치하도록 합니다.

 

사용 방법

1. DB 연결 및 커서 생성

import pymysql

#초기설정
conn = pymysql.connect(host='localhost', 
                       port=3306,
                       user='root',
                       password='1q2w3e4r',
                       db = 'example',
                       charset='utf8')
#커서생성
cursor = conn.cursor()

connect 함수의 인자는 다음과 같습니다.

• host - 접속할 mysql 서버 주소. 자신의 컴퓨터를 서버로 사용한다면 ‘localhost’ 또는 127.0.0.1 을 입력한다.
• port - 접속할 mysql 서버의 포트 번호.
• user - 사용자 이름을 입력한다.
• password - 사용자의 암호를 입력한다.
• db - 데이터베이스 이름을 입력한다.
• charset - 캐릭터셋을 입력한다. 한글 사용시에는 utf-8 규격을 사용한다.

실행하게 되면 초기 설정한 값을 바탕으로 DB에 연결하게 됩니다. DB에 연결하면 커서를 생성하도록 합니다. 커서 객체에 DB 작업을 할 수 있는 함수들이 포함되어 있어 생성해야 합니다.

 

2. 데이터 작업 및 후처리

데이터를 삽입, 수정, 삭제, 조회하도록 하겠습니다.

 

데이터 조회 (select)

# 조회 쿼리
sql = 'SELECT * FROM student WHERE student_id = %s'
vals = (student_id,)
cursor.execute(sql, vals)

# 여러 줄 출력
rows = cursor.fetchall()
for row in rows:
    print(row)

# 한 줄 출력
row = cursor.fetchone()
if row is None:
    print('검색 결과 없음')
else:
    print(row)

참고로 포맷 문자는 다음과 같습니다.

• %s - “이 자리에 문자열”
• %d - “이 자리에 정수”
• %f - “이 자리에 실수”

 

데이터 삽입 (insert)

# 초기 데이터 설정
student_id = 12345
pwd = '1q2w3e4r'
name = '달서비'
email = 'example@naver.com'

# 인자를 사용하지 않는 경우
sql = "INSERT INTO student VALUES(12345, '1q2w3e4r', '달서비', 'example@naver.com')"
cursor.execute(sql)

# 인자를 사용하는 경우
sql = "INSERT INTO student VALUES(%s, %s, %s, %s)"
vals = (student_id, pwd, name, email)
cursor.execute(sql, vals)

# 커밋
conn.commit()

 

데이터 업데이트 (update)

sql = "UPDATE student SET name = %s, email = %s WHERE student_id = %s"
vals = ('새로운 이름', 'new_example@naver.com', student_id)
cursor.execute(sql, vals)

# 커밋
conn.commit()

 

데이터 삭제 (delete)

sql = "DELETE FROM student WHERE student_id = %s"
vals = (student_id,)
cursor.execute(sql, vals)

# 커밋
conn.commit()

 

3. 커밋 및 종료

#커밋 및 종료
conn.commit()
conn.close()

모든 작업이 완료되면 커밋하고 연결을 종료합니다.

 

함수로 구현하기

 SQL에서 가장 많이 사용하는 명령어로 Select, Create, Update, Delete가 있습니다. 코드 재사용성을 높이기 위해 함수로 구현하였습니다.

import pymysql

# 데이터베이스 연결 설정
def get_connection():
    return pymysql.connect(
        host='localhost',
        port=3306,
        user='root',
        password='1q2w3e4r',
        db='example',
        charset='utf8'
    )

# SELECT 쿼리
def select_record(conn, record_id):
    try:
        cursor = conn.cursor()
        select_query = "SELECT * FROM student WHERE student_id = %s"
        cursor.execute(select_query, (record_id,))
        result = cursor.fetchone()
        return result
    except pymysql.Error as e:
        print(f"SELECT 실행 중 에러: {e}")
        return None
    finally:
        cursor.close()

# INSERT 쿼리
def insert_record(conn, data):
    try:
        cursor = conn.cursor()
        insert_query = "INSERT INTO student (student_id, pwd, name, email) VALUES (%s, %s, %s, %s)"
        cursor.execute(insert_query, (data['student_id'], data['pwd'], data['name'], data['email']))
        conn.commit()
        return cursor.lastrowid
    except pymysql.Error as e:
        print(f"INSERT 실행 중 에러: {e}")
        return None
    finally:
        cursor.close()

# UPDATE 쿼리
def update_record(conn, record_id, new_data):
    try:
        cursor = conn.cursor()
        update_query = "UPDATE student SET name = %s, email = %s WHERE student_id = %s"
        cursor.execute(update_query, (new_data['name'], new_data['email'], record_id))
        conn.commit()
        return True
    except pymysql.Error as e:
        print(f"UPDATE 실행 중 에러: {e}")
        return False
    finally:
        cursor.close()

# DELETE 쿼리
def delete_record(conn, record_id):
    try:
        cursor = conn.cursor()
        delete_query = "DELETE FROM student WHERE student_id = %s"
        cursor.execute(delete_query, (record_id,))
        conn.commit()
        return True
    except pymysql.Error as e:
        print(f"DELETE 실행 중 에러: {e}")
        return False
    finally:
        cursor.close()
        
#=================================================================
# 데이터베이스 연결 설정
conn = get_connection()

# 초기 데이터 설정
student_data = {
    'student_id': 12345,
    'pwd': '1q2w3e4r',
    'name': '달서비',
    'email': 'example@naver.com'
}

# INSERT 실행
record_id = insert_record(conn, student_data)
print(f"생성된 레코드 ID: {record_id}")

# SELECT 실행
selected_record = select_record(conn, student_data['student_id'])
if selected_record:
    print("검색된 레코드:", selected_record)
else:
    print("데이터가 없습니다.")

# UPDATE 실행
new_data = {'name': '서비', 'email': 'new_example@naver.com'}
update_success = update_record(conn, student_data['student_id'], new_data)
print(f"수정 완료: {update_success}")

# DELETE 실행
delete_success = delete_record(conn, student_data['student_id'])
print(f"삭제 완료: {delete_success}")

# 연결 종료
conn.close()

 

 

4. Procedure 실행

기본적인 CRUD뿐만이 아니라 프로시저를 이용하여 SQL 작업을 할 수 있습니다.

먼저 예시가 되는 프로시저입니다.

CREATE PROCEDURE GetUserInfo(IN userid INT)
BEGIN
    SELECT * FROM student WHERE student_id = userid;
END;

 

해당 프로시저를 파이썬으로 실행하도록 하겠습니다.

import pymysql

def call_procedure(conn, record_id):
    try:
        cursor = conn.cursor()
        cursor.callproc('GetUserInfo', (record_id,))
        result = cursor.fetchall()
        if result:
            for row in result:
                print(row)
        else:
            print("결과가 없습니다.")
    except pymysql.Error as e:
        print(f"실행 중 오류가 발생하였습니다: {e}")
    finally:
        cursor.close()

# 데이터베이스 연결 설정
conn = pymysql.connect(
    host='localhost', 
    port=3306,
    user='root',
    password='1q2w3e4r',
    db='example',
    charset='utf8'
)

# 프로시저 호출
user_id = 12345
call_procedure(conn, user_id)

# 종료
conn.close()

 

참고 자료

게시글 작 중 참조한 게시글 및 document 링크를 첨부합니다.

https://pymysql.readthedocs.io/en/latest/#

Welcome to PyMySQL’s documentation! — PyMySQL 0.7.2 documentation

 

https://passwd.tistory.com/entry/Python-PyMySQL-MySQL-SELECT-%ED%95%98%EA%B8%B0

[Python] PyMySQL - MySQL SELECT 하기