Coverage for mindsdb / integrations / handlers / tripadvisor_handler / tripadvisor_table.py: 0%

1import pandas as pd 

2from mindsdb.integrations.libs.api_handler import APITable 

3from mindsdb_sql_parser import ast 

4from mindsdb.integrations.utilities.sql_utils import extract_comparison_conditions 

5 

6 

7class SearchLocationTable(APITable): 

8 def select(self, query: ast.Select) -> pd.DataFrame: 

9 """Select data from the search_location table and return it as a pandas DataFrame. 

10 

11 Args: 

12 query (ast.Select): The SQL query to be executed. 

13 

14 Returns: 

15 pandas.DataFrame: A pandas DataFrame containing the selected data. 

16 """ 

17 

18 self.handler.connect() 

19 

20 conditions = extract_comparison_conditions(query.where) 

21 

22 allowed_keys = set( 

23 [ 

24 "searchQuery", 

25 "category", 

26 "phone", 

27 "address", 

28 "latLong", 

29 "radius", 

30 "radiusUnit", 

31 "language", 

32 ] 

33 ) 

34 

35 params = {} 

36 filters = [] 

37 for op, arg1, arg2 in conditions: 

38 if op == "or": 

39 raise NotImplementedError("OR is not supported") 

40 elif op == "=" and arg1 in allowed_keys: 

41 params[arg1] = arg2 

42 elif op != "=": 

43 raise NotImplementedError(f"Unknown op: {op}") 

44 else: 

45 filters.append([op, arg1, arg2]) 

46 

47 if query.limit is not None: 

48 params["max_results"] = query.limit.value 

49 

50 if "searchQuery" not in params and "latLong" not in params: 

51 # search not works without searchQuery, use 'London' 

52 params["searchQuery"] = "London" 

53 

54 result = self.handler.call_tripadvisor_searchlocation_api(params=params) 

55 

56 # filter targets 

57 columns = [] 

58 for target in query.targets: 

59 if isinstance(target, ast.Star): 

60 columns = [] 

61 break 

62 elif isinstance(target, ast.Identifier): 

63 columns.append(target.parts[-1]) 

64 else: 

65 raise NotImplementedError 

66 

67 if len(columns) == 0: 

68 columns = self.get_columns() 

69 

70 # columns to lower case 

71 columns = [name.lower() for name in columns] 

72 

73 if len(result) == 0: 

74 result = pd.DataFrame([], columns=columns) 

75 else: 

76 # add absent columns 

77 for col in set(columns) & set(result.columns) ^ set(columns): 

78 result[col] = None 

79 

80 # filter by columns 

81 result = result[columns] 

82 return result 

83 

84 def get_columns(self): 

85 """Get the list of column names for the search_location table. 

86 

87 Returns: 

88 list: A list of column names for the search_location table. 

89 """ 

90 return [ 

91 "location_id", 

92 "name", 

93 "distance", 

94 "rating", 

95 "bearing", 

96 "street1", 

97 "street2", 

98 "city", 

99 "state", 

100 "country", 

101 "postalcode", 

102 "address_string", 

103 "phone", 

104 "latitude", 

105 "longitude", 

106 ] 

107 

108 

109class LocationDetailsTable(APITable): 

110 result_json = [] 

111 

112 def select(self, query: ast.Select) -> pd.DataFrame: 

113 """Select data from the location_details table and return it as a pandas DataFrame. 

114 

115 Args: 

116 query (ast.Select): The SQL query to be executed. 

117 

118 Returns: 

119 pandas.DataFrame: A pandas DataFrame containing the selected data. 

120 """ 

121 

122 self.handler.connect() 

123 

124 conditions = extract_comparison_conditions(query.where) 

125 

126 allowed_keys = set(["locationId", "currency", "language"]) 

127 

128 params = {} 

129 filters = [] 

130 for op, arg1, arg2 in conditions: 

131 if op == "or": 

132 raise NotImplementedError("OR is not supported") 

133 elif op == "=" and arg1 in allowed_keys: 

134 params[arg1] = arg2 

135 elif op != "=": 

136 raise NotImplementedError(f"Unknown op: {op}") 

137 else: 

138 filters.append([op, arg1, arg2]) 

139 

140 if query.limit is not None: 

141 params["max_results"] = query.limit.value 

142 

143 if "locationId" not in params: 

144 # search not works without searchQuery, use 'London' 

145 params["locationId"] = "23322232" 

146 

147 result = self.handler.call_tripadvisor_location_details_api(params=params) 

148 

149 # filter targets 

150 columns = [] 

151 for target in query.targets: 

