Полученную информацию в кэше хранить нецелесообразно, необходимо использовать базу данных.
В статье рассмотрю:
- создание простой SQLite базы данных;
- запись информации с помощь Python;
- чтение данных и перевод в формат DataFrame;
- обновление парсинга с учетом данных БД.
Требования к базе данных
Основное требование к БД по проекту: хранить данные и иметь возможность их оперативно извлечь.
Нашей БД не требуется:
- разграничивать доступ к схемам, т.к. доступ будет только у пользователя парсингом;
- сохранять доступ 24/7, т.к. извлечение данных допустимо по мере необходимости проведения анализа;
- создание процедур, т.к. все вычисления будут проводится в python.
Поэтому для проекта возможно использовать простую базу данных в SQLite. Хранить её можно в виде файла либо на жестком диске, либо на флешке, либо на облачном диске для доступа с других устройств.
Особенности работы с SQLite через python
Для работы с SQLite через python используем библиотеку sqlite3.
Подключаемся к базе данных простой командой:
sqlite3.connect(путь к файлу)Если файл отсутствует, будет создана новая база.
Запросы к базе выполняются следующим образом:
conn = sqlite3.connect(путь к файлу) cur = conn.cursor() cur.execute(запрос) df = cur.fetchall() cur.fetchall() выполняется в том случае, когда в результате запроса мы хотим получить данные из БД.
В конце записи данных в БД не забывайте заканчивать транзакцию:
conn.commit() а в конце работы с базой не забывайте её закрывать:
conn.close() иначе база будет блокироваться на запись или на открытие.
Создание таблиц стандартное:
CREATE TABLE t1 (поле1 тип, поле2 тип...) или более универсальный вариант, создающий таблицу в случае её отсутствия:
CREATE TABLE IF NOT EXISTS t1 (поле1 тип, поле2 тип...)Записываем данные в таблицу, избегая повторов:
INSERT OR IGNORE INTO t1 (поле1, поле2, ...) VALUES(значение1, значение2, ...)Обновляем данные:
UPDATE t1 SET поле1 = значение1 WHERE поле2 = значение2Для более удобной работы с SQLite можно использовать SQLite Manager или DB Browser for SQLite.
Первая программа является расширением к браузеру и выглядит как чередование строки запроса и блока ответа:
Вторая программа — полноценное desktop-приложение:
Структура базы данных
БД будет состоять из 4 таблиц: студии, залы, 2 таблицы бронирования.
В выгружаемых данных по бронированию есть информация о будущих периодах, которая с новым парсингом может измениться. Затирать данные нежелательно (их можно использовать, например, для вычисления дня/часа, когда была сделана бронь). Поэтому, одна таблица бронирования необходима для сырых данных парсинга, вторая — для последних, актуальных.
def create_tables(conn, table = 'all'): cur = conn.cursor() if (table == 'all') or (table == 'uStudios'): cur.execute(''' CREATE TABLE IF NOT EXISTS uStudios (studio_id INT PRIMARY KEY UNIQUE, name TEXT UNIQUE, metro TEXT, address TEXT, phone TEXT, email TEXT, established_date DATE) ''') print('Table uStudios is created.') if (table == 'all') or (table == 'uHalls'): cur.execute(''' CREATE TABLE IF NOT EXISTS uHalls (hall_id INT PRIMARY KEY UNIQUE, studio_id INT, name TEXT, is_hall INT, square FLOAT, ceiling FLOAT, open_date DATE) ''') print('Table uHalls is created.') if (table == 'all') or (table == 'uBooking_parsing'): cur.execute(''' CREATE TABLE IF NOT EXISTS uBooking_parsing (hall_id INT, date DATE, hour INT, is_working_hour INT, min_hours INT, price INTEGER, is_booked INT, duration INT, parsing_date DATE) ''') print ('Table uBooking_parsing is created.') if (table == 'all') or (table == 'uBooking'): cur.execute(''' CREATE TABLE IF NOT EXISTS uBooking (hall_id INT, date DATE, hour INT, is_working_hour INT, min_hours INT, price INTEGER, is_booked INT, duration INT, parsing_date DATE) ''') print ('Table uBooking is created.')
Параметром table задаю название таблицы, которую необходимо создать. По умолчанию создает все.
В полях таблиц видны данные, которые не парсили (дата открытия студии, дата открытия зала). Вычисление этих полей опишу позже.
Взаимодействие с базой данных
Создадим 6 процедур для взаимодействия с базой данных:
- Запись списка фотостудий в базу данных;
- Выгрузка списка фотостудий из базы данных;
- Запись списка залов;
- Выгрузка списка залов;
- Выгрузка данных по бронированию;
- Запись данных по бронированию.
1. Запись списка фотостудий в базу данных
На входе в процедуру передаем параметры соединения с БД и таблицу в виде DataFrame. Записываем данные построчно, перебирая все строчки циклом. Полезным для этой операции свойством строковых данных в python является замена символов "?" элементами кортежа, указанным после.
def studios_to_db(conn, studio_list): cur = conn.cursor() for i in studio_list.index: cur.execute('INSERT OR IGNORE INTO uStudios (studio_id, name, metro, address, phone, email) VALUES(?, ?, ?, ?, ?, ?)', (i, studio_list.loc[i, 'name'], studio_list.loc[i, 'metro'], studio_list.loc[i, 'address'], studio_list.loc[i, 'phone'], studio_list.loc[i, 'email']))
2. Выгрузка списка фотостудий из базы данных
На вход в процедуру передаем параметры соединения с БД. Выполняем select-запрос, перехватываем выгружаемые данные и записываем в DataFrame. Переводим дату основания фотостудии в формат даты.
def db_to_studios(conn): cur = conn.cursor() cur.execute('SELECT * FROM uStudios') studios = pd.DataFrame(cur.fetchall() , columns=['studio_id', 'name', 'metro', 'address', 'phone', 'email', 'established_date'] ).set_index('studio_id') studios['established_date'] = pd.to_datetime(studios['established_date']) return studios
3. Запись списка залов в базу данных
Процедура аналогична записи списка фотостудий: передаем параметры подключения и таблицу залов, построчно записываем данные в базу.
def halls_to_db(conn, halls): cur = conn.cursor() for i in halls.index: cur.execute('INSERT OR IGNORE INTO uHalls (hall_id, studio_id, name, is_hall, square, ceiling) VALUES(?, ?, ?, ?, ?, ?)', (i, halls.loc[i, 'studio_id'], halls.loc[i, 'name'], halls.loc[i, 'is_hall'], halls.loc[i, 'square'], halls.loc[i, 'ceiling']))
4. Выгрузка списка залов из базы данных
Процедура аналогична выгрузки списка фотостудий: передача параметров подключения, select-запрос, перехват, запись в DataFrame, перевод даты открытия зала в формат даты.
Единственное отличие: id студии и признак зала записались в байтовом виде. Возвращаем значение функцией:
int.from_bytes(число, 'little')
def db_to_halls(conn): cur = conn.cursor() cur.execute('SELECT * FROM uHalls') halls = pd.DataFrame(cur.fetchall(), columns=['hall_id', 'studio_id', 'name', 'is_hall', 'square', 'ceiling', 'open_date']).set_index('hall_id') for i in halls.index: halls.loc[i, 'studio_id'] = int.from_bytes(halls.loc[i, 'studio_id'], 'little') halls.loc[i, 'is_hall'] = int.from_bytes(halls.loc[i, 'is_hall'], 'little') halls['open_date'] = pd.to_datetime(halls['open_date']) return halls
5. Выгрузка информации по бронированию из базы данных
В процедуру передаем параметры подключения к БД и параметр parsing, показывающий из какой таблицы по бронированию запрашиваем информацию: 0 — из актуальной (по умолчанию), 1 — из таблицы парсинга. Далее выполняем select-запрос, перехватываем его, переводим в DataFrame. Даты переводим в формат дат, числа из байтового формата в формат чисел.
def db_to_booking(conn, parsing = 0): cur = conn.cursor() if parsing == 1: cur.execute('SELECT * FROM uBooking_parsing') else: cur.execute('SELECT * FROM uBooking') booking = pd.DataFrame(cur.fetchall(), columns=['hall_id', 'date', 'hour', 'is_working_hour', 'min_hours', 'price', 'is_booked', 'duration', 'parsing_date']) booking['hall_id'] = [int.from_bytes(x, 'little') if not isinstance(x, int) else x for x in booking['hall_id']] booking['is_booked'] = [int.from_bytes(x, 'little') if not isinstance(x, int) else x for x in booking['is_booked']] booking['date'] = pd.DataFrame(booking['date']) booking['parsing_date'] = pd.DataFrame(booking['parsing_date']) return booking
6. Запись информации по бронированию в базу данных
Самая сложная функция взаимодействия с БД, т.к. она инициирует парсинг данных по бронированию. На входе передаем процедуре параметры подключения к базе данных и список id залов, который необходимо актуализировать.
Чтобы определить последнюю дату актуальных данных,
parsing_date = db_to_booking(conn, parsing = 1).groupby('hall_id').agg(np.max)['parsing_date']
Перебираем каждый id зала с помощью цикла.
В каждом id зала первом делом определяем
try: last_day_str = parsing_date[id] last_day = datetime.datetime.strptime(last_day_str, '%Y-%m-%d') delta_days = (datetime.datetime.now() - last_day).days weeks_ago = delta_days // 7 except: last_day_str = '2010-01-01' last_day = datetime.datetime.strptime(last_day_str, '%Y-%m-%d') weeks_ago = 500
Если id зала есть в базе, то вычисляем. Если нет, то парсим 500 недель в прошлое или останавливаемся, когда 2 месяца не было броней (ограничение описано в предыдущей статье).
d = get_past_booking(id, weeks_ago = weeks_ago) d.update(get_future_booking(id)) book = hall_booking(d)
Вначале парсим информацию по бронированию из прошлого до актуальных данных, потом из будущего (до 2 месяцев, когда записей не было) и в конце переводим данные из формата json в DataFrame.
Завершающим этапом записываем данные по бронированию зала в базу и закрываем транзакцию.
def booking_to_db(conn, halls_id): cur = conn.cursor() cur_date = pd.Timestamp(datetime.date.today()) parsing_date = db_to_booking(conn, parsing = 1).groupby('hall_id').agg(np.max)['parsing_date'] for id in halls_id: #download last parsing_date from DataBase try: last_day_str = parsing_date[id] last_day = datetime.datetime.strptime(last_day_str, '%Y-%m-%d') delta_days = (datetime.datetime.now() - last_day).days weeks_ago = delta_days // 7 except: last_day_str = '2010-01-01' last_day = datetime.datetime.strptime(last_day_str, '%Y-%m-%d') weeks_ago = 500 d = get_past_booking(id, weeks_ago = weeks_ago) d.update(get_future_booking(id)) book = hall_booking(d) for i in list(range(len(book))):#book.index: cur.execute('INSERT OR IGNORE INTO uBooking_parsing (hall_id, date, hour, is_working_hour, min_hours, price, is_booked, duration, parsing_date) VALUES(?,?,?,?,?,?,?,?,?)', (book.iloc[i]['hall_id'], book.iloc[i]['date'].date().isoformat(), book.iloc[i]['hour'], book.iloc[i]['is_working_hour'], book.iloc[i]['min_hours'], book.iloc[i]['price'], book.iloc[i]['is_booked'], book.iloc[i]['duration'], cur_date.date().isoformat())) conn.commit() print('hall_id ' + str(id) + ' added. ' + str(list(halls_id).index(id) + 1) + ' from ' + str(len(halls_id)))
Обновление дней открытия студии и залов
Дата открытия зала — это самая ранняя дата бронирования зала.
Дата открытия фотостудии — это самая ранняя дата открытия зала этой студии.
Исходя из этой логики,
halls = db_to_booking(conn).groupby('hall_id').agg(min)['date']
for i in list(range(len(halls))): cur.execute('''UPDATE uHalls SET open_date = '{1}' WHERE hall_id = {0}''' .format(halls.index[i], str(halls.iloc[i])))
Данные открытия фотостудий обновляем аналогично: выгружаем данные по датам открытия залов из базы данных, вычисляем наименьшую дату для каждой студии, переписываем дату открытия фотостудии.
def update_open_dates(conn): cur = conn.cursor() #update open date in uHalls halls = db_to_booking(conn).groupby('hall_id').agg(min)['date'] for i in list(range(len(halls))): cur.execute('''UPDATE uHalls SET open_date = '{1}' WHERE hall_id = {0}''' .format(halls.index[i], str(halls.iloc[i]))) #update open date in uStudios studios = db_to_halls(conn) studios['open_date'] = pd.to_datetime(studios['open_date']) studios = studios.groupby('studio_id').agg(min)['open_date'] for i in list(range(len(studios))): cur.execute('''UPDATE uStudios SET established_date = '{1}' WHERE studio_id = {0}''' .format(studios.index[i], str(studios.iloc[i]))) conn.commit()
Обновление парсинга
Все процедуры в этой и предыдущей статьях мы объединим в данной процедуре. Её можно запускать как при первом парсинге, так и при обновлении данных.
def update_parsing(directory = './/', is_manual = 0): start_time = time.time() #is DataBase exists? if not os.path.exists(directory + 'photostudios_moscow1.sqlite'): if is_manual == 1: print('Data base is not exists. Do you want to create DataBase (y/n)? ') answer = input().lower() else: answer == 'y' if answer == 'y': conn = sqlite3.connect(directory + 'photostudios_moscow1.sqlite') conn.close() print('DataBase is created') elif answer != 'n': print('Error in input!') return list() print('DataBase is exists') print("--- %s seconds ---" % (time.time() - start_time)) start_time = time.time() #connect to DataBase conn = sqlite3.connect(directory + 'photostudios_moscow1.sqlite') cur = conn.cursor() #has DataBase 4 tables? tables = [x[0] for x in list(cur.execute('SELECT name FROM sqlite_master WHERE type="table"'))] if not ('uStudios' in tables) & ('uHalls' in tables) & ('uBooking_parsing' in tables) & ('uBooking' in tables): if is_manual == 1: print('Do you want to create missing tables (y/n)? ') answer = input().lower() else: answer = 'y' if anwer == 'y': if not ('uStudios' in tables): create_tables(conn, table = 'uStudios') if not ('uHalls' in tables): create_tables(conn, table = 'uHalls') if not ('uBooking_parsing' in tables): create_tables(conn, table = 'uBooking_parsing') if not ('uBooking' in tables): create_tables(conn, table = 'uBooking') elif answer != 'n': print('Error in input!') return list() conn.commit() print(str(tables) + ' are exist in DataBase') print("--- %s seconds ---" % (time.time() - start_time)) start_time = time.time() #update uStudios studios = studio_list() new_studios = studios[[x not in list(db_to_studios(conn).index) for x in list(studios.index)]] if len(new_studios) > 0: print(str(len(new_studios)) + ' new studios detected: \n' + str(list(new_studios['name']))) studios_to_db(conn, new_studios) conn.commit() print('Studio list update was successful') print("--- %s seconds ---" % (time.time() - start_time)) start_time = time.time() #update uHalls halls = hall_list(list(studios.index)).sort_index() new_halls = halls[[x not in list(db_to_halls(conn).index) for x in list(halls.index)]] if len(new_halls) > 0: halls_to_db(conn, new_halls) conn.commit() print('Halls list update was successful') print("--- %s seconds ---" % (time.time() - start_time)) start_time = time.time() #update uBooking_parsing booking_to_db(conn, halls.index) conn.commit() print('Booking_parsing update was successful') print("--- %s seconds ---" % (time.time() - start_time)) start_time = time.time() #update uBooking from uBooking_parsing cur.execute('DELETE FROM uBooking') cur.execute(''' insert into uBooking (hall_id, date, hour, is_working_hour, min_hours, price, is_booked, duration, parsing_date) select hall_id, date, hour, is_working_hour, min_hours, price, is_booked, duration, parsing_date from ( select *, row_number() over(partition by hall_id, date, hour order by parsing_date desc) rn from uBooking_parsing ) t where rn = 1 ''') conn.commit() print('Booking update was successful') print("--- %s seconds ---" % (time.time() - start_time)) start_time = time.time() update_open_dates(conn) conn.commit() print('Open date update was successful') print("--- %s seconds ---" % (time.time() - start_time)) conn.close()
Разберем ее работу по порядку.
На входе в процедуру передаем 2 параметра: адрес папки, откуда брать базу данных или куда ее установить (по умолчанию берем папку с python-документов), и необязательный параметр is_manual, который при значении «1» будет запрашивать необходимость создания базы данных или таблиц в случае их отсутствия.
if not os.path.exists(directory + 'photostudios_moscow1.sqlite'): if is_manual == 1: print('Data base is not exists. Do you want to create DataBase (y/n)? ') answer = input().lower() else: answer == 'y' if answer == 'y': conn = sqlite3.connect(directory + 'photostudios_moscow1.sqlite') conn.close() print('DataBase is created') elif answer != 'n': print('Error in input!') return list()
conn = sqlite3.connect(directory + 'photostudios_moscow1.sqlite') cur = conn.cursor()
tables = [x[0] for x in list(cur.execute('SELECT name FROM sqlite_master WHERE type="table"'))] if not ('uStudios' in tables) & ('uHalls' in tables) & ('uBooking_parsing' in tables) & ('uBooking' in tables): if is_manual == 1: print('Do you want to create missing tables (y/n)? ') answer = input().lower() else: answer = 'y' if anwer == 'y': if not ('uStudios' in tables): create_tables(conn, table = 'uStudios') if not ('uHalls' in tables): create_tables(conn, table = 'uHalls') if not ('uBooking_parsing' in tables): create_tables(conn, table = 'uBooking_parsing') if not ('uBooking' in tables): create_tables(conn, table = 'uBooking') elif answer != 'n': print('Error in input!') return list() conn.commit()
studios = studio_list() new_studios = studios[[x not in list(db_to_studios(conn).index) for x in list(studios.index)]] if len(new_studios) > 0: print(str(len(new_studios)) + ' new studios detected: \n' + str(list(new_studios['name']))) studios_to_db(conn, new_studios)conn.commit()
halls = hall_list(list(studios.index)).sort_index() new_halls = halls[[x not in list(db_to_halls(conn).index) for x in list(halls.index)]] if len(new_halls) > 0: halls_to_db(conn, new_halls) conn.commit()
booking_to_db(conn, halls.index) conn.commit()
cur.execute('DELETE FROM uBooking') cur.execute(''' insert into uBooking (hall_id, date, hour, is_working_hour, min_hours, price, is_booked, duration, parsing_date) select hall_id, date, hour, is_working_hour, min_hours, price, is_booked, duration, parsing_date from ( select *, row_number() over(partition by hall_id, date, hour order by parsing_date desc) rn from uBooking_parsing ) t where rn = 1 ''') conn.commit()
update_open_dates(conn) conn.commit()
conn.close()
Парсинг с сохранением данных в БД настроен успешно!
update_parsing()
Итог
В данной и предыдущей статьях мы рассмотрели алгоритм работы парсинга открытой информацией по фотостудиям. Полученные данные собрали в базу данных.
В следующей статье рассмотрим примеры анализа полученных данных.
Готовый проект вы можете найти на моей странице в github.
ссылка на оригинал статьи https://habr.com/ru/post/512208/
Добавить комментарий