๐Ÿ“

Chapter2. MySQL

ย 
ย 
ย 
ย 
ย 

2.1 MySQL์ด๋ž€?

ย 
MySQL์€ ๊ฐ€์žฅ ๋งŽ์ด ์‚ฌ์šฉ๋˜๊ณ  ์žˆ๋Š” ๊ด€๊ณ„ํ˜• ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ๊ด€๋ฆฌ ์‹œ์Šคํ…œ(RDBMS: Relational DBMS)์ž…๋‹ˆ๋‹ค. ์˜คํ”ˆํ˜• ์†Œ์Šค์ด๊ธฐ์— ๋ฌด๋ฃŒ๋กœ ์‚ฌ์šฉ ๊ฐ€๋Šฅํ•˜์ง€๋งŒ ์ƒ์—…์ ์œผ๋กœ ์‚ฌ์šฉํ•  ๋•Œ๋Š” ์ƒ์—…์šฉ ๋ผ์ด์„ผ์Šค๋ฅผ ๊ตฌ์ž…ํ•ด์•ผ๋งŒ ํ•ฉ๋‹ˆ๋‹ค.
ย 
ํ‘œ์ค€ SQLย ํ˜•์‹์„ย ์‚ฌ์šฉํ•˜๋ฉฐ, ๋‹ค์ค‘ ์‚ฌ์šฉ์ž ๋ฐ ๋‹ค์ค‘ ์Šค๋ ˆ๋“œ๋ฅผ ์ง€์›ํ•˜๊ธฐ์— ํฐ ํฌ๊ธฐ์˜ ๋ฐ์ดํ„ฐ ์ง‘ํ•ฉ๋„ ๋น ๋ฅด๊ณ  ํšจ๊ณผ์ ์œผ๋กœ ์ฒ˜๋ฆฌํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ๋˜ํ•œ, MySQL ์‘์šฉ ํ”„๋กœ๊ทธ๋žจ์„ย ์‚ฌ์šฉ์ž์˜ ์šฉ๋„์— ๋งž๊ฒŒ ์ˆ˜์ •ํ•˜์—ฌ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.
ย 
์œˆ๋„์šฐ, ๋ฆฌ๋ˆ…์Šค ๋“ฑ ๋‹ค์–‘ํ•œ ์šด์˜์ฒด์ œ์—์„œ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ์œผ๋ฉฐ, ํŠนํžˆ PHP์™€ ํ•จ๊ป˜ ์›น ๊ฐœ๋ฐœ์— ์ž์ฃผ ์‚ฌ์šฉ๋ฉ๋‹ˆ๋‹ค. C++, C์–ธ์–ด, PHP, JAVA ๋“ฑ ๋งŽ์€ ํ”„๋กœ๊ทธ๋ž˜๋ฐ ์–ธ์–ด๋ฅผ ์œ„ํ•œ ์—ฌ๋Ÿฌ API๋ฅผ ์ œ๊ณตํ•˜๊ณ  ์žˆ์Šต๋‹ˆ๋‹ค.
ย 
ย 

2.2 MySQL ๊ธฐ๋ฒ•

2.2.1 CREATE๋ฌธ

CREATE ๋ฌธ์„ ์‚ฌ์šฉํ•˜์—ฌ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์™€ ํ…Œ์ด๋ธ”์„ ๋งŒ๋“ค ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.
ย 
๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์ƒ์„ฑ
โžค CREATE DATABASE ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ช…;
# SCHOOL์ด๋ผ๋Š” ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ฅผ ์ƒ์„ฑํ•ฉ๋‹ˆ๋‹ค. CREATE DATABASE SCHOOL;
ย 
ํ…Œ์ด๋ธ” ์ƒ์„ฑ
โžค CREATE TABLE ํ…Œ์ด๋ธ”(์นผ๋Ÿผ1 ์นผ๋Ÿผํƒ€์ž… ,์นผ๋Ÿผ2 ์นผ๋Ÿผํƒ€์ž… , โ€ฆ);
# STUDENT๋ผ๋Š” ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•ฉ๋‹ˆ๋‹ค. # NAME, STUDENT_NUM, ADDRESS, BIRTH๋ผ๋Š” ์นผ๋Ÿผ์„ ๊ฐ€์ง„ ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•ฉ๋‹ˆ๋‹ค. # VARCHAR()๋Š” ๊ฐ€๋ณ€ ๊ธธ์ด ๋ฌธ์ž์—ด, INT๋Š” ์ˆซ์žํ˜•, DATE๋Š” ๋‚ ์งœํ˜•์„ ์˜๋ฏธํ•ฉ๋‹ˆ๋‹ค. CREATE TABLE STUDENT(NAME VARCHAR(20) ,STUDENT_NUM INT ,ADDRESS VARCHAR(50) ,BIRTH DATE)
NAME
STUDENT_NUM
ADDRESS
BIRTH
ย 

