[sql] python에서 mysql 연결, CRUD, postman

Python에서 MySQL에 연결하고 데이터를 업로드하고 불러오는 방법.

Python의 mysql-connector-python 라이브러리를 사용하여 MySQL 데이터베이스와 상호 작용할 수 있다.

우선 연동하기위해 postman 프로그램이 필요하다

1.Postman

Postman은 API 개발 및 테스트를 위한 협력 도구입니다. 사용자는 Postman을 사용하여 HTTP 요청을 만들고, 보내고, 테스트할 수 있습니다. Postman의 기능은 다음과 같습니다.

다양한 HTTP 요청 지원: GET, POST, PUT, DELETE 등의 다양한 HTTP 요청을 생성하고 보낼 수 있습니다.
요청 파라미터 및 헤더 관리: 요청 URL에 쿼리 문자열 추가, 요청 헤더 수정 등의 작업을 할 수 있습니다.
응답 확인: 서버에서 받은 응답을 쉽게 확인할 수 있습니다. JSON, XML 등 다양한 형식을 지원하며, 응답 코드, 헤더, 본문 등을 확인할 수 있습니다.
환경 및 변수 사용: 환경 변수를 설정하고, 변수를 사용하여 요청을 보낼 수 있습니다. 이를 통해 반복적인 작업을 자동화하고, 테스트를 관리할 수 있습니다.
콜렉션 및 폴더 구성: 테스트를 구조화하고, 관련된 요청을 그룹으로 묶어 콜렉션으로 관리할 수 있습니다.
테스트 및 스크립트 작성: 테스트를 자동화하고, 스크립트를 작성하여 요청과 응답을 자동으로 처리할 수 있습니다.
모니터링 및 공유: 팀원과 테스트 결과를 공유하고, 모니터링하여 API의 상태를 확인할 수 있습니다.
자동화 및 테스트 수행: Postman의 Collection Runner를 사용하여 여러 요청을 자동으로 실행하고, 결과를 확인할 수 있습니다.

2.mysql

사용하고자 하는 db를 만들어 둔다.

recipe_db 라는 데이터베이스에 recipe 라는 컬럼을 만들었다.

3.python

메인 파일

from flask import Flask
from flask_restful import Api
from resources.recipe import RecipeListResource

app = Flask(__name__)

api = Api(app)
# 경로(path)와 리소스(api 코드)를 연결한다.
api.add_resource(RecipeListResource , '/recipes')


if __name__=='__main__':
    app.run()

위의 Flask 애플리케이션 예제는 Flask와 Flask-RESTful 라이브러리를 사용하여 RESTful API를 설정하는 간단한 방법을 보여준다.

Flask 애플리케이션 생성: Flask(__name__)으로 애플리케이션 인스턴스를 생성한다.
API 객체 생성: Api(app)으로 API 객체를 생성하여 애플리케이션과 연결한다.
리소스와 경로 연결: api.add_resource 메서드를 사용하여 리소스 클래스를 특정 경로와 연결한다.
애플리케이션 실행: app.run()으로 Flask 개발 서버를 실행한다.

이러한 구조를 통해 Flask 애플리케이션을 쉽게 설정하고, RESTful API를 정의하여 클라이언트의 요청을 처리할 수 있다.

1. MySQL 커넥터 설치

pip install mysql-connector-python

2. MySQL에 연결

MySQL 데이터베이스에 연결하기 위해 필요한 코드를 작성한다.

파일 2개를 만든다.

mysql_connection.py

import mysql.connector
from config import Config

# mysql db 에 접속하는 함수
def get_connection() : 
    connection = mysql.connector.connect(
        host = Config.HOST ,
        database = Config.DATABASE ,
        user = Config.DB_USER ,
        password = Config.DB_PASSWORD
        
    )

    return connection

config.py

class Config:
    HOST = aws RDS 엔드포인트
    DATABASE = sql db 이름'recipe_db'
    DB_USER = sql user 이름
    DB_PASSWORD = sql password

python과 연동하기위해 sql의 정보를 입력해야하는데, 보안을 위해파일 2개를 만들어 config.py 파일에 db의 정보를 클래스 형식으로 변수로 지정해서
mysql_connection 파일에 입력해준다. 이것으로 mysql 과 python 연동이 됐다.

데이터 만들기 CREATE

데이터를 관리하는 파일 recipe.py을 생성한다.

from flask import request
from flask_restful import Resource

from mysql_connection import get_connection
from mysql.connector import Error
class RecipeListResource(Resource) :

    def post(self) :

        
        # 1. 클라이언트가 보내준 데이터가 있으면 
        #    그 데이터를 받아준다.
        data = request.get_json()

        # 2. 이 정보를 DB에 저장한다.
        try :
            ### 1. DB에 연결
            connection = get_connection()
            ### 2. 쿼리문 만들기 
            query = '''insert into recipe 
                        (name, description, num_of_servings, cook_time, directions)
                        values
                        ( %s , %s, %s, %s, %s );'''

            ### 3. 쿼리에 매칭되는 변수 처리 => 튜플로!!
            record = (data['name'], data['description'], data['num_of_servings'], data['cook_time'], data['directions'])

            ### 4. 커서를 가져온다.
            cursor = connection.cursor()

            ### 5. 쿼리문을 커서로 실행한다.
            cursor.execute(query, record)

            ### 6. DB에 완전히 반영하기 위해서는 commit한다.
            connection.commit()

            ### 7. 자원 해제
            cursor.close()
            connection.close()
            
        except Error as e :
            if cursor is not None :
                cursor.close()
            if connection is not None :
                connection.close()
            return {'result' : 'fail', 'error' : str(e)}, 500
        
        return {'result' : 'success'}, 200

