Skip to content

Instantly share code, notes, and snippets.

@lukebarousse
Last active January 13, 2024 09:33
Show Gist options
  • Save lukebarousse/ded1fc3dbde6e0050d45635140480aee to your computer and use it in GitHub Desktop.
Save lukebarousse/ded1fc3dbde6e0050d45635140480aee to your computer and use it in GitHub Desktop.
SerpApi Results to BigQuery - Google Cloud Function
import base64
import pandas as pd
from serpapi import GoogleSearch
from google.cloud import bigquery
import datetime
def hello_pubsub(event, context):
search_term = "data analyst"
search_location = "United States"
for num in range(45):
start = num * 10
params = {
"api_key": "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx", #Fill in with your API key from SerpApi
"device": "desktop",
"engine": "google_jobs",
"google_domain": "google.com",
"q": search_term,
"hl": "en",
"gl": "us",
"location": search_location,
"chips": "date_posted:today",
"start": start,
}
search = GoogleSearch(params)
results = search.get_dict()
# check if the last search page (i.e., no results)
try:
if results['error'] == "Google hasn't returned any results for this query.":
break
except KeyError:
print(f"Getting SerpAPI data for page: {start}")
else:
continue
# create dataframe of 10 pulled results
jobs = results['jobs_results']
jobs = pd.DataFrame(jobs)
jobs = pd.concat([pd.DataFrame(jobs),
pd.json_normalize(jobs['detected_extensions'])],
axis=1).drop('detected_extensions', 1)
jobs['date_time'] = datetime.datetime.utcnow()
# concat dataframe
if start == 0:
jobs_all = jobs
else:
jobs_all = pd.concat([jobs_all, jobs])
jobs_all['search_term'] = search_term
jobs_all['search_location'] = search_location
# send resluts to BigQuery
table_id = "xxxxxxxxxxxxxxxxxxxxxxxx" # BigQuery Table name
client = bigquery.Client()
table = client.get_table(table_id)
errors = client.insert_rows_from_dataframe(table, jobs_all)
if errors == []:
print("Data loaded into table")
return "Success"
else:
print(errors)
return "Failed"
@thomastibouche
Copy link

codes are correct, function is running on GCP but in my system its data is not showing in Bigquery table..why?

Same Issue here, i rechecked the code, Serp API key is correct and Big query table_id too, data just don't shows up in Bigquery no error in the code.

@rohan472000
Copy link

codes are correct, function is running on GCP but in my system its data is not showing in Bigquery table..why?

Same Issue here, i rechecked the code, Serp API key is correct and Big query table_id too, data just don't shows up in Bigquery no error in the code.

well @thomastibouche I tried 2 times but didn't got those data into my BigQuery, then I left because of unavailability of free time to look into it again.
But if you also getting the same error then there is a problem that we aren't aware of..... @lukebarousse ..have you heard this error from anyone else?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment