May-03-2019, 01:15 PM
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
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')