Python x MySQL 연동

pymysql 모듈 설치 및 사용

데이터 소스로 접속

•

데이터 소스 : DB 접속 정보

◦

계정

◦

비밀번호

◦

호스트

◦

스키마(데이터베이스)

데이터 베이스 접속

커서 생성

조회 및 조작 함수 사용

•

조회 함수 : fetchall(), fetchone()

•

조작 함수 : excute() - 등록,수정,삭제

pymysql 모듈 설치 및 사용

•

pymysql 모듈

•

pymysql 모듈 설치

•

pymysql 모듈 사용

pymysql 모듈

Python에서 MySQL 데이터베이스와 상호작용하기 위한 모듈

pymysql 모듈을 사용하면 Python 코드에서 MySQL 데이터베이스를 다룰 수 있습니다. 일반적으로 다음과 같은 작업을 수행할 수 있습니다:

연결: MySQL 데이터베이스에 연결합니다.

쿼리 실행: SQL 쿼리를 실행하여 데이터를 조회, 추가, 수정 또는 삭제합니다.

결과 처리: 쿼리 실행 결과를 받아와서 Python 코드에서 처리합니다.

연결 종료: 데이터베이스와의 연결을 종료합니다.

pymysql 모듈 설치

pip install pymysql

Bash

복사

MySQL 서버와의 통신을 보안하기 위해 TLS/SSL을 사용하기 위해, cryptography 라이브러리도 설치합니다.

pip install cryptography

Bash

복사

pymysql 모듈 사용

import pymysql

Bash

복사

pymysql 을 사용하이 위한 기본 코드

import pymysql

