Python Forum

Full Version: How to fix and extract Google CSE JSON API using Python.exe
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Hi Friends,

I am newbie and came here with a an code error and needs some code corrections to my python script.
I been facing struggling find the correct code.

Below script extracts google CSE Json API search results into output.xlsx file
And list of urls to extract from input file name apis.xlsx

I am facing some problem when this script start extracting
1. It does not output a file with results till where it has extracted before it finds error and stops
2. It does not even show how many urls extracted at the end of output file.
3. Script automatically closes without displaying any errors and output files with results till where it has extracted.
4. It can only open/request one by one url at a time.
5. I need this script to open/request 10 urls at a time

Note: I have pre-constructed urls list in apis.xlsx for example here is the file attached.
Python Script and Sample APIs List

import requests
import json
import openpyxl
from openpyxl import Workbook
from openpyxl.styles import Font
from openpyxl.styles import Alignment
from pathlib import Path

book = Workbook()
sheet = book.active

## Excel styling 
sheet['A1'].value = 'Titles'
sheet['B1'].value = 'Links'
sheet['C1'].value = 'Snippets'
sheet.column_dimensions['A'].width=48
sheet.column_dimensions['B'].width=80
sheet.column_dimensions['C'].width=80
sheet['A1'].font=Font(sz=14, bold=True)
sheet['B1'].font=Font(sz=14, bold=True)
sheet['C1'].font=Font(sz=14, bold=True)
sheet["A1"].alignment=Alignment(horizontal='center')
sheet["B1"].alignment=Alignment(horizontal='center')
sheet["C1"].alignment=Alignment(horizontal='center')


path = Path("./apis.xlsx")


if (path.is_file()):
	# to open the workbook  
	# workbook object is created
	wb_obj = openpyxl.load_workbook(path) 
	sheet_obj = wb_obj.active
else:
	print("File doesn't exists !")

 

urls= []
# print the total number of rows
for i in range(sheet_obj.max_row):
	cell_obj = sheet_obj.cell(row = i+1, column = 1)
	urls.append(cell_obj.value)


## Parsing stage
xlsx = []
for i,url in enumerate(urls):
	response = requests.get(url)
	#print (response)
	content = response.text
	parsed = json.loads(content)

		## Get Title from Nested JSON data

	if (response.status_code==403):
			print(i,"This API requires billing to be enabled on the project. Visit https://console.developers.google.com/billing?project=482194656690 to enable billing.")
			continue
	else:
		#print (int(parsed["searchInformation"]["totalResults"]))
		for i in range(10):

			if ("items" in parsed):
				title = parsed["items"][i]["title"]
				link = parsed["items"][i]["link"]
				snippet = parsed["items"][i]["snippet"]
				print ("- - TITLE - -\n", "title: ", title, "\n")
				print ("- - LINK - -\n", "link: ", link, "\n")
				print ("- - SNIPPET - -\n", "snippet: ", snippet, "\n")
				xlsx.append([title, link, snippet])
			else:
				print("API is empty!")

			## Saving into Excel file
		for i in range(len(xlsx)):
			for j in range(len(xlsx[i])):
				sheet.cell(row=i+2, column=j+1).value = xlsx[i][j]
		book.save(filename='Output.xlsx')
can anyone help me with this script?