이 코드는 Flask RESTful을 사용하여 레시피를 생성하는 API 엔드포인트를 정의한다.

1. post 메서드: 이 메서드는 POST 요청을 처리한다. 클라이언트가 JSON 형식으로 데이터를 전송하면 해당 데이터를 받아서 데이터베이스에 저장한다.

data = request.get_json(): 클라이언트가 전송한 JSON 데이터를 받아온다.
connection = get_connection(): 데이터베이스에 연결하기 위해 get_connection() 함수를 호출한다.
cursor.execute(query, record): 받아온 데이터를 바탕으로 데이터베이스에 삽입할 쿼리를 실행한다.
connection.commit(): 쿼리를 실행한 후 변경사항을 데이터베이스에 반영한다.
cursor.close() 및 connection.close(): 데이터베이스 커넥션과 커서를 닫아서 자원을 해제한다.
except Error as e: 예외가 발생한 경우, 쿼리 실행 전에 열려 있던 커넥션과 커서를 닫고 에러를 클라이언트에 반환한다.
return {'result': 'success'}, 200: 성공적으로 데이터가 데이터베이스에 삽입되면 성공 메시지를 반환한다.

이러한 방식으로 클라이언트가 전송한 데이터를 받아서 데이터베이스에 저장하는 과정을 수행한다.

flask run 을 터미널에 입력해서 실행해준다.

postman 프로그램으로 돌아가서

좌측에 collections 를 추가한 후 중앙 위에 POST로 바꾼 후 터미널에 입력한 주소와 코드에 입력한 path 주소를 입력하고

Body에 들어가 딕셔너리 형태의 recipe 데이터를 입력 후 send 를 클릭하면 result:success가 뜨며

db에 데이터가 추가된것을 볼 수있다.

데이터 불러오기 READ

위에 recipe.py 파일 아래에 아래 코드를 입력한다.

def get(self) : 
        #1. 클라이언트가 보낸 데이터가 있으면 받아준다.
        offset = request.args['offset']
        limit = request.args['limit']

        print(offset,limit)
        # 2. db로부터 데이터를 가져온다.
        try :
            connection = get_connection()
            query = '''select *
                        from recipe
                        limit '''+offset+''','''+limit+''';'''
            cursor=connection.cursor(dictionary=True)
            cursor.execute(query)
            result_list = cursor.fetchall()
            print(result_list)
            cursor.close()
            connection.close()
        except Error as e:
            if cursor is not None:
                cursor.close()
            if connection is not None:
                connection.close()
            return {'result' : 'fale', 'error' : 'str'(e)}, 500
        # 3. 클라이언트에 json 만들어서 응답한다.
        i=0
        for row in result_list : 
            result_list[i]['created_at']= row['created_at'].isoformat()
            result_list[i]['updated_at']= row['updated_at'].isoformat()
            i=i+1
        print()
        print(result_list)
        return  {'items' : result_list,
                 'count' : len(result_list),
                 'result' : 'success'}

이 메서드는 클라이언트가 전달한 오프셋(offset)과 리미트(limit) 값을 받아서, 해당 범위에 맞는 데이터를 데이터베이스에서 가져와 클라이언트에 응답하는 역할을 한다.

클라이언트 데이터 수신: 클라이언트가 전달한 오프셋(offset)과 리미트(limit) 값을 받아온다. 이는 URL의 쿼리 파라미터로 전달된다.
데이터베이스에서 데이터 가져오기: 받아온 오프셋(offset)과 리미트(limit) 값을 이용하여 데이터베이스에서 해당 범위에 맞는 레시피 데이터를 조회한다. 쿼리를 실행한 후 결과를 리스트 형태로 가져온다.
데이터 가공: 가져온 결과를 순회하면서 각 레시피의 생성일자와 수정일자를 ISO 형식의 문자열로 변환한다.
응답 생성: 가공된 결과를 바탕으로 클라이언트에게 JSON 형식으로 응답한다. 응답에는 레시피 데이터 리스트(items), 데이터 개수(count), 성공 여부(result)가 포함된다.
예외 처리: 데이터베이스 조회 중 오류가 발생하면 적절한 오류 메시지와 함께 500 상태 코드를 반환한다.

이러한 과정을 통해 클라이언트가 요청한 오프셋(offset)과 리미트(limit)에 맞는 데이터를 데이터베이스에서 가져와서 응답한다.

POSTMAN