2.2.2 ALTER๋ฌธ

ALTER ๋ฌธ์„ ์‚ฌ์šฉํ•˜์—ฌ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์™€ ํ…Œ์ด๋ธ”์˜ ๋‚ด์šฉ์„ ์ˆ˜์ •ํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.
ย 
๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ๋ฌธ์ž์ง‘ํ•ฉ ๋ณ€๊ฒฝ
โžค ALTER DATABASE ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ช… CHARACTER SET = ๋ฌธ์ž์ง‘ํ•ฉ์ด๋ฆ„;
# SCHOOL์ด๋ผ๋Š” ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์˜ ๋ฌธ์ž์ง‘ํ•ฉ์„ UTF8๋กœ ๋ณ€๊ฒฝํ•ฉ๋‹ˆ๋‹ค. ALTER DATABASE SCHOOL CAHRACTER SET = utf8
ย 
ํ…Œ์ด๋ธ” ๋‚ด ์นผ๋Ÿผ ํƒ€์ž… ๋ณ€๊ฒฝ
โžค ALTER TABLE ํ…Œ์ด๋ธ”๋ช… ADD ์นผ๋Ÿผ๋ช… ์นผ๋Ÿผํƒ€์ž…;
# STUDENT ํ…Œ์ด๋ธ”์— INTํƒ€์ž…์˜ GRADE ์นผ๋Ÿผ์„ ์ถ”๊ฐ€ํ•ฉ๋‹ˆ๋‹ค. ALTER TABLE STUDENT ADD GRADE INT;
NAME
STUDENT_NUM
ADDRESS
BIRTH
GRADE
ย 
ํ…Œ์ด๋ธ” ๋‚ด ์นผ๋Ÿผ ์‚ญ์ œ
โžค ALTER TABLE ํ…Œ์ด๋ธ”๋ช… DROP ์นผ๋Ÿผ๋ช…;
# STUDENT ํ…Œ์ด๋ธ”์˜ STUDENT_NUM ์นผ๋Ÿผ์„ ์‚ญ์ œํ•ฉ๋‹ˆ๋‹ค. ALTER TABLE STUDENT DROP STUDENT_NUM;
NAME
ADDRESS
BIRTH
GRADE
ย 
ํ…Œ์ด๋ธ” ๋‚ด ์นผ๋Ÿผ ํƒ€์ž… ๋ณ€๊ฒฝ
โžค ALTER TABLE ํ…Œ์ด๋ธ”๋ช… MODIFY COLUMN ์นผ๋Ÿผ๋ช… ์นผ๋Ÿผํƒ€์ž…;
# STUDENT ํ…Œ์ด๋ธ”์˜ NAME์นผ๋Ÿผ์˜ ํƒ€์ž…์„ VARCHAR(20)์—์„œ VARCHAR(10)์œผ๋กœ ๋ณ€๊ฒฝํ•ฉ๋‹ˆ๋‹ค. ALTER TABLE SUTDENT MODIFY COLUMN NAME VARCHAR(10);
ย 

2.2.3 DROP๋ฌธ

DROP ๋ฌธ์„ ์‚ฌ์šฉํ•˜์—ฌ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์™€ ํ…Œ์ด๋ธ”์„ ์‚ญ์ œํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.
ย 
๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์‚ญ์ œ
โžค DROP DATABASE ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ช…;
# SCHOOL ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ฅผ ์‚ญ์ œํ•ฉ๋‹ˆ๋‹ค DROP DATABASE SCHOOL;
ย 
ํ…Œ์ด๋ธ” ์‚ญ์ œ
โžค DROP TABLE ํ…Œ์ด๋ธ”๋ช…;
# STUDENT๋ผ๋Š” ํ…Œ์ด๋ธ”์„ ์‚ญ์ œํ•ฉ๋‹ˆ๋‹ค DROP TABLE STUDENT;
ย 

2.2.4 INSERT๋ฌธ

