호쌤
호쌤 Just For Fun

[Python] pymysql을 활용한 MySQL연동

크리에이티브 커먼즈 라이선스 ITPAPER(호쌤,쭈쌤)에 의해 작성된 ≪[Python] pymysql을 활용한 MySQL연동≫은(는) 크리에이티브 커먼즈 저작자표시-비영리-동일조건변경허락 4.0 국제 라이선스에 따라 이용할 수 있습니다.
이 라이선스의 범위 이외의 이용허락을 얻기 위해서는 leekh4232@gmail.com으로 문의하십시오.

[Python] pymysql을 활용한 MySQL연동

python 데이터베이스 연동 패키지인 pymysql을 활용하면 MySQL과 연동할 수 있습니다.

준비사항

  • MySQL8 설치
  • root계정 비밀번호는 123qwe!@#로 가정함
  • myschool 데이터베이스 생성
  • 아래의 SQL문을 통한 샘플 테이블 생성
1
2
3
4
5
6
7
8
9
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
  `deptno` int(4) NOT NULL AUTO_INCREMENT COMMENT '부서번호(학과번호)',
  `dname` varchar(16) NOT NULL COMMENT '부서명(학과명)',
  `loc` varchar(10) DEFAULT NULL COMMENT '위치',
  PRIMARY KEY (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='부서(학과) ';

INSERT INTO `department` VALUES ('101', '컴퓨터공학과', '1호관'), ('102', '멀티미디어학과', '2호관'), ('201', '전자공학과', '3호관'), ('202', '기계공학과', '4호관');

#01. 패키지 설치

1
pip install --upgrade pymysql
▶ 출력결과
1
2
Requirement already up-to-date: pymysql in c:\python36\lib\site-packages (0.10.0)
Note: you may need to restart the kernel to use updated packages.

#02. 연동준비

1) 패키지 참조

1
2
import pymysql
from pandas import DataFrame

2) 데이터베이스 연동에 필요한 접속 정보

1
2
3
4
5
6
HOSTNAME = "localhost"
PORT     = 3306
USERNAME = "root"
PASSWORD = "123qwe!@#"
DATABASE = "myschool"
CHARSET  = "utf8"

#03. 데이터베이스 접속하기

1
2
3
4
5
dbcon = pymysql.connect(host=HOSTNAME, port=PORT, user=USERNAME, password=PASSWORD, 
                        db=DATABASE, charset=CHARSET)
print("데이터베이스 접속 성공")
print(type(dbcon))
print(dbcon)
▶ 출력결과
1
2
3
데이터베이스 접속 성공
<class 'pymysql.connections.Connection'>
<pymysql.connections.Connection object at 0x00000232FAC342B0>

#04. SQL 수행하기

1) 커서객체 생성

커서란 SQL을 실행하는 기능을 갖는 객체이다.

cursor()함수에 파라미터를 생략할 경우 SQL에 전달되는 파라미터를 리스트 타입으로 관리해야 한다. (인덱스 번호에 따른 위치 지정)

pymysql.cursors.DictCursor를 지정할 경우 SQL에 전달되는 파라미터를 딕셔너리 형태로 관리할 수 있다.

1
cursor = dbcon.cursor(pymysql.cursors.DictCursor)

2) 데이터 저장하기

INSERT문은 SQL수행 후 AUTO_INCREMENT로 지정된 Primarykey 값을 반환받을 수 있다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 저장할 데이터 설정
dname = 'Python'
loc = '1401호'

# 저장을 위한 SQL 실행(문자열 데이터도 홑따옴표 사용 안함)
sql = "INSERT INTO department (dname, loc) VALUES (%s, %s)"
cursor.execute(sql, (dname, loc))

# insert시에 생성된 Primary key 얻기
# -> 반드시 commit 전에 조회해야 한다.(UPDATE,DELETE는 불필요)
new_deptno = dbcon.insert_id()
print("생성된 학과번호: %d" % new_deptno)

# 변경사항 저장하기
dbcon.commit()
▶ 출력결과
1
생성된 학과번호: 203

3) 데이터 수정하기

앞에서 저장한 데이터의 학과명과 위치 수정하기

