본 게시물은 데이터베이스 과목의 강의영상과 강의자료를 바탕으로 작성한 학습용 게시물입니다.
DB 사용자 관리
- 사용자 생성
- mySQL의 root 사용자를 모든 용도에 사용하지 말고, 적절한 권한을 가지는 사용자를 생성하여 사용하는게 바람직
- ex) create users 'db2020'@'%'identicied by 'db2020'; - 권한 부여
- 특정 데이터베이스, 테이블 등에 읽기/쓰기 등의 권한을 부여할 수 있음
- ex) grant all privileges on university.*to'dv2020'@'%';
DB 응용프로그램 API 호출 순서
- DB connection 실행
- Cursor 생성
- SQL문 실행
- SQL 검색결과 가져오기
- Cursor 닫기
- DB connection 닫기
DB connecion 설정
- Pymysql를 import
- import pymysql - DB connection 설정
conn = pymysql.connect(host='localhost', user='db2020', password='db2020', db='university')
Cursor 설정
- cursor : 하나의 DB connection에 대하여 독립적으로 SQL문을 실행할 수 있는 작업환경을 제공하는 객체
- 하나의 connection에 동시에 한개의 cursor만 생성할 수 있음
- cursor를 통해서 SQL문을 실행할 수 있으며, 응용 프로그램이 실행결과를 튜플 단위로 접근할 수 있도록 해줌
- curs = conn.cursor()
SQL문 실행
- SQL문 정의
- sql = "select * from student" - SQL 문 실행
- curs.execute(sql)
SQL 검색결과 가져오기
- 방법1 : 검색결과의 모든 튜플을 한 번에 가져오기
rows = curs.fetchall()
print(rows)
- 검색결과의 모든 튜플을 응용프로그램 메모리 공간으로 모두 복사함
- 튜플이 과다하게 많을 경우, 메모리 복사 오버헤드 발생 - 방법 2 : 검색결과의 튜플을 루프를 돌면서 하나씩 차례대로 가져오기
rows = curs.fetchone()
while row:
print(row)
row = curs.fetchone()
- 메모리 관리가 효율적
- 튜플의 갯수가 현저히 작을때에만 fetchall()을 사용하고,
대부분의 경우에는 fetchone() 을 사용하는 습관을 가지는 게 바람직함
cursor 및 connection 닫기
- cursor 닫기
curs.close() - de connection 닫기
- 좀비 생기지 않도록 유의
conn.close()
SQL 검색결과에서 각 튜플의 애트리뷰트 접근하기
- cursor 생성시 pymysql.cursors.DictCursor 값을 설정해 주면 튜플들의 애트리뷰트를 이름으로 접근이 가능
- curs = conn.cursor(pymysql.cursors.DictCursor)
sql = "select sno, sname from student"
curs.execute(sql)
row = curs.fetchone()
while row : print("학번 : %d, 이름 : %s" %(row['sno'], row['sname']))
row = curs.fetchone()
튜플 삽입
- curs = conn.cursor()
sql = "insert into student (sno,sname,dept) values (%s,%s,%s)"
a = (3000,'김선경','컴퓨터')
curs.execute(sql,a)
conn.commit() - insert, delete, update 문 이후에는 connection을 commit() 해야, 데이터베이스 반영이 됨
동시에 복수의 튜플 삽입
- curs = conn.cursor()
sql = "insert into student (sno,sname,dept) values (%s,%s,%s)"
a = (3000,'김선경','컴퓨터')
b = (4000,'황산성','산업공학')
c = (5000,'김호일','경영학')
student_list =[a,b,c]
curs.executemany(sql,student_list)
conn.commit()
튜플 삭제
- curs = conn.cursor()
sno = 1000
sql = "delete from student where sno = %s" %(sno)
curs.execute(sql)
conn.commit()
튜플 업데이트
- curs = conn.cursor()
dept = 'CS'
sno = 800
sql = "update student set dept = '%s' where sno = %d" %(dept,sno)
curs.execute(sql)
conn.commit()
동시에 여러개의 connection 및 cursor 생성하는 예
- 하나의 connection으로 동시에 하나의 cursor만을 생성하는 게 바람직함
- 하나의 cursor는 한번에 하나의 SQL문 실행 가능
- 동시에 여러 SQL문을 실행하고자 할 때는 다중 connection 및 cursor 생성할 수 있음
- 한 문장의 SQL로 표현하기 어렵거나 비효율적인 경우, 복수의 SQL문을 중첩으로 사용가능
2단계 SQL문
- "컴퓨터과 학생들 중에서 2과목 이상을 수강하는 학생들의 기말고사 점수를 10점씩 올려라"
- 전략 : 2단계의 중첩 SQL문 수행
- 1단계 : 2과목 이상 듣는 학생들의 학번 검색
select s.sno
from student s, enrol e
where s.sno = e.sno and s.dept = '컴퓨터'
group by e.sno
having count(*) >= 2 - 2단계 : 각 학생에 대하여 기말고사 점수를 10점씩 올린다.
update enrol
set final = final + 10
where sno = %d
복수의 connection/cursor 예
- import mysql
conn1 = pymysql.connet(host='localhost', user = 'db201902', password = 'db2020', db = 'university')
curs1 = conn1.cursor(pymysql.cursors.DictCursor)
conn2 = pymysql.connect(host='localhost', user = 'db201902', password = 'db2020', db = 'university')
curs2 = conn2.cursor(pymysql.cursors.DictCursor)
sql1 = """select s.sno from student s, enrol e where s.sno = e.sno and s.dept = '컴퓨터'
grpup by e.sno having count(*) >= 2 """
curs1.execute(sql)
row = curs1.fetchone()
while (row) :
sql2 = """ update enrol set final = final + 10 where sno = %d"""%(row['sno'])
curs2.execute(sql2)
conn2.commit()
row = curs1.fetchone()
curs1.close()
curs2.close()
conn1.close()
cinn2,close()
'LECTURE > [2021-1] 데이터베이스' 카테고리의 다른 글
| [데이터베이스] Database Index (0) | 2021.06.10 |
|---|---|
| [데이터베이스] E/R Model을 Relational Database로 변환 (0) | 2021.06.10 |
| [데이터베이스] E-R modeling (0) | 2021.06.10 |
| [데이터베이스] 데이터베이스 설계 (0) | 2021.06.10 |