INSERT ๋ฌธ์„ ์‚ฌ์šฉํ•˜์—ฌ ํ…Œ์ด๋ธ”์— ์ƒˆ๋กœ์šด ๋ ˆ์ฝ”๋“œ๋ฅผ ์ถ”๊ฐ€ํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.
ย 
ํ…Œ์ด๋ธ”์— ๊ฐ’ ์ถ”๊ฐ€(์›ํ•˜๋Š” ์นผ๋Ÿผ๋งŒ ์ž…๋ ฅ๊ฐ€๋Šฅ)
โžค INSERT INTO ํ…Œ์ด๋ธ”๋ช… (์นผ๋Ÿฌ๋ช…1, ์นผ๋Ÿผ๋ช…2, ์นผ๋Ÿผ๋ช…3, โ€ฆ) VALUES (๊ฐ’1, ๊ฐ’2, ๊ฐ’3, โ€ฆ);
# STUDENT ํ…Œ์ด๋ธ” ์นผ๋Ÿผ NAME, ADDRESS์— '์„ฑ์ถ˜ํ–ฅ','๋ถ€์‚ฐ'์„ ์ถ”๊ฐ€ํ•ฉ๋‹ˆ๋‹ค. INSERT INTO STUDENT (NAME, ADDRESS) VALUES ('์„ฑ์ถ˜ํ–ฅ', '๋ถ€์‚ฐ');
NAME
ADDRESS
BIRTH
GRADE
์„ฑ์ถ˜ํ–ฅ
๋ถ€์‚ฐ
ใ…ค
ใ…ค
ย 
ํ…Œ์ด๋ธ”์— ๊ฐ’ ์ถ”๊ฐ€(๋ชจ๋“  ์นผ๋Ÿผ์— ๊ฐ’ ์ถ”๊ฐ€)
โžค INSERT INTO ํ…Œ์ด๋ธ”๋ช… VALUES (๊ฐ’1, ๊ฐ’2, ๊ฐ’3, โ€ฆ);
''' STUDENT ํ…Œ์ด๋ธ”์— '์„ฑ์ถ˜ํ–ฅ','๋ถ€์‚ฐ','1999.11.12',2๋ฅผ ์ถ”๊ฐ€ํ•ฉ๋‹ˆ๋‹ค. ํ…Œ์ด๋ธ”๋ช… ๋’ค์— ์นผ๋Ÿผ๋ช…์„ ์ž‘์„ฑํ•˜์ง€ ์•Š์€ ๊ฒฝ์šฐ์—๋Š” ๋ชจ๋“  ์นผ๋Ÿผ ๊ฐ’๋“ค์„ ์ž‘์„ฑํ•ด์ค˜์•ผ ํ•ฉ๋‹ˆ๋‹ค. ํ…Œ์ด๋ธ”๋ช… ๋’ค์— ์นผ๋Ÿผ๋ช…์„ ์ž‘์„ฑํ•˜์ง€ ์•Š์€ ๊ฒฝ์šฐ์—๋Š” ์นผ๋Ÿผ ์ˆœ์„œ๋ฅผ ๋งž์ถฐ์„œ ์ž‘์„ฑํ•ด์ค˜์•ผ ํ•ฉ๋‹ˆ๋‹ค. ''' INSERT INTO STUDENT VALUES ('ํ™๊ธธ๋™', '๊ด‘์ฃผ', '1999.11.12', 2);
NAME
ADDRESS
BIRTH
GRADE
์„ฑ์ถ˜ํ–ฅ
๋ถ€์‚ฐ
ใ…ค
ใ…ค
ํ™๊ธธ๋™
๊ด‘์ฃผ
1999.11.12
2
ย 

2.2.5 UPDATE๋ฌธ