152 if isinstance(target, ast.Star): 

153 columns = [] 

154 break 

155 elif isinstance(target, ast.Identifier): 

156 columns.append(target.parts[-1]) 

157 else: 

158 raise NotImplementedError 

159 

160 if len(columns) == 0: 

161 columns = self.get_columns() 

162 

163 # columns to lower case 

164 columns = [name.lower() for name in columns] 

165 

166 if len(result) == 0: 

167 result = pd.DataFrame([], columns=columns) 

168 else: 

169 # add absent columns 

170 for col in set(columns) & set(result.columns) ^ set(columns): 

171 result[col] = None 

172 

173 # filter by columns 

174 result = result[columns] 

175 return result 

176 

177 def get_columns(self): 

178 """Get the list of column names for the location_details table. 

179 

180 Returns: 

181 list: A list of column names for the location_details table. 

182 """ 

183 return [ 

184 "location_id", 

185 "distance", 

186 "name", 

187 "description", 

188 "web_url", 

189 "street1", 

190 "street2", 

191 "city", 

192 "state", 

193 "country", 

194 "postalcode", 

195 "address_string", 

196 "latitude", 

197 "longitude", 

198 "timezone", 

199 "email", 

200 "phone", 

201 "website", 

202 "write_review", 

203 "ranking_data", 

204 "rating", 

205 "rating_image_url", 

206 "num_reviews", 

207 "photo_count", 

208 "see_all_photos", 

209 "price_level", 

210 "brand", 

211 "parent_brand", 

212 "ancestors", 

213 "periods", 

214 "weekday", 

215 "features", 

216 "cuisines", 

217 "amenities", 

218 "trip_types", 

219 "styles", 

220 "awards", 

221 "neighborhood_info", 

222 "parent_brand", 

223 "brand", 

224 "groups", 

225 ] 

226 

227 

228class ReviewsTable(APITable): 

229 def select(self, query: ast.Select) -> pd.DataFrame: 

230 """Select data from the reviews table and return it as a pandas DataFrame. 

231 

232 Args: 

233 query (ast.Select): The SQL query to be executed. 

234 

235 Returns: 

236 pandas.DataFrame: A pandas DataFrame containing the selected data. 

237 """ 

238 

239 self.handler.connect() 

240 

241 conditions = extract_comparison_conditions(query.where) 

242 

243 allowed_keys = set(["locationId", "language"]) 

244 

245 params = {} 

246 filters = [] 

247 for op, arg1, arg2 in conditions: 

248 if op == "or": 

249 raise NotImplementedError("OR is not supported") 

250 elif op == "=" and arg1 in allowed_keys: 

251 params[arg1] = arg2 

252 elif op != "=": 

253 raise NotImplementedError(f"Unknown op: {op}") 

254 else: 

255 filters.append([op, arg1, arg2]) 

256 

257 if query.limit is not None: 

258 params["max_results"] = query.limit.value 

259 

260 if "locationId" not in params: 

261 # search not works without searchQuery, use 'London' 

262 params["locationId"] = "23322232" 

263 

264 result = self.handler.call_tripadvisor_reviews_api(params=params) 

265 

266 # filter targets 

267 columns = [] 

268 for target in query.targets: 

269 if isinstance(target, ast.Star): 

270 columns = [] 

271 break 

272 elif isinstance(target, ast.Identifier): 

273 columns.append(target.parts[-1]) 

274 else: 

275 raise NotImplementedError 

276 

277 if len(columns) == 0: 

278 columns = self.get_columns() 

279 

280 # columns to lower case 

281 columns = [name.lower() for name in columns] 

282 

283 if len(result) == 0: 

284 result = pd.DataFrame([], columns=columns) 

285 else: 

286 # add absent columns 

287 for col in set(columns) & set(result.columns) ^ set(columns): 

288 result[col] = None 

289 

290 # filter by columns 

291 result = result[columns] 

292 return result 

293 

294 def get_columns(self): 

295 """Get the list of column names for the reviews table. 

296 

297 Returns: 

298 list: A list of column names for the reviews table. 

299 """ 

300 return [ 

301 "id", 

302 "lang", 

303 "location_id", 

304 "published_date", 

305 "rating", 

306 "helpful_votes", 

307 "rating_image_url", 

308 "url", 

309 "trip_type", 

310 "travel_date", 

311 "text_review", 

312 "title", 

313 "owner_response", 

314 "is_machine_translated", 

315 "user", 

316 "subratings", 

317 ] 

318 

319 

320class PhotosTable(APITable): 

321 def select(self, query: ast.Select) -> pd.DataFrame: 

