파이썬과 MySQL 연동하기

1. MySQLclient

• 파이썬에서 MySQL 데이터베이스와 상호작용하기 위해 사용하는 라이브러리
• PyMySQL, mysqlclient를 가장 많이 사용함
• 사용법은 비슷하나 mysqlclient가 속도상으로 유리하기 때문에 mysqlclient를 권장하고 있음

기능

• 데이터베이스 연결: MySQL 서버에 연결을 설정하고 세션을 시작한다
• 쿼리 실행: SQL 명령을 실행하여 데이터를 조회, 삽입, 수정, 삭제할 수 있다
• 트랜잭션 관리: 트랜잭션을 시작, 커밋, 롤백하는 기능을 지원한다
• 커서 사용: 데이터베이스 쿼리 결과를 처리하기 위한 커서 객체를 지원한다

설치 방법

MySQLclient는 pip를 사용하여 쉽게 설치할 수 있다. 아래는 설치 명령이다

!pip install mysqlclient

 

Collecting mysqlclient
  Downloading mysqlclient-2.2.4-cp311-cp311-win_amd64.whl.metadata (4.6 kB)
Downloading mysqlclient-2.2.4-cp311-cp311-win_amd64.whl (203 kB)
   ---------------------------------------- 0.0/203.2 kB ? eta -:--:--
   ------------------------ --------------- 122.9/203.2 kB 7.0 MB/s eta 0:00:01
   ---------------------------------------- 203.2/203.2 kB 6.0 MB/s eta 0:00:00
Installing collected packages: mysqlclient
Successfully installed mysqlclient-2.2.4

[notice] A new release of pip is available: 24.0 -> 24.2
[notice] To update, run: python.exe -m pip install --upgrade pip

 

MySQL 데이터베이스를 사용하기 위해 MySQLdb 모듈을 import한다

import MySQLdb

 

1-1. MySQL 접속하기

• 실행 형식 : MySQLdb.connect(host='ip주소', user='사용자명', password='비밀번호', db='데이터베이스명')
• host : 데이터베이스 서버의 호스트 이름 또는 ip주소이다 로컬 서버에 연결하는 경우 'localhost'로 설정하고, 원격 서버의 경우 ip 주소나 도메인 이름을 제공한다
• user : 데이터베이스 서버에 접근할 때 사용할 사용자 계정이름이다
• password : 해당 계정의 비밀번호 이다
• db : 연결하고자 하는 데이터베이스의 이름이다

MySQLdb.connect() 함수를 사용해 MySQL DB 서버에 연결해보자

참고로 앞에 host,user,password,db를 정의해 주지 않고 MySQLdb.connect(호스트, 사용자 이름, 비밀번호, 데이터베이스)하여도 된다

db = MySQLdb.connect(host='localhost', user='root', password='1234', db='kdt')
print(db)

<_mysql.connection open to 'localhost' at 0000028B743AC270>

1-2. cursor 생성하기

• 하나의 데이터베이스 connection에 대해 독립적으로 SQL문을 실행할 수 있는 작업환경을 제공하는 객체이다
• 하나의 connection에 동시에 한 개의 cursor만 생성할 수 있으며, cursor를 통해 SQL문을 실행하면 실행결과를 튜플 단위로 반환한다

cur = db.cursor()
cur.execute('select userid, name, gender, hp from member')

6

1-3. SQL문의 결과 가져오기

• fetchall(): 한번에 모든 tuple을 가져옴. 검색 결과가 매우 많다면 메모리 오버헤드가 발생할 수 있다
• fetchone(): 한번에 하나의 tuple을 가져옴. 다시 fetchone() 메서드를 호출하면 다음 데이터를 가져온다

data = cur.fetchall() 문은 SQL 쿼리 실행 후, fetchall() 메서드를 사용하여 SQL 쿼리 결과를 모두 가져와서 data 변수에 저장한다

data = cur.fetchall()
print(data)

(('apple', '김사과', '여자', '010-1111-1111'), ('avocado', '안가도', '남자', '010-5555-5555'), ('banana', '바나나', '여자', '010-2222-2222'), ('cherry', '채리', '여자', '010-6666-6666'), ('melon', '이메론', '남자', '010-4444-4444'), ('orange', '오렌지', '남자', '010-3333-3333'))