UPDATE ๋ฌธ์„ ์‚ฌ์šฉํ•˜์—ฌ ๋ ˆ์ฝ”๋“œ์˜ ๋‚ด์šฉ์„ ์ˆ˜์ •ํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.
ย 
ํ…Œ์ด๋ธ”๋‚ด ๊ฐ’ ๋ณ€๊ฒฝ
โžค UPDATE ํ…Œ์ด๋ธ”๋ช… SET ์นผ๋Ÿฌ๋ช…1 = ๊ฐ’1, ์นผ๋Ÿผ๋ช…2 = ๊ฐ’2, โ€ฆ WHERE ์นผ๋Ÿผ๋ช… = ์กฐ๊ฑด๊ฐ’;
''' STUDENT ํ…Œ์ด๋ธ”์—์„œ NAME์ด ์„ฑ์ถ˜ํ–ฅ์ธ ๊ฐ’์„ ์ฐพ์•„ ADDRESS, BIRTH, GRADE๋ฅผ ๋Œ€์ „, 1999.11.12, 3์œผ๋กœ ๋ณ€๊ฒฝํ•ฉ๋‹ˆ๋‹ค. ''' UPDATE STUDENT SET ADDRESS = '๋Œ€์ „', BIRTH = '1999.11.12', GRADE = 3 WHERE NAME = '์„ฑ์ถ˜ํ–ฅ';
NAME
ADDRESS
BIRTH
GRADE
์„ฑ์ถ˜ํ–ฅ
๋ถ€์‚ฐ
ใ…ค
ใ…ค
ํ™๊ธธ๋™
๊ด‘์ฃผ
1999.11.12
2
โ‡“
NAME
ADDRESS
BIRTH
GRADE
์„ฑ์ถ˜ํ–ฅ
๋Œ€์ „
1995.10.28
3
ํ™๊ธธ๋™
๊ด‘์ฃผ
1999.11.12
2
ย 

2.2.6 DELETE๋ฌธ

DELETE ๋ฌธ์„ ์‚ฌ์šฉํ•˜์—ฌ ํ…Œ์ด๋ธ”์˜ ๋ ˆ์ฝ”๋“œ๋ฅผ ์‚ญ์ œํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.
ย 
ํ…Œ์ด๋ธ” ๋‚ด ์›ํ•˜๋Š” ๊ฐ’ ์‚ญ์ œ
โžค DELETE FROM ํ…Œ์ด๋ธ”๋ช… WHERE ์นผ๋Ÿผ๋ช… = ์กฐ๊ฑด๊ฐ’;
# STUDENT ํ…Œ์ด๋ธ”์—์„œ NAME์ด ํ™๊ธธ๋™์ธ ๋ ˆ์ฝ”๋“œ๋ฅผ ์‚ญ์ œํ•ฉ๋‹ˆ๋‹ค. DELETE FROM STUDENT WHERE NAME = 'ํ™๊ธธ๋™';
NAME
ADDRESS
BIRTH
GRADE
์„ฑ์ถ˜ํ–ฅ
๋Œ€์ „
1995.10.28
3
ํ™๊ธธ๋™
๊ด‘์ฃผ
1999.11.12
2
โ‡“
NAME
ADDRESS
BIRTH
GRADE
์„ฑ์ถ˜ํ–ฅ
๋Œ€์ „
1995.10.28
3
ย 

2.2.7 SELECT๋ฌธ

SELECT ๋ฌธ์„ ์‚ฌ์šฉํ•˜์—ฌ ํ…Œ์ด๋ธ”์˜ ๋ ˆ์ฝ”๋“œ๋ฅผ ์„ ํƒํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.
ย 
ํ…Œ์ด๋ธ” ๋‚ด ๋ชจ๋“  ๊ฐ’ ์„ ํƒ
โžค SELECT * FROM ํ…Œ์ด๋ธ”๋ช…;
# STUDENT ํ…Œ์ด๋ธ”์— ํฌํ•จ๋œ ๋ชจ๋“  ๊ฐ’๋“ค์„ ๊ฐ€์ ธ์˜ต๋‹ˆ๋‹ค. SELECT * FROM STUDENT;
NAME
STUDENT_NUM
ADDRESS
BIRTH
ํ™๊ธธ๋™
1
์„œ์šธ
1999.11.14
๊น€์˜ํฌ
2
์ˆ˜์›
2000.02.14
๊น€์ฒ ์ˆ˜
3
์ธ์ฒœ
1998.04.05
ย 
ํ…Œ์ด๋ธ” ๋‚ด ์›ํ•˜๋Š” ์นผ๋Ÿผ ๊ฐ’ ์„ ํƒ
โžค SELECT ์นผ๋Ÿผ1, ์นผ๋Ÿผ2โ€ฆ FROM ํ…Œ์ด๋ธ”๋ช…;
# STUDENT ํ…Œ์ด๋ธ”์—์„œ NAME, ADDRESS ์นผ๋Ÿผ ๊ฐ’๋“ค์„ ๊ฐ€์ ธ์˜ต๋‹ˆ๋‹ค. SELECT NAME, ADDRESS FROM STUDENT;
NAME
ADDRESS
ํ™๊ธธ๋™
์„œ์šธ
๊น€์˜ํฌ
์ˆ˜์›
๊น€์ฒ ์ˆ˜
์ธ์ฒœ
ย 
ํ…Œ์ด๋ธ” ๋‚ด ์›ํ•˜๋Š” ๊ฐ’ ์„ ํƒ
โžค SELECT * FROM ํ…Œ์ด๋ธ”๋ช… WHERE ์นผ๋Ÿผ๋ช… = ์กฐ๊ฑด๊ฐ’;
# STUDENT ํ…Œ์ด๋ธ”์—์„œ NAME์นผ๋Ÿผ์˜ ๊ฐ’์ด 'ํ™๊ธธ๋™'์ธ ๋ชจ๋“  ์นผ๋Ÿผ์˜ ๊ฐ’์„ ๊ฐ€์ ธ์˜ต๋‹ˆ๋‹ค. SELECT * FROM STUDENT WHERE NAME = 'ํ™๊ธธ๋™';
NAME
STUDENT_NUM
ADDRESS
BIRTH
ํ™๊ธธ๋™
1
์„œ์šธ
1999.11.14
ย 
ย 

