📕 들어가며
이번 글에서는
pymysql 활용문제 5개
를 다뤄보도록 하겠습니다.
📕 데이터 준비
CREATE DATABASE testdb;
DROP TABLE IF EXISTS `testdb`.`member`;
DROP TABLE IF EXISTS `testdb`.`product`;
DROP TABLE IF EXISTS `testdb`.`buy`;
CREATE TABLE `testdb`.`member`(
id BIGINT AUTO_INCREMENT NOT NULL PRIMARY KEY,
member_id VARCHAR(30),
name VARCHAR(10),
address VARCHAR(10),
phone_number VARCHAR(20),
create_date datetime,
update_date datetime
);
CREATE TABLE `testdb`.`product`(
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20),
qty INT,
price INT,
create_date datetime,
update_date datetime
);
CREATE TABLE `testdb`.`buy` (
`id` bigint NOT NULL AUTO_INCREMENT,
`member_id` bigint DEFAULT NULL,
`product_id` bigint DEFAULT NULL,
`qty` int DEFAULT NULL,
`create_date` datetime DEFAULT CURRENT_TIMESTAMP,
`update_date` datetime DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
);
INSERT INTO `testdb`.`member` (member_id,name,address,phone_number,create_date,update_date) VALUES
('TWC','트와이스','Seoul','010-1111-1111','2023-02-06 14:04:08','2023-02-06 14:04:08'),
('BLK','블랙핑크','Seoul','010-1111-2222','2023-02-06 14:04:08','2023-02-06 14:04:08'),
('WMN','여자친구','Daegu','010-1111-3333','2023-02-06 14:04:08','2023-02-06 14:04:08'),
('OMY','오마이걸','Daegu','010-1111-4444','2023-02-06 14:04:08','2023-02-06 14:04:08'),
('GRL','소녀시대','Daegeon','010-1111-5555','2023-02-06 14:04:08','2023-02-06 14:04:08'),
('ITZ','잇지','Daegeon','010-2222-1111','2023-02-06 14:04:08','2023-02-06 14:04:08'),
('RED','레드밸벳','Daegeon','010-2222-1111','2023-02-06 14:04:08','2023-02-06 14:04:08'),
('APN','에이핑크','Busan','010-2222-2222','2023-02-06 14:04:08','2023-02-06 14:04:08'),
('SPC','우주소녀','Junnam','010-2222-2222','2023-02-06 14:04:08','2023-02-06 14:04:08');
INSERT INTO `testdb`.`product`
(`name`, `qty`, `price`, `create_date`, `update_date`)
VALUES
('carrot', 10, 1000, NOW(), NOW()),
('apple', 100, 500, NOW(), NOW()),
('pear', 30, 800, NOW(), NOW()),
('orange', 50, 800, NOW(), NOW()),
('honey', 10, 3000, NOW(), NOW()),
('cabage', 15, 3000, NOW(), NOW()),
('pine', 20, 5000, NOW(), NOW()),
('melon', 10, 10000, NOW(), NOW());
INSERT INTO `testdb`.`buy` (member_id,product_id,qty,create_date,update_date) VALUES
(1,1,10,'2023-02-07 08:24:33','2023-02-07 08:24:33'),
(1,2,30,'2023-02-07 08:29:48','2023-02-07 08:29:48'),
(2,1,10,'2023-02-07 08:29:58','2023-02-07 08:29:58'),
(5,2,10,'2023-02-07 08:30:07','2023-02-07 08:30:07'),
(6,8,5,'2023-02-07 08:30:30','2023-02-07 08:30:30'),
(3,3,4,'2023-02-07 08:30:39','2023-02-07 08:30:39'),
(3,5,10,'2023-02-07 08:30:50','2023-02-07 08:30:50'),
(4,4,10,'2023-02-07 08:38:40','2023-02-07 08:38:40'),
(5,2,10,'2023-02-07 08:38:40','2023-02-07 08:38:40'),
(4,1,20,'2023-02-07 08:38:40','2023-02-07 08:38:40');
INSERT INTO `testdb`.`buy` (member_id,product_id,qty,create_date,update_date) VALUES
(6,7,10,'2023-02-07 08:38:40','2023-02-07 08:38:40'),
(9,4,10,'2023-02-07 08:38:40','2023-02-07 08:38:40'),
(7,2,30,'2023-02-07 08:38:40','2023-02-07 08:38:40'),
(1,7,20,'2023-02-07 08:38:40','2023-02-07 08:38:40');📕 기초지식
✏️ pymysql 기본 사용법
# 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과 똑같이 동작합니다.
📕 pymysql 연습 문제
문제 1
member_id가 구매한
물건(product)의 이름(name)과 가격(price)을 가져온다.
(단, 중복은 허용하지 않는다.)
문제 2
apple을 구매한 고객의 리스트(member)를 가져온다.
구매양(buy.qty)를 가져온다.
단, buy.qty의 역순으로 데이터를 정렬한다.
문제 3
member_id가 'TWC', 'ITZ'라는 멤버가
구매한 물건의 qty의 합을 구하고 싶다.
구매 수량의 역순으로 정렬
문제 4
Daegeon에 살고 있는 사람이
구매한 product들의 가격합을 구하시오. (qty*price)
단, member_id별로 그룹핑
문제 5
누군가가 구매를 했다고 가정
구매 테이블에 이력이 하나 쌓여야 할텐데,
'RED' 가 'APPLE'를 20개 샀을 때
(1) member 테이블과 product 테이블의 아이디를 직접 보지 말고
조회문으로 각각의 아이디를 가져와서
구매 테이블에 구매 정보를 입력
(2) 구매를 진행했다면 product 테이블의 재고가 그만큼 감소하도록 구현
문제1 정답
# 문제 1 정답
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 DISTINCT b.member_id, p.name, p.price
FROM buy b
INNER JOIN product p
ON b.product_id = p.id
ORDER BY b.member_id
'''
# SQL 내에서 변수 치환이 필요할 때
# SQL 안에 변수가 필요한 부분을 %s로 변경
# %s에 필요한 값들을 변수에 담아서 execute할 때 같이 매개변수로 전달하면 된다.
# parameter = []
# cursor를 이용해서 SQL을 실행한다.
cursor.execute(SQL)
datas = cursor.fetchall()
# insert, update, delete의 경우 자원 닫아주기 전에 commit을 해야 DB에 작업 내용이 저장됩니다.
# connection.commit()
# 쿼리로 받아온 데이터 확인
datas<실행결과>
문제2 정답
# 문제 2 정답
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 m.* , b.qty
FROM buy b
INNER JOIN member m
ON b.member_id = m.id
INNER JOIN product p
ON b.product_id = p.id
WHERE 1=1
AND p.name = 'apple'
ORDER BY b.qty DESC
'''
# SQL 내에서 변수 치환이 필요할 때
# SQL 안에 변수가 필요한 부분을 %s로 변경
# %s에 필요한 값들을 변수에 담아서 execute할 때 같이 매개변수로 전달하면 된다.
# parameter = []
# cursor를 이용해서 SQL을 실행한다.
cursor.execute(SQL)
datas = cursor.fetchall()
# insert, update, delete의 경우 자원 닫아주기 전에 commit을 해야 DB에 작업 내용이 저장됩니다.
# connection.commit()
# 쿼리로 받아온 데이터 확인
datas<실행결과>
문제3 정답
# 문제 3 정답
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 m.member_id, SUM(b.qty) AS qty
FROM buy b
INNER JOIN member m
ON b.member_id = m.id
INNER JOIN product p
ON b.product_id = p.id
WHERE 1=1
AND m.member_id in (%s, %s)
GROUP BY m.member_id
ORDER BY qty DESC
'''
# SQL 내에서 변수 치환이 필요할 때
# SQL 안에 변수가 필요한 부분을 %s로 변경
# %s에 필요한 값들을 변수에 담아서 execute할 때 같이 매개변수로 전달하면 된다.
parameter = ["TWC", "ITZ"]
# cursor를 이용해서 SQL을 실행한다.
cursor.execute(SQL, parameter)
datas = cursor.fetchall()
# insert, update, delete의 경우 자원 닫아주기 전에 commit을 해야 DB에 작업 내용이 저장됩니다.
# connection.commit()
# 쿼리로 받아온 데이터 확인
datas<실행결과>
문제4 정답
# 문제 4 정답
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 m.member_id, SUM(b.qty*p.price) AS '가격합'
FROM buy b
INNER JOIN member m
ON b.member_id = m.id
INNER JOIN product p
ON b.product_id = p.id
WHERE 1=1
AND m.address = %s
GROUP BY m.member_id
'''
# SQL 내에서 변수 치환이 필요할 때
# SQL 안에 변수가 필요한 부분을 %s로 변경
# %s에 필요한 값들을 변수에 담아서 execute할 때 같이 매개변수로 전달하면 된다.
parameter = ["Daegeon"]
# cursor를 이용해서 SQL을 실행한다.
cursor.execute(SQL, parameter)
datas = cursor.fetchall()
# insert, update, delete의 경우 자원 닫아주기 전에 commit을 해야 DB에 작업 내용이 저장됩니다.
# connection.commit()
# 쿼리로 받아온 데이터 확인
datas<실행결과>
문제5 정답
# 문제 5 정답
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)
# "RED"가 "apple"을 20개 산다고 가정
member = "RED"
product = "apple"
qty = 20
# 1. member_id 가져오기
SQL = '''
SELECT id
FROM member
WHERE member_id = %s
'''
parameter = member
cursor.execute(SQL, parameter)
member = cursor.fetchone()
member_id = member['id']
# 2. product_id 가져오기
SQL = '''
SELECT id, qty
FROM product
WHERE name = %s
'''
parameter = product
cursor.execute(SQL, parameter)
product = cursor.fetchone()
product_id = product['id']
product_qty = product['qty']
if product_qty < qty :
print("재고가 부족하여 물건 구매에 실패하였습니다.")
else:
# 3. buy 테이블에 구매이력 추가
SQL = '''
INSERT INTO buy
(member_id, product_id, qty, create_date, update_date)
VALUES(%s, %s, %s, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP)
'''
parameter = [member_id, product_id, qty]
cursor.execute(SQL, parameter)
# 4. 물건 재고 줄이기
SQL = '''
UPDATE product
SET qty = qty - %s
WHERE id=%s;
'''
parameter = [qty, product_id]
cursor.execute(SQL, parameter)
# 여기까지 아무 이상 없으면 DB에 반영
connection.commit()<실행결과> buy 테이블에 구매 이력 생성
<실행결과> product 테이블의 재고 변화
1.01^365