[PYTHON] MySQL에 연결하고 데이터 가져오기 (CONNECT, SELECT)

 

1. PyMySQL 라이브러리 설치하기 

pip install PyMySQL

 

2. pymysql 라이브러리 불러오기

import pymysql

 

3. DB와 연결하기 

import pymysql

# DB connect 
mysqldb = pymysql.connect( 
    user='userId',
    password='password',
    host='DBserverIP',
    db='DB명',
    charset='encoding')

 

4. 커서(cursor) 생성하기

커서(COUSOR)란 1) ? 
- sql 커서는 DB 서버에서 할당한 전용 메모리 영역에 대한포인터
- 질의의 결과로 얻어진 여러 행이 저장된 메모리 상의 위치
- 커서는 select 문의 결과 집합을 처리하는데 사용된다. 

 

PYTHON에서 실행한 QUERY

결과를 담을 공간이라고 생각하면 됩니다.

어떤 형식으로 데이터를

저장할지를 설정할 수 있습니다.

 

방법 1. pymysql.cursors.Cursor

가장 기본적인 cursor입니다. 

인스턴스를 직접 만들지 않습니다.

connection.cursor()로 호출하면 됩니다.

결과 예시 : ( val1 ,  val2) 
(('aa', 'bb'))

import pymysql

# CONNECT 
mysqldb = pymysql.connect( 
    user='userId',
    password='password',
    host='DBserverIP',
    db='DB명',
    charset='encoding')
    
# CURSOR (Default)
cursor = mysqldb.cursor()

 

방법 2. pymysql.cursor.Dictcursor 

쿼리의 결과값은 dictionary 형태로 받을 수 있는 커서입니다.

 

결과 예시  : {'컬럼명' : 'value'} 형식
 [{'col1': 'aa', 'col2': 'bb'}]  

# Dict형식의 CURSOR  
cursor = mysqldb.cursor(pymysql.cursors.DictCursor)

5. QUERY 실행하기

cursor의 execute() 메소드를 이용해서

쿼리를 실행 할 수 있습니다. 

import pymysql

# CONNECT 
mysqldb = pymysql.connect( 
    user='userId',
    password='password',
    host='DBserverIP',
    db='DB명',
    charset='encoding')
    
# Dict형식의 CURSOR  
cursor = mysqldb.cursor(pymysql.cursors.DictCursor)

# SQL 작성 
sql = "select * from mysql_table"

# SQL 실행
cursor.execute(query=sql)

6. 결과데이터 가져오기 : fetchall(), fetchone(), fetchmany(size) 

결과데이터는 전부 가져올 것인지

한 row만 가져올 것인지,

여러 row를 가져올 것인지 정할 수 있습니다.

 

방법 1. fetchall() 

결과 예시
[{'col1': 'aa', 'col2': 'bb'}, {'col1': 'aa', 'col2': 'bb'}, {'col1': 'aa', 'col2': 'bb'}]

import pymysql

# CONNECT 
mysqldb = pymysql.connect( 
    user='userId',
    password='password',
    host='DBserverIP',
    db='DB명',
    charset='encoding')
    
# Dict형식의 CURSOR  
cursor = mysqldb.cursor(pymysql.cursors.DictCursor)

# SQL 작성 
sql = "select * from mysql_table"

# SQL 실행
cursor.execute(query=sql)

# 결과 가져오기
result = cursor.fetchall()

 

방법 2. fetchone()

결과 예시
{'col1': 'aa', 'col2': 'bb'}

# 결과 가져오기
result = cursor.fetchone()

 

방법 3. fetchmany(size)

결과 예시
{'col1': 'aa', 'col2': 'bb'}[{'col1': 'aa', 'col2': 'bb'}, {'col1': 'aa', 'col2': 'bb'}]

# 결과 가져오기
result = cursor.fetchmany(size=2)

---

Q1. Hoxy .... fetchone 또는 fetchmany(size)를 했는데 너무 느린가요? 

fetchone과 fetchmany는 limit 기능이 아닙니다. 

쿼리를 다 실행한 다음의 Buffer에 담긴 결과값 몇 개만 가져오는 것이기 때문에 

데이터가 많이 담긴 테이블이라면

단순한 쿼리를 실행해도 오래 걸릴 수 있습니다.

 

쿼리를 효율적으로 실행할 수 있도록 하는 조건을 걸거나

limit을 걸어서 실행하는 것이 좋습니다.

---

Q2. 조건을 걸어도 데이터의 사이즈가 너무 큰가요? 

그렇다면,

SSCursor 또는 SSDictCursor을 이용해 보세요. 

pymysql.cursors.SSCursor는 

Unbuffed Cursor로 주로 데이터의 양이 많거나,

네트워크 속도가 느린 원격서버에 접속할 때 사용합니다. 

buffer에 모든 데이터를 저장하는 대신에 필요에 따라 행을 가져옵니다.

 

하지만

현재행만 메모리에 저장되므로 전제 행의 수를 알거나,

이전의 행을 알 수 없는 단점이 있습니다. 

 

SSCursor는 주로 For문과 함께 사용합니다.

import pymysql

# CONNECT 
mysqldb = pymysql.connect( 
    user='userId',
    password='password',
    host='DBserverIP',
    db='DB명',
    charset='encoding')
    
# Dict형식의 CURSOR  
cursor = mysqldb.cursor(pymysql.cursors.SSDictCursor)

# SQL 작성 
sql = "select * from mysql_table"

# SQL 실행
cursor.execute(query=sql)

# 결과 가져오기
for row in cursor:
    print(row)

---

 

Q3. DB Connect과 Server 상태를 확인할 수 있는 방법을 찾나요? 

그럴때는 open와 ping()으로 확인할 수 있습니다.

 

• open : 연결이 되면 TRUE을 반환합니다. 

mysqldb = pymysql.connect( 
    user='userId',
    password='password',
    host='DBserverIP',
    db='DB명',
    charset='encoding')

# connect open 확인
mysqldb.open

 

• ping() : 서버가 살아 있는지 확인하고 접속이 안될때 에러를 발생합니다.

mysqldb = pymysql.connect( 
    user='userId',
    password='password',
    host='DBserverIP',
    db='DB명',
    charset='encoding')

# DB Server 살아 있는지 확인
mysqldb.ping()

 

 

 

참고자료 

1) https://pymysql.readthedocs.io/en/latest/modules/index.html

1) https://goddaehee.tistory.com/117