2.3 MySQL๊ณผ Jupyter Notebook ์—ฐ๋™

2.3.1 pymysql ๋ผ์ด๋ธŒ๋Ÿฌ๋ฆฌ ์„ค์น˜ ๋ฐ ์—ฐ๊ฒฐ

ย 
  1. pymysql ๋ผ์ด๋ธŒ๋Ÿฌ๋ฆฌ ์„ค์น˜ ๋ฐ import
์ฃผํ”ผํ„ฐ ๋…ธํŠธ๋ถ์—์„œ ์ƒˆ ํŒŒ์ผ์„ ์—ด๊ณ , ์•„๋ž˜์˜ ์ฝ”๋“œ๋ฅผ ์‹คํ–‰ํ•˜์—ฌ pymysql ๋ผ์ด๋ธŒ๋Ÿฌ๋ฆฌ๋ฅผ ์„ค์น˜ํ•ฉ๋‹ˆ๋‹ค.
# pymysql ๋ผ์ด๋ธŒ๋Ÿฌ๋ฆฌ ์„ค์น˜ !pip install pymysql
ย 
pymysql ๋ผ์ด๋ธŒ๋Ÿฌ๋ฆฌ๋ฅผ importํ•ฉ๋‹ˆ๋‹ค.
import pymysql
ย 
  1. Connection ๊ฐ์ฒด ์ƒ์„ฑ
์—ฐ๊ฒฐ์— ํ•„์š”ํ•œ ํ˜ธ์ŠคํŠธ๋ช…, ํฌํŠธ๋ฒˆํ˜ธ, ์œ ์ €๋ช…, ๋น„๋ฐ€๋ฒˆํ˜ธ๋ฅผ ๋ณ€์ˆ˜์— ์ €์žฅํ•ฉ๋‹ˆ๋‹ค.
# ๋ณธ์ธ์˜ username, password๋ฅผ ๋ณ€์ˆ˜์— ์ €์žฅํ•ฉ๋‹ˆ๋‹ค. host = '127.0.0.1' # ๋กœ์ปฌ ํ˜ธ์ŠคํŠธ port = 3307 username = 'root' password = '0000'
ย 
pymysql ๋ผ์ด๋ธŒ๋Ÿฌ๋ฆฌ์˜ Connection ๊ฐ์ฒด๋Š” ํŒŒ๋ผ๋ฏธํ„ฐ๋กœ ๋ฐ›์•„ ๊ฐ€๋Š” ๊ฐ’๋“ค์„ ํ†ตํ•ด ํŒŒ์ด์ฌ๊ณผ SQL์„ ์—ฐ๊ฒฐ์‹œ์ผœ ์ค๋‹ˆ๋‹ค. ๋”ฐ๋ผ์„œ ์•„๋ž˜์™€ ๊ฐ™์ด Connection ๊ฐ์ฒด๋ฅผ ์ƒ์„ฑํ•˜์—ฌ ํŒŒ์ด์ฌ๊ณผ MySQL์„ ์—ฐ๊ฒฐํ•ฉ๋‹ˆ๋‹ค.
# MySQL Connection conn = pymysql.connect(host = host, port = port, user = username, password = password, charset='utf8')
ย 
Connection ๊ฐ์ฒด๋ฅผ ๊ฐ€์ง€๊ณ  ์žˆ๋Š” conn ๋ณ€์ˆ˜๋ฅผ ์ถœ๋ ฅํ•ด ๋ด„์œผ๋กœ Connection ๊ฐ์ฒด๊ฐ€ ์ •์ƒ์ ์œผ๋กœ ์ƒ์„ฑ๋œ ๊ฒƒ์„ ํ™•์ธํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.
conn
โžก๏ธ
๊ฒฐ๊ณผ
<pymysql.connections.Connection at 0x21895704e80>
ย 
  1. Cusor ๊ฐ์ฒด ์ƒ์„ฑ
