[데이터 분석] pymysql 라이브러리를 활용한 데이터베이스에 저장된 데이터 조회하기(cursor, execute, fetchall & fetchone, close)

 이번 글에서는 "데이터베이스에 저장된 데이터를 호출하고 조회"하는 데에 초점을 맞추어 글을 작성할 것이다.

 [차례]
 첫 번째, 데이터베이스에 저장된 데이터를 호출하기 위해, 데이터베이스와 연결(connect)하는 방법을 작성할 것이다.
 두 번째, 연결한 데이터베이스 내의 저장된 데이터를 호출할 것이다.
 세 번째, 조회 결과가 여러 건일 때와 한 건일 때의 경우로 나누어 조회를 수행할 것이다.

 [사용 툴]
- Jupyter notebook(웹 기반 대화형 코딩 환경)
- MariaDB(오픈 소스 관계형 데이터베이스 관리 시스템)

 ("일반 파일에 저장된 데이터를 호출하는 방법"에 대해서는 아래의 링크를 확인)
2023.11.29 - [[파이썬]/데이터 분석] - [데이터 분석] 판다스를 활용한 데이터 수집

 

---

데이터 조회

 데이터 조회는 데이터 분석에 필요한 특정 정보를 찾아내는 과정이다. 수집 및 저장한 데이터를 호출하여, 필요한 정보를 추출하고, 데이터를 정렬, 필터링, 그룹화 등의 데이터 분석에 필요한 다양한 작업을 수행할 수 있다. 이러한 작업을 통해 데이터를 더욱 명확하게 이해할 수 있으며, 이를 바탕으로 더욱 정확한 분석 결과를 도출할 수 있다.

 

---

· 데이터베이스에 연결하기 위한 접속 정보 입력

 데이터베이스에 연결하기 위해서는 우선 'pymysql'라이브러리를 Import 해야 한다.
(pymysql  라이브러리 설치 : 아나콘다 실행 프롬프트 - 가상환경 접속 - pip install pymysql 명령어 입력)

import pymysql

 'pymysql' 라이브러리는 파이썬에서 MySQL기반의 데이터베이스(MariaDB)에 연결하고 작업을 수행하기 위한 라이브러리이다. 이 라이브러리를 사용하면 MySQL 데이터베이스에 직접 접근하여 데이터를 생성, 조회, 업데이트, 삭제(CRUD)하는 작업을 수행할 수 있다.

 이제 데이터베이스에 연결하기 위한 '접속 정보'를 입력해 주도록 하자.

# 접속 정보

host = "localhost"

user = "gjuser"

password = "dbdb"

db = "gjdb"

charset = "utf8"

cursorclass = pymysql.cursors.DictCursor

autocommit = True

 

• host : 데이터베이스 서버의 위치를 나타낸다. 접속 ID 또는 도메인을 입력한다.
  
  
• user : 데이터베이스에 접근할 때 사용하는 사용자 이름이다. 본인은 MairaDB의 'gjuser'라는 사용자로 접근할 것이다.
  
  
• password : 사용자의 비밀번호이다.
  
  
• db : 접근하려는 데이터베이스의 이름이다.
  
  
• charset : 데이터베이스와의 통신에 사용되는 문자 인코딩 방식이다. 'utf-8'은 유니코드 문자를 인코딩하는 방식 중 하나이다.
  
  
• cursorclass : 데이터베이스에서 데이터를 조회할 때 사용하는 커서의 종류를 나타냅니다. 'DictCursor'는 조회 결과를 딕셔너리 형태로 반환하는 커서이다.
  
  
• autocommit : 데이터베이스에 대한 변경 사항을 자동으로 커밋할지 여부를 설정한다. 이 값을 'True'로 설정하면, SQL 문이 실행된 직후에 변경 사항이 데이터베이스에 반영된다.

 

---

· 접속 정보를 기반으로 데이터베이스에 연결(connect)

  위에서 설정한 접속 정보를 바탕으로 'pymysql.connect()'함수를 호출하면, 입력한 정보를 토대로 데이터베이스에 연결할 수 있다.

# 데이터베이스에 연결(connect)하기

try :
	conn = pymysql.connect(
    	host=host,
        user=user,
        password=password,
        db=db,
        charset=charset,
        autocommit=autocommit,
        cursorclass=cursorclass)
    print("DB 접속 성공", conn)
    