# MySQL 데이터베이스에 연결
connection = pymysql.connect(host='호스트명',
                             user='사용자명',
                             password='비밀번호',
                             database='데이터베이스명',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:
        # 쿼리 실행
        sql = "SELECT * FROM 테이블명"
        cursor.execute(sql)
        # 결과 가져오기
        result = cursor.fetchall()
        for row in result:
            print(row)
finally:
    # 연결 종료
    connection.close()

Python

복사

데이터 소스로 접속

데이터 소스 : DB 접속 정보

•

계정

•

비밀번호

•

호스트

•

스키마(데이터베이스)

속성	값
호스트	127.0.0.1
사용자	aloha
비밀번호	123456
데이터베이스	aloha

호스트는 데이터베이스 서버의 ip 주소를 의미합니다. 현재 PC 로 접속한다면, 127.0.0.1 또는 localhost 로 지정하면 됩니다. 만약 다른 DB 서버가 다른 컴퓨터라면 해당 DB서버의 IP 주소를 입력합니다.

사용자는 여기서는 aloha 라고 생성하고 접근합니다. 비밀번호도 123456 으로 지정하였습니다. 데이터 소스 (접속정보)는 미리 생성된 계정의 정보를 입력합니다. database(스키마)는 여기서는 aloha 라고 생성하고 사용합니다.

샘플 테이블 생성

학생 테이블 생성

CREATE TABLE `학생` (
  `no` int NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT '이름없음',
  `age` int DEFAULT '1',
  PRIMARY KEY (`no`)
) ;

SQL

복사

학생 기본 데이터 추가

INSERT INTO `학생` ( name, age )
VALUES 
	('김조은' , 20),
  ('홍민택' , 40),
  ('신창섭' , 30);

SQL

복사

데이터 베이스 접속

pymysql 모듈의 connect 함수를 사용하여 DB에 접속합니다.

# MySQL 서버에 접속
conn = pymysql.connect(
    host='127.0.0.1',
    user='aloha',
    password='123456',
    database='aloha',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor
)

Python

복사

pymysql.connect( 데이터소스 ) 형태로 함수를 호출하고 정상적으로 DB에 접속이 성공되면, 연결 객체를 반환합니다. 이 conn 객체를 통해서 커서를 생성할 수 있습니다.

커서 생성

데이터베이스에서 쿼리를 실행하고 그 결과를 가져오는 데 사용되는 객체입니다.

커서의 역할

쿼리 실행: 커서를 사용하여 데이터베이스에 SQL 쿼리를 실행합니다. 쿼리는 데이터를 조회, 추가, 수정, 삭제하는 데 사용될 수 있습니다.

결과 가져오기: 커서를 사용하여 실행된 쿼리의 결과를 가져옵니다. 이 결과는 하나 이상의 레코드로 구성되며, 각 레코드는 하나 이상의 열을 포함합니다.

결과 처리: 가져온 결과를 Python 코드에서 처리하고 사용할 수 있도록 합니다. 이를 통해 개발자는 데이터베이스에서 가져온 데이터를 조작하고 분석할 수 있습니다.

트랜잭션 관리: 커서를 사용하여 트랜잭션을 시작하고 종료하며, 트랜잭션 내에서 쿼리를 실행합니다. 이를 통해 데이터베이스의 일관성과 무결성을 유지할 수 있습니다.

커서 생성

with connection.cursor() as cursor:
        # 쿼리 실행
        sql = "SQL 쿼리"
        cursor.execute(sql)
        # 결과 가져오기
        result = cursor.조회및조작함수()

Python

복사

위와 같이 실행할 SQL 문장을 “문자열” 로 작성하고, excute(sql) 함수로 쿼리를 DB 에 실행 요청합니다. 그 후, 데이터 조회 및 조작 함수를 호출하여 결과를 반환받습니다.

조회 및 조작 함수 사용

•

조회 함수 : fetchall(), fetchone()

•

조작 함수 : excute() - 등록,수정,삭제

조회 함수 : fetchall(), fetchone()

함수	설명	반환값
fetchall()	현재 실행된 쿼리의 모든 결과를 가져옵니다.	결과를 포함하는 모든 행을 반환
fetchone()	현재 실행된 쿼리의 다음 결과 행 하나를 가져옵니다.	다음 결과 행을 반환

fetchall() 함수는 현재 실행된 쿼리의 모든 결과 행을 가져옵니다. 이 함수를 호출하면 쿼리의 모든 결과를 가져와서 리스트 형태로 반환합니다. 각 행은 리스트의 원소로 표현됩니다.

fetchone() 함수는 현재 실행된 쿼리의 결과 행 중 다음 행 하나를 가져옵니다. 이 함수를 호출할 때마다 다음 결과 행이 반환됩니다. 만약 더 이상 결과가 없으면 None을 반환합니다.

•

학생 테이블에서 데이터 목록 조회

# pip install pymysql
# pip install cryptography
import pymysql

# MySQL 서버에 접속
conn = pymysql.connect(
    host='127.0.0.1',
    user='aloha',
    password='123456',
    database='aloha',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor
)

try:
    with conn.cursor() as cursor:
        sql = "SELECT * FROM 학생"
        cursor.execute(sql)             # DB에 쿼리 요청

        students = cursor.fetchall()    # 결과 

        for student in students:
            print( student )
except pymysql.MySQLError as e:
    print('MySQL 에러 : ', e)
finally:
    conn.close()

Python

복사

•

학생 테이블에서 단일 테이터 조회

# pip install pymysql
# pip install cryptography
import pymysql

# MySQL 서버에 접속
conn = pymysql.connect(
    host='127.0.0.1',
    user='aloha',
    password='123456',
    database='aloha',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor
)

try:
    with conn.cursor() as cursor:
        sql = "SELECT * FROM 학생 WHERE no = 1 "
        cursor.execute(sql)             # DB에 쿼리 요청

        student = cursor.fetchone()    # 결과 
        print( student )
except pymysql.MySQLError as e:
    print('MySQL 에러 : ', e)
finally:
    conn.close()

Python

복사

조작 함수 : excute() - 등록,수정,삭제

excute( SQL, 파라미터 )

파라미터	설명
SQL	실행할 SQL 쿼리입니다.
파라미터	필요에 따라 SQL 쿼리에 전달할 파라미터입니다. 단일 데이터 또는 튜플 형태로 지정합니다.

SQL 문에 파라미터를 매핑하기 위해서 ‘%s’ 기호를 작성합니다.

아래와 같이 데이터 조회, 등록, 수정, 삭제 쿼리에 대하여 파라미터를 매핑하는 경우 %s 기호를 사용합니다.

sql = 'SELECT * FROM 학생 WHERE no = %s'

Python

복사

 sql = " INSERT INTO 학생 (std_id, name, tel) "\
     + " VALUES (%s, %s, %s) "

Python

복사

sql = " UPDATE 학생 "\
    + " SET std_id = %s "\
    + "    ,name = %s "\
    + "    ,tel = %s "\
    + "    ,upd_date = now() "\
    + " WHERE std_id = %s "
    

Python

복사

sql = " DELETE FROM 학생 "\
    + " WHERE std_id = %s "

Python

복사

위에 작성한 SQL 문장에 대하여, execute( sql, (파라미터1, 파라미터2,) ) 함수를 호출하면,

%s 기호에 파라미터1, 2 를 순서를 맞춰서 매핑합니다.

•

학생 데이터 조회

•

학생 데이터 추가

•

학생 데이터 수정

•

학생 데이터 삭제

학생 데이터 조회

# pip install pymysql
# pip install cryptography
import pymysql

# MySQL 서버에 접속
conn = pymysql.connect(
    host='127.0.0.1',
    user='aloha',
    password='123456',
    database='aloha',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor
)

try:
    with conn.cursor() as cursor:
        sql = "SELECT * FROM 학생 WHERE no = %s "
        cursor.execute(sql, 1)             # DB에 쿼리 요청

        student = cursor.fetchone()    # 결과 
        print( student )
except pymysql.MySQLError as e:
    print('MySQL 에러 : ', e)
finally:
    conn.close()

Python

복사

학생 데이터 추가

import pymysql

# MySQL 서버에 접속
conn = pymysql.connect(
    host='127.0.0.1',
    user='aloha',
    password='123456',
    database='aloha',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor
)

std_id = input('학번 : ')
name = input('이름 : ')
tel = input('전화번호 : ')

try:
    # 커서 생성
    with conn.cursor() as cursor:
        # 데이터 등록 쿼리 
        sql = " INSERT INTO 학생 (std_id, name, tel) "\
            + " VALUES (%s, %s, %s) "
            # + " VALUES ('B2401', '홍길동', '010-1234-1234') "
        
        # result = cursor.execute(sql)                      # 쿼리 실행 요청
        result = cursor.execute(sql, (std_id, name, tel))   # 파라미터 지정
        print('{}행의 데이터 등록 완료'.format(result))

    # 변경사항 적용
    conn.commit()

except pymysql.MySQLError as e:
    print("데이터 등록 중 에러 발생 : ", e)
finally:
    conn.close()

Python

복사

학생 데이터 수정

import pymysql

# MySQL 서버에 접속
conn = pymysql.connect(
    host='127.0.0.1',
    user='aloha',
    password='123456',
    database='aloha',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor
)

std_id = input('학번 : ')
name = input('이름 : ')
tel = input('전화번호 : ')

try:
    # 커서 생성
    with conn.cursor() as cursor:
        # 데이터 수정 쿼리 
        sql = " UPDATE 학생 "\
            + " SET std_id = %s "\
            + "    ,name = %s "\
            + "    ,tel = %s "\
            + "    ,upd_date = now() "\
            + " WHERE std_id = %s "
            
            # + " VALUES ('B2401', '홍길동', '010-1234-1234') "
        
        # result = cursor.execute(sql)                              # 쿼리 실행 요청
        result = cursor.execute(sql, (std_id, name, tel, std_id))   # 파라미터 지정
        print('{}행의 데이터 수정 완료'.format(result))

    # 변경사항 적용
    conn.commit()

except pymysql.MySQLError as e:
    print("데이터 수정 중 에러 발생 : ", e)
finally:
    conn.close()

Python

복사

학생 데이터 삭제

import pymysql

# MySQL 서버에 접속
conn = pymysql.connect(
    host='127.0.0.1',
    user='aloha',
    password='123456',
    database='aloha',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor
)

std_id = input('학번 : ')

try:
    # 커서 생성
    with conn.cursor() as cursor:
        # 데이터 삭제 쿼리 
        sql = " DELETE FROM 학생 "\
            + " WHERE std_id = %s "
        
        result = cursor.execute(sql, (std_id))   # 파라미터 지정
        print('{}행의 데이터 삭제 완료'.format(result))

    # 변경사항 적용
    conn.commit()

except pymysql.MySQLError as e:
    print("데이터 삭제 중 에러 발생 : ", e)
finally:
    conn.close()

Python

복사