SQL ๋ฌธ์„ ์‹คํ–‰ํ•˜๊ธฐ ์œ„ํ•ด์„œ๋Š” DB์—์„œ SQL ๋ฌธ์„ ์‹คํ–‰ํ•˜๊ณ  ์‹คํ–‰๋œ ๊ฒฐ๊ณผ๋ฅผ ๋Œ๋ ค๋ฐ›๋Š” ํ†ต๋กœ์˜ ์—ญํ• ์„ ํ•˜๋Š” Cusor ๊ฐ์ฒด๊ฐ€ ํ•„์š”ํ•ฉ๋‹ˆ๋‹ค. ์•ž์—์„œ ์—ฐ๊ฒฐํ•œ Connection ๊ฐ์ฒด๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ์ปค์„œ ๊ฐ์ฒด๋ฅผ ์ƒ์„ฑํ•˜์˜€์Šต๋‹ˆ๋‹ค.
cur = conn.cursor()
ย 
Cursor ๊ฐ์ฒด๋ฅผ ๊ฐ€์ง€๊ณ  ์žˆ๋Š” cur ๋ณ€์ˆ˜๋ฅผ ์ถœ๋ ฅํ•ด ๋ด„์œผ๋กœ Cursor ๊ฐ์ฒด๊ฐ€ ์ •์ƒ์ ์œผ๋กœ ์ƒ์„ฑ๋œ ๊ฒƒ์„ ํ™•์ธํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.
cur
โžก๏ธ
๊ฒฐ๊ณผ
<pymysql.cursors.Cursor at 0x218957178e0>
ย 

2.3.2 SQL๋ฌธ ์‹คํ–‰

์•ž์—์„œ Cusor ๊ฐ์ฒด๊นŒ์ง€ ์ •์ƒ์ ์œผ๋กœ ์ƒ์„ฑ๋œ ๊ฒƒ์„ ํ™•์ธํ•˜์˜€์œผ๋‹ˆ, ์‹ค์ œ๋กœ SQL ๋ฌธ์„ ์‹คํ–‰ํ•ด ๋ณด๊ฒ ์Šต๋‹ˆ๋‹ค.
ย 
  1. DB ์ƒ์„ฑ
๋ฐ์ดํ„ฐ๋ฅผ ์ €์žฅํ•  DB๋ฅผ ๋จผ์ € ์ƒ์„ฑํ•ด ๋ณด๊ฒ ์Šต๋‹ˆ๋‹ค. DB ์ด๋ฆ„์€ โ€˜test_dbโ€™๋กœ ๋ช…๋ช…ํ•˜์˜€์Šต๋‹ˆ๋‹ค.
# SQL๋ฌธ ์‹คํ–‰ - DB ์ƒ์„ฑ cur.execute('CREATE DATABASE test_db') conn.commit()
ย 
์•„๋ž˜์˜ SQL ๋ฌธ์„ ์‹คํ–‰ํ•˜์—ฌ โ€˜test_dbโ€™ DB๊ฐ€ ์ •์ƒ์ ์œผ๋กœ ์ƒ์„ฑ๋œ ๊ฒƒ์„ ํ™•์ธํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. Connection ๊ฐ์ฒด์™€ ์ปค์„œ ๊ฐ์ฒด๋ฅผ close๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ์ข…๋ฃŒํ•ฉ๋‹ˆ๋‹ค.
# SQL๋ฌธ ์‹คํ–‰ - DB ๋ฆฌ์ŠคํŠธ ํ™•์ธ cur.execute('SHOW DATABASES') for data in cur: print(data) # Connection, ์ปค์„œ ๊ฐ์ฒด ์ข…๋ฃŒ cur.close() conn.close()
โžก๏ธ
๊ฒฐ๊ณผ
('information_schema',) ('mysql',) ('performance_schema',) ('sakila',) ('sys',) ('test_db',) ('world',)
ย 
  1. ํ…Œ์ด๋ธ” ์ƒ์„ฑ