except :
	print("DB 접속 실패")

 'try~except' 구문은 데이터베이스 연결 과정에서 발생할 수 있는 예외 처리를 하기 위해 사용하였다. try 블록 안의 코드는 'pymysql.connect()' 함수를 호출하여 데이터베이스에 연결을 시도하며, 만약 이 과정에서 어떤 문제가 발생하면 except 블록의 코드가 실행된다. 사용자의 편의와 더불어 프로그램의 안전성을 유지하기 위해 'try~except' 구문을 사용하였다.

 위 코드를 실행한 결과 "DB 접속 성공"이라는 문구가 출력되는 것을 볼 수 있다.

데이터베이스 연결

 

---

· SQL 쿼리 실행과 데이터 조회를 위한 커서 준비하기

 본인은 위에서 'conn'이라는 데이터베이스 연결 객체를 생성하였다. 이 객체로부터 커서를 받아오는 작업을 수행할 것이다. 커서(cursor)는 SQL 쿼리를 데이터베이스에 전달하고, 응답받은 결과 저장하는 객체이다.

# 커서 호출하기
cur = conn.cursor()

cur

 이 코드에서 'conn.cursor()'를 호출하면, 앞의 접속 정보를 생성할 때 설정한 'cursorclass'에 따라 생성된 커서 객체(Dictcursor)가 반환되며, 이를 'cur' 변수에 저장한다. 이후 변수 'cur'을 통해 SQL 쿼리를 데이터베이스에 전달하고, 그에 해당하는 결과를 딕셔너리 형태로 저장한다.

 따라서, 이 구문은 SQL 쿼리를 데이터베이스로 전달하고, 그 결과를 받아오는 과정을 나타낸다. 즉, 데이터 조회에 필요한 데이터를 받아오는 작업을 수행한다.

 

---

· SQL 쿼리 실행과 데이터베이스에게 요청 및 응답받기

 본인은 데이터베이스에 저장된 'time_power_demand' 테이블에서 필요한 데이터를 조회하고자 한다. 이를 위해 작성한 SQL 문을 변수 'sql'에 저장하였다. 'execute()' 함수는 작성한 SQL 문(sql)을 실행하는데 사용된다.
 따라서 'cur.execute(sql)' 구문를을 통해 작성한 SQL 문은 데이터베이스에 전달 및 실행되어, 실행 결과가 'cur'에 저장된다.
 
 'execute()' 함수의 주 기능은 SQL 쿼리를 실행하는 것이다. 이 함수에 SQL 쿼리를 문자열 형태로 전달하면, 그 쿼리가 데이터베이스로 전달되어 실행된다. Select 문과 같은 조회 쿼리를 실행했을 때에는 일반적으로 'None'을 반환하며, Insert, Update, Delete 등의 데이터 변경 쿼리를 실행했을 때는 영향받은 행의 수를 반환한다.
 하지만, 일부 데이터베이스 드라이버에서는 Select 문 실행 후에도 영향받은 행의 수를 반환할 수 있다. 이는 아래의 반환된 결과값을 통해 확인할 수 있다.

 따라서 execute() 함수로 Select 쿼리를 실행한 후에는, 영향받은 행의 수만을 응답받으므로, 실제 값에 대한 조회를 하기 위해서는 별도의 함수를 사용하여야 한다. 이에 대한 부분은 아래에 별도로 명시하였다.

sql = "Select * From time_power_demand where power >= 60000"

cur.execute(sql)

 이 SQL 문은 "time_power_demand 테이블에서 'power' 컬럼의 값이 6만 이상의인 모든 데이터를 조회해."라는 명령이다. 이 SQL 문은 따옴표로 묶인 문자열 형태로 작성되었으며, 이를 'cur.execute(sql)' 함수를 통해 데이터베이스에 전달한다. 이 구문은 데이터베이스에 전달한 SQL 문을 실행하도록 요청하고, 그 결과에 대해 반환된 응답을 커서'cur'에 저장한다. 반환된 값은 "조회된 데이터의 건수"를 의미한다.

데이터베이스에 요청 및 응답받은 결과

 

---

