[Python] 파이썬으로 mysql 데이터베이스 접근하기 (pymysql 사용법)

파이썬 스크립트를 통해 mysql 데이터베이스를 다뤄야 할때가 있습니다. 파이썬은 mysql과의 연동을 위해 pymysql이라는 모듈을 제공하는데요. 해당 모듈을 사용하면 파이썬을 사용하여 쉽게 mysql db를 다룰 수 있습니다. 그럼 지금부터  pymysql을 통한 mysql 데이터베이스 다루는 예제를 살펴보도록 하겠습니다.

사전준비

접속에 필요한 mysql database를 확보해야 합니다. 
python3에서 진행합니다.
pymysql을 설치합니다.

 

pymysql 인스톨

$ pip install pymysql 

명령어를 통해 pymysql을 설치합니다.
이후 파이썬에서는 아래와 같이 해당 모듈을 임포트하여 사용합니다.

import pymysql

 

 

데이터베이스 생성

import pymysql 

conn = pymysql.connect(host='localhost', user='root', password='password', charset='utf8') 
cursor = conn.cursor() 

sql = "CREATE DATABASE developer" 

cursor.execute(sql) 

conn.commit() 
conn.close() 

데이터베이스를 생성하는 코드는 위와 같습니다. developer라는 데이터베이스를 만들라는 명령문을 execute 함수를 사용해 실행합니다.

mysql 화면

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| developer          |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

 

developer란 database가 생성된 것을 보실 수 있습니다.

테이블 생성

import pymysql 

conn = pymysql.connect(host='localhost', user='root', password='password', db='developer', charset='utf8') 
cursor = conn.cursor() 

sql = '''CREATE TABLE user ( 
id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, 
email varchar(255), 
department varchar(255) 
) 
''' 

cursor.execute(sql) 

conn.commit() 
conn.close() 

데이터베이스 안에 테이블을 사용하기 위해서는 CREATE TABLE 명령어를 이용합니다. connect하는 부분에서 사용할 데이터베이스 명을 명시해 줍니다.

mysql 화면

mysql> desc user; 
+------------+--------------+------+-----+---------+----------------+ 
| Field      | Type         | Null | Key | Default | Extra          | 
+------------+--------------+------+-----+---------+----------------+ 
| id         | int(11)      | NO   | PRI | NULL    | auto_increment | 
| email      | varchar(255) | YES  |     | NULL    |                | 
| department | varchar(255) | YES  |     | NULL    |                | 
+------------+--------------+------+-----+---------+----------------+ 
3 rows in set (0.00 sec) 

작성한 대로 테이블 스키마가 보이는 것을 확인하실 수 있습니다.

테이터 삽입 (insert)

import pymysql 

conn = pymysql.connect(host='localhost', user='root', password='password', db='developer', charset='utf8') 

cursor = conn.cursor() 

sql = "INSERT INTO user (email, department) VALUES (%s, %s)" 

cursor.execute(sql,("developer_lim@limcoding.com", "AI")) 
cursor.execute(sql,("developer_kim@limcoding.com", "AI")) 
cursor.execute(sql,("developer_song@limcoding.com", "AI")) 

conn.commit() 

conn.close() 

 

생성한 테이블에 데이터를 추가(insert) 합니다. "AI"부서에 3명을 추가합니다.


mysql 확인

mysql> select * from user; 
+----+---------------------------+------------+ 
| id | email                     | department | 
+----+---------------------------+------------+ 
|  1 | developer_lim@limcoding.com  | AI         | 
|  2 | developer_kim@limcoding.com  | AI         | 
|  3 | developer_song@limcoding.com | AI         | 
+----+---------------------------+------------+ 
3 rows in set (0.00 sec) 

3명이 추가된 것을 확인하실 수 있습니다.

데이터 검색

import pymysql 

conn = pymysql.connect(host='localhost', user='root', password='password', db='developer', charset='utf8') 

cursor = conn.cursor() 

sql = "SELECT * FROM user where department = %s" 

cursor.execute(sql, ("AI")) 
res = cursor.fetchall() 

for data in res: 
        print(data) 

conn.commit() 
conn.close() 

 

특정 테이블에 있는 정보를 확인합니다. AI 부서에 있는 개발자 명단을 받아오는 코드입니다. fetchall을 하면 모든 정보를 가져옵니다. fetchone을 하는 경우 하나의 데이터만 가져오게 됩니다.

파이썬 실행화면

root@vultr:~/python_test# python pymy.py 
(1, u'developer_lim@limcoding.com', u'AI') 
(2, u'developer_kim@limcoding.com', u'AI') 
(3, u'developer_song@limcoding.com', u'AI') 

데이터 수정 (update)

import pymysql 

conn = pymysql.connect(host='localhost', user='root', password='password', db='developer', charset='utf8') 

cursor = conn.cursor() 

sql = "UPDATE user SET department = %s WHERE email = %s" 
cursor.execute(sql, ("Testing", "developer_song@limcoding.com")) 

conn.commit() 
conn.close() 

 

테이터를 수정하는 방법은 위와 같습니다. developer_song을 Testing부서로 옮깁니다.

mysql 화면

mysql> select * from user; 
+----+---------------------------+------------+ 
| id | email                     | department | 
+----+---------------------------+------------+ 
|  1 | developer_lim@limcoding.com  | AI         | 
|  2 | developer_kim@limcoding.com  | AI         | 
|  3 | developer_song@limcoding.com | Testing    | 
+----+---------------------------+------------+ 
3 rows in set (0.00 sec) 

위 파이썬을 실행하면 해당 개발자의 부서가 변경된 것을 확인하실 수 있습니다.

데이터 삭제

import pymysql 

conn = pymysql.connect(host='localhost', user='root', password='password', db='developer', charset='utf8') 

cursor = conn.cursor() 

sql = "DELETE FROM user WHERE email = %s" 
cursor.execute(sql, ("developer_song@limcoding.com")) 
conn.commit() 

conn.close() 

 

데이터를 삭제하는 코드입니다. developer_song이 퇴사를 하는 바람에 데이터베이스에서 삭제를 하였습니다.

 

mysql 화면

mysql> select * from user; 
+----+--------------------------+------------+ 
| id | email                    | department | 
+----+--------------------------+------------+ 
|  1 | developer_lim@limcoding.com | AI         | 
|  2 | developer_kim@limcoding.com | AI         | 
+----+--------------------------+------------+ 
2 rows in set (0.00 sec) 

해당 코드를 실행하면 mysql에서 지워진 것을 확인하실 수 있습니다.

---

이처럼 pymysql을 사용하면 python을 활용하여 mysql의 데이터에 접근하실 수 있습니다!

감사합니다.