📕 들어가며
이번 글에서는
파이썬 개발 환경에서 pymysql 패키지를 활용해
mysql 데이터베이스를 조작하는 방법에 대해 배워보겠습니다.
📕 pymysql 기본 사용방법
✏️1. pymysql 사용법1
# pymysql 기본 사용법1
import pymysql
# 데이터 베이스에 연결한다.
# host, user, password, db에는 접속 정보 입력
connection = pymysql.connect(host = "localhost",user="test",password ='1111',db='testdb',charset='utf8')
# cursor를 생성한다.
# cursor에 데이터 딕셔너리 옵션을 준다. (결과를 데이터 딕셔너리로 자동으로 바꿔주는 옵션)
cursor = connection.cursor(pymysql.cursors.DictCursor)
# SQL을 하나 만든다.
SQL = '''
SELECT
*
FROM
`member`
'''
# cursor를 이용해서 SQL을 실행한다.
cursor.execute(SQL)
# 모든 데이터를 가져온다.
datas = cursor.fetchall()
# insert, update, delete의 경우 자원 닫아주기 전에 commit을 해야 DB에 작업 내용이 저장됩니다.
# connection.commit()
# 사용했던 데이터베이스 관련 자원들을 닫아준다.
cursor.close()
connection.close()
# 가져온 데이터 확인을 위한 코드
datas실행 결과 :
✏️2. pymysql 사용법2 (with 구문 활용)
위의 코드에서
cursor.close(), connection.close()와 같은
반복되는 코드를 제거하기 위해
with 구문을 활용할 수 있습니다.with 구문에서 선언한 connection은
사용자가 반환하지 않아도
작업이 끝나면 자동 반환됩니다.
# pymysql 기본 사용법2 (with절 활용 자원 자동반환)
import pymysql
# 데이터 베이스에 연결한다.
# host, user, password, db에는 접속 정보 입력
with pymysql.connect(host = "localhost",user="test",password ='1111',db='testdb',charset='utf8') as connection:
# cursor를 생성한다.
# cursor에 데이터 딕셔너리 옵션을 준다. (결과를 데이터 딕셔너리로 자동으로 바꿔주는 옵션)
cursor = connection.cursor(pymysql.cursors.DictCursor)
# SQL을 하나 만든다.
SQL = '''
SELECT
*
FROM
`member`
'''
# cursor를 이용해서 SQL을 실행한다.
cursor.execute(SQL)
# 모든 데이터를 가져온다.
datas = cursor.fetchall()
# insert, update, delete의 경우 자원 닫아주기 전에 commit을 해야 DB에 작업 내용이 저장됩니다.
# connection.commit()
# 가져온 데이터 확인을 위한 코드
datas실행 결과 : with를 사용해도 방법1과 똑같이 동작합니다.
📕 본문
그럼 이제 본격적으로
테이블 생성 및 CRUD 작업을
하나씩 진행해보도록 하겠습니다. ^^
✏️0. 테이블 생성
CRUD 연습용으로
id, name, asset 컬럼을 가진
user 테이블을 만들어 보았습니다.
# pymysql 활용 테이블 생성
import pymysql
# 데이터 베이스에 연결한다.
# host, user, password, db에는 접속 정보 입력
with pymysql.connect(host = "localhost",user="test",password ='1111',db='testdb',charset='utf8') as connection:
# cursor를 생성한다.
# cursor에 데이터 딕셔너리 옵션을 준다. (결과를 데이터 딕셔너리로 자동으로 바꿔주는 옵션)
cursor = connection.cursor(pymysql.cursors.DictCursor)
# SQL을 만든다.
SQL = '''
DROP TABLE IF EXISTS `user`
'''
# cursor를 이용해서 SQL을 실행한다.
cursor.execute(SQL)
# SQL을 만든다.
SQL = '''
CREATE TABLE `user` (
`id` BIGINT AUTO_INCREMENT NOT NULL PRIMARY KEY,
`name` VARCHAR(50) NULL,
`asset` BIGINT NULL
)
'''
# cursor를 이용해서 SQL을 실행한다.
cursor.execute(SQL)
# insert, update, delete의 경우 자원 닫아주기 전에 commit을 해야 DB에 작업 내용이 저장됩니다.
# connection.commit()<실행결과> : user 테이블이 잘 생성되었습니다.
✏️1. CREATE
user 테이블에 여러 데이터들을 넣어보겠습니다.
# pymysql CRUD작업
# 1. CREATE
import pymysql
# 데이터 베이스에 연결한다.
# host, user, password, db에는 접속 정보 입력
with pymysql.connect(host = "localhost",user="test",password ='1111',db='testdb',charset='utf8') as connection:
# cursor를 생성한다.
# cursor에 데이터 딕셔너리 옵션을 준다. (결과를 데이터 딕셔너리로 자동으로 바꿔주는 옵션)
cursor = connection.cursor(pymysql.cursors.DictCursor)
# SQL을 만든다.
SQL = '''
INSERT INTO testdb.`user`
(name, asset)
VALUES
('짱구', 1000000),
('철수', 5000000),
('유리', 1000000),
('훈이', 2000000),
('맹구', 3000000)
'''
# cursor를 이용해서 SQL을 실행한다.
cursor.execute(SQL)
# insert, update, delete의 경우 자원 닫아주기 전에 commit을 해야 DB에 작업 내용이 저장됩니다.
connection.commit()<실행결과> : 5건의 데이터가 생성되었습니다.
✏️2-1. SELECT
user 테이블의 데이터를 조회해 보겠습니다.
# pymysql CRUD작업
# 2-1. READ
import pymysql
# 데이터 베이스에 연결한다.
# host, user, password, db에는 접속 정보 입력
with pymysql.connect(host = "localhost",user="test",password ='1111',db='testdb',charset='utf8') as connection:
# cursor를 생성한다.
# cursor에 데이터 딕셔너리 옵션을 준다. (결과를 데이터 딕셔너리로 자동으로 바꿔주는 옵션)
cursor = connection.cursor(pymysql.cursors.DictCursor)
# SQL을 만든다.
SQL = '''
SELECT id, name, asset
FROM testdb.`user`
'''
# cursor를 이용해서 SQL을 실행한다.
cursor.execute(SQL)
datas = cursor.fetchall()
# insert, update, delete의 경우 자원 닫아주기 전에 commit을 해야 DB에 작업 내용이 저장됩니다.
# connection.commit()
# 쿼리로 받아온 데이터 확인
datas <실행결과> : 5건의 데이터가 datas에 담겨있습니다.
✏️2-2. SELECT (변수 사용)
SQL 내에서 변수를 사용하고 싶다면
다음과 같이 코드를 작성하시면 됩니다.
# pymysql CRUD작업
# 2-2. READ (SQL내에 변수 사용)
import pymysql
# 데이터 베이스에 연결한다.
# host, user, password, db에는 접속 정보 입력
with pymysql.connect(host = "localhost",user="test",password ='1111',db='testdb',charset='utf8') as connection:
# cursor를 생성한다.
# cursor에 데이터 딕셔너리 옵션을 준다. (결과를 데이터 딕셔너리로 자동으로 바꿔주는 옵션)
cursor = connection.cursor(pymysql.cursors.DictCursor)
# SQL을 만든다.
SQL = '''
SELECT id, name, asset
FROM testdb.`user`
WHERE 1 = 1
AND (name = %s OR name = %s)
'''
# SQL 내에서 변수 치환이 필요할 때
# SQL 안에 변수가 필요한 부분을 %s로 변경
# %s에 필요한 값들을 변수에 담아서 execute할 때 같이 매개변수로 전달하면 된다.
parameter = ["짱구", "철수"]
# cursor를 이용해서 SQL을 실행한다.
cursor.execute(SQL, parameter)
datas = cursor.fetchall()
# insert, update, delete의 경우 자원 닫아주기 전에 commit을 해야 DB에 작업 내용이 저장됩니다.
# connection.commit()
# 쿼리로 받아온 데이터 확인
datas<실행결과> : 조건에 맞는 데이터만 가져왔습니다.
✏️3. UPDATE
짱구의 자산에 1000000원을 추가하는 코드입니다.
# pymysql CRUD작업
# 3. UPDATE
import pymysql
# 데이터 베이스에 연결한다.
# host, user, password, db에는 접속 정보 입력
with pymysql.connect(host = "localhost",user="test",password ='1111',db='testdb',charset='utf8') as connection:
# cursor를 생성한다.
# cursor에 데이터 딕셔너리 옵션을 준다. (결과를 데이터 딕셔너리로 자동으로 바꿔주는 옵션)
cursor = connection.cursor(pymysql.cursors.DictCursor)
# SQL을 만든다.
SQL = '''
UPDATE testdb.`user`
SET asset = asset + %s
WHERE name='짱구'
'''
# SQL 내에서 변수 치환이 필요할 때
# SQL 안에 변수가 필요한 부분을 %s로 변경
# %s에 필요한 값들을 변수에 담아서 execute할 때 같이 매개변수로 전달하면 된다.
parameter = 1000000
# cursor를 이용해서 SQL을 실행한다.
cursor.execute(SQL, parameter)
# insert, update, delete의 경우 자원 닫아주기 전에 commit을 해야 DB에 작업 내용이 저장됩니다.
connection.commit()<실행결과> : 자산이 업데이트 되었습니다.
✏️4. DELETE
유리 데이터를 삭제합니다.
# pymysql CRUD작업
# 4. DELETE
import pymysql
# 데이터 베이스에 연결한다.
# host, user, password, db에는 접속 정보 입력
with pymysql.connect(host = "localhost",user="test",password ='1111',db='testdb',charset='utf8') as connection:
# cursor를 생성한다.
# cursor에 데이터 딕셔너리 옵션을 준다. (결과를 데이터 딕셔너리로 자동으로 바꿔주는 옵션)
cursor = connection.cursor(pymysql.cursors.DictCursor)
# SQL을 만든다.
SQL = '''
DELETE FROM testdb.`user`
WHERE name = %s
'''
# SQL 내에서 변수 치환이 필요할 때
# SQL 안에 변수가 필요한 부분을 %s로 변경
# %s에 필요한 값들을 변수에 담아서 execute할 때 같이 매개변수로 전달하면 된다.
parameter = "유리"
# cursor를 이용해서 SQL을 실행한다.
cursor.execute(SQL, parameter)
# insert, update, delete의 경우 자원 닫아주기 전에 commit을 해야 DB에 작업 내용이 저장됩니다.
connection.commit()<실행결과> : 유리 데이터가 삭제되었습니다.
📕 pymysql 활용 CRUD 연습을 하고 싶다면?!
여기서 연습하시면 됩니다. ^^
연습문제로 5문제 정도 마련되어있으니
연습하시고 다들 고수 되셔요 !!
1.01^365