[Python MySQL] Python에서 MySQL 연결하고 데이터 조회, 추가하기

Python 설치부터 VSCode, Jupyter, venv설정까지

위 글을 참고하여

터미널 cmd로 설정 후 venv 생성 후 아래 내용 실행

 

[MySQL] 서브쿼리(Sub Query) - 단일행, 다중행, 스칼라, 인라인 뷰

SQL에서 위 글 예제파트에 있는 테이블과 값 ai 데이터베이스에 생성 후 아래 내용 실행

---

💡 Python과 SQL을 함께 사용하는 이유

SQL로 만들어진 많은양의 데이터를 빠르게 조회하고,

파이썬으로 해당 데이터를 분석, 시각화하여

자동화 된 프로그램을 만들기 위해 함께 사용한다.

 

💡 mysqlclient를 쓰는 이유

mysqlclient은 Python에서 SQL 데이터베이스와 상호작용하기 위해 사용하는 공식 라이브러리로

PyMySQL도 있긴 하지만 mysqlclient이 C언어 기반으로 제작되어 있어

속도상으로 mysqlclient이 훨씬 유리하다.

그렇기 때문에 mysqlclient사용을 권장한다.

※ MAC에서 지원이 안되거나, 실무에선 SQLalchemy를 사용하기도 한다.

---

1. mysqlclient 설치

1_1) 터미널에 아래 코드 입력하여 실행

python -m pip install mysqlclient

 

1_2) 실행 후 코드셀에 아래 코드 입력하여 실행

import MySQLdb

---

2. MySQL 접속방법

1) db 연결

# 기본 문법
MySQLdb.connect(host='IP주소', user='사용자명', password='비밀번호', db='데이터베이스명')

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

---

2) cursor 생성

cursor는 데이터베이스에 SQL문을 전달하고

실행 결과를 받아오기 위한 객체이다.

 

하나의 connection에 대해서 동시에 하나의 cursor만 생성할 수 있으며,

cursor를 통해 SQL문을 실행하면 실행 결과를 튜플단위로 반한한다.

 

즉, 하나의 데이터베이스에선 하나의 cursor 객체만 생성할 수 있고,

데이터베이스가 바뀌면 새로운 cursor를 생성할 수 있다.

# cursor객체 생성 (SQL 실행 객체)
cur = db.cursor()

cur.execute("select * from Mytest")

실행결과 ▶ 6개의 행이 반환되었다는 뜻

---

3) SQL문의 결과 반환

3_1) fetchall()

한 번에 모든 결과를 가져오는 방식으로

검색 결과가 매우 많다면 메모리 오버헤드가 발생할 수 있다.

 

fetchall()로 데이터를 모두 가져오면 Cursor의 위치가 마지막으로 이동하기 때문에

다시 fetchall()을 실행하면 결과가 없다.

data = cur.fetchall()
data

실행결과 ▶ 최초 1회 실행

 

실행결과 ▶ 1회 실행 후 한번 더 실행

---

3_2) fetchone()

한 번에 한 개의 행(Row)만 가져오는 방식이다.

실행 후 다시 한번 더 fetchone() 메서드를 호출하면 그 다음 데이터를 가져온다.

 

아래 SQL 테이블 이미지로 예를 들어보면

처음 실행때는 idx1번의 내용을 결과로 보여주고

그 다음 실행할때는 idx 2번의 내용을 결과로 보여주는 next의 개념을 가지고 있다.

 

row = cur.fetchone()
row

실행결과 ▶ 최초 1회 실행

실행결과 ▶ 1회 실행 후 한번 더 실행

---

3_3) While문을 사용하여 결과 반환하기

# SQL 실행
while True:
    # 한 행씩 가져오기
    row = cur.fetchone()

    # 데이터가 존재하면 출력
    if row:
        print(row)

    # 더 이상 데이터가 없으면 반복 종료
    else:
        break

실행결과 ▶

---

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

기본적으로 MySQL 결과는 Tuple 형태로 반환이 된다.

하지만 DictCursor를 사용하여

컬럼명을 Key로 설정하는 Dictionary 형태로도 결과를 반환할 수 있다.

