Python으로 Mysql 다루기 (pymysql)

DB 사용자관리

• 사용자 생성 : mySQL 의 root 사용자를 모든 용도에 사용하지 말고, 적절한 권한을 가지는 사용자를 생성하여 사용하는 것이 바람직
• 예) create user 'db2020'@’%’ identified by 'db2020';
• 권한 부여 : 특정 데이터베이스, 테이블 등에 읽기/쓰기 등의 권한을 부여할 수 있음
• 예) grant all privileges on university.* to 'db2020'@'%';

---

DB 응용프로그램 API 호출 순서

1. DB connection 설정

#Pymysql를 import
import pymysql

#DB connection 설정
conn = pymysql.connect(
  host='localhost',
  user='db2020',
  password='db2020',
  db='university')

2. Cusor 생성

curs = conn.cursor()

• cursor: 하나의 DB connection에 대하여 독립적으로 SQL 문을 실행할 수 있는 작업환경을 제공하는 객체.
• 하나의 connection에 동시에 한개의 cursor만 생성가능
• cursor를 통해서 SQL 문을 실행할 수 있으며, 응용 프로그램이 실행결과를 투플 단위로 접근할 수 있도록함.

3. SQL문 실행

#SQL 문 정의
sql = "select * from student"
#SQL 문 실행
curs.execute(sql)

4. SQL 결과 가져오기

rows = curs.fetchall()
print(rows)

• 한번에 모든 tuple 가져오기
• 이 경우 검색결과가 매우 클 경우 메모리 오버헤드가 발생할 수 있음에 주의.

row = curs.fetchone()
while row:
  print(row)
  row = curs.fetchone()

• 루프를 돌면서 하나씩 가져오기

5. Cursor 및 Connection 닫기

# cursor 닫기
curs.close()
# db connection 닫기
conn.close()

---

SQL 검색결과에서 각 투플의 애트리뷰트 접근하기

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()

• cursor 생성시 pymysql.cursors.DictCursor 값을 설정해 주면 투플들의 애트리뷰트를 이름으로 접근이 가능

---

commit

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()

• executemany로 여러개 동시 작업도 가능