Tidak praktis untuk menyimpan informasi yang diperoleh di cache; perlu menggunakan database.
Dalam artikel ini saya akan mempertimbangkan:
- membuat database SQLite sederhana;
- menulis informasi menggunakan Python;
- membaca data dan mengonversi ke format DataFrame;
- update parsing berdasarkan data database.
Persyaratan database
Persyaratan utama untuk database proyek adalah untuk menyimpan data dan dapat mengambilnya kembali dengan cepat.
Database kami tidak diperlukan:
- batasi akses ke skema, sejak hanya pengguna yang akan memiliki akses dengan mengurai;
- tetap akses 24/7, karena ekstraksi data dapat diterima sesuai kebutuhan untuk analisis;
- pembuatan prosedur, sejak semua perhitungan akan dilakukan dengan python.
Oleh karena itu, proyek dapat menggunakan database sederhana di SQLite. Anda dapat menyimpannya sebagai file baik di hard drive Anda, atau di flash drive USB, atau di cloud drive untuk akses dari perangkat lain.
Fitur bekerja dengan SQLite melalui python
Untuk bekerja dengan SQLite melalui python, kami menggunakan pustaka sqlite3 .
Kami terhubung ke database dengan perintah sederhana:
sqlite3.connect( )Jika file hilang, database baru akan dibuat.
Kueri database dilakukan sebagai berikut:
conn = sqlite3.connect( )
cur = conn.cursor()
cur.execute()
df = cur.fetchall()
cur.fetchall () dijalankan ketika, sebagai hasil dari permintaan, kita ingin mengambil data dari database.
Di akhir penulisan data ke database, jangan lupa akhiri transaksi:
conn.commit()
dan di akhir bekerja dengan database, jangan lupa untuk menutupnya:
conn.close()
jika tidak, alas akan dikunci untuk menulis atau membuka.
Pembuatan tabel adalah standar:
CREATE TABLE t1 (1 , 2 ...)
atau opsi yang lebih serbaguna yang membuat tabel jika tidak ada:
CREATE TABLE IF NOT EXISTS t1 (1 , 2 ...)Kami menulis data ke tabel, menghindari pengulangan:
INSERT OR IGNORE INTO t1 (1, 2, ...) VALUES(1, 2, ...)Memperbarui data:
UPDATE t1 SET 1 = 1 WHERE 2 = 2Untuk pekerjaan yang lebih nyaman dengan SQLite, Anda dapat menggunakan SQLite Manager atau DB Browser untuk SQLite .
Program pertama adalah ekstensi browser dan terlihat seperti pergantian baris permintaan dan blok respons:
Program kedua adalah aplikasi desktop lengkap:
Struktur database
Database akan terdiri dari 4 tabel: studio, aula, 2 tabel pemesanan.
Data pemesanan yang diunggah berisi informasi tentang periode mendatang, yang dapat berubah dengan penguraian baru. Tidak diinginkan untuk menimpa data (data dapat digunakan, misalnya, untuk menghitung hari / jam saat reservasi dibuat). Oleh karena itu, satu tabel pemesanan diperlukan untuk data penguraian mentah, yang kedua untuk yang terbaru, yang relevan.
Kami membuat tabel:
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.')
Parameter tabel mengatur nama tabel yang akan dibuat. Membuat semuanya secara default.
Pada kolom tabel terlihat data yang belum diurai (tanggal pembukaan studio, tanggal pembukaan aula). Saya akan menjelaskan perhitungan bidang ini nanti.
Interaksi dengan database
Mari buat 6 prosedur untuk berinteraksi dengan database:
- Menulis daftar studio foto ke database;
- Mengupload daftar studio foto dari database;
- Merekam daftar aula;
- Bongkar daftar aula;
- Mengupload data pemesanan;
- Pencatatan data pemesanan.
1. Menulis daftar studio foto ke database
Di pintu masuk ke prosedur, kami meneruskan parameter untuk menghubungkan ke database dan tabel dalam bentuk DataFrame. Kami menulis data baris demi baris, mengulangi semua baris dalam satu lingkaran. Properti berguna dari data string dalam python untuk operasi ini adalah "?" elemen tupel yang ditentukan setelah.
Prosedur pencatatan daftar studio foto adalah sebagai berikut:
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. Mengupload daftar studio foto dari database
Kami meneruskan parameter koneksi database untuk masuk ke prosedur. Kami menjalankan kueri pemilihan, mencegat data yang dibongkar dan menuliskannya ke DataFrame. Kami menerjemahkan tanggal dasar studio foto dalam format tanggal.
Keseluruhan prosedur adalah sebagai berikut:
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. Menulis daftar hall ke database
Prosedurnya mirip dengan merekam daftar studio fotografi: kami mentransfer parameter koneksi dan tabel aula, menulis data baris demi baris ke database.
Prosedur untuk merekam daftar aula dalam database
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. Bongkar daftar aula dari database
Prosedurnya mirip dengan membongkar daftar studio foto: mentransfer parameter koneksi, permintaan-pilih, intersepsi, menulis ke DataFrame, mengubah tanggal pembukaan aula menjadi format tanggal.
Satu-satunya perbedaan: id studio dan tanda aula direkam dalam bentuk byte. Kami mengembalikan nilai dengan fungsi:
int.from_bytes(, 'little')
Prosedur pembongkaran daftar balai adalah sebagai berikut:
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. Mengupload informasi pemesanan dari database
Kami meneruskan parameter koneksi database dan parameter parsing ke prosedur, menunjukkan dari tabel pemesanan mana kami meminta informasi: 0 - dari saat ini (secara default), 1 - dari tabel parsing. Selanjutnya, kami menjalankan permintaan pemilihan, mencegatnya, dan menerjemahkannya ke dalam DataFrame. Tanggal dikonversi ke format tanggal, angka dari format byte ke format angka.
Prosedur untuk mengunggah informasi pemesanan:
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. Penulisan informasi pemesanan ke database
Fungsi paling kompleks dari interaksi dengan database, sejak itu memulai penguraian data pemesanan. Di pintu masuk, kami meneruskan ke prosedur parameter untuk menghubungkan ke database dan daftar id ruang yang harus diperbarui.
Untuk menentukan tanggal terbaru dari data terbaru,
meminta dari database tanggal parsing terbaru untuk setiap id hall:
parsing_date = db_to_booking(conn, parsing = 1).groupby('hall_id').agg(np.max)['parsing_date']Kami mengulangi setiap id ruang menggunakan loop.
Di setiap hall id, hal pertama yang kita lakukan adalah mendefinisikan
jumlah minggu untuk diurai di masa lalu:
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 = 500Jika hall id ada di database, maka kita hitung. Jika tidak, maka kami mengurai 500 minggu yang lalu atau berhenti ketika tidak ada reservasi selama 2 bulan (batasan dijelaskan di artikel sebelumnya ).
Kemudian kami melakukan prosedur parsing:
d = get_past_booking(id, weeks_ago = weeks_ago)
d.update(get_future_booking(id))
book = hall_booking(d)Pertama, kami mengurai informasi pemesanan dari masa lalu ke data aktual, kemudian dari masa depan (hingga 2 bulan, ketika tidak ada catatan) dan pada akhirnya kami mentransfer data dari format json ke DataFrame.
Pada tahap terakhir, kami menulis data pemesanan aula ke dalam database dan menutup transaksi.
Prosedur pencatatan informasi pemesanan ke dalam database adalah sebagai berikut:
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)))Memperbarui hari pembukaan studio dan aula
Tanggal Pembukaan Lounge adalah tanggal pemesanan paling awal untuk Lounge.
Tanggal pembukaan studio foto tersebut merupakan tanggal paling awal untuk pembukaan balai studio.
Berdasarkan logika ini,
kami membongkar tanggal pemesanan paling awal untuk setiap kamar dari database
halls = db_to_booking(conn).groupby('hall_id').agg(min)['date']
Kemudian kami memperbarui data pembukaan baris demi baris:
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])))Kami memperbarui data pembukaan studio foto dengan cara yang sama: kami mengunduh data tanggal pembukaan aula dari database, menghitung tanggal terkecil untuk setiap studio, menulis ulang tanggal pembukaan studio foto.
Prosedur untuk memperbarui tanggal pembukaan:
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()Pembaruan parsing
Kami akan menggabungkan semua prosedur di sini dan artikel sebelumnya dalam prosedur ini. Ini dapat diluncurkan selama penguraian pertama dan saat memperbarui data.
Prosedurnya terlihat seperti ini:
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()
Mari kita analisis pekerjaannya secara berurutan.
Di pintu masuk ke prosedur, kami meneruskan 2 parameter: alamat folder tempat mendapatkan database atau tempat menginstalnya (secara default, kami mengambil folder dengan dokumen python), dan parameter is_manual opsional, yang, jika disetel ke "1", akan meminta kebutuhan untuk membuat database atau tabel di dalam ketidakhadiran mereka.
. , :
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()Parsing dengan menyimpan data ke database berhasil dikonfigurasi!
Kami memulai penguraian / pembaruan dengan prosedur berikut:
update_parsing()Hasil
Di artikel ini dan sebelumnya , kami memeriksa algoritme untuk mengurai informasi terbuka untuk studio foto. Data yang diperoleh dikumpulkan dalam database.
Pada artikel berikutnya, kami akan membahas contoh-contoh menganalisis data yang diperoleh.
Anda dapat menemukan proyek yang sudah selesai di halaman github saya .