И. В. Щуров, НИУ ВШЭ
Авторы задач в подборке: Ф. Тюрин, И. Щуров.
На странице курса находятся дополнительные материалы.
Для получения полного балла нужно решить все задачи (набрать 13 баллов).
import sqlite3
Мы будем работать с данными о приложениях из Google Play Store. У нас будут два датасета: apps
с техническими данными о приложениях, и reviews
с отзывами пользователей. Описание можно посмотреть по ссылке. Для начала загрузим базу на диск (это надо будет сделать только один раз).
import requests
import os
from hashlib import md5
class MyError(Exception):
pass
def get_file_md5(filename):
### FROM: https://stackoverflow.com/a/3431838/3025981
hash_md5 = md5()
with open(filename, "rb") as f:
for chunk in iter(lambda: f.read(1024), b""):
hash_md5.update(chunk)
return hash_md5.hexdigest()
### END FROM
try:
if get_file_md5("apps.sqlite") != "5eddd7a74c85226de623f209a5c5d4e3":
raise MyError
except (MyError, FileNotFoundError):
try:
r = requests.get("https://github.com/ischurov/pythonhse/raw/master/apps.sqlite")
with open("apps.sqlite", "wb") as f:
f.write(r.content)
assert get_file_md5("apps.sqlite") == "5eddd7a74c85226de623f209a5c5d4e3", \
"База данных повреждена — обратитесь к преподавателю"
except requests.ConnectionError:
print("Что-то со связью — проверьте, что интернет работает, и запустите ячейку заново")
Чтобы заработали тесты, нам понадобится также такая функция. (Если вы получите сообщение об ошибке о том, что myhash
не определено — запустите эту ячейку ещё раз.)
from hashlib import md5
def myhash(x):
return int(md5(str(x).encode('utf-8')).hexdigest()[:6], 16)
Теперь подключимся к базе
conn = sqlite3.connect("apps.sqlite")
c = conn.cursor()
Начнём работать с таблицей apps
. Вот такие столбцы в ней есть:
c.execute("""
PRAGMA table_info(apps);
""").fetchall()
С помощью подходящего SQL-запроса запишите в список genres
все жанры, встречающиеся в базе, упорядоченные по алфавиту. Каждый жанр должен упоминаться только один раз. Запрещено использовать set
и sort
.
После выполнения ячейки с решением genres
должен выглядеть примерно так:
['Action',
'Action;Action & Adventure',
'Adventure',
'Adventure;Action & Adventure',
'Adventure;Brain Games',
'Adventure;Education',
'Arcade',
...]
Подсказка. Чтобы получить все различные значения, нужно использовать команду DISTINCT
после SELECT
. Ещё вам нужен ORDER BY
. Помните о том, что c.execute(...).fetchall()
возвращает список кортежей.
"# YOUR CODE HERE"
assert myhash(genres) == 10234285
assert len(genres) == 120
Напишите функцию get_good_free_apps(rating)
, которая принимает на вход число с плавающей точкой rating
, выбирает колонки App
и Category
из датасета apps
и возвращает только те приложения и их категории, которые можно скачать бесплатно и рейтинг которых не меньше rating
. Функция должна возвращать список кортежей типа такого:
[('Hojiboy Tojiboyev Life Hacks', 'COMICS'),
('American Girls Mobile Numbers', 'DATING'),
('Awake Dating', 'DATING'),
...]
Примечание Для подстановки значения переменной в запрос используйте второй аргумент у функции c.execute
, как рекомендовано документацией. Обратите внимание: второй аргумент должен быть списком или кортежем.
"# YOUR CODE HERE"
assert sorted(get_good_free_apps(5))[:5] == [('420 BZ Budeze Delivery', 'MEDICAL'),
('420 BZ Budeze Delivery', 'MEDICAL'),
('A-Y Collection', 'SHOPPING'),
('AI Today : Artificial Intelligence News & AI 101', 'NEWS_AND_MAGAZINES'),
('AJ Cam', 'PHOTOGRAPHY')]
assert (sum(myhash(x) for x in get_good_free_apps(4.9))) == 2675354175
assert (sum(myhash(x) for x in get_good_free_apps(5))) == 2003112710
assert set(get_good_free_apps(4.7)).issubset(set(get_good_free_apps(4.6)))
Сделайте запрос, который для каждой категории приложений из датасета apps
посчитает средний рейтинг, минимальный рейтинг и максимальный рейтинг, после чего отсортирует категории по среднему рейтингу в порядке убывания и оставит только те категории, для которых минимальный рейтинг больше 1. Результат запроса (список, состоящий из четырёхэлементных кортежей: категория и три числа) поместите в переменную catstat
. Все вычисления должны производиться средствами SQL: вам нужно написать только текст запроса.
Подсказка. В числе прочего, вам понадобится HAVING
. Посмотрите в документации, чем он отличается от WHERE
.
catstat = c.execute(
"# YOUR CODE HERE"
).fetchall()
assert myhash(catstat) == 6699152
Одна из категорий выглядит очень странно — похоже на ошибку в базе. Удалите все записи с этой категорией. (Не записывайте результат в базу, то есть не делайте conn.commit()
, чтобы задачи выше продолжили работать. Если вы сделали запрос, модифицирующий базу, но ещё не сделали conn.commit()
, вы можете вернуть её в исходное состояние с помощью conn.rollback()
.)
Подсказка. Чтобы удалить строчки из таблицы нужно использовать команду DELETE
.
"# YOUR CODE HERE"
assert c.execute("""
SELECT * FROM apps WHERE [index] = 10472
""").fetchall() == []
Сделайте запрос, который находит 50 самых дорогих приложений, сортирует их по цене и помещает в список их название и цену. Результат должен быть сохранён в список expensive_apps
.
Этот список должен выглядеть примерно так:
[("I'm Rich - Trump Edition", 400.0),
('most expensive app (H)', 399.99),
("💎 I'm rich", 399.99),
('I am rich', 399.99),
...]
Подсказка. Цена в базе записана в виде строк, начинающихся с символа $
. Вам необходимо отрезать этот символ и переконверировать результат в вещественное число. Это можно и нужно сделать средствами SQL: вам понадобятся функции SUBSTR
и CAST
. Как обычно, нельзя пользоваться ничем, кроме SQL, то есть редактировать можно только текст запроса.
expensive_apps = c.execute(
"# YOUR CODE HERE"
).fetchall()
assert myhash(expensive_apps) in [13992335, 5391293]
Перейдем ко второму датасету reviews
.
c.execute("""
PRAGMA table_info(reviews);
""").fetchall()
Удалите из датасета reviews
все строчки, у которых Sentiment
является пропущенным значением (NULL
). (Не записывайте результат в базу, то есть не делайте conn.commit()
. Если вы сделали запрос, модифицирующий базу, но ещё не сделали conn.commit()
, вы можете вернуть её в исходное состояние с помощью conn.rollback()
)
Подсказка. Для проверки того, что что-то является NULL
, нужно использовать сравнение IS
, а не =
.
"# YOUR CODE HERE"
assert c.execute("""
SELECT count(*) FROM reviews
""").fetchone()[0] == 37432
Для каждой категории найдите количество отрицательных отзывов, оставленных ко всем приложениям, находящимся в этой категории. Отсортируйте категории по убыванию количества отрицательных отзывов. Поместите результат в список cat_neg_rev
. Он должен выглядеть примерно так:
[('GAME', 7270),
('FAMILY', 1559),
('SPORTS', 821),
...]
Как обычно, можно пользоваться только SQL.
cat_neg_rev = c.execute(
"# YOUR CODE HERE"
).fetchall()
assert myhash(cat_neg_rev) == 9929126
Повторите предыдущее упражнение, но в этот раз вместо числа отрицательных отзывов выведите долю отрицательных отзывов в общем числе отзывов по всем приложениям из данной категории. (При вычислении доли следует игнорировать записи, в которых в Sentiment
записан NULL
, как если бы их не было.) Поместите результат (список двухэлементных кортежей — категория и доля (число с плавающей точкой)) в переменную cat_neg_share
. Изменился ли порядок категорий? Как обычно, можно пользоваться только SQL.
Подсказка. Вам может пригодиться CASE
.
cat_neg_share = c.execute(
"# YOUR CODE HERE"
).fetchall()
assert myhash([(a, round(b, 4)) for a, b in cat_neg_share]) == 2803714
Когда всё сделано можно закрыть соединение с базой и удалить файл.
conn.close()
import os
try:
os.unlink("apps.sqlite")
except FileNotFoundError:
pass