โ€˜test_dbโ€™ DB ๋‚ด์— ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•ด ๋ณด๊ฒ ์Šต๋‹ˆ๋‹ค. ๋จผ์ € MySQL DB์— root ๊ณ„์ •์œผ๋กœ ์ ‘์†ํ•˜๋Š” Connection ๊ฐ์ฒด๋ฅผ ์ƒ์„ฑํ•ฉ๋‹ˆ๋‹ค.
# MySQL DB Connection conn = pymysql.connect(host = host, port = port, user = username, password = password, charset='utf8', database='test_db')
ย 
์•„๋ž˜๋Š” โ€˜studentโ€™ ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•˜๋Š” SQL ๋ฌธ์ž…๋‹ˆ๋‹ค. ์ด๋ฆ„(name), ์ฃผ์†Œ(address), ์ƒ์ผ(birth), ์„ฑ์  ๋“ฑ๊ธ‰(grade), ์ด 4๊ฐœ์˜ Column์„ ๊ฐ€์ง„ ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•˜๊ฒ ์Šต๋‹ˆ๋‹ค.
# ํ…Œ์ด๋ธ” ์ƒ์„ฑ SQL๋ฌธ sql = '''create table student ( name varchar(5) primary key, address varchar(5), birth varchar(11), grade int)'''
ย 
Connection ๊ฐ์ฒด์˜ ์ปค์„œ(cusor) ๊ฐ์ฒด๋ฅผ ์ƒ์„ฑํ•˜๊ณ , โ€˜test_dbโ€™ DB์— โ€˜studentโ€™ ํ…Œ์ด๋ธ”์„ ์ƒ์„ฑํ•˜๋Š” SQL ๋ฌธ์„ ์‹คํ–‰ํ•ฉ๋‹ˆ๋‹ค. ์‹คํ–‰ํ•œ SQL ๋ฌธ์„ ์‹ค์ œ DB์— ์ ์šฉํ•˜๊ธฐ ์œ„ํ•ด commit์„ ์‹คํ–‰ํ•ฉ๋‹ˆ๋‹ค. with ์ ˆ์„ ์ด์šฉํ•˜์˜€๊ธฐ์— ์ปค์„œ๋Š” ์ฝ”๋“œ๋ฅผ ๋ชจ๋‘ ์‹คํ–‰ํ•œ ํ›„์— ์ž๋™์œผ๋กœ close ๋ฉ๋‹ˆ๋‹ค.
# SQL๋ฌธ ์‹คํ–‰ - ํ…Œ์ด๋ธ” ์ƒ์„ฑ with conn.cursor() as cur: cur.execute(sql) conn.commit()
ย 
์œ„์˜ ๋ฐฉ์‹๊ณผ ๊ฐ™์ด โ€˜test_dbโ€™ DB ๋‚ด์˜ ํ…Œ์ด๋ธ” ๋ฆฌ์ŠคํŠธ๋ฅผ ์ถœ๋ ฅํ•˜์˜€์Šต๋‹ˆ๋‹ค. โ€˜studentโ€™ ํ…Œ์ด๋ธ”์ด ์ •์ƒ์ ์œผ๋กœ ์ƒ์„ฑ๋œ ๊ฒƒ์„ ํ™•์ธํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.
# SQL๋ฌธ ์‹คํ–‰ - ํ…Œ์ด๋ธ” ๋ฆฌ์ŠคํŠธ ํ™•์ธ with conn.cursor() as cur: cur.execute('SHOW TABLES') conn.commit() for data in cur: print(data)
โžก๏ธ
๊ฒฐ๊ณผ
('student',)
ย 
  1. ๋ฐ์ดํ„ฐ ์ €์žฅ