주의사항!!! SQL에 지정되는 모든 변수는 %s를 사용해야 하고 데이터 타입은 string이어야 한다.

1
2
3
4
5
6
7
8
9
10
11
12
# 수정할 데이터 설정
dname = '빅데이터'
loc = '공학관'

# 수정을 위한 SQL 실행(문자열 데이터도 홑따옴표 사용 안함)
sql = "UPDATE department SET dname=%s, loc=%s WHERE deptno=%s"
edit_count = cursor.execute(sql, (dname, loc, str(new_deptno)))

print("수정된 데이터 수: %d" % edit_count)

# 변경사항 저장하기
dbcon.commit()
▶ 출력결과
1
수정된 데이터 수: 1

4) 데이터 삭제하기

1
2
3
4
5
6
7
8
# 삭제를 위한 SQL 실행
sql = "DELETE FROM department WHERE deptno=%s"
delete_count = cursor.execute(sql, (str(new_deptno)))

print("삭제된 데이터 수: %d" % delete_count)

# 변경사항 저장하기
dbcon.commit()
▶ 출력결과
1
삭제된 데이터 수: 1

5) 단일행 데이터 조회하기

SQL문에서 별칭을 적용할 경우 딕셔너리의 키가 별칭으로 설정되고, 별칭을 사용하지 않을 경우 원래의 컬럼이름이 딕셔너리의 키가 된다.

SQL문 안에서 줄바꿈이 필요한 경우 전체 구문을 삼중따옴표로 감싸야 한다.

1
2
3
4
5
6
7
8
9
10
11
12
# 단일행 조회를 위한 SQL구문 형식 설정
# -> 변수로 치환할 부분은 무조건 %s사용 (숫자값이더라도 %s)
sql = """SELECT deptno AS `학과번호`, dname AS `학과이름`, loc AS `학과위치`
       FROM department WHERE deptno=%s"""

# %d에 101이라는 값을 적용하여 SQL 실행
# -> sql구문에 치환문자가 없을 경우 두 번째 파라미터는 생략 가능함.
cursor.execute(sql, (202))

# 조회 결과 한 줄을 딕셔너리로 변환
result = cursor.fetchone()
result
▶ 출력결과
1
{'학과번호': 202, '학과이름': '기계공학과', '학과위치': '4호관'}

6) 다중행 데이터 조회하기

SQL문 실행하기

fetchall() 함수를 사용하면 동일한 구조의 딕셔너리를 원소로 갖는 리스트로 반환된다.

1
2
3
4
5
6
7
# 다중행 조회를 위한 SQL 실행
sql = "SELECT deptno, dname, loc FROM department"
cursor.execute(sql)

# 조회 결과 전체를 딕셔너리를 원소로 갖는 리스트로 변환
result = cursor.fetchall()
result
▶ 출력결과
1
2
3
4
[{'deptno': 101, 'dname': '컴퓨터공학과', 'loc': '1호관'},
 {'deptno': 102, 'dname': '멀티미디어학과', 'loc': '2호관'},
 {'deptno': 201, 'dname': '전자공학과', 'loc': '3호관'},
 {'deptno': 202, 'dname': '기계공학과', 'loc': '4호관'}]

데이터 조회 결과를 데이터프레임으로 변환

1
2
3
df = DataFrame(result)
df.set_index('deptno', inplace=True)
df
▶ 출력결과
dname loc
deptno
101 컴퓨터공학과 1호관
102 멀티미디어학과 2호관
201 전자공학과 3호관
202 기계공학과 4호관

#05 데이터베이스 접속 해제

1
2
dbcon.close()
print("데이터베이스 접속 해제")
▶ 출력결과
1
데이터베이스 접속 해제
Rating:

크리에이티브 커먼즈 라이선스 ITPAPER(호쌤,쭈쌤)에 의해 작성된 ≪[Python] pymysql을 활용한 MySQL연동≫은(는) 크리에이티브 커먼즈 저작자표시-비영리-동일조건변경허락 4.0 국제 라이선스에 따라 이용할 수 있습니다.
이 라이선스의 범위 이외의 이용허락을 얻기 위해서는 leekh4232@gmail.com으로 문의하십시오.

comments powered by Disqus