Flask 영화의 리스트를 가져오되, 내가 좋아요 한 영화면 좋아요표시

order = cnt, avg ( 갯수정렬, 평균정렬)

offset, limit = 페이징처리 

테스트를 위해 user_id = 1004 jwt토큰 로그인

 

 

 

 

mysql workbench 조인할 테이블의 상태

 

user_movie = 두개를 묶는 조합을 유니크하게 처리

 

 

 

사용한 sql문 (정리안됨 주의)

 

SELECT * FROM movie_db.rating;

insert into rating
(user_id,movie_id,rating)
values
( %s, %s, %s);

select * from rating
where user_id = 1004;

delete from rating
where movie_id = 1;

select rating.id, rating.movie_id, rating.rating, movie.title
from rating
inner join movie
on rating.movie_id = movie.id
where rating.user_id = 1004 
limit 0,2;

select rating.id, rating.movie_id, rating.rating, movie.title
from rating
inner join movie
on rating.movie_id = movie.id ;

select m.id,
		m.title, 
		ifnull(count(r.movie_id), 0) as cnt,
		ifnull(avg(r.rating), 0) as avg
from movie m
left join rating r 
on r.movie_id = m.id
group by m.id 
order by cnt desc;

select m.title, count(r.movie_id) as cnt , ifnull(avg(r.rating),0 ) as avg
from movie m
left join rating r
on m.id = r.movie_id
group by m.id having title like "%he%";


select m.title, r.user_id , r.rating
from movie m
left join rating r
on m.id = r.movie_id;

select * from movie;

select * from favorite; 

insert into favorite(user_id, movie_id)
values(1004, 1);

delete from favorite
where user_id = 1004 and movie_id =1;                

select f.id, f.movie_id, m.title, m.genre, ifnull(count(r.movie_id), 0 )as cnt , ifnull(avg(r.rating),0 ) as avg
from favorite f
join movie m
on f.movie_id = m.id
left join rating r
on m.id = r.movie_id
where f.user_id = 1004
group by f.movie_id
limit 0, 25 ; 

select * from favorite; 

insert into favorite(user_id, movie_id,is_favorite)
values(1004, 50, 1);

delete from favorite
where user_id = 1004 and movie_id =50;                

select m.id,
		m.title, 
		ifnull(count(r.movie_id), 0) as cnt,
		ifnull(avg(r.rating), 0) as avg , if(f.user_id is null, 0, 1) as is_favorite
from movie m
left join rating r 
on r.movie_id = m.id
left join favorite f
on m.id = f.movie_id and f.user_id = 1004 
group by m.id 
order by cnt desc
limit 0,25;

 

아래는 VScode

 

from flask import request # 클라이언트가 보낸 데이터를 받기 위한 라이브러리
from flask_restful import Resource # API를 만들기 위한 라이브러리
from mysql.connector import Error # DB에 연결할 때, 에러가 발생할 수 있으므로, 에러처리를 위한 라이브러리
from flask_jwt_extended import jwt_required, get_jwt_identity # JWT를 사용하기 위한 라이브러리

from mysql_connection import get_connection # DB에 연결하기 위한 함수

# 영화 정보 가져오는 API 
# 로그인한 유저 or 비로그인유저 둘다 만들어본다(Optional)
class MovieResource(Resource) : 

    @jwt_required(optional=True) # optional=True 토큰이 없어도 접근 가능 
    def get(self) : # 영화 정보를 가져오는 API
        
        user_id = get_jwt_identity()
        order = request.args.get('order')
        offset = request.args.get('offset')
        limit = request.args.get('limit')

        # if user_id is None : 
        #     print("비로그인 유저")
        # else: 
        #     print("로그인 유저")

        try :
            connection = get_connection()

            if user_id is None :
                query = '''
                    select m.id,
                            m.title, 
                            ifnull(count(r.movie_id), 0) as cnt,
                            ifnull(avg(r.rating), 0) as avg 
                    from movie m
                    left join rating r 
                    on r.movie_id = m.id
                    group by m.id 
                    order by '''+ order +''' desc
                    limit ''' + offset + ''', ''' + limit + ''';
                    '''

                cursor = connection.cursor(dictionary=True)
                cursor.execute(query)
            
            else:
                query = '''select m.id,
                            m.title, 
                            ifnull(count(r.movie_id), 0) as cnt,
                            ifnull(avg(r.rating), 0) as avg , if(f.user_id is null, 0, 1) as is_favorite
                            from movie m
                            left join rating r 
                            on r.movie_id = m.id
                            left join favorite f
                            on m.id = f.movie_id and f.user_id = %s 
                            group by m.id 
                            order by cnt desc
                            limit '''+offset+''','''+limit+''';'''
                record = (user_id, )
                cursor = connection.cursor(dictionary=True)
                cursor.execute(query,record)
            
            result_list = cursor.fetchall()
           
            i = 0
            for row in result_list :
                result_list[i]['avg'] = round(float(row['avg']), 1)
                i += 1

            cursor.close()
            connection.close()

        except Error as e :
            print(e)
            cursor.close()
            connection.close()

            return {"result": "fail", "error" : str(e)}, 500
        
        return {"result": "success", "items" : result_list, "count" : len(result_list)}, 200

# 영화 검색해서 가져오기
class MovieSearchResource(Resource) : 

    def get(self) : # 영화 정보를 가져오는 API
            
        keyword = request.args.get('keyword')
        order = request.args.get('order')
        offset = request.args.get('offset')
        limit = request.args.get('limit')

        try :
            connection = get_connection()
            
            # 2가지 정렬조건을 만들어서, 사용자가 선택한 정렬조건에 따라서 쿼리를 실행하도록 한다.
            query = '''
                select m.id,
                        m.title, 
                        ifnull(count(r.movie_id), 0) as cnt,
                        ifnull(avg(r.rating), 0) as avg 
                from movie m
                left join rating r 
                on r.movie_id = m.id
                group by m.id having title like "%''' + keyword + '''%"
                order by '''+ order +''' desc
                limit ''' + offset + ''', ''' + limit + ''';
                '''
                # mysql 워크벤치 테이블 인덱스에 title 컬럼 fulltext 추가해야함
                # 속도를 위해서 
            cursor = connection.cursor(dictionary=True)

            cursor.execute(query)

            result_list = cursor.fetchall()

            i = 0
            for row in result_list :
                result_list[i]['avg'] = round(float(row['avg']), 1)
                i += 1

            cursor.close()
            connection.close()


        except Error as e :
            print(e)
            cursor.close()
            connection.close()
            return {"result": "fail", "error" : str(e)}, 500

        return {"result": "success", "items" : result_list, "count" : len(result_list)}, 200

이로써 비로그인유저와 로그인유저 둘다 사용가능하면서.

로그인유저라면 좋아요표시도 해주는 API를 만들었다.