์ƒ์„ฑ๋œ โ€˜studentโ€™ ํ…Œ์ด๋ธ”์— ๋ฐ์ดํ„ฐ๋ฅผ ์ €์žฅํ•ด ๋ณด๊ฒ ์Šต๋‹ˆ๋‹ค.
ย 
INSERT ๋ฌธ์„ ์‚ฌ์šฉํ•˜์—ฌ ๋ฐ์ดํ„ฐ๋ฅผ ์‚ฝ์ž…ํ•˜๋Š” SQL ๋ฌธ์„ ์ž‘์„ฑํ•˜์˜€์Šต๋‹ˆ๋‹ค. ์ปค์„œ ๊ฐ์ฒด๋ฅผ ์ƒ์„ฑํ•˜๊ณ  SQL ๋ฌธ์„ ์‹คํ–‰์‹œ์ผœ์ค๋‹ˆ๋‹ค.
# ๋ฐ์ดํ„ฐ ์ €์žฅ SQL๋ฌธ sql = '''insert into student values ('์„ฑ์ถ˜ํ–ฅ', '๋Œ€์ „', '1995.10.28', 3)''' # SQL๋ฌธ ์‹คํ–‰ - ๋ฐ์ดํ„ฐ ์ €์žฅ with conn.cursor() as cur: cur.execute(sql) conn.commit()
ย 
์œ„์™€ ๊ฐ™์€ ๋ฐฉ์‹์œผ๋กœ ๋ฐ์ดํ„ฐ๋ฅผ ํ•˜๋‚˜ ๋” ์ €์žฅํ•ด ๋ณด์•˜์Šต๋‹ˆ๋‹ค.
# ๋ฐ์ดํ„ฐ ์ €์žฅ SQL๋ฌธ sql = '''insert into student values ('ํ™๊ธธ๋™', '๊ด‘์ฃผ', '1999.11.12', 2)''' # SQL๋ฌธ ์‹คํ–‰ - ๋ฐ์ดํ„ฐ ์ €์žฅ with conn.cursor() as cur: cur.execute(sql) conn.commit()
ย 
  1. ์ €์žฅ๋œ ๋ฐ์ดํ„ฐ ํ™•์ธ
์œ„์—์„œ ์ €์žฅํ•œ ๋ฐ์ดํ„ฐ๋ฅผ ํ™•์ธํ•ด ๋ณด๊ฒ ์Šต๋‹ˆ๋‹ค.
ย 
์ปค์„œ ๊ฐ์ฒด๋ฅผ ์ƒ์„ฑํ•˜์—ฌ SELECT ๋ฌธ์„ ์‹คํ–‰์‹œํ‚ค๊ณ , ๋ฐ›์•„์˜จ ๋ฐ์ดํ„ฐ๋ฅผ ์ถœ๋ ฅํ•ด ์ค๋‹ˆ๋‹ค. ๋ฐ์ดํ„ฐ๋“ค์ด ์ •์ƒ์ ์œผ๋กœ ์ €์žฅ๋œ ๊ฒƒ์„ ํ™•์ธํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.
# query sql = '''select * from student''' # SQL๋ฌธ ์‹คํ–‰ - ๋ฐ์ดํ„ฐ ํ™•์ธ with conn.cursor() as cur: cur.execute(sql) conn.commit() for data in cur: print(data)
โžก๏ธ
๊ฒฐ๊ณผ
('์„ฑ์ถ˜ํ–ฅ', '๋Œ€์ „', '1995.10.28', 3) ('ํ™๊ธธ๋™', '๊ด‘์ฃผ', '1999.11.12', 2)
ย 
๋ชจ๋“  ์ž‘์—…์„ ๋งˆ์ณค์œผ๋‹ˆ โ€˜test_dbโ€™ DB์— ์—ฐ๊ฒฐ์‹œ์ผœ๋‘” Connection ๊ฐ์ฒด๋ฅผ close ํ•ฉ๋‹ˆ๋‹ค.
# MySQL DB Connection ์ข…๋ฃŒ conn.close()
ย 
๐Ÿ“ข
์ง€๊ธˆ๊นŒ์ง€ ํ™œ์šฉ ํŽธ์—์„œ ์ž์ฃผ ์‚ฌ์šฉํ•  SQL ๋ฌธ์„ ์ค‘์ ์œผ๋กœ ์‹คํ–‰ ๋ฐฉ๋ฒ•์„ ์‚ดํŽด๋ณด์•˜์Šต๋‹ˆ๋‹ค. ์‚ฌ์šฉํ•˜์ง€ ์•Š์€ ALTER ๋ฌธ, DROP ๋ฌธ, UPDATE ๋ฌธ, DELETE ๋ฌธ ๋˜ํ•œ pymysql ๋ผ์ด๋ธŒ๋Ÿฌ๋ฆฌ์™€ execute ๋ฉ”์„œ๋“œ๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ๋™์ผํ•œ ๋ฐฉ์‹์œผ๋กœ ์‹คํ–‰ํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย 
ย