[Python] 데이터 베이스(mariaDB)를 연결하여 사용하는 방법

안녕하세요. 명월입니다.

이 글은 Python에서 데이터 베이스(mariaDB)를 연결하여 사용하는 방법에 대한 글입니다.

저의 경우는 Python을 로컬 스크립트 언어로 많이 활용합니다. 로컬 스크립트 언어로 활용한다는 건, 프로그램 개발하거나 운영등에 일회성일 수도 있고, 스케줄로 정해놓던가, batch를 만들어서 문제가 생기거나 업데이트시에 사용되는 스크립트 언어입니다.

컴파일 언어도 로컬 스크립트 언어로 사용할 수도 있으나 소스 수정이 힘들기(build가 필요하기 때문에) 때문에, 스크립트 언어가 더 편하지 않을까 싶네요. 그 외에 이유로는 node.js보다 설치도 편하기(라이브러리가 많음) 때문에 python을 선택했습니다.

그래서 프로그램을 개발하다 보면 데이터 베이스에 접속하는 경우가 많은데, 간단한 검색등은 sql browser등을 이용해서 검색하면 되지만, 데이터 검증이던가 여러 sql 스크립트 관리등을 필요로 할 때는 python을 사용합니다.

여기서 제가 테스트할 데이터 베이스는 mariaDB입니다.

데이터 베이스의 종류는 Oracle과 sql server(MsSql), postgre 등등 많이 있습니다만, 저의 경우는 mariaDB를 자주 사용합니다. 이유는 무료이기도 하고 가장 안정적이고 빠르다고 생각돼서 입니다.

mariaDB는 구 mysql과 같기 때문에 아마 거의 사용 방법이 비슷할 것입니다.

먼저 python에서 mysql를 접속하기 위해서는 pip로 모듈을 설치해야 합니다.

pip install mysql-connector

모듈이 설치가 완료가 되었으면 python에서 mariaDB를 접속해 보곘습니다.

# mysql connector를 사용하기 위한 모듈 선언
import mysql.connector;

# mysql connection을 선언한다. 파라미터는 host는 접속 주소, user는 ID, passwd는 패스워드, database는 접속할 데이터 베이스이다.
dbconn = mysql.connector.connect(host="localhost", user="test", passwd="password", database="blogtest");

# 검색을 할 경우 사용되는 함수.
def select(query, bufferd=True):
  # 전역에 선언되어 있는 connection을 가져온다.
  global dbconn;
  # 커서를 취득한다. (bufferd는 내부의 검색 버퍼를 사용하는데(connection 리소스를 아끼기 위한 값)
  # 검색되어 있는 값은 메모리에 두고 재 요청이 올 경우, 디비에 검색을 하지 않고 메모리의 값이 리턴 됨, 특히 대용량 페이징을 사용할 때 사용하면 좋음.
  cursor = dbconn.cursor(buffered=bufferd);
  # 쿼리를 실행한다.
  cursor.execute(query);
  # 검색 결과를 확인하기 위해서는 커서를 리턴해야 한다.
  # cursor.fetchall()로 결과를 리스트로 내보낼 수도 있다. 
  # 그러나 결과가 대용량일 경우 fetchall로 대량의 값을 메모리에 넣으면 느려질 수 있다.
  return cursor;

# DML(Data Manipulation Language)의 insert, update, delete를 처리하는 함수
def merge(query, values, bufferd=True):
  # 전역에 선언되어 있는 connection을 가져온다.
  global dbconn;
  try:
    # 커서를 취득한다. 
    cursor = dbconn.cursor(buffered=bufferd);
    # 쿼리를 실행한다. values는 query 값에 있는 sql query식의 바인딩 값이다.
    # 문자열 포멧팅으로 설정된다. values는 튜플 값으로 입력된다.
    cursor.execute(query, values);
    # 쿼리를 커밋한다.
    dbconn.commit();
  except Exception as e:
    # 에러가 발생하면 쿼리를 롤백한다.
    dbconn.rollback();
    raise e;