새로운 collentions을 추가 한 후 아까 주소를 입력하고 , params에 offset과 limit을 설정준 후 send 를 누르면

db에 있는 데이터가 모두 불러와진다. 이 과정에서 created_at 와 update_at는 datetime 타입으로 돼있어서

i=0
        for row in result_list : 
            result_list[i]['created_at']= row['created_at'].isoformat()
            result_list[i]['updated_at']= row['updated_at'].isoformat()
            i=i+1

이 코드를 입력하지 않으면 에러가 뜸으로 문자열로 변경 해야한다.

데이터 업로드 PUT

POSTMAN에서 request를 만든 후 기존 링크에서 업로드 하고싶은 데이터의 ID 번호를 써준다.

class RecipeResource(Resource):
    def get(self, recipe_id):
        #1. 클라잉너트로부터 데이터를 받는다.
        #2. DB로 부터 데이터를 가져온다.

        # 2. db로부터 데이터를 가져온다.
        try :
            connection = get_connection()
            query = '''select *
                        from recipe
                        where id =  %s;'''
            recode= (recipe_id, )
            cursor=connection.cursor(dictionary=True)
            cursor.execute(query,recode)
            result_list = cursor.fetchall()
            cursor.close()
            connection.close()
        except Error as e:
            if cursor is not None:
                cursor.close()
            if connection is not None:
                connection.close()
            return {'result' : 'fale', 'error' : str(e)}, 500
        # 3. 클라이언트에 json 만들어서 응답한다.
        i=0
        for row in result_list : 
            result_list[i]['created_at']= row['created_at'].isoformat()
            result_list[i]['updated_at']= row['updated_at'].isoformat()
            i=i+1
    
        if len(result_list) == 0:
            return {'result': 'fail', 
                    'error': '해당 아이디는 존재하지 않습니다.'},400
        else:
            return {'items': result_list[0], 
                    'result': 'success'}

클라이언트에게 받아야할 데이터가 늘어났음으로 함수를 새로 만들어준다.

get 메서드는 HTTP GET 요청을 처리한다. 이 메서드는 recipe_id라는 URL 경로 매개변수를 받아온다.
데이터베이스 연결을 설정한다. get_connection() 함수를 호출하여 데이터베이스에 연결한다.
SQL 쿼리를 작성한다. query 변수에 SELECT * FROM recipe WHERE id = %s; 쿼리를 저장하고, recipe_id를 쿼리의 매개변수로 사용한다.
커서를 생성한다. cursor = connection.cursor(dictionary=True)를 사용하여 커서를 생성하고, 쿼리를 실행한다. cursor.execute(query, record)에서 record는 (recipe_id,) 튜플이다.
쿼리 결과를 가져온다. result_list = cursor.fetchall()을 사용하여 쿼리 결과를 가져온다.
커서와 연결을 닫는다. cursor.close()와 connection.close()를 호출하여 자원을 해제한다.
예외 처리 블록을 사용한다. 데이터베이스 작업 중 예외가 발생하면, 커서와 연결을 닫고, 에러 메시지를 반환한다.
결과 리스트를 처리한다. result_list의 각 항목에서 created_at과 updated_at 날짜 필드를 ISO 형식으로 변환한다.
조건문을 사용하여 응답을 생성한다. result_list의 길이가 0이면, 실패 메시지를 반환한다. 그렇지 않으면, 성공 메시지와 함께 첫 번째 결과 항목을 반환한다.

app.py 파일로 넘어와서 만들어준 클래스를 api와 연결하기위해 코드를 추가해준다.

api.add_resource(RecipeResource , '/recipes/<int:recipe_id>')

를 추가해준다.

from flask import Flask
from flask_restful import Api
from resources.recipe import RecipeListResource, RecipeResource

app = Flask(__name__)

api = Api(app)
# 경로(path)와 리소스(api 코드)를 연결한다.
api.add_resource(RecipeListResource , '/recipes')
api.add_resource(RecipeResource , '/recipes/<int:recipe_id>')


if __name__=='__main__':
    app.run()

POSTMAN 에서 id 3 번 컬럼의 데이터를 보기와 같이 수정하면

slq 에서 바뀐걸 볼 수 있다.

데이터 삭제 DELETE

마찬가지로 delete request 를 만들어 준 후 주소와 삭제하고싶은 데이터 id 번호를 입력 해준다.

delete는 update와 마찬가지로 클라이언트 한테 받는 정보가 같음으로 똑같이 RecipeResource 클래스에서 진행한다.

 def delete(self, recipe_id) :
        try:
            connection = get_connection()
            query = '''
                    delete from recipe
                    where id = %s'''
            record = (recipe_id,)
            cursor = connection.cursor()
            cursor.execute(query, record)
            connection.commit()
            cursor.close()
            connection.close()
        except Error as e:
            if cursor is not None:
                cursor.close()
            if connection is not None:
                connection.close()
            return {'result':'fail',
                    'error' : str(e)}, 500
        return {'result' : 'success'}

코드를 입력후 POSTMAN 을 실행하면

sql의 2번 데이터가 삭제된 것을 볼 수 있다.

코딩일지