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')