cur.fetchall()을 두 번 호출하면 첫 번째 호출에서 결과 값을 모두 호출하고 내부 포인터가 집합의 끝으로 이동하기 때문에 두 번째 호출에서는 반환할 값이 없기 때문에 아래 처럼 빈값이 반환 된다

data = cur.fetchall()
print(data)

()

 

cur.execute(sql)문을 통해 sql 변수에 담긴 SQL쿼리를 데이터베이스에서 실행하게 한다.

sql = 'select userid, name, gender, hp from member'
cur.execute(sql)

6

fetchone(): 한 번 호출에 하나의 행만 반환합니다. 결과 집합에서 다음 행을 반환하고, 더 이상의 행이 없으면 None을 반환합니다.

row = cur.fetchone()
print(row)

('apple', '김사과', '여자', '010-1111-1111')

row = cur.fetchone()
print(row)

('avocado', '안가도', '남자', '010-5555-5555')

row = cur.fetchone()
print(row)

('banana', '바나나', '여자', '010-2222-2222')

cur.execute(sql)

6

while문을 통해 모든 모든 행을 출력한다

while True:
    row = cur.fetchone()
    if row:
        print(row)
    else:
        break

('apple', '김사과', '여자', '010-1111-1111')
('avocado', '안가도', '남자', '010-5555-5555')
('banana', '바나나', '여자', '010-2222-2222')
('cherry', '채리', '여자', '010-6666-6666')
('melon', '이메론', '남자', '010-4444-4444')
('orange', '오렌지', '남자', '010-3333-3333')

1-4. 딕셔너리 형태로 결과를 반환하기

• cursor(MySQLdb.cursors.DictCursor) 를 이용해 쿼리를 딕셔너리 형태로 반환 할 수 있다

cur = db.cursor(MySQLdb.cursors.DictCursor)
sql = 'select userid, name, gender, hp from member'
cur.execute(sql)
result = cur.fetchall()
print(result)

({'userid': 'apple', 'name': '김사과', 'gender': '여자', 'hp': '010-1111-1111'}, {'userid': 'avocado', 'name': '안가도', 'gender': '남자', 'hp': '010-5555-5555'}, {'userid': 'banana', 'name': '바나나', 'gender': '여자', 'hp': '010-2222-2222'}, {'userid': 'cherry', 'name': '채리', 'gender': '여자', 'hp': '010-6666-6666'}, {'userid': 'melon', 'name': '이메론', 'gender': '남자', 'hp': '010-4444-4444'}, {'userid': 'orange', 'name': '오렌지', 'gender': '남자', 'hp': '010-3333-3333'})

 

문제

• member 테이블의 데이터를 하나의 row씩 딕셔너리로 가져와 아래와 같이 출력해보자.
• 아이디: XXX, 이름: XXX, 성별: XXX, 전화번호: XXX

while True:
    row = cur.fetchone()
    if row:
        print(f"아이디:{row['userid']}, 이름:{row['name']}, 성별:{row['gender']}, 전화번호:{row['hp']}")
    else:
        break

아이디:apple, 이름:김사과, 성별:여자, 전화번호:010-1111-1111
아이디:avocado, 이름:안가도, 성별:남자, 전화번호:010-5555-5555
아이디:banana, 이름:바나나, 성별:여자, 전화번호:010-2222-2222
아이디:cherry, 이름:채리, 성별:여자, 전화번호:010-6666-6666
아이디:melon, 이름:이메론, 성별:남자, 전화번호:010-4444-4444
아이디:orange, 이름:오렌지, 성별:남자, 전화번호:010-3333-3333

1-5. Cursor와 Connection 닫기

데이터베이스를 사용하고 나서는 적절한 리소스 관리는 데이터베이스 성능 유지 및 안정적인 애플리케이션 운영을 위해  데이터베이스 작업 후에는 반드시 연결과 커서를 닫아야 합니다.

cur.close() # 커서 닫기
db.close() # 커넥션 닫기