Python with mysql8 - 데이터베이스 연결 및 예제

Python + Mysql

예제 환경

• Mysql8 on docker
• Visual studio code
• MySql 도커 실행환경은 전 블로그 참고

2022.01.05 - [DevOps/Docker] - Docker - MySql 8 개발환경, docker-compose로 간단하게 구성하기

1. pymysql 설치

python 라이브러리로 mysql 접속 및 쿼리 기능을 제공한다.

python3 -m pip install PyMySQL

예제 테이블 생성문

CREATE TABLE `student` (
  `ID` mediumint NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `dob` char(8) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
)

2. 데이터베이스 연결

def get_conn():
    return pymysql.connect(
        host="127.0.0.1",
        user="username",
        password="user_password",
        database="user_database",
        charset="utf8",
        cursorclass=pymysql.cursors.DictCursor)

• pymysql은 cursorclass로 DictCursor, SSCursor 와 SSDictCursor를 제공한다.
• SS시리즈 커서는 Unbuffered Cursor로서, 대용량 데이터 조회에 적합하다.
• connect option은 아래 레퍼런스 사이트 참조
• https://pymysql.readthedocs.io/en/latest/modules/connections.html#module-pymysql.connections 

커서 별 결과 Type은 아래와 같다.

2.1. DictCursor & SSDictCursor

{'ID': 35, 'name': 'choi', 'dob': '20000101', 'created_at': datetime.datetime(2022, 9, 2, 1, 54, 14), 'updated_at': datetime.datetime(2022, 9, 2, 1, 54, 14)}

2.2. SSCursor

(30, 'choi', '20000101', datetime.datetime(2022, 9, 2, 1, 48, 21), datetime.datetime(2022, 9, 2, 1, 48, 21))

3. CRUD ( 저장, 조회, 업데이트, 삭제 )

3.1. 저장

def save_data_for_one(conn, data):
    with conn.cursor() as cursor:
        query = None
        if ( type(data) == type({})):
            query = """
                insert into student ( name, dob, created_at, updated_at )
                values (%(name)s, %(dob)s, CURRENT_TIMESTAMP() , CURRENT_TIMESTAMP() )
            """
        else:
            query = """
                insert into student ( name, dob, created_at, updated_at )
                values (%s, %s, CURRENT_TIMESTAMP() , CURRENT_TIMESTAMP() )
            """

        cursor.execute(query, data)

• 입력 데이터 Type이 dict, tuple 형태에 따라 위와 같이 데이터를 바인딩 한다.
• 단 건은 execute 사용

def save_data_for_many(conn, data):
    with conn.cursor() as cursor:
        query = """
            insert into student ( name, dob, created_at, updated_at )
            values (%(name)s, %(dob)s, CURRENT_TIMESTAMP() , CURRENT_TIMESTAMP() )
        """
        cursor.executemany(query, data)

• 다 건은 executemany 사용

3.2. 조회

def select_data(conn):
    with conn.cursor() as cursor:
        pass
        query = """
            select * from student
        """
        cursor.execute(query)
        result = cursor.fetchall()
        print("데이터 수:", len(result))
        for data in result:
            print(data)

    return result

3.3. 업데이트

def update_dob_by_name(conn, dob, name):
    with conn.cursor() as cursor:
        print(cursor)
        query = """
            update student set dob = %(dob)s student where name = %(name)s
        """
        cursor.execute(query)

3.4. 삭제

def delete_all_data(conn):
    with conn.cursor() as cursor:
        query = """
            delete from student
        """
        cursor.execute(query)

4. 테스트 실행

if __name__ == '__main__':
    conn = get_conn()
    try:
        # 단건 저장
        delete_all_data(conn)
        # for dict
        save_data_for_one(conn, data={'name': 'choi', 'dob': '20000101'})
        # for tuple
        save_data_for_one(conn, data=('kim', '19830101'))
        result = select_data(conn)

        # 다건 저장
        # for dict
        save_data_for_many(conn, data=[
            {'name': 'choi1', 'dob': '20000101'},
            {'name': 'choi2', 'dob': '20000102'},
            {'name': 'choi3', 'dob': '20000103'}
        ])
        result = select_data(conn)

        conn.commit()
    except pymysql.Error as mye:
        code, msg = mye.args
        print(f"Error occured [{code}] - {msg}")
    except Exception as e:
        print(e)
    finally:
        conn.close()

• pymysql.Error를 구현하여 code, msg를 받아 출력한다.
• 프로세스 종료 후 close()를 호출하여 connection 자원을 반환한다.

결과

$ python python_mysql.py 
데이터 수: 2
{'ID': 40, 'name': 'choi', 'dob': '20000101', 'created_at': datetime.datetime(2022, 9, 2, 2, 7, 2), 'updated_at': datetime.datetime(2022, 9, 2, 2, 7, 2)}
{'ID': 41, 'name': 'kim', 'dob': '19830101', 'created_at': datetime.datetime(2022, 9, 2, 2, 7, 2), 'updated_at': datetime.datetime(2022, 9, 2, 2, 7, 2)}
데이터 수: 5
{'ID': 40, 'name': 'choi', 'dob': '20000101', 'created_at': datetime.datetime(2022, 9, 2, 2, 7, 2), 'updated_at': datetime.datetime(2022, 9, 2, 2, 7, 2)}
{'ID': 41, 'name': 'kim', 'dob': '19830101', 'created_at': datetime.datetime(2022, 9, 2, 2, 7, 2), 'updated_at': datetime.datetime(2022, 9, 2, 2, 7, 2)}
{'ID': 42, 'name': 'choi1', 'dob': '20000101', 'created_at': datetime.datetime(2022, 9, 2, 2, 7, 2), 'updated_at': datetime.datetime(2022, 9, 2, 2, 7, 2)}
{'ID': 43, 'name': 'choi2', 'dob': '20000102', 'created_at': datetime.datetime(2022, 9, 2, 2, 7, 2), 'updated_at': datetime.datetime(2022, 9, 2, 2, 7, 2)}
{'ID': 44, 'name': 'choi3', 'dob': '20000103', 'created_at': datetime.datetime(2022, 9, 2, 2, 7, 2), 'updated_at': datetime.datetime(2022, 9, 2, 2, 7, 2)}

 

Reference

https://pymysql.readthedocs.io/en/latest/index.html

Welcome to PyMySQL’s documentation! — PyMySQL 0.7.2 documentation