설치
일반적인 mysql 핸들링 코드 작성 순서
import pymysql
db = pymysql.connect(host='funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com', port=3306, user='davelee', passwd='korea123', db='student_mgmt', charset='utf8')
db
cursor = db.cursor()
cursor
sql = """
CREATE TABLE korea (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
model_num VARCHAR(10) NOT NULL,
model_type VARCHAR(10) NOT NULL,
PRIMARY KEY(id)
);
"""
cursor.execute(sql)
cursor.execute("SHOW TABLES")
db.commit()
db.close()
import pymysql
# 접속
db = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='korea123', db='dave', charset='utf8')
# 커서 가져오기
cursor = db.cursor()
# SQL 문 만들기
sql = '''
CREATE TABLE korea2 (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
model_num VARCHAR(10) NOT NULL,
model_type VARCHAR(10) NOT NULL,
PRIMARY KEY(id)
);
'''
# 실행하기
cursor.execute(sql)
# DB에 Complete 하기
db.commit()
# DB 연결 닫기
db.close()
import pymysql
db = pymysql.connect(host='funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com', port=3306, user='davelee', passwd='korea123', db='dave_db', charset='utf8')
try:
with db.cursor() as cursor:
sql = '''
CREATE TABLE korea (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
model_num VARCHAR(10) NOT NULL,
model_type VARCHAR(10) NOT NULL,
PRIMARY KEY(id)
);
'''
cursor.execute(sql)
db.commit()
finally:
db.close()
import pymysql
db = pymysql.connect(host='funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com', port=3306, user='davelee', passwd='korea123', db='dave_db', charset='utf8')
cursor = db.cursor()
sql = '''
CREATE TABLE cpu_info (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
model_num VARCHAR(10) NOT NULL,
model_type VARCHAR(10) NOT NULL,
PRIMARY KEY(id)
)
'''
cursor.execute(sql)
db.commit()
db.close()
# 잘못만들어졌으면, DROP DATABASE [IF EXISTS] dbname; 로 테이블 삭제 후 재생성
db = pymysql.connect(host='funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com', port=3306, user='davelee', passwd='korea123', db='dave_db', charset='utf8')
try:
cursor = db.cursor()
for num in range(10, 20):
sql = "INSERT INTO cpu_info (id, name, model_num, model_type) VALUES(" + str(num) + ", 'i5', '7700', 'Kaby Lake')"
print(sql)
cursor.execute(sql)
db.commit()
print(cursor.lastrowid)
finally:
db.close()
db = pymysql.connect(host='funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com', port=3306, user='davelee', passwd='korea123', db='dave_db', charset='utf8')
try:
cursor = db.cursor()
sql = "SELECT * FROM cpu_info"
cursor.execute(sql)
result = cursor.fetchall()
for row_data in result:
print(row_data[0])
print(row_data[1])
print(row_data[2])
print(row_data[3])
finally:
db.close()
db = pymysql.connect(host='funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com', port=3306, user='davelee', passwd='korea123', db='dave_db', charset='utf8')
cursor = db.cursor()
sql = "SELECT * FROM cpu_info WHERE name = 'i5'"
cursor.execute(sql)
result = cursor.fetchall()
result
type(result)
for data in result:
print(data[0])
print(data[1])
print(data[2])
print(data[3])
result = cursor.fetchone()
print(result)
for row_data in result:
print(row_data[0], row_data[1], row_data[2], row_data[3])
db = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='korea123', db='dave', charset='utf8')
try:
with db.cursor() as cursor:
sql = "SELECT * FROM cpu_info WHERE name = 'i5'"
cursor.execute(sql)
while result:
result = cursor.fetchone()
print(result)
finally:
db.close()
db = pymysql.connect(host='funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com', port=3306, user='davelee', passwd='korea123', db='dave_db', charset='utf8')
cursor = db.cursor()
sql = "SELECT * FROM cpu_info WHERE name = 'i5'"
cursor.execute(sql)
while result:
result = cursor.fetchone()
print(result)
db.close()
db = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='korea123', db='dave', charset='utf8')
try:
with db.cursor() as cursor:
sql = "SELECT * FROM cpu_info"
cursor.execute(sql)
result = cursor.fetchall()
for row_data in result:
print(row_data)
finally:
db.close()
db = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='korea123', db='dave', charset='utf8')
cursor = db.cursor()
sql = "SELECT * FROM cpu_info WHERE name = 'i5'"
cursor.execute(sql)
result = cursor.fetchall()
for row_data in result:
print(row_data)
db.close()
import pymysql
db = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='korea123', db='dave', charset='utf8')
try:
with db.cursor() as cursor:
sql = "UPDATE cpu_info SET model_type='%s' WHERE name = 'i7'" % '카비레이크'
cursor.execute(sql)
db.commit()
print(cursor.rowcount)
finally:
db.close()
db = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='korea123', db='dave', charset='utf8')
hit_count = 20
try:
with db.cursor() as cursor:
sql = "DELETE FROM cpu_info WHERE name = '%s'" % 'i7'
cursor.execute(sql)
db.commit()
print(cursor.rowcount)
finally:
db.close()
db = pymysql.connect(host='funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com', port=3306, user='davelee', passwd='korea123', db='dave_db', charset='utf8')
try:
cursor = db.cursor()
for num in range(10, 20):
sql = "INSERT INTO cpu_info (id, name, model_num, model_type) VALUES(" + str(num) + ", 'i5', '7700', 'Kaby Lake')"
print(sql)
cursor.execute(sql)
db.commit()
print(cursor.lastrowid)
finally:
db.close()
db = pymysql.connect(host='funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com', port=3306, user='davelee', passwd='korea123', db='dave_db', charset='utf8')
cursor = db.cursor()
sql = "INSERT INTO cpu_info (id, name, model_num, model_type) VALUES(1, 'i5', '7700', 'Kaby Lake')"
cursor.execute(sql)
db.commit()
db.close()
mysql> SELECT COUNT(*) FROM cpu_info; +----------+ | COUNT(*) | +----------+ | 9 | +----------+
db = pymysql.connect(host='funcoding-db.ca1fydhpobsc.ap-northeast-2.rds.amazonaws.com', port=3306, user='davelee', passwd='korea123', db='dave_db', charset='utf8')
try:
with db.cursor() as cursor:
sql = "SELECT name, COUNT(*) FROM cpu_info GROUP BY name"
cursor.execute(sql)
result = cursor.fetchall()
for row_data in result:
print(row_data)
finally:
db.close()
SELECT COUNT(*) AS N_STUDENT FROM STUDENT;
SELECT ID_LEC COUNT(*) AS N_REG FROM LECTURES GROUP BY ID_LEC HAVING COUNT(*) >= 5;
SELECT b.dname, COUNT(a.empno) FROM emp a, dept b WHERE a.deptno = b.deptno
SELECT job, SUM(sal) FROM emp WHERE job != 'SALES' -- 판매원은 제외 GROUP BY job -- 업무별로 Group By HAVING SUM(sal) > 5000 -- 전체 월급이 5000을 초과하는 ORDER BY SUM(sal) DESC; -- 월급여 합계로 내림차순 정렬 JOB 급여합계 ------------------ ---------- MANAGER 8275 ANALYST 6000 SALESMAN 5600
SELECT b.dname, COUNT(a.empno) FROM emp a, dept b WHERE a.deptno = b.deptno GROUP BY b.dname HAVING COUNT(a.empno) > 5; DNAME 사원수 ------------ ------- SALES 6