# 기본 문법
cursor(MySQLdb.cursors.DictCursor)

# DictCursor 생성 : 딕셔너리 형태로 결과 반환
cur = db.cursor(MySQLdb.cursors.DictCursor)

# SQL 실행
cur.execute(sql)

# 모든 결과를 한 번에 가져오기
result = cur.fetchall()

# 결과 출력
result

실행결과 ▶

 

# SQL 다시 실행
# : fetchall()로 데이터를 모두 읽었기 때문에
# : Cursor 위치를 처음으로 되돌리기 위해 다시 실행
cur.execute(sql)

while True:
    # 한 행씩 가져오기
    row = cur.fetchone()
    
    # 가져 온 튜플에 데이터가 있다면
    if row:

        # 딕셔너리 Key를 이용하여 값 출력 : 따옴표 주의하기!
        print(f"아이디 : {row['userid']}, 이름 : {row['name']}, 성별 : {row['gender']}, 전화번호 : {row['hp']}")
    
    # 더 이상 데이터가 없으면 반복 종료
    else:
        break

실행결과 ▶

 

📌 다시 SQL을 실행하는 이유

앞에서 이미 fetchall()을 사용하여 모든 데이터를 가져왔기 때문에

Cursor의 위치가 결과의 마지막 행으로 이동한 상태이다.

 

마지막 행으로 이동했기 때문에 fetchone()을 실행해도 더 이상 가져올 데이터가 없다.

그래서 다시 execute()를 실행하여 Cursor 위치를 처음으로 되돌린 후 데이터를 가져와야 한다.

---

5. 데이터 삽입하기

5_1) MySQL 데이터베이스 연결 설정

db = MySQLdb.connect(host='localhost', user='root', password='1234', db='ai')
cur = db.cursor()

---

5_2) 데이터 한개 추가하기 (execute)

데이터베이스 테이블에 하나의 코드(행)을 추가하는 방법으로

데이터 형식을 처리할때는 안전하게 처리하기 위해 %s를 사용한다.

# %s = 문자열로 넣겠다.
sql = "insert into member (userid, userpw, name, hp, email, gender, ssn1, ssn2) values (%s, %s, %s, %s, %s, %s, %s, %s)"

# 튜플 형태로 추가 할 데이터 준비
data = ('mango', '7777', '마앙고', '010-7777-7777', 'mango@mango.com', '남자', '000000', '0000000')

#cur.execute() 사용 : 데이터 추가하여 실행
cur.execute(sql, data)

# 최종 반영
db.commit()

실행결과 ▶SQL로 가서 확인 (select * from MyTest;)

---

5_3) 데이터 여러개 추가하기 (executemany)

# 데이터 여러개 집어넣기
sql = "insert into member (userid, userpw, name, hp, email, gender, ssn1, ssn2) values (%s, %s, %s, %s, %s, %s, %s, %s)"

# 리스트로 넣으면 됨
data = [('pear', '8888', '배철수', '010-8888-8888', 'pear@pear.com', '남자', '000000', '0000000'), ('peach', '9999', '복숭아', '010-9999-9999', 'peach@peach.com', '남자', '000000', '0000000')]

# execute할 때 executemany로 사용
cur.executemany(sql, data)

# 반영
db.commit()

실행결과 ▶ SQL로 가서 확인 (select * from MyTest;)

---

6. Cursor와 Connection 닫기

💡 왜 닫아야 할까?

데이터베이스 연결(Connection)과 Cursor는 서버의 자원을 사용하고 있다.

 

작업이 끝난 후 닫지 않으면

데이터베이스는 사용자가 계속 연결되어있는것으로 인식하여 자원을 유지하게 된다.

 

연결이 계속 누적되면 

메모리 사용량이 증가하고 데이터베이스 성능에 영향을 주기 때문에

작업이 끝나면 반드시 Cursor와 Connection을 종료해주어야 한다.

# Cursor 종료
cur.close()

# Database 연결 종료
db.close()

---

정리

• Cursor는 SQL을 실행하는 작업 도구
• Connection은 데이터베이스와 연결된 통로
• 순서 : SQL 실행 → Cursor 사용 → 결과 조회 → Cursor 종료 → Connection 종료