· 응답받은 결과로부터 실제 데이터 조회하기

 응답받은 결과는 행의 개수(5442)이다. 조회된 결과로부터의 실제 값을 조회하기 위해서는 'fetchall()' 또는 'fetchone()' 등의 함수를 사용하여 실제 데이터를 조회하여야 한다.

# 한 건에 대한 결과 조회
cur.fetchone()

# 여러 건에 대한 결과 조회
cur.fetchall()

 

• fetchall() : 이 메서드는 SQL 쿼리의 결과로 반환된 모든 행을 가져온다. 반환값은 튜플의 리스트 형태로, 각 튜플은 한 행의 데이터를 나타낸다. 만약 결과가 없다면, 빈 리스트를 반환한다. 단, 결과의 크기가 클 때 메모리를 많이 사용하므로 사용에 주의가 필요하다.
  
  
• fetchone() : 이 메서드는 SQL 쿼리의 결과로 반환된 행 중에서 하나만 가져온다. 반환값은 한 행의 데이터를 나타내는 튜플 형태이다. 만약 결과가 없다면 'None'을 반환한다. 

 하지만 본인은 접속 정보를 생성할 때, 'cursorclass = pymysql.cursors.DictCursor' 구문을 사용하여, 커서의 반환되는 값이 딕셔너리 형태임을 지정하였다. 그리하여 'fetch' 함수를 사용했을 때, 그 결과가 튜플이 아닌 딕셔너리 형태로 반환되는 것이다. 이렇게 하면 각 컬럼의 이름을 키로 사용해서 데이터를 조회할 수 있다는 장점이 있다.

 그렇다면 실제 데이터를 조회하여 보자.

fetchone(), fetchall() => 실제 데이터 조회

 'cur.execute(sql)' 명령어를 사용하여 SQL 문의 결과를 딕셔너리 형태로 커서에 저장하였다. 그리고 저장된 "응답받은 행의 수" 대해 실제 데이터를 조회하고자 'fetchone()', 'fetchall()' 함수를 사용하였다. 'fetchone()' 함수를 사용하여 커서에 저장된 행들 중 첫 번째 행을 조회하였고, 'fetchall()' 함수를 사용하여 'fetchone()'이 반환한 첫 번 째 행을 제외한 나머지의 모든 행을 조회하였다. 위는 각 함수별로 실제 출력된 값을 보여준다.

 

---

· 데이터베이스와의 연결 해제

 데이터베이스 연결은 시스템 리소스를 사용하므로, 사용이 끝난 후에는 반드시 연결을 하제하는 것이 좋다.
 데이터베이스 연결을 유지하는 데는 네트워크 자원과 메모리 등의 시스템 리소스가 사용되며, 이는 한정된 리소스이다.
 
 따라서 데이터베이스 작업이 끝난 후에는 반드시 'close()' 함수를 사용하여 데이터베이스 연결을 해제하도록 하자.

데이터베이스 연결 해체

 

---

SQL 쿼리는 데이터베이스에 저장된 데이터를 조회하는데 필요한 명령을 입력하는  전달하는 방법이다.

커서(cursor)는 이 SQL 문의 실행 결과를 저장하고 처리하는 데 사용된다.

'execute()' 함수를 사용하여 커서를 통해 데이터베이스에 전달된 SQL 문을 실행하고,
'fetchone()', 'fetchall()' 등의 함수를 사용하여 결과에 대한 실제 데이터를 출력한다.

' fetchone()'은 결과 중 첫 번째 행을,
' fetchall()'은 결과 중 첫 번째 행을 제외한 나머지 모든 행을 출력한다.

데이터베이스는 연결하는 동안 시스템 리소스를 사용하므로,
사용이 끝난 후에는 반드시 종료해주어야 한다.

이번 글에서는 "데이터베이스에 저장된 데이터 조회"를 위한 방법 및 절차에 대해 알아보았다.

다음 글에서는 데이터의 입력, 수정, 삭제에 대해 다뤄보도록 하겠다.
(해당 글은 아래의 링크 참조)
2023.12.02 - [[파이썬]/데이터 분석] - [데이터 분석] SQL 명령어를 이용한 데이터베이스 관리 : 입력, 수정, 삭제

[데이터 분석] SQL 명령어를 이용한 데이터베이스 관리 : 입력, 수정, 삭제