322 """Select data from the photos table and return it as a pandas DataFrame. 

323 

324 Args: 

325 query (ast.Select): The SQL query to be executed. 

326 

327 Returns: 

328 pandas.DataFrame: A pandas DataFrame containing the selected data. 

329 """ 

330 

331 conditions = extract_comparison_conditions(query.where) 

332 

333 allowed_keys = set(["locationId", "language"]) 

334 

335 params = {} 

336 filters = [] 

337 for op, arg1, arg2 in conditions: 

338 if op == "or": 

339 raise NotImplementedError("OR is not supported") 

340 elif op == "=" and arg1 in allowed_keys: 

341 params[arg1] = arg2 

342 elif op != "=": 

343 raise NotImplementedError(f"Unknown op: {op}") 

344 else: 

345 filters.append([op, arg1, arg2]) 

346 

347 if query.limit is not None: 

348 params["max_results"] = query.limit.value 

349 

350 if "locationId" not in params: 

351 params["locationId"] = "23322232" 

352 

353 result = self.handler.call_tripadvisor_photos_api(params=params) 

354 

355 # filter targets 

356 columns = [] 

357 for target in query.targets: 

358 if isinstance(target, ast.Star): 

359 columns = [] 

360 break 

361 elif isinstance(target, ast.Identifier): 

362 columns.append(target.parts[-1]) 

363 else: 

364 raise NotImplementedError 

365 

366 if len(columns) == 0: 

367 columns = self.get_columns() 

368 

369 # columns to lower case 

370 columns = [name.lower() for name in columns] 

371 

372 if len(result) == 0: 

373 result = pd.DataFrame([], columns=columns) 

374 else: 

375 # add absent columns 

376 for col in set(columns) & set(result.columns) ^ set(columns): 

377 result[col] = None 

378 

379 # filter by columns 

380 result = result[columns] 

381 return result 

382 

383 def get_columns(self): 

384 """Get the list of column names for the photos table. 

385 

386 Returns: 

387 list: A list of column names for the photos table. 

388 """ 

389 return [ 

390 "id", 

391 "is_blessed", 

392 "album", 

393 "caption", 

394 "published_date", 

395 "images", 

396 "source", 

397 "user", 

398 ] 

399 

400 

401class NearbyLocationTable(APITable): 

402 def select(self, query: ast.Select) -> pd.DataFrame: 

403 """Select data from the nearby_location table and return it as a pandas DataFrame. 

404 

405 Args: 

406 query (ast.Select): The SQL query to be executed. 

407 

408 Returns: 

409 pandas.DataFrame: A pandas DataFrame containing the selected data. 

410 """ 

411 

412 conditions = extract_comparison_conditions(query.where) 

413 

414 allowed_keys = set(["latLong", "language", "category", "phone", "address", "radius", "radiusUnit"]) 

415 

416 params = {} 

417 filters = [] 

418 for op, arg1, arg2 in conditions: 

419 if op == "or": 

420 raise NotImplementedError("OR is not supported") 

421 elif op == "=" and arg1 in allowed_keys: 

422 params[arg1] = arg2 

423 elif op != "=": 

424 raise NotImplementedError(f"Unknown op: {op}") 

425 else: 

426 filters.append([op, arg1, arg2]) 

427 

428 if query.limit is not None: 

429 params["max_results"] = query.limit.value 

430 

431 if "latLong" not in params: 

432 params["latLong"] = "40.780825, -73.972781" 

433 

434 result = self.handler.call_tripadvisor_nearby_location_api(params=params) 

435 

436 # filter targets 

437 columns = [] 

438 for target in query.targets: 

439 if isinstance(target, ast.Star): 

440 columns = [] 

441 break 

442 elif isinstance(target, ast.Identifier): 

443 columns.append(target.parts[-1]) 

444 else: 

445 raise NotImplementedError 

446 

447 if len(columns) == 0: 

448 columns = self.get_columns() 

449 

450 # columns to lower case 

451 columns = [name.lower() for name in columns] 

452 

453 if len(result) == 0: 

454 result = pd.DataFrame([], columns=columns) 

455 else: 

456 # add absent columns 

457 for col in set(columns) & set(result.columns) ^ set(columns): 

458 result[col] = None 

459 

460 # filter by columns 

461 result = result[columns] 

462 return result 

463 

464 def get_columns(self): 

465 """Get the list of column names for the nearby_location table. 

466 

467 Returns: 

468 list: A list of column names for the nearby_location table. 

469 """ 

470 return [ 

471 "location_id", 

472 "name", 

473 "distance", 

474 "rating", 

475 "bearing", 

476 "address_obj", 

477 ]