Il n'est pas pratique de stocker les informations obtenues dans le cache, il est nécessaire d'utiliser une base de données.
Dans l'article, je considérerai:
- créer une base de données SQLite simple;
- écrire des informations en utilisant Python;
- lecture de données et conversion au format DataFrame;
- mise à jour d'analyse basée sur les données de la base de données.
Exigences de la base de données
La principale exigence pour une base de données de projet est de stocker des données et de pouvoir les récupérer rapidement.
Notre base de données n'est pas requise:
- délimiter l'accès aux schémas, car seul l'utilisateur y aura accès par analyse;
- garder l'accès 24/7, car l'extraction des données est acceptable au besoin pour l'analyse;
- création de procédures, depuis tous les calculs seront effectués en python.
Par conséquent, il est possible pour un projet d'utiliser une base de données simple dans SQLite. Vous pouvez le stocker sous forme de fichier sur votre disque dur, sur une clé USB ou sur un lecteur cloud pour y accéder depuis d'autres appareils.
Fonctionnalités de l'utilisation de SQLite via Python
Pour travailler avec SQLite via python, nous utilisons la bibliothèque sqlite3 .
Nous nous connectons à la base de données avec une simple commande:
sqlite3.connect( )Si le fichier est manquant, une nouvelle base de données sera créée.
Les requêtes de base de données sont effectuées comme suit:
conn = sqlite3.connect( )
cur = conn.cursor()
cur.execute()
df = cur.fetchall()
cur.fetchall () est exécuté lorsque, à la suite de la requête, nous voulons obtenir des données de la base de données.
À la fin de l'écriture des données dans la base de données, n'oubliez pas de terminer la transaction:
conn.commit()
et à la fin du travail avec la base de données, n'oubliez pas de la fermer:
conn.close()
sinon, la base sera verrouillée pour l'écriture ou l'ouverture.
La création de tableaux est standard:
CREATE TABLE t1 (1 , 2 ...)
ou une option plus polyvalente qui crée une table si elle est manquante:
CREATE TABLE IF NOT EXISTS t1 (1 , 2 ...)Nous écrivons des données dans la table en évitant les répétitions:
INSERT OR IGNORE INTO t1 (1, 2, ...) VALUES(1, 2, ...)Mise à jour des données:
UPDATE t1 SET 1 = 1 WHERE 2 = 2Pour un travail plus pratique avec SQLite, vous pouvez utiliser SQLite Manager ou DB Browser pour SQLite .
Le premier programme est une extension de navigateur et ressemble à une alternance d'une ligne de requête et d'un bloc de réponse:
Le deuxième programme est une application de bureau à part entière:
Structure de la base de données
La base de données sera composée de 4 tables: studios, halls, 2 tables de réservation.
Les données de réservation téléchargées contiennent des informations sur les périodes futures, qui peuvent changer avec une nouvelle analyse. Il n'est pas souhaitable d'écraser les données (elles peuvent être utilisées, par exemple, pour calculer le jour / l'heure de la réservation). Par conséquent, une table de réservation est nécessaire pour les données d'analyse brutes, la seconde pour les dernières données pertinentes.
Nous créons des tableaux:
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.')
Le paramètre table définit le nom de la table à créer. Crée tout par défaut.
Dans les champs des tableaux, vous pouvez voir des données non analysées (date d'ouverture du studio, date d'ouverture de la salle). Je décrirai le calcul de ces champs plus tard.
Interaction avec la base de données
Créons 6 procédures pour interagir avec la base de données:
- Rédaction d'une liste de studios photo dans la base de données;
- Téléchargement d'une liste de studios photo à partir de la base de données;
- Enregistrement d'une liste de salles;
- Déchargement de la liste des salles;
- Téléchargement des données de réservation;
- Enregistrement des données de réservation.
1. Rédaction d'une liste de studios photo dans la base de données
A l'entrée de la procédure, on passe les paramètres de connexion à la base de données et à la table sous la forme d'un DataFrame. Nous écrivons les données ligne par ligne, en itérant sur toutes les lignes dans une boucle. Une propriété utile des données de chaîne en python pour cette opération est le "?" les éléments du tuple spécifié après.
La procédure d'enregistrement d'une liste de studios photo est la suivante:
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. Téléchargement de la liste des studios photo à partir de la base de données
Nous transmettons les paramètres de connexion à la base de données à l'entrée dans la procédure. Nous exécutons la requête de sélection, interceptons les données déchargées et les écrivons dans le DataFrame. Nous traduisons la date de fondation du studio photo au format date.
L'ensemble de la procédure est la suivante:
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. Écriture de la liste des salles dans la base de données
La procédure est similaire à l'enregistrement d'une liste de studios photographiques: nous transférons les paramètres de connexion et un tableau des salles, écrivons les données ligne par ligne dans la base de données.
La procédure d'enregistrement de la liste des salles dans la base de données
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. Déchargement de la liste des salles de la base de données
La procédure est similaire au déchargement d'une liste de studios photo: transfert des paramètres de connexion, select-request, interception, écriture dans un DataFrame, conversion de la date d'ouverture de la salle en format de date.
La seule différence: l'identifiant du studio et le signe du hall ont été enregistrés sous forme d'octets. Nous renvoyons la valeur par la fonction:
int.from_bytes(, 'little')
La procédure de déchargement de la liste des halls est la suivante:
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. Téléchargement d'informations sur la réservation à partir de la base de données
Nous transmettons les paramètres de connexion à la base de données et le paramètre d'analyse à la procédure, indiquant à partir de quelle table de réservation nous demandons des informations: 0 - à partir du réel (par défaut), 1 - à partir de la table d'analyse. Ensuite, nous exécutons une requête de sélection, l'interceptons et la traduisons en DataFrame. Les dates sont converties au format de date, les nombres du format octet au format numérique.
Procédure de téléchargement des informations de réservation:
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. Écriture des informations de réservation dans la base de données
La fonction la plus complexe d'interaction avec la base de données, car il lance l'analyse des données de réservation. A l'entrée, on passe à la procédure les paramètres de connexion à la base de données et la liste des identifiants de chambre à mettre à jour.
Pour déterminer la dernière date des données à jour,
demander à la base de données la dernière date d'analyse pour chaque identifiant de salle:
parsing_date = db_to_booking(conn, parsing = 1).groupby('hall_id').agg(np.max)['parsing_date']Nous itérons sur chaque identifiant de salle en utilisant une boucle.
Dans chaque identifiant de salle, la première chose que nous faisons est de définir
nombre de semaines à analyser dans le passé:
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 l'identifiant de la salle est dans la base de données, nous calculons. Sinon, nous analysons 500 semaines dans le passé ou nous nous arrêtons lorsqu'il n'y a pas eu de réservation pendant 2 mois (la limitation est décrite dans l' article précédent ).
Ensuite, nous effectuons les procédures d'analyse:
d = get_past_booking(id, weeks_ago = weeks_ago)
d.update(get_future_booking(id))
book = hall_booking(d)Tout d'abord, nous analysons les informations de réservation du passé aux données réelles, puis du futur (jusqu'à 2 mois, lorsqu'il n'y avait pas d'enregistrements) et à la fin nous transférons les données du format json vers DataFrame.
À l'étape finale, nous écrivons les données de réservation de la salle dans la base de données et clôturons la transaction.
La procédure d'enregistrement des informations de réservation dans la base de données est la suivante:
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)))Mise à jour des jours d'ouverture du studio et des salles
La date d'ouverture du salon est la première date de réservation pour le salon.
La date d'ouverture du studio photo est la première date d'ouverture de la salle du studio.
Sur la base de cette logique,
nous déchargons les premières dates de réservation pour chaque chambre de la base de données
halls = db_to_booking(conn).groupby('hall_id').agg(min)['date']
Ensuite, nous mettons à jour les données d'ouverture ligne par ligne:
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])))Nous mettons à jour les données d'ouverture du studio photo de la même manière: nous téléchargeons les données sur les dates d'ouverture des salles à partir de la base de données, calculons la plus petite date pour chaque studio et réécrivons la date d'ouverture du studio photo.
Procédure de mise à jour des dates d'ouverture:
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()Mise à jour d'analyse
Nous combinerons toutes les procédures de cet article et des articles précédents de cette procédure. Il peut être lancé à la fois lors de la première analyse et lors de la mise à jour des données.
La procédure ressemble à ceci:
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()
Analysons son travail dans l'ordre.
A l'entrée de la procédure, on passe 2 paramètres: l'adresse du dossier où se procurer la base de données ou où l'installer (par défaut, on prend le dossier avec les documents python), et le paramètre optionnel is_manual, qui, s'il est mis à "1", demandera la nécessité de créer une base de données ou des tables dans en leur absence.
. , :
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()L'analyse avec enregistrement des données dans la base de données est configurée avec succès!
Nous lançons l'analyse / la mise à jour avec la procédure suivante:
update_parsing()Résultat
Dans cet article et dans le précédent , nous avons examiné l'algorithme d'analyse des informations ouvertes pour les studios photo. Les données obtenues ont été collectées dans une base de données.
Dans le prochain article, nous examinerons des exemples d'analyse des données obtenues.
Vous pouvez trouver le projet fini sur ma page github .