كيفية تحليل سوق استوديو الصور باستخدام Python (2/3). قاعدة البيانات

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.')

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']))

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 studios

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']))

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 halls

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 booking

parsing_date = db_to_booking(conn, parsing = 1).groupby('hall_id').agg(np.max)['parsing_date']

        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)

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']

    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()

    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()

    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)

    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()

    booking_to_db(conn, halls.index)   
    
    conn.commit()

    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()