목차
개요
프로젝트를 진행하다가 제작하는 프로젝트에서 사용자에게 맞춤형 정보를 전해주기 위해 특정 사이트에서 일정 시간을 간격으로 공지사항을 크롤링하여 유효한 키워드를 찾아내 제공해주어야하는 api를 제작해야했다.
DB에 있는 자료들에서 SQL문을 사용해 필요한 정보만을 얻어내는것도 구현해야할 API이지만 DB를 실시간으로 업데이트 해야하는 API도 제작했어야했다.
최대한 DOM 기반 크롤러를 만드려 해도 사이트 구조는 계속 바뀌고 아직 프로젝트에서 어떠한 정보를 가져올지에 대한 내용이 픽스되지 않았기에 클라이언트에게 웹사이트 관련한 정보를 제공해주는 SpringBoot 어플리케이션과 나름의 MSA를 만들어보고 싶어서 Python 기반 DB관리 어플리케이션을 따로 만들기로 하였다.
외부에서 크롤링을 해오건 수강편람 등이 저장되어있는 엑셀 파일에서 데이터를 불러와서 DB에 삽입하건 MySQL이 탑재되어있는 Amazon RDS 에는 입력해야하기 때문에 이를 파이썬 환경에서 간단한 클래스 구현 등을 통해 연결해보고 기본적으로 삽입삭제조회에 대해서 알아보자
PymySQL이란?
pymysql은 Python과 MySQL을 연결해주는 라이브러리로, Python에서 MySQL을 다루기 위한 기능을 제공한다.
설치
이 글을 읽는 사람이라면 pip 등에 대한 이해는 있을것으로 생각된다.
pymysql은 외부 라이브러리이기 때문에 사용하기 전에 설치가 필요하다. 설치는 아래와 같은 명령어로 간단히 진행할 수 있다
pip install pymysql
pymysql을 이용한 MySQL 연결
pymysql을 이용해 Python에서 MySQL에 접속하려면, 먼저 연결 정보를 지정해야 한다.
호스트, 포트, 사용자 이름, 비밀번호, 데이터베이스 이름 등의 정보를 입력하면, 이를 통해 연결할 수 있다.
#RDSmysql
import pymysql
import pymysql.cursors
#RDS imformation
//해당 위치에 중요한 정보등을 넣어놓고 igitignore을 이용해 git에 업로드를 방지하자
from AmazonRDSManage.dbinfo import dbsecurity
//RDS와 연결하는 함수
def connectRDS():
try:
# RDS information
ihost = dbsecurity.host
iusername = dbsecurity.username
ipassword = dbsecurity.password
idatabase = dbsecurity.database
connection = pymysql.connect(host=str(ihost),
user=str(iusername),
password=str(ipassword),
database=str(idatabase),
use_unicode=True,
cursorclass=pymysql.cursors.DictCursor)
// 연결이 되지 않았을때
except:
print("RDS connection Error")
exit(1)
// connection 을 반납한다.
return connection
사실 본 예제에서는 RDS를 예로 들었지만 dbsecurity(개인 정보 입력란) 의 host에 본인이 mySQL을 실행시킨 엔드 포인트를 입력해놓으면 된다.
dbinfo.py에서
def dbsecurity():
host = "your_host_url"
username = "your_username"
password = "your_password"
database = "your_database_name"
연결 정보를 입력한 후에는 pymysql.connect() 함수를 사용하여 MySQL에 접속한다.
연결이 성공적으로 이루어진 경우, 이제 커서(cursor) 객체를 생성해야 한다. 커서 객체는 쿼리를 실행하고 그 결과를 받아오는 역할을 수행한다. 커서는 여러 가지 형태로 생성할 수 있는데, 일반적으로 사용하는 딕셔너리 커서를 추천한다.
커서 생성은 아래에서 보여주겠다.
쿼리 실행과 결과 가져오기 그리고 실행 종료
커서 객체를 생성한 후에는 쿼리를 실행할 수 있다.
쿼리는 SQL 문을 문자열로 작성하고, cursor.execute() 메서드를 사용하여 실행한다.
def 함수 이름():
//위에서 만들었던 connectRDS의 return값 connection을 받아온다.
connection = connectRDS()
db_name = "데이터 베이스 이름"
# start, runtime, building, room
try:
//connection.cursor을 cursor으로 받아와서
with connection.cursor() as cursor:
# Create a new record
//SQL 구문을 이용해 삽입을 실행한다.
dbNearRoom = f"INSERT INTO {db_name} (`building_num`,`room_num`,`start_time`, `run_time`) VALUES (%s, %s, %s, %s)"
//아래 구문의 경우에는 db안에 들어있던 데이터가 다중행으로 이루어져있어서 index값만큼 반복하게 한것이다. 원하는대로 변형하자
for i in index값:
cursor.execute(dbNearRoom, (readData['building'][i], readData['room'][i],readData['start'][i], readData['runtime'][i]))
//가장 중요한건 모든 변경 사항이 끝났다면 commit을 통해 MySQL에 업데이트를 진행해주자
connection.commit()
// 선택사항인데 해당 db가 잘 들어갔는지를 알려준다. 일종의 로그체크
print(f" >>{db_name}<< Auto Build COMPLETE")
finally:
//끝났다면 close를 통해서 연결을 종료한다.
connection.close()
쿼리 실행 후에는 cursor.fetchall() 또는 cursor.fetchone() 메서드를 사용하여 결과를 가져올 수 있다.
데이터베이스 작업이 모두 완료되었다면, 커서를 닫고 연결을 종료해야 한다.
이는 cursor.close()와 connection.close() 메서드를 차례대로 호출하여 진행한다.
마무리
이렇게 pymysql을 사용하여 Python에서 MySQL과 쉽게 연결할 수 있다.
데이터베이스 작업을 필요로 하는 프로젝트에서는 pymysql을 활용해 높은 효율성과 편리함을 얻을 수 있다.
하지만 한계는 존재하는데 장점이자 단점이 바로 간단하다는 것이다. 따라서 규모가 큰 프로젝트나 DB가 바뀔수 있는 프로젝트에서는 ORM을 이용하는 것이 좋은데
SqlAlchemy라고 마치 자바의 JPA같은 입지를 가지고 있는 파이썬 ORM 라이브러리이다.
나또한 Pymysql을 통해 프로젝트를 작성하고 있었고 큰 문제는 없지만 DB를 구성할때 기능상으로는 두드러지는 성능개선 효과는 없지만 정규화를 전부 해가면서 만드는 경험도 가지고 싶었기에 대안을 찾아보다가 찾게된 방식이다. SqlAlchemy는 본질적으로 ORM이지만 기본적으로 MySQL과의 연결기능도 지원하고 있기에 제작하던 프로젝트의 방향을 돌리려한다.
