def _finish_restaurant(self, restaurant_id, status_code=2): with db_utils.connect_database(self.db_names['status']) as conn: cursor = conn.cursor() cursor.execute( '''UPDATE restaurants SET fetch_status = ?,commit_date = datetime('now','localtime') WHERE id = ?''', (status_code, restaurant_id)) conn.commit() row = cursor.execute( 'select count(*) from restaurants where fetch_status != 0 and fetch_status != 1').fetchone() if row is not None: self.num_finished = row[0] with db_utils.connect_database(self.db_names['data']) as conn: row = conn.execute('SELECT COUNT(*) FROM menus').fetchone() if row is not None: self.num_menus = row[0] self._refresh_output()
def _write_cache_to_database(self): with db_utils.connect_database(self.db_names['data']) as conn: conn.executemany(''' INSERT INTO menus(restaurant_id,name,pinyin_name,rating,rating_count,price,month_sales,description,category_id) VALUES(?,?,?,?,?,?,?,?,?) ''', self._menu_cache) conn.commit() self._menu_cache = []
def _finish_geohash(self, geohash): with db_utils.connect_database(self.db_names['status']) as conn: cursor = conn.cursor() cursor.execute( '''UPDATE grid SET fetch_status = 2,commit_date = datetime('now','localtime') WHERE geohash = ?''', (geohash,)) conn.commit() row = cursor.execute('select count(*) from grid where fetch_status != 0 and fetch_status != 1').fetchone() if row is not None: self.num_finished = row[0] with db_utils.connect_database(self.db_names['data']) as conn: row = conn.execute('SELECT COUNT(*) FROM restaurants').fetchone() if row is not None: self.num_restaurants = row[0] self._refresh_output()
def _take_geohash(self): with db_utils.connect_database(self.db_names['status'], isolation_level='EXCLUSIVE') as conn: cursor = conn.cursor() cursor.execute('BEGIN EXCLUSIVE') geohash = cursor.execute('SELECT geohash FROM grid WHERE fetch_status = 0 LIMIT 1').fetchone() if geohash is not None: cursor.execute('UPDATE grid SET fetch_status = 1 WHERE geohash = ?', geohash) conn.commit() return geohash
def _write_cache_to_database(self): with db_utils.connect_database(self.db_names['data']) as conn: conn.executemany( ''' INSERT INTO menus(restaurant_id,name,pinyin_name,rating,rating_count,price,month_sales,description,category_id) VALUES(?,?,?,?,?,?,?,?,?) ''', self._menu_cache) conn.commit() self._menu_cache = []
def _finish_restaurant(self, restaurant_id, status_code=2): with db_utils.connect_database(self.db_names['status']) as conn: cursor = conn.cursor() cursor.execute( '''UPDATE restaurants SET fetch_status = ?,commit_date = datetime('now','localtime') WHERE id = ?''', (status_code, restaurant_id)) conn.commit() row = cursor.execute( 'select count(*) from restaurants where fetch_status != 0 and fetch_status != 1' ).fetchone() if row is not None: self.num_finished = row[0] with db_utils.connect_database(self.db_names['data']) as conn: row = conn.execute('SELECT COUNT(*) FROM menus').fetchone() if row is not None: self.num_menus = row[0] self._refresh_output()
def _take_restaurant(self): with db_utils.connect_database(self.db_names['status'], isolation_level='EXCLUSIVE') as conn: cursor = conn.cursor() cursor.execute('BEGIN EXCLUSIVE') row = cursor.execute('SELECT id FROM restaurants WHERE fetch_status = 0 LIMIT 1').fetchone() if row is not None: cursor.execute('UPDATE restaurants SET fetch_status = 1 WHERE id = ?', row) conn.commit() return row
def _finish_geohash(self, geohash): with db_utils.connect_database(self.db_names['status']) as conn: cursor = conn.cursor() cursor.execute( '''UPDATE grid SET fetch_status = 2,commit_date = datetime('now','localtime') WHERE geohash = ?''', (geohash, )) conn.commit() row = cursor.execute( 'select count(*) from grid where fetch_status != 0 and fetch_status != 1' ).fetchone() if row is not None: self.num_finished = row[0] with db_utils.connect_database(self.db_names['data']) as conn: row = conn.execute('SELECT COUNT(*) FROM restaurants').fetchone() if row is not None: self.num_restaurants = row[0] self._refresh_output()
def _write_cache_to_database(self): with db_utils.connect_database(self.db_names['data']) as conn: cursor = conn.cursor() cursor.executemany(''' INSERT OR IGNORE INTO restaurants VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ''', self._restaurant_cache) cursor.executemany(''' INSERT INTO restaurant_categories(category_id,restaurant_id) SELECT ?,? WHERE NOT EXISTS(SELECT 1 FROM restaurant_categories WHERE category_id = ? AND restaurant_id = ?) ''', self._category_cache) conn.commit() self._restaurant_cache = [] self._category_cache = []
def _take_geohash(self): with db_utils.connect_database(self.db_names['status'], isolation_level='EXCLUSIVE') as conn: cursor = conn.cursor() cursor.execute('BEGIN EXCLUSIVE') geohash = cursor.execute( 'SELECT geohash FROM grid WHERE fetch_status = 0 LIMIT 1' ).fetchone() if geohash is not None: cursor.execute( 'UPDATE grid SET fetch_status = 1 WHERE geohash = ?', geohash) conn.commit() return geohash
def _take_restaurant(self): with db_utils.connect_database(self.db_names['status'], isolation_level='EXCLUSIVE') as conn: cursor = conn.cursor() cursor.execute('BEGIN EXCLUSIVE') row = cursor.execute( 'SELECT id FROM restaurants WHERE fetch_status = 0 LIMIT 1' ).fetchone() if row is not None: cursor.execute( 'UPDATE restaurants SET fetch_status = 1 WHERE id = ?', row) conn.commit() return row
def _write_cache_to_database(self): with db_utils.connect_database(self.db_names['data']) as conn: cursor = conn.cursor() cursor.executemany( ''' INSERT OR IGNORE INTO restaurants VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ''', self._restaurant_cache) cursor.executemany( ''' INSERT INTO restaurant_categories(category_id,restaurant_id) SELECT ?,? WHERE NOT EXISTS(SELECT 1 FROM restaurant_categories WHERE category_id = ? AND restaurant_id = ?) ''', self._category_cache) conn.commit() self._restaurant_cache = [] self._category_cache = []
def _log_exception(self, geohash, exception): with db_utils.connect_database(self.db_names['log']) as conn: conn.execute('INSERT INTO fetch_restaurant_exception VALUES(?,?)', (geohash, exception)) conn.commit()
def _log_http_error(self, geohash, http_code, error_msg): with db_utils.connect_database(self.db_names['log']) as conn: conn.execute('INSERT INTO fetch_restaurant_log VALUES(?,?,?)', (geohash, http_code, error_msg)) conn.commit()
def _num_restaurants(self): with db_utils.connect_database(self.db_names['status']) as conn: row = conn.execute('SELECT COUNT(*) FROM restaurants').fetchone() return row[0] if row is not None else 0