# DML(Data Manipulation Language)의 insert, update, delete를 대랑 처리하는 함수
def merge_bulk(query, values, bufferd=True):
  # 전역에 선언되어 있는 connection을 가져온다.
  global dbconn;
  try:
    # 커서를 취득한다. 
    cursor = dbconn.cursor(buffered=bufferd);
    # 쿼리를 실행한다. values는 query 값에 있는 sql query식의 바인딩 값이다.
    # 문자열 포멧팅으로 설정된다. values는 리스트 튜플 값으로 입력된다.
    cursor.executemany(query, values);
    # 쿼리를 커밋한다.
    dbconn.commit();
  except Exception as e:
    # 에러가 발생하면 쿼리를 롤백한다.
    dbconn.rollback();
    raise e;

# DML이외의 쿼리를 실행하는 함수.
def execute(query, bufferd=True):
  # 전역에 선언되어 있는 connection을 가져온다.
  global dbconn;
  try:
    # 커서를 취득한다. 
    cursor = dbconn.cursor(buffered=bufferd);
    # 쿼리를 실행한다.
    cursor.execute(query);
    # 쿼리를 커밋한다.
    dbconn.commit();
  except Exception as e:
    # 에러가 발생하면 쿼리를 롤백한다.
    dbconn.rollback();
    raise e;

try:
  # 테이블 PythonTable를 삭제한다.(이전 실행 중 에러가 발생하면 테이블을 지우고 시작한다. 다음 CREATE에서 에러난다.)
  #execute("DROP TABLE PythonTable");
  # 테이블 PythonTable를 생성한다.
  execute("""
CREATE TABLE PythonTable (
  idx int auto_increment primary key,
  data1 varchar(255),
  data2 varchar(255)
)
""");
  # 테이블 값을 대량 insert하기 위한 리스트 튜플 값
  values = [('data1', 'test1'),
            ('data2', 'test2'),
            ('data3', 'test3'),
            ('data4', 'test4'),
            ('data5', 'test5')];
  # 데이터를 대량 입력한다.
  merge_bulk("INSERT INTO PythonTable (data1, data2) VALUES (%s, %s)", values);
  # PythonTable를 출력한다.
  for row in select("SELECT * FROM PythonTable"):
    print(row);
  # data1의 데이터 값을 update1로 수정한다.
  merge("UPDATE PythonTable set data2=%s where data1=%s", ('update1','data1'));
  # PythonTable를 출력한다.
  for row in select("SELECT * FROM PythonTable"):
    print(row);
  # 테이블 PythonTable를 삭제한다.
  execute("DROP TABLE PythonTable");
except Exception as e:
  print(e);
finally:
  # connection을 다 사용하면 반드시 connection 리소스를 닫는다.
  dbconn.close();

위 예제는 제가 테이블 추가부터 데이터 입력, 수정, 삭제 그리고, 테이블 삭제까지 처리하는 식을 일렬로 작성하였습니다.

그냥 소스 복사해서 사용해도 될 듯싶네요.

먼저 dbconn이라는 mysql 커넥션을 받았습니다. python은 스크립트가 종료되면 자동으로 리소스를 반환하기는 하지만, 명확하게 소스 말미에는 close로 리소스를 닫고 반환하는 처리식 작성합니다.

그리고 execute를 실행하면 for문으로 데이터 셋을 받을 수 있습니다. execute로 쿼리를 수행할 수도 있고 두개의 파라미터로 쿼리 바인딩도 가능합니다.

executemany함수의 경우는 대량 처리도 가능합니다. fetchall함수를 사용하면 하나의 fetch 데이터를 가져오는 것이 아니고 전체를 가져오게도 할 수 있습니다. 그럼 컴프리헨션을 통해서 데이터 정리가 가능하겠네요.

그러나 대용량의 경우는 메모리 압박이 생길 수 있으니 사양에 따라 만들면 됩니다.

참조 - https://dev.mysql.com/doc/connector-python/en/connector-python-tutorial-cursorbuffered.html

참조 - https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursorbuffered.html

여기까지 Python에서 데이터 베이스(mariaDB)를 연결하여 사용하는 방법에 대한 설명이었습니다.

궁금한 점이나 잘못된 점이 있으면 댓글 부탁드립니다.