取得した情報をキャッシュに保存することは実用的ではなく、データベースを使用する必要があります。
記事で私は検討します：
- 単純なSQLiteデータベースの作成。
- Pythonを使用して情報を書き込む。
- データの読み取りとDataFrame形式への変換。
- データベースデータに基づいて更新を解析します。
データベース要件
プロジェクトデータベースの主な要件は、データを保存し、すばやく取得できるようにすることです。
私たちのデータベースは必要ありません：
- スキームへのアクセスを区切る ユーザーのみが解析によってアクセスできます。
- 24時間年中無休でアクセスを維持する データ抽出は、分析の必要に応じて受け入れられます。
- 以来、手順の作成 すべての計算はpythonで行われます。
したがって、プロジェクトでSQLiteの単純なデータベースを使用することができます。ハードドライブ、USBフラッシュドライブ、または他のデバイスからアクセスできるようにクラウドドライブにファイルとして保存できます。
pythonを介してSQLiteを操作する機能
pythonを介してSQLiteを操作するには、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 = 2SQLiteのとより便利な作業については、使用することができますSQLiteのマネージャまたはSQLiteのためにDB Browserを。
最初のプログラムはブラウザ拡張機能であり、要求行と応答ブロックの交互のように見えます
。2番目のプログラムは本格的なデスクトップアプリケーションです。
データベース構造
データベースは、スタジオ、ホール、2つの予約テーブルの4つのテーブルで構成されます。
アップロードされた予約データには、将来の期間に関する情報が含まれています。これは、新しい解析によって変更される可能性があります。データを上書きすることは望ましくありません（たとえば、予約が行われた日/時間を計算するために使用できます）。したがって、生の解析データ用に1つの予約テーブルが必要であり、最新の関連データ用に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の形式で渡します。データを1行ずつ書き込み、ループ内のすべての行を繰り返します。この操作のための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 studios3.ホールのリストをデータベースに書き込む
手順は、フォトスタジオのリストを記録するのと似ています。接続パラメータとホールのテーブルを1行ずつデータベースに書き込みます。
ホールのリストをデータベースに記録する手順
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.データベースからホールのリストをアンロードします
手順は、フォトスタジオのリストをアンロードするのと似ています。接続パラメーターの転送、選択要求、傍受、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 halls5.データベースから予約に関する情報をアップロードする
データベース接続パラメーターと解析パラメーターをプロシージャーに渡し、情報を要求している予約テーブルを示します。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 booking6.予約情報をデータベースに書き込む
以来、データベースとの相互作用の最も複雑な機能 予約データの解析を開始します。入り口で、データベースに接続するためのパラメータと更新する必要のあるホールIDのリストをプロシージャに渡します。
現在のデータの最新の日付を判別するには、
各ホール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']
次に、開始データを1行ずつ更新します。
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()
. :
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()データベースへのデータの保存による解析が正常に構成されました。
次の手順で解析/更新を開始します。
update_parsing()結果
今回と前回の記事では、フォトスタジオのオープン情報を解析するためのアルゴリズムを検討しました。得られたデータはデータベースに収集されました。
では次の記事、私たちは、得られたデータを解析した例を検討します。
完成したプロジェクトは私のgithubページにあります。