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 _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 _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 _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
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
