Não é prático armazenar no cache as informações obtidas, é necessário usar um banco de dados.
No artigo, irei considerar:
- criar um banco de dados SQLite simples;
- escrever informações usando Python;
- leitura de dados e conversão para o formato DataFrame;
- análise de atualização com base nos dados do banco de dados.
Requisitos de banco de dados
O principal requisito para um banco de dados de projeto é armazenar dados e ser capaz de recuperá-los rapidamente.
Nosso banco de dados não é necessário:
- delimitar o acesso aos esquemas, uma vez que apenas o usuário terá acesso por análise;
- mantenha o acesso 24/7, porque a extração de dados é aceitável conforme necessário para análise;
- criação de procedimentos, desde todos os cálculos serão feitos em python.
Portanto, é possível para um projeto usar um banco de dados simples em SQLite. Você pode armazená-lo como um arquivo em seu disco rígido ou em uma unidade flash USB ou em uma unidade de nuvem para acesso de outros dispositivos.
Recursos de trabalhar com SQLite por meio de python
Para trabalhar com SQLite por meio de python, usamos a biblioteca sqlite3 .
Conectamos ao banco de dados com um comando simples:
sqlite3.connect( )Se o arquivo estiver faltando, um novo banco de dados será criado.
As consultas de banco de dados são realizadas da seguinte forma:
conn = sqlite3.connect( )
cur = conn.cursor()
cur.execute()
df = cur.fetchall()
cur.fetchall () é executado quando, como resultado da solicitação, queremos obter dados do banco de dados.
Ao finalizar a gravação dos dados no banco de dados, não se esqueça de encerrar a transação:
conn.commit()
e ao final de trabalhar com o banco de dados, não se esqueça de fechá-lo:
conn.close()
caso contrário, a base será bloqueada para escrita ou abertura.
A criação de tabelas é padrão:
CREATE TABLE t1 (1 , 2 ...)
ou uma opção mais versátil que cria uma tabela se ela estiver faltando:
CREATE TABLE IF NOT EXISTS t1 (1 , 2 ...)Gravamos dados na tabela, evitando repetições:
INSERT OR IGNORE INTO t1 (1, 2, ...) VALUES(1, 2, ...)Atualizando os dados:
UPDATE t1 SET 1 = 1 WHERE 2 = 2Para um trabalho mais conveniente com o SQLite, você pode usar o SQLite Manager ou o DB Browser for SQLite .
O primeiro programa é uma extensão do navegador e parece uma alternância de uma linha de solicitação e um bloco de resposta:
O segundo programa é um aplicativo de desktop completo:
Estrutura de banco de dados
O banco de dados será composto por 4 tabelas: estúdios, salas, 2 tabelas de reserva.
Os dados de reserva carregados contêm informações sobre períodos futuros, que podem mudar com uma nova análise. Não é desejável sobrescrever os dados (eles podem ser usados, por exemplo, para calcular o dia / hora em que a reserva foi feita). Portanto, uma tabela de reserva é necessária para os dados de análise bruta, a segunda para os mais recentes e relevantes.
Nós criamos tabelas:
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.')
O parâmetro table define o nome da tabela a ser criada. Cria tudo por padrão.
Nos campos das tabelas, você pode ver os dados que não foram analisados (data de inauguração do estúdio, data de inauguração do hall). Descreverei o cálculo desses campos posteriormente.
Interação com o banco de dados
Vamos criar 6 procedimentos para interagir com o banco de dados:
- Escrever uma lista de estúdios fotográficos no banco de dados;
- Upload de uma lista de estúdios fotográficos do banco de dados;
- Gravando uma lista de corredores;
- Descarregando a lista de corredores;
- Upload de dados de reserva;
- Gravação de dados de reserva.
1. Escrever uma lista de estúdios de fotografia no banco de dados
Na entrada do procedimento, passamos os parâmetros de conexão ao banco de dados e à tabela em forma de DataFrame. Escrevemos os dados linha por linha, iterando todas as linhas em um loop. Uma propriedade útil de dados de string em python para esta operação é o "?" os elementos da tupla especificados depois.
O procedimento para gravar uma lista de estúdios fotográficos é o seguinte:
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. Carregando a lista de estúdios fotográficos do banco de dados
Passamos os parâmetros de conexão do banco de dados para a entrada no procedimento. Executamos a consulta de seleção, interceptamos os dados descarregados e os gravamos no DataFrame. Traduzimos a data de fundação do estúdio fotográfico para o formato de data.
Todo o procedimento é o seguinte:
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. Gravando a lista de corredores no banco de dados
O procedimento é semelhante ao registro de uma lista de estúdios fotográficos: transferimos os parâmetros de conexão e uma tabela de corredores, gravamos os dados linha por linha no banco de dados.
O procedimento para registrar a lista de corredores no banco de dados
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. Descarregar a lista de corredores do banco de dados
O procedimento é semelhante ao descarregamento de uma lista de estúdios de fotografia: transferência de parâmetros de conexão, solicitação de seleção, interceptação, gravação em um DataFrame, conversão da data de inauguração do salão em formato de data.
A única diferença: o id do estúdio e o sinal do corredor eram gravados na forma de bytes. Retornamos o valor pela função:
int.from_bytes(, 'little')
O procedimento para descarregar a lista de corredores é o seguinte:
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. Upload de informações de reserva do banco de dados
Passamos os parâmetros de conexão do banco de dados e o parâmetro de análise para o procedimento, mostrando de qual tabela de reserva estamos solicitando informações: 0 - da atual (por padrão), 1 - da tabela de análise. Em seguida, executamos uma solicitação de seleção, interceptamos e transferimos para um DataFrame. As datas são convertidas para o formato de data, os números do formato de bytes para o formato de número.
Procedimento para enviar informações de 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. Gravando informações de reserva no banco de dados
A função mais complexa de interação com o banco de dados, uma vez que ele inicia a análise dos dados de reserva. Na entrada, passamos para o procedimento os parâmetros de conexão ao banco de dados e a lista de ids de quartos que devem ser atualizados.
Para determinar a data mais recente de dados atualizados,
solicitar do banco de dados a última data de análise para cada id de corredor:
parsing_date = db_to_booking(conn, parsing = 1).groupby('hall_id').agg(np.max)['parsing_date']Nós iteramos sobre cada id de corredor usando um loop.
Em cada identificação de corredor, a primeira coisa que fazemos é definir
número de semanas para analisar no passado:
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 = 500Se a identificação do corredor estiver no banco de dados, então calculamos. Do contrário, analisamos 500 semanas no passado ou paramos quando não houve reserva por 2 meses (a limitação é descrita no artigo anterior ).
Em seguida, realizamos os procedimentos de análise:
d = get_past_booking(id, weeks_ago = weeks_ago)
d.update(get_future_booking(id))
book = hall_booking(d)Primeiro, analisamos as informações de reserva do passado para os dados reais, depois do futuro (até 2 meses, quando não havia registros) e no final transferimos os dados do formato json para o DataFrame.
Na fase final, gravamos os dados da reserva do salão no banco de dados e fechamos a transação.
O procedimento para registrar informações de reserva no banco de dados é o seguinte:
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)))Atualizando os dias de abertura do estúdio e corredores
A Data de Abertura do Lounge é a primeira data de reserva do Lounge.
A data de inauguração do estúdio fotográfico é a primeira data para a inauguração da sala do estúdio.
Com base nesta lógica,
nós descarregamos as primeiras datas de reserva para cada quarto do banco de dados
halls = db_to_booking(conn).groupby('hall_id').agg(min)['date']
Em seguida, atualizamos os dados de abertura linha por linha:
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])))Atualizamos os dados de abertura do estúdio fotográfico da mesma forma: baixamos os dados sobre as datas de abertura dos corredores do banco de dados, calculamos a menor data para cada estúdio e reescrevemos a data de abertura do estúdio fotográfico.
Procedimento para atualização das datas de abertura:
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()Atualização de análise
Combinaremos todos os procedimentos neste artigo e no artigo anterior deste procedimento. Ele pode ser iniciado durante a primeira análise e ao atualizar os dados.
O procedimento é parecido com este:
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()
Vamos analisar seu trabalho em ordem.
Na entrada do procedimento, passamos 2 parâmetros: o endereço da pasta de onde obter o banco de dados ou onde instalá-lo (por padrão, pegamos a pasta com documentos python) e o parâmetro opcional is_manual, que, se definido como "1", solicitará a necessidade de criar um banco de dados ou tabelas em sua ausência.
. , :
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()A análise com salvamento de dados no banco de dados foi configurada com sucesso!
Iniciamos a análise / atualização com o seguinte procedimento:
update_parsing()Resultado
Neste artigo e no anterior , examinamos o algoritmo para analisar informações abertas para estúdios de fotografia. Os dados obtidos foram coletados em um banco de dados.
No próximo artigo, consideraremos exemplos de análise dos dados obtidos.
Você pode encontrar o projeto concluído na minha página do github .