@denislysenko

Как исправить SQL скрипт?

Здравствуйте. В бд есть две таблицы: movies(на 10 000 строк) и rating(на 100 000 строк)

таблица movies имеет следующую структуру:

movie_id, title, year, genres
'1', 'Toy Story ', '1995', 'Adventure|Animation|Children|Comedy|Fantasy'
'2', 'Jumanji ', '1995', 'Adventure|Children|Fantasy'
'3', 'Grumpier Old Men ', '1995', 'Comedy|Romance'
'4', 'Waiting to Exhale ', '1995', 'Comedy|Drama|Romance'
...

То есть, в таблице movies есть movie_id, который встречается только один посколько принадлежит к конкретному фильму, название фильма, год выпуска этого фильма, и жанры этого фильма

а таблица rating имеет следующую структуру:

id, movie_id, rating
'1', '1', '4.00'
'2', '3', '4.00'
'3', '6', '4.00'
'5', '50', '5.00'
'6', '3', '5.00'
...
вот sql запросы на создание этих таблиц:
CREATE TABLE movies (
movie_id        INT,
title           VARCHAR(256),
year            INT,
genres          TEXT,
PRIMARY KEY     (movie_id)
);

CREATE TABLE rating (
id              INT                 AUTO_INCREMENT,
movie_id        INT(20)             NOT NULL,
rating          DECIMAL(3, 2),
PRIMARY KEY     (id)
);


То есть, в таблице rating, собраны оценки(отзывы) о этих фильмах, id это индентификатор отзыва и он уникальный, movie_id это id фильма которому ставили оценку(рейтинг), rating это оценка, которую поставили этому фильму. У каждого фильма есть несколько оценок.

Мне нужно выводить в консоль отсортированные фильмы исходя из аргументов командной строки
в формате:
Genre, title, year, svg(rating)

вот мой код
import argparse
from config import host, user, passwd, db_name, default_genres
import pymysql
from pymysql.constants import CLIENT


def argpars():
    parser = argparse.ArgumentParser()
    parser.add_argument('-N',
                        type=int,
                        help='the number of the highest rated films for each genre',
                        default=1000
                        )
    parser.add_argument('-genres',
                        type=str,
                        help='filter by genre',
                        default ='Action|Adventure|Animation|Children|Comedy|Crime|Documentary|Drama|Fantasy|Film-Noir|Horror|IMAX|Musical|Mystery|Romance|Sci-Fi|Thriller|War|Western'
                        )
    parser.add_argument('-year_from',
                        type=int,
                        help='filter by year (FROM YEAR)',
                        default=1800
                        )
    parser.add_argument('-year_to',
                        type=int,
                        help='filter by year (TO YEAR)',
                        default=2025
                        )
    parser.add_argument('-regexp',
                        type=str,
                        help='filter on the movie name',
                        default=''
                        )
    return parser.parse_args()


def get_connection_to_db(host, user, passwd, db_name):
    connection = None
    try:
        connection = pymysql.connect(
            host = host,
            user = user,
            passwd = passwd,
            database = db_name,
            client_flag = CLIENT.MULTI_STATEMENTS
        )
        print('Connection to MySQl_db successful')
    except Error as e:
        print(f"The error '{e}' occurred")

    return connection


def print_result(connection, sql_script_path, args):
        genres = args.genres.split("|")
        for gener in genres:
            with connection.cursor() as cursor:
                with open(sql_script_path) as file:
                    script = file.read()
                    cursor.execute(script.format(year_from=args.year_from,
                                                 year_to=args.year_to,
                                                 name=connection.escape(args.regexp),
                                                 gener=connection.escape(gener),
                                                 rating=1,
                                                 N=args.N))
                    rows = cursor.fetchall()
                    for row in rows:
                        print('{};{};{};{}'.format(gener, row[0], row[1], row[2]))



def main():
    args = argpars()
    connection = get_connection_to_db(host, user, passwd, db_name)
    print_result(connection, './files/sql/GET_RESULT_MOVIES.sql', args)



main()


это мой sql запрос, где в нем ошибка? То есть, мне нужно чтобы этот sql запрос возвращал title фильма, год фильма, и средний рейтинг к этому фильму

SELECT m.title, year, avg(r.rating)
FROM movies AS m
JOIN rating AS r
ON m.movie_id = r.movie_id
WHERE m.year BETWEEN {year_from} and {year_to} AND m.title LIKE "%{name}%" AND m.genres LIKE "%{gener}%"
GROUP BY  r.movie_id, m.title, m.year
HAVING avg(r.rating) > {rating}
ORDER BY avg(r.rating) DESC, m.year DESC, m.title asc
LIMIT {N};


при запуске программы в консоль ничего не выводит
  • Вопрос задан
  • 173 просмотра
Решения вопроса 1
@galaxy
Есть мысль, что кавычки мешают. Засчет вот этого gener=connection.escape(gener) вы получаете в запросе LIKE "%'blabla'%".

connection.escape вообще к использованию не рекомедуется:
def escape(self, obj, mapping=None):
        """Escape whatever value is passed.
        Non-standard, for internal use; do not use this in your applications.
        """


Формируйте строку в питоне. Только если вы не хотите, чтобы пользователи могли использовать специ-символы LIKE в строке поиска, их нужно заэскейпить:
cursor.execute(script.format(year_from=args.year_from,
             year_to=args.year_to,
             name="%{}%".format( (args.regexp.translate(str.maketrans({'%': '\%', '_': '\_'})),) ),
             gener="%{}%".format( (gener.translate(str.maketrans({'%': '\%', '_': '\_'})),) ),
             rating=1,
             N=args.N))


SELECT m.title, year, avg(r.rating)
FROM movies AS m
JOIN rating AS r
ON m.movie_id = r.movie_id
WHERE m.year BETWEEN {year_from} and {year_to} AND m.title LIKE {name} AND m.genres LIKE {gener}
GROUP BY  r.movie_id, m.title, m.year
HAVING avg(r.rating) > {rating}
ORDER BY avg(r.rating) DESC, m.year DESC, m.title asc
LIMIT {N};


как-то так
Ответ написан
Комментировать
Пригласить эксперта
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы