No es práctico almacenar la información obtenida en la caché, es necesario utilizar una base de datos.
En el artículo consideraré:
- crear una base de datos SQLite simple;
- escribir información usando Python;
- leer datos y convertirlos a formato DataFrame;
- Actualización de análisis basada en datos de la base de datos.
Requisitos de la base de datos
El principal requisito para una base de datos de proyectos es almacenar datos y poder recuperarlos rápidamente.
Nuestra base de datos no es necesaria:
- delimitar el acceso a los esquemas, ya que solo el usuario tendrá acceso mediante análisis;
- mantenga el acceso 24/7, porque la extracción de datos es aceptable según sea necesario para el análisis;
- creación de procedimientos, ya que Todos los cálculos se realizarán en Python.
Por lo tanto, es posible que un proyecto utilice una base de datos simple en SQLite. Puede almacenarlo como un archivo en su disco duro, en una unidad flash USB o en una unidad en la nube para acceder desde otros dispositivos.
Características de trabajar con SQLite a través de python
Para trabajar con SQLite a través de Python, usamos la biblioteca sqlite3 .
Nos conectamos a la base de datos con un simple comando:
sqlite3.connect( )Si falta el archivo, se creará una nueva base de datos.
Las consultas a la base de datos se realizan de la siguiente manera:
conn = sqlite3.connect( )
cur = conn.cursor()
cur.execute()
df = cur.fetchall()
cur.fetchall () se ejecuta cuando, como resultado de una solicitud, queremos obtener datos de la base de datos.
Al final de escribir datos en la base de datos, no olvide finalizar la transacción:
conn.commit()
y al terminar de trabajar con la base de datos, no olvide cerrarla:
conn.close()
de lo contrario, la base se bloqueará para escribir o abrir.
La creación de tablas es estándar:
CREATE TABLE t1 (1 , 2 ...)
o una opción más versátil que crea una tabla si falta:
CREATE TABLE IF NOT EXISTS t1 (1 , 2 ...)Escribimos datos en la tabla, evitando repeticiones:
INSERT OR IGNORE INTO t1 (1, 2, ...) VALUES(1, 2, ...)Actualización de los datos:
UPDATE t1 SET 1 = 1 WHERE 2 = 2Para un trabajo más conveniente con SQLite, puede usar SQLite Manager o DB Browser para SQLite .
El primer programa es una extensión del navegador y parece una alternancia de una línea de solicitud y un bloque de respuesta:
el segundo programa es una aplicación de escritorio completa:
Estructura de la base de datos
La base de datos constará de 4 mesas: estudios, salas, 2 mesas de reserva.
Los datos de reserva cargados contienen información sobre períodos futuros, que pueden cambiar con un nuevo análisis. No es deseable sobrescribir los datos (se pueden usar, por ejemplo, para calcular el día / hora en que se realizó la reserva). Por lo tanto, se necesita una tabla de reserva para los datos de análisis sin procesar y la segunda para los últimos y relevantes.
Creamos tablas:
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.')
El parámetro de la tabla establece el nombre de la tabla que se creará. Crea todo por defecto.
En los campos de las tablas, puede ver los datos que no se analizaron (fecha de apertura del estudio, fecha de apertura de la sala). Describiré el cálculo de estos campos más adelante.
Interacción con la base de datos
Creemos 6 procedimientos para interactuar con la base de datos:
- Escribir una lista de estudios fotográficos en la base de datos;
- Cargar una lista de estudios fotográficos de la base de datos;
- Grabación de una lista de salas;
- Descarga de la lista de salas;
- Carga de datos de reserva;
- Registro de datos de reserva.
1. Escribir una lista de estudios fotográficos en la base de datos
En la entrada del procedimiento, pasamos los parámetros de la conexión a la base de datos y la tabla en forma de DataFrame. Escribimos los datos línea por línea, iterando sobre todas las líneas en un ciclo. Una propiedad útil de los datos de cadena en Python para esta operación es el "?" los elementos de la tupla especificados después.
El procedimiento para grabar una lista de estudios fotográficos es el siguiente:
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. Carga de la lista de estudios fotográficos de la base de datos
En la entrada del procedimiento, pasamos los parámetros para conectarse a la base de datos. Ejecutamos la consulta de selección, interceptamos los datos descargados y los escribimos en el DataFrame. Traducimos la fecha de fundación del estudio fotográfico al formato de fecha.
Todo el procedimiento es el siguiente:
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 studios3. Escribir la lista de salas en la base de datos
El procedimiento es similar a registrar una lista de estudios fotográficos: transferimos los parámetros de conexión y una tabla de salas, escribimos los datos línea por línea en la base de datos.
El procedimiento para registrar la lista de salas en la base de datos.
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. Descarga de la lista de salas de la base de datos
El procedimiento es similar a descargar una lista de estudios fotográficos: transferir parámetros de conexión, seleccionar-solicitar, interceptar, escribir en un DataFrame, convertir la fecha de apertura de la sala en formato de fecha.
La única diferencia: la identificación del estudio y el letrero de la sala se registraron en forma de bytes. Devolvemos el valor por la función:
int.from_bytes(, 'little')
El procedimiento para descargar la lista de salas es el siguiente:
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 halls5. Carga de información sobre la reserva desde la base de datos
Pasamos los parámetros de conexión de la base de datos y el parámetro de análisis al procedimiento, mostrando de qué tabla de reserva estamos solicitando información: 0 - de la actual (por defecto), 1 - de la tabla de análisis. A continuación, ejecutamos una solicitud de selección, la interceptamos y la transferimos a un DataFrame. Las fechas se convierten al formato de fecha, los números del formato de bytes al formato de número.
Procedimiento para cargar la información de la reserva:
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 booking6. Escribir información de reserva en la base de datos
La función más compleja de interacción con la base de datos, ya que inicia el análisis de los datos de reserva. En la entrada pasamos al procedimiento los parámetros de conexión a la base de datos y el listado de ids de habitación que se deben actualizar.
Para determinar la última fecha de los datos actualizados,
solicite de la base de datos la última fecha de análisis para cada ID de sala:
parsing_date = db_to_booking(conn, parsing = 1).groupby('hall_id').agg(np.max)['parsing_date']Repetimos cada ID de sala usando un bucle.
En cada ID de sala, lo primero que hacemos es definir
número de semanas para analizar en el pasado:
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 = 500Si la identificación de la sala está en la base de datos, calculamos. Si no es así, analizamos 500 semanas en el pasado o nos detenemos cuando no hubo reserva durante 2 meses (la limitación se describe en el artículo anterior ).
Luego realizamos los procedimientos de análisis:
d = get_past_booking(id, weeks_ago = weeks_ago)
d.update(get_future_booking(id))
book = hall_booking(d)Primero, analizamos la información de reserva del pasado a los datos reales, luego del futuro (hasta 2 meses, cuando no había registros) y al final transferimos los datos del formato json a DataFrame.
En la etapa final, escribimos los datos sobre la reserva de la sala en la base de datos y cerramos la transacción.
El procedimiento para registrar la información de la reserva en la base de datos es el siguiente:
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)))Actualización de los días de apertura del estudio y salas
La fecha de apertura del salón es la fecha de reserva más temprana para el salón.
La fecha de apertura del estudio fotográfico es la fecha más temprana para la apertura de la sala del estudio.
Basado en esta lógica,
descargamos las fechas de reserva más tempranas para cada habitación de la base de datos
halls = db_to_booking(conn).groupby('hall_id').agg(min)['date']
Luego actualizamos los datos de apertura línea por línea:
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])))Actualizamos los datos de apertura del estudio fotográfico de la misma manera: descargamos los datos sobre las fechas de apertura de las salas de la base de datos, calculamos la fecha más pequeña para cada estudio, reescribimos la fecha de apertura del estudio fotográfico.
Procedimiento de actualización de fechas de apertura:
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()Actualización de análisis
Combinaremos todos los procedimientos en este y el artículo anterior en este procedimiento. Se puede iniciar tanto durante el primer análisis como al actualizar los datos.
El procedimiento tiene este aspecto:
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()
Analicemos su trabajo en orden.
En la entrada del procedimiento pasamos 2 parámetros: la dirección de la carpeta de donde sacar la base de datos o donde instalarla (por defecto tomamos la carpeta con documentos de python), y el parámetro opcional is_manual, que si se establece en "1", solicitará la necesidad de crear una base de datos o tablas en en su ausencia.
. , :
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()
. :
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()
uBooking_parsing. , .. booking_to_db
booking_to_db(conn, halls.index)
conn.commit()
uBooking. uBooking 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()
:
update_open_dates(conn)
conn.commit() conn.close()¡El análisis con el guardado de datos en la base de datos se configuró correctamente!
Iniciamos el análisis / actualización con el siguiente procedimiento:
update_parsing()Salir
En este artículo y en el anterior , examinamos el algoritmo para analizar información abierta para estudios fotográficos. Los datos obtenidos se recogieron en una base de datos.
En el próximo artículo, consideraremos ejemplos de análisis de los datos obtenidos.
Puedes encontrar el proyecto terminado en mi página de github .