Skip to content

Instantly share code, notes, and snippets.

@gitgithan

gitgithan/Scraping_Excel_Online_with_Requests.ipynb

Created September 9, 2020 13:36
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save gitgithan/b9f48e1b23e88f1fb1c56ad9b739adef to your computer and use it in GitHub Desktop.
Save gitgithan/b9f48e1b23e88f1fb1c56ad9b739adef to your computer and use it in GitHub Desktop.
Download ZIP
Display the source blob
Display the rendered blob
Raw
Scraping_Excel_Online_with_Requests.ipynb
{
"cells": [
{
"cell_type": "code",
"execution_count": 74,
"metadata": {
"ExecuteTime": {
"end_time": "2020-09-09T13:27:32.428484Z",
"start_time": "2020-09-09T13:27:32.424506Z"
}
},
"outputs": [],
"source": [
"from lxml import etree,html\n",
"import json\n",
"from itertools import repeat, chain\n",
"from concurrent.futures import ThreadPoolExecutor\n",
"\n",
"import requests\n",
"import pandas as pd\n",
"\n",
"from IPython.core.interactiveshell import InteractiveShell\n",
"InteractiveShell.ast_node_interactivity = \"all\" \n",
"\n",
"columns = ['RAMA',\n",
" 'AGENCIA7',\n",
" 'AGENCIA',\n",
" 'NOMBRE_AGENCIA',\n",
" 'NUM_PROGRAMA',\n",
" 'NOMBRE_PROGRAMA',\n",
" 'FONDO',\n",
" 'CODIGO_CONCEPTO',\n",
" 'NUM_CUENTA',\n",
" 'DESCRIPCION',\n",
" 'JOINT_RESOLUTION_CATEGORY',\n",
" 'AF_2020_APR',\n",
" 'AF_2021_FOMB Baseline',\n",
" 'AF_2021_Propuesto Gob']"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Testing requests \n",
"\n",
"1. Visit http://www.presupuesto.pr.gov/PRESUPUESTOPROPUESTO2020-2021/_layouts/15/WopiFrame.aspx?sourcedoc=%7B566feecf-1e0d-46b8-a505-7cd762665268%7D&action=edit&source=http%3A%2F%2Fwww%2Epresupuesto%2Epr%2Egov%2FPRESUPUESTOPROPUESTO2020%2D2021%2FFOMB%2520Budget%2520Requirements%2520FY%25202021%2FForms%2FAllItems%2Easpx%3FRootFolder%3D%252FPRESUPUESTOPROPUESTO2020%252D2021%252FFOMB%2520Budget%2520Requirements%2520FY%25202021\n",
"\n",
"2. Filter/Look for `GetRangeContent` in the network tab \n",
"3. `Copy as cURL(bash)` from Chrome Developer Tools into https://curl.trillworks.com/ and paste generated code below"
]
},
{
"cell_type": "code",
"execution_count": 75,
"metadata": {
"ExecuteTime": {
"end_time": "2020-09-09T13:27:35.625797Z",
"start_time": "2020-09-09T13:27:35.121998Z"
}
},
"outputs": [],
"source": [
"import requests\n",
"\n",
"cookies = {\n",
" '_ga': 'GA1.2.303617128.1598854462',\n",
" 'DcLcid': 'ui=1033&data=1033',\n",
"}\n",
"\n",
"headers = {\n",
" 'Connection': 'keep-alive',\n",
" 'X-WacNoAuth': '1',\n",
" 'X-AccessToken': 'eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsIng1dCI6Inh6NEZ3LW04c1JaTE03c1M1bnNwcXQzaTlKZyJ9.eyJhdWQiOiJ3b3BpL3d3dy5wcmVzdXB1ZXN0by5wci5nb3ZAMzMxY2ZhNTAtM2NiYi00M2U4LWIzZmYtOGJiOWE4MmI5ZThkIiwiaXNzIjoiMDAwMDAwMDMtMDAwMC0wZmYxLWNlMDAtMDAwMDAwMDAwMDAwQDMzMWNmYTUwLTNjYmItNDNlOC1iM2ZmLThiYjlhODJiOWU4ZCIsIm5iZiI6IjE1OTk2NTc1MzMiLCJleHAiOiIxNTk5NjkzNTMzIiwibmFtZWlkIjoiQCIsIm5paSI6Im1pY3Jvc29mdC5zaGFyZXBvaW50IiwiaXN1c2VyIjoidHJ1ZSIsImlzbG9vcGJhY2siOiJUcnVlIiwiYXBwY3R4IjoiNTY2ZmVlY2YxZTBkNDZiOGE1MDU3Y2Q3NjI2NjUyNjg7S0tTbnZqK0hyVWRqd0E4amNMVHRkelIvRm9FPTtEZWZhdWx0OzsxMDAwMDMxMDQxO1RydWU7In0.mMRAS06tZUVwhxnj64M50u4IYwSsHliKhk1vtTYqHRJ73Lt6Mo83Y6s8YEtXfQf11g5PQvLPFE0qUf72gkjTgsvNkh3jz8s7BuyHaJoXdRm_HY1XdoEKaOacpZXr1tdeIswW3qMIv_h7VPddds8v9oryGiiaoPESWXbCfuhgf_dZInIyrHYNs34-lAa0VOBqOX4NOGNzqtRCkG2YChHZDHq8l4F0Dn5-roYIMRA6YasixPVL-dFgNl0kakAw4Ryn5kcQSWYo4QQEMLmRRPAHp17Bg1LkoEm_iVsXADWZL1eguHMGa6k5cMQSvp9zAWGj6tu8y6NbhXvXbtfNS7KACw',\n",
" 'X-UserSessionId': '1b53789f-9970-d0e8-8a7d-a5610b4128c8',\n",
" 'X-OfficeVersion': '16.0.8471.8525',\n",
" 'X-Key': '',\n",
" 'Content-Type': 'application/json; charset=utf-8',\n",
" 'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/85.0.4183.83 Safari/537.36',\n",
" 'X-Requested-With': 'XMLHttpRequest',\n",
" 'X-UserType': 'WOPI',\n",
" 'X-xhr': '1',\n",
" 'X-XL-SessionId': '32.d29cbc9c8f8c41ba8be0791e875a799b1.A87.1.V23.154cu/rV0Ji5XiY/QuB4nS/14.5.en-US5.en-US32.92602af5d3f6481ba973828cb9649e0d1.S1.N14.16.0.8471.852514.5.en-US5.en-US1.V1.N0.1.S',\n",
" 'Accept': '*/*',\n",
" 'Accept-Language': 'en-US,en;q=0.9',\n",
"}\n",
"\n",
"params = (\n",
" ('context', '{\"SessionId\":\"32.d29cbc9c8f8c41ba8be0791e875a799b1.A87.1.V23.154cu/rV0Ji5XiY/QuB4nS/14.5.en-US5.en-US32.92602af5d3f6481ba973828cb9649e0d1.S1.N14.16.0.8471.852514.5.en-US5.en-US1.V1.N0.1.S\",\"TransientEditSessionToken\":null,\"PermissionFlags\":786361,\"Configurations\":1577744,\"CompleteResponseTimeout\":0,\"CollaborationParameter\":{},\"WorkbookMetadataParameter\":{\"WorkbookMetadataState\":{\"MetadataVersion\":0,\"ServerEventVersion\":0}},\"MachineCluster\":\"\",\"AjaxOptions\":0,\"ViewModeStateId\":0,\"OperationVersion\":null,\"paneType\":0,\"CellHtml\":null}'),\n",
" ('ewaControlId', '\"m_excelWebRenderer_ewaCtl_m_ewa\"'),\n",
" ('currentObject', '\"08-May-2020\"'),\n",
" ('namedObjectViewData', '{\"Mode\":1,\"Settings\":2}'),\n",
" ('row', '0'),\n",
" ('column', '0'),\n",
" ('rowCount', '28'),\n",
" ('columnCount', '30'),\n",
" ('blockPosition', '{\"X\":0,\"Y\":0,\"PaneType\":1}'),\n",
" ('revision', '0'),\n",
" ('previousRevision', '-1'),\n",
" ('digest', 'null'),\n",
" ('renderingOptions', '0'),\n",
" ('colorScheme', 'null'),\n",
" ('WacCluster', ''),\n",
")\n",
"\n",
"response = requests.get('http://azure.oos.pr.gov/x/_vti_bin/DynamicGridContent.json/GetRangeContent', headers=headers, params=params, cookies=cookies, verify=False)\n",
"\n",
"#NB. Original query string below. It seems impossible to parse and\n",
"#reproduce query strings 100% accurately so the one below is given\n",
"#in case the reproduced version is not \"correct\".\n",
"# response = requests.get('http://azure.oos.pr.gov/x/_vti_bin/DynamicGridContent.json/GetRangeContent?context=%7B%22SessionId%22%3A%2232.d29cbc9c8f8c41ba8be0791e875a799b1.A87.1.V23.154cu%2FrV0Ji5XiY%2FQuB4nS%2F14.5.en-US5.en-US32.92602af5d3f6481ba973828cb9649e0d1.S1.N14.16.0.8471.852514.5.en-US5.en-US1.V1.N0.1.S%22%2C%22TransientEditSessionToken%22%3Anull%2C%22PermissionFlags%22%3A786361%2C%22Configurations%22%3A1577744%2C%22CompleteResponseTimeout%22%3A0%2C%22CollaborationParameter%22%3A%7B%7D%2C%22WorkbookMetadataParameter%22%3A%7B%22WorkbookMetadataState%22%3A%7B%22MetadataVersion%22%3A0%2C%22ServerEventVersion%22%3A0%7D%7D%2C%22MachineCluster%22%3A%22%22%2C%22AjaxOptions%22%3A0%2C%22ViewModeStateId%22%3A0%2C%22OperationVersion%22%3Anull%2C%22paneType%22%3A0%2C%22CellHtml%22%3Anull%7D&ewaControlId=%22m_excelWebRenderer_ewaCtl_m_ewa%22&currentObject=%2208-May-2020%22&namedObjectViewData=%7B%22Mode%22%3A1%2C%22Settings%22%3A2%7D&row=0&column=0&rowCount=28&columnCount=30&blockPosition=%7B%22X%22%3A0%2C%22Y%22%3A0%2C%22PaneType%22%3A1%7D&revision=0&previousRevision=-1&digest=null&renderingOptions=0&colorScheme=null&WacCluster=', headers=headers, cookies=cookies, verify=False)\n"
]
},
{
"cell_type": "code",
"execution_count": 35,
"metadata": {
"ExecuteTime": {
"end_time": "2020-09-09T13:12:28.707055Z",
"start_time": "2020-09-09T13:12:28.703067Z"
}
},
"outputs": [
{
"data": {
"text/plain": [
"{'Cache-Control': 'private, max-age=604800', 'Content-Type': 'text/xml; charset=utf-8', 'Content-Encoding': 'gzip', 'Vary': 'Accept-Encoding', 'Server': 'Microsoft-IIS/10.0', 'Timing-Allow-Origin': '*', 'X-CorrelationId': '51399f1c-5ec6-471e-9abe-b4813f7cbc25', 'X-OfficeFE': 'd29cbc9c8f8c41ba8be0791e875a799b', 'X-OfficeVersion': '16.0.8471.8525', 'P3P': 'CP=\"CAO DSP COR ADMa DEV CONi TELi CUR PSA PSD TAI IVDi OUR SAMi BUS DEM NAV STA UNI COM INT PHY ONL FIN PUR\"', 'x-content-type-options': 'nosniff', 'XlsEcsCid': '51399f1c-5ec6-471e-9abe-b4813f7cbc25', 'XlsWfeCid': '51399f1c-5ec6-471e-9abe-b4813f7cbc25', 'XlsEcsId': 'd29cbc9c8f8c41ba8be0791e875a799b', 'X-AspNet-Version': '4.0.30319', 'X-Powered-By': 'ASP.NET', 'Date': 'Wed, 09 Sep 2020 13:08:06 GMT', 'Content-Length': '8358'}"
]
},
"execution_count": 35,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"len(response.text)\n",
"response.headers"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Defining functions "
]
},
{
"cell_type": "code",
"execution_count": 77,
"metadata": {
"ExecuteTime": {
"end_time": "2020-09-09T13:27:48.022121Z",
"start_time": "2020-09-09T13:27:48.013124Z"
}
},
"outputs": [],
"source": [
"def make_requests(rowCount): \n",
" headers = {\n",
" 'Content-Type': 'application/json; charset=utf-8'\n",
" }\n",
" row = 0\n",
" all_responses = []\n",
" while row < 15253:\n",
" params = (\n",
" ('context', '{\"SessionId\":\"32.d29cbc9c8f8c41ba8be0791e875a799b1.A87.1.V23.154cu/rV0Ji5XiY/QuB4nS/14.5.en-US5.en-US32.92602af5d3f6481ba973828cb9649e0d1.S1.N14.16.0.8471.852514.5.en-US5.en-US1.V1.N0.1.S\",\"TransientEditSessionToken\":null,\"PermissionFlags\":786361,\"Configurations\":1577744,\"CompleteResponseTimeout\":0,\"CollaborationParameter\":{},\"WorkbookMetadataParameter\":{\"WorkbookMetadataState\":{\"MetadataVersion\":0,\"ServerEventVersion\":0}},\"MachineCluster\":\"\",\"AjaxOptions\":0,\"ViewModeStateId\":0,\"OperationVersion\":null,\"paneType\":0,\"CellHtml\":null}'),\n",
" ('ewaControlId', '\"m_excelWebRenderer_ewaCtl_m_ewa\"'),\n",
" ('currentObject', '\"08-May-2020\"'),\n",
" ('namedObjectViewData', '{\"Mode\":1,\"Settings\":2}'),\n",
" ('row', f'{row}'),\n",
" ('column', '0'),\n",
" ('rowCount', f'{rowCount}'),\n",
" ('columnCount', '30'),\n",
" ('blockPosition', '{\"X\":0,\"Y\":3,\"PaneType\":1}'),\n",
" ('revision', '0'),\n",
" ('previousRevision', '-1'),\n",
" ('digest', 'null'),\n",
" ('renderingOptions', '0'),\n",
" ('colorScheme', 'null'),\n",
" )\n",
" response = requests.get('http://azure.oos.pr.gov/x/_vti_bin/DynamicGridContent.json/GetRangeContent', headers=headers, params=params, verify=False)\n",
" all_responses.append(response)\n",
" row += rowCount\n",
" print('Row after add is :',row)\n",
" \n",
" return all_responses\n",
"\n",
"def clean_data(gridhtml):\n",
" print('cleaning block')\n",
" data = []\n",
" row_elements = gridhtml.xpath(\"//div[contains(@class, 'ewr-nglr')]\")\n",
" \n",
" for row in row_elements:\n",
" row_text = row.xpath(\".//text()\")\n",
" \n",
" if len(row_text) > 0:\n",
" if len(row_text) < 14:\n",
" row_text.insert(0,'')\n",
" data.append(row_text)\n",
" \n",
" return data\n",
"\n",
"def parse_response(response, rowCount):\n",
"# # [38:] to throw away <?xml version=\"1.0\" encoding=\"utf-8\"?> to prevent ValueError: Unicode strings with encoding declaration are not supported. Please use bytes input or XML fragments without declaration\n",
"# root = etree.fromstring(response.text[38:]) \n",
"# gridhtml = html.fromstring(json.loads(root.find('Json').text)['Result']['Content']['GridHtml']) # html.fromstring rather than etree.fromstring to handle XMLSyntaxError: Entity 'nbsp' not defined\n",
" \n",
" # [166:-25] to directly extract CDATA section to prevent etree.fromstring() giving XMLSyntaxError: CData section too big found when requesting more than 3845 rows at once.\n",
" gridhtml = html.fromstring(json.loads(response.text[166:-25])['Result']['Content']['GridHtml']) \n",
" \n",
" iterable = gridhtml.xpath(\"//text()\")\n",
" \n",
" if len(iterable) < rowCount*14:\n",
" data = clean_data(gridhtml) \n",
" else:\n",
" data = zip(*[iter(iterable)]*14)\n",
" \n",
" return data"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Executing singlethreaded pipeline (~30 secs)"
]
},
{
"cell_type": "code",
"execution_count": 78,
"metadata": {
"ExecuteTime": {
"end_time": "2020-09-09T13:28:20.937486Z",
"start_time": "2020-09-09T13:27:50.899373Z"
},
"scrolled": true
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Row after add is : 2000\n",
"Row after add is : 4000\n",
"Row after add is : 6000\n",
"Row after add is : 8000\n",
"Row after add is : 10000\n",
"Row after add is : 12000\n",
"Row after add is : 14000\n",
"Row after add is : 16000\n",
"cleaning block\n",
"cleaning block\n"
]
},
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>RAMA</th>\n",
" <th>AGENCIA7</th>\n",
" <th>AGENCIA</th>\n",
" <th>NOMBRE_AGENCIA</th>\n",
" <th>NUM_PROGRAMA</th>\n",
" <th>NOMBRE_PROGRAMA</th>\n",
" <th>FONDO</th>\n",
" <th>CODIGO_CONCEPTO</th>\n",
" <th>NUM_CUENTA</th>\n",
" <th>DESCRIPCION</th>\n",
" <th>JOINT_RESOLUTION_CATEGORY</th>\n",
" <th>AF_2020_APR</th>\n",
" <th>AF_2021_FOMB Baseline</th>\n",
" <th>AF_2021_Propuesto Gob</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>E</td>\n",
" <td>0150000</td>\n",
" <td>015</td>\n",
" <td>Oficina del Gobernador</td>\n",
" <td>2075</td>\n",
" <td>Comunicación y Servicios al Ciudadano</td>\n",
" <td>AE</td>\n",
" <td>OG</td>\n",
" <td>2970_7</td>\n",
" <td>Otros Gastos No Clasificados</td>\n",
" <td>Other operating expenses</td>\n",
" <td>1000</td>\n",
" <td>516</td>\n",
" <td>516</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>E</td>\n",
" <td>0170000</td>\n",
" <td>017</td>\n",
" <td>Asignaciones bajo la Custodia de la Oficina de...</td>\n",
" <td>1248</td>\n",
" <td>Gerencia Gubernamental-Recursos Bajo la Custod...</td>\n",
" <td>AE</td>\n",
" <td>D</td>\n",
" <td>6310</td>\n",
" <td>Sentencias e Indemnizaciones</td>\n",
" <td>Other donations and subsidies</td>\n",
" <td>3500</td>\n",
" <td>3500</td>\n",
" <td>3500</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>E</td>\n",
" <td>0170000</td>\n",
" <td>017</td>\n",
" <td>Asignaciones bajo la Custodia de la Oficina de...</td>\n",
" <td>1248</td>\n",
" <td>Gerencia Gubernamental-Recursos Bajo la Custod...</td>\n",
" <td>AE</td>\n",
" <td>D</td>\n",
" <td>6890</td>\n",
" <td>Otros subsidios - no clasificados</td>\n",
" <td>Other donations and subsidies</td>\n",
" <td>105484</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>E</td>\n",
" <td>0180000</td>\n",
" <td>018</td>\n",
" <td>Junta de Planificación</td>\n",
" <td>1353</td>\n",
" <td>Planificación Física</td>\n",
" <td>AE</td>\n",
" <td>AENG</td>\n",
" <td>6170_14</td>\n",
" <td>Donativos y Aportaciones a individuos y entida...</td>\n",
" <td>Appropriations to Non-Governmental entities</td>\n",
" <td>45</td>\n",
" <td>54</td>\n",
" <td>61</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>E</td>\n",
" <td>0180000</td>\n",
" <td>018</td>\n",
" <td>Junta de Planificación</td>\n",
" <td>2042</td>\n",
" <td>Planificación Económica y Social</td>\n",
" <td>AE</td>\n",
" <td>AENG</td>\n",
" <td>6190_14</td>\n",
" <td>Donativos y Aportaciones a Individuos excepto ...</td>\n",
" <td>Appropriations to Non-Governmental entities</td>\n",
" <td>77</td>\n",
" <td>215</td>\n",
" <td>61</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>15248</th>\n",
" <td>E</td>\n",
" <td>3290000</td>\n",
" <td>329</td>\n",
" <td>Oficina de Desarrollo Socioeconómico</td>\n",
" <td>1632</td>\n",
" <td>Desarrollo de Asuntos Comunitarios y el Tercer...</td>\n",
" <td>RC</td>\n",
" <td>SC</td>\n",
" <td>2780</td>\n",
" <td>Conservación y Reparación otros equipos - por ...</td>\n",
" <td>Maintenance &amp; Repairs</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>15249</th>\n",
" <td>E</td>\n",
" <td>3290000</td>\n",
" <td>329</td>\n",
" <td>Oficina de Desarrollo Socioeconómico</td>\n",
" <td>1632</td>\n",
" <td>Desarrollo de Asuntos Comunitarios y el Tercer...</td>\n",
" <td>RC</td>\n",
" <td>SC</td>\n",
" <td>2880</td>\n",
" <td>Primas de fianzas de fidelidad</td>\n",
" <td>PRIMAS</td>\n",
" <td>67</td>\n",
" <td>17</td>\n",
" <td>17</td>\n",
" </tr>\n",
" <tr>\n",
" <th>15250</th>\n",
" <td>E</td>\n",
" <td>3290000</td>\n",
" <td>329</td>\n",
" <td>Oficina de Desarrollo Socioeconómico</td>\n",
" <td>1632</td>\n",
" <td>Desarrollo de Asuntos Comunitarios y el Tercer...</td>\n",
" <td>RC</td>\n",
" <td>SP</td>\n",
" <td>1210_6</td>\n",
" <td>Servicios Legales</td>\n",
" <td>Legal expenses</td>\n",
" <td>0</td>\n",
" <td>18</td>\n",
" <td>18</td>\n",
" </tr>\n",
" <tr>\n",
" <th>15251</th>\n",
" <td>E</td>\n",
" <td>3290000</td>\n",
" <td>329</td>\n",
" <td>Oficina de Desarrollo Socioeconómico</td>\n",
" <td>1632</td>\n",
" <td>Desarrollo de Asuntos Comunitarios y el Tercer...</td>\n",
" <td>RC</td>\n",
" <td>SP</td>\n",
" <td>1290</td>\n",
" <td>Servicios profesionales y consultivos - no cla...</td>\n",
" <td>Other professional service fees</td>\n",
" <td>54</td>\n",
" <td>341</td>\n",
" <td>341</td>\n",
" </tr>\n",
" <tr>\n",
" <th>15252</th>\n",
" <td>E</td>\n",
" <td>9280000</td>\n",
" <td>928</td>\n",
" <td>Administración del Sistema de Retiro de Emplea...</td>\n",
" <td>4073</td>\n",
" <td>Administración del  Sistema de Retiro de Emple...</td>\n",
" <td>RC</td>\n",
" <td>PP</td>\n",
" <td>7003</td>\n",
" <td>Pensiones del ERS</td>\n",
" <td>PayGo</td>\n",
" <td>8681</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>15253 rows × 14 columns</p>\n",
"</div>"
],
"text/plain": [
" RAMA AGENCIA7 AGENCIA \\\n",
"0 E 0150000 015 \n",
"1 E 0170000 017 \n",
"2 E 0170000 017 \n",
"3 E 0180000 018 \n",
"4 E 0180000 018 \n",
"... ... ... ... \n",
"15248 E 3290000 329 \n",
"15249 E 3290000 329 \n",
"15250 E 3290000 329 \n",
"15251 E 3290000 329 \n",
"15252 E 9280000 928 \n",
"\n",
" NOMBRE_AGENCIA NUM_PROGRAMA \\\n",
"0 Oficina del Gobernador 2075 \n",
"1 Asignaciones bajo la Custodia de la Oficina de... 1248 \n",
"2 Asignaciones bajo la Custodia de la Oficina de... 1248 \n",
"3 Junta de Planificación 1353 \n",
"4 Junta de Planificación 2042 \n",
"... ... ... \n",
"15248 Oficina de Desarrollo Socioeconómico 1632 \n",
"15249 Oficina de Desarrollo Socioeconómico 1632 \n",
"15250 Oficina de Desarrollo Socioeconómico 1632 \n",
"15251 Oficina de Desarrollo Socioeconómico 1632 \n",
"15252 Administración del Sistema de Retiro de Emplea... 4073 \n",
"\n",
" NOMBRE_PROGRAMA FONDO \\\n",
"0 Comunicación y Servicios al Ciudadano AE \n",
"1 Gerencia Gubernamental-Recursos Bajo la Custod... AE \n",
"2 Gerencia Gubernamental-Recursos Bajo la Custod... AE \n",
"3 Planificación Física AE \n",
"4 Planificación Económica y Social AE \n",
"... ... ... \n",
"15248 Desarrollo de Asuntos Comunitarios y el Tercer... RC \n",
"15249 Desarrollo de Asuntos Comunitarios y el Tercer... RC \n",
"15250 Desarrollo de Asuntos Comunitarios y el Tercer... RC \n",
"15251 Desarrollo de Asuntos Comunitarios y el Tercer... RC \n",
"15252 Administración del  Sistema de Retiro de Emple... RC \n",
"\n",
" CODIGO_CONCEPTO NUM_CUENTA \\\n",
"0 OG 2970_7 \n",
"1 D 6310 \n",
"2 D 6890 \n",
"3 AENG 6170_14 \n",
"4 AENG 6190_14 \n",
"... ... ... \n",
"15248 SC 2780 \n",
"15249 SC 2880 \n",
"15250 SP 1210_6 \n",
"15251 SP 1290 \n",
"15252 PP 7003 \n",
"\n",
" DESCRIPCION \\\n",
"0 Otros Gastos No Clasificados \n",
"1 Sentencias e Indemnizaciones \n",
"2 Otros subsidios - no clasificados \n",
"3 Donativos y Aportaciones a individuos y entida... \n",
"4 Donativos y Aportaciones a Individuos excepto ... \n",
"... ... \n",
"15248 Conservación y Reparación otros equipos - por ... \n",
"15249 Primas de fianzas de fidelidad \n",
"15250 Servicios Legales \n",
"15251 Servicios profesionales y consultivos - no cla... \n",
"15252 Pensiones del ERS \n",
"\n",
" JOINT_RESOLUTION_CATEGORY AF_2020_APR \\\n",
"0 Other operating expenses 1000 \n",
"1 Other donations and subsidies 3500 \n",
"2 Other donations and subsidies 105484 \n",
"3 Appropriations to Non-Governmental entities 45 \n",
"4 Appropriations to Non-Governmental entities 77 \n",
"... ... ... \n",
"15248 Maintenance & Repairs 1 \n",
"15249 PRIMAS 67 \n",
"15250 Legal expenses 0 \n",
"15251 Other professional service fees 54 \n",
"15252 PayGo 8681 \n",
"\n",
" AF_2021_FOMB Baseline AF_2021_Propuesto Gob \n",
"0 516 516 \n",
"1 3500 3500 \n",
"2 0 0 \n",
"3 54 61 \n",
"4 215 61 \n",
"... ... ... \n",
"15248 1 1 \n",
"15249 17 17 \n",
"15250 18 18 \n",
"15251 341 341 \n",
"15252 0 0 \n",
"\n",
"[15253 rows x 14 columns]"
]
},
"execution_count": 78,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"rowCount = 2000\n",
"\n",
"all_responses = make_requests(rowCount=rowCount)\n",
"parsed_responses = [parse_response(response,rowCount) for response in all_responses] \n",
"df = pd.DataFrame(chain.from_iterable(parsed_responses),columns=columns)\n",
"df"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Going Multithreaded (~5 secs)"
]
},
{
"cell_type": "code",
"execution_count": 79,
"metadata": {
"ExecuteTime": {
"end_time": "2020-09-09T13:28:20.942470Z",
"start_time": "2020-09-09T13:28:20.938449Z"
}
},
"outputs": [],
"source": [
"def make_requests_multithread(rowCount, row): \n",
" headers = {\n",
" 'Content-Type': 'application/json; charset=utf-8'\n",
" }\n",
" params = (\n",
" ('context', '{\"SessionId\":\"32.d29cbc9c8f8c41ba8be0791e875a799b1.A87.1.V23.154cu/rV0Ji5XiY/QuB4nS/14.5.en-US5.en-US32.92602af5d3f6481ba973828cb9649e0d1.S1.N14.16.0.8471.852514.5.en-US5.en-US1.V1.N0.1.S\",\"TransientEditSessionToken\":null,\"PermissionFlags\":786361,\"Configurations\":1577744,\"CompleteResponseTimeout\":0,\"CollaborationParameter\":{},\"WorkbookMetadataParameter\":{\"WorkbookMetadataState\":{\"MetadataVersion\":0,\"ServerEventVersion\":0}},\"MachineCluster\":\"\",\"AjaxOptions\":0,\"ViewModeStateId\":0,\"OperationVersion\":null,\"paneType\":0,\"CellHtml\":null}'),\n",
" ('ewaControlId', '\"m_excelWebRenderer_ewaCtl_m_ewa\"'),\n",
" ('currentObject', '\"08-May-2020\"'),\n",
" ('namedObjectViewData', '{\"Mode\":1,\"Settings\":2}'),\n",
" ('row', f'{row}'),\n",
" ('column', '0'),\n",
" ('rowCount', f'{rowCount}'),\n",
" ('columnCount', '30'),\n",
" ('blockPosition', '{\"X\":0,\"Y\":3,\"PaneType\":1}'),\n",
" ('revision', '0'),\n",
" ('previousRevision', '-1'),\n",
" ('digest', 'null'),\n",
" ('renderingOptions', '0'),\n",
" ('colorScheme', 'null'),\n",
" )\n",
" response = requests.get('http://azure.oos.pr.gov/x/_vti_bin/DynamicGridContent.json/GetRangeContent', headers=headers, params=params, verify=False)\n",
" return response"
]
},
{
"cell_type": "code",
"execution_count": 29,
"metadata": {
"ExecuteTime": {
"end_time": "2020-09-09T13:03:02.871713Z",
"start_time": "2020-09-09T13:03:00.388124Z"
}
},
"outputs": [],
"source": [
"rowCount = 350\n",
"with ThreadPoolExecutor(max_workers=50) as executor:\n",
" args = zip(repeat(rowCount),range(0,15253,rowCount))\n",
" all_responses = executor.map(lambda p: make_requests_multithread(*p),args)"
]
},
{
"cell_type": "code",
"execution_count": 80,
"metadata": {
"ExecuteTime": {
"end_time": "2020-09-09T13:28:39.236620Z",
"start_time": "2020-09-09T13:28:36.760753Z"
}
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"cleaning block\n",
"cleaning block\n"
]
},
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>RAMA</th>\n",
" <th>AGENCIA7</th>\n",
" <th>AGENCIA</th>\n",
" <th>NOMBRE_AGENCIA</th>\n",
" <th>NUM_PROGRAMA</th>\n",
" <th>NOMBRE_PROGRAMA</th>\n",
" <th>FONDO</th>\n",
" <th>CODIGO_CONCEPTO</th>\n",
" <th>NUM_CUENTA</th>\n",
" <th>DESCRIPCION</th>\n",
" <th>JOINT_RESOLUTION_CATEGORY</th>\n",
" <th>AF_2020_APR</th>\n",
" <th>AF_2021_FOMB Baseline</th>\n",
" <th>AF_2021_Propuesto Gob</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>E</td>\n",
" <td>0150000</td>\n",
" <td>015</td>\n",
" <td>Oficina del Gobernador</td>\n",
" <td>2075</td>\n",
" <td>Comunicación y Servicios al Ciudadano</td>\n",
" <td>AE</td>\n",
" <td>OG</td>\n",
" <td>2970_7</td>\n",
" <td>Otros Gastos No Clasificados</td>\n",
" <td>Other operating expenses</td>\n",
" <td>1000</td>\n",
" <td>516</td>\n",
" <td>516</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>E</td>\n",
" <td>0170000</td>\n",
" <td>017</td>\n",
" <td>Asignaciones bajo la Custodia de la Oficina de...</td>\n",
" <td>1248</td>\n",
" <td>Gerencia Gubernamental-Recursos Bajo la Custod...</td>\n",
" <td>AE</td>\n",
" <td>D</td>\n",
" <td>6310</td>\n",
" <td>Sentencias e Indemnizaciones</td>\n",
" <td>Other donations and subsidies</td>\n",
" <td>3500</td>\n",
" <td>3500</td>\n",
" <td>3500</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>E</td>\n",
" <td>0170000</td>\n",
" <td>017</td>\n",
" <td>Asignaciones bajo la Custodia de la Oficina de...</td>\n",
" <td>1248</td>\n",
" <td>Gerencia Gubernamental-Recursos Bajo la Custod...</td>\n",
" <td>AE</td>\n",
" <td>D</td>\n",
" <td>6890</td>\n",
" <td>Otros subsidios - no clasificados</td>\n",
" <td>Other donations and subsidies</td>\n",
" <td>105484</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>E</td>\n",
" <td>0180000</td>\n",
" <td>018</td>\n",
" <td>Junta de Planificación</td>\n",
" <td>1353</td>\n",
" <td>Planificación Física</td>\n",
" <td>AE</td>\n",
" <td>AENG</td>\n",
" <td>6170_14</td>\n",
" <td>Donativos y Aportaciones a individuos y entida...</td>\n",
" <td>Appropriations to Non-Governmental entities</td>\n",
" <td>45</td>\n",
" <td>54</td>\n",
" <td>61</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>E</td>\n",
" <td>0180000</td>\n",
" <td>018</td>\n",
" <td>Junta de Planificación</td>\n",
" <td>2042</td>\n",
" <td>Planificación Económica y Social</td>\n",
" <td>AE</td>\n",
" <td>AENG</td>\n",
" <td>6190_14</td>\n",
" <td>Donativos y Aportaciones a Individuos excepto ...</td>\n",
" <td>Appropriations to Non-Governmental entities</td>\n",
" <td>77</td>\n",
" <td>215</td>\n",
" <td>61</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>15248</th>\n",
" <td>E</td>\n",
" <td>3290000</td>\n",
" <td>329</td>\n",
" <td>Oficina de Desarrollo Socioeconómico</td>\n",
" <td>1632</td>\n",
" <td>Desarrollo de Asuntos Comunitarios y el Tercer...</td>\n",
" <td>RC</td>\n",
" <td>SC</td>\n",
" <td>2780</td>\n",
" <td>Conservación y Reparación otros equipos - por ...</td>\n",
" <td>Maintenance &amp; Repairs</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" <td>1</td>\n",
" </tr>\n",
" <tr>\n",
" <th>15249</th>\n",
" <td>E</td>\n",
" <td>3290000</td>\n",
" <td>329</td>\n",
" <td>Oficina de Desarrollo Socioeconómico</td>\n",
" <td>1632</td>\n",
" <td>Desarrollo de Asuntos Comunitarios y el Tercer...</td>\n",
" <td>RC</td>\n",
" <td>SC</td>\n",
" <td>2880</td>\n",
" <td>Primas de fianzas de fidelidad</td>\n",
" <td>PRIMAS</td>\n",
" <td>67</td>\n",
" <td>17</td>\n",
" <td>17</td>\n",
" </tr>\n",
" <tr>\n",
" <th>15250</th>\n",
" <td>E</td>\n",
" <td>3290000</td>\n",
" <td>329</td>\n",
" <td>Oficina de Desarrollo Socioeconómico</td>\n",
" <td>1632</td>\n",
" <td>Desarrollo de Asuntos Comunitarios y el Tercer...</td>\n",
" <td>RC</td>\n",
" <td>SP</td>\n",
" <td>1210_6</td>\n",
" <td>Servicios Legales</td>\n",
" <td>Legal expenses</td>\n",
" <td>0</td>\n",
" <td>18</td>\n",
" <td>18</td>\n",
" </tr>\n",
" <tr>\n",
" <th>15251</th>\n",
" <td>E</td>\n",
" <td>3290000</td>\n",
" <td>329</td>\n",
" <td>Oficina de Desarrollo Socioeconómico</td>\n",
" <td>1632</td>\n",
" <td>Desarrollo de Asuntos Comunitarios y el Tercer...</td>\n",
" <td>RC</td>\n",
" <td>SP</td>\n",
" <td>1290</td>\n",
" <td>Servicios profesionales y consultivos - no cla...</td>\n",
" <td>Other professional service fees</td>\n",
" <td>54</td>\n",
" <td>341</td>\n",
" <td>341</td>\n",
" </tr>\n",
" <tr>\n",
" <th>15252</th>\n",
" <td>E</td>\n",
" <td>9280000</td>\n",
" <td>928</td>\n",
" <td>Administración del Sistema de Retiro de Emplea...</td>\n",
" <td>4073</td>\n",
" <td>Administración del  Sistema de Retiro de Emple...</td>\n",
" <td>RC</td>\n",
" <td>PP</td>\n",
" <td>7003</td>\n",
" <td>Pensiones del ERS</td>\n",
" <td>PayGo</td>\n",
" <td>8681</td>\n",
" <td>0</td>\n",
" <td>0</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>15253 rows × 14 columns</p>\n",
"</div>"
],
"text/plain": [
" RAMA AGENCIA7 AGENCIA \\\n",
"0 E 0150000 015 \n",
"1 E 0170000 017 \n",
"2 E 0170000 017 \n",
"3 E 0180000 018 \n",
"4 E 0180000 018 \n",
"... ... ... ... \n",
"15248 E 3290000 329 \n",
"15249 E 3290000 329 \n",
"15250 E 3290000 329 \n",
"15251 E 3290000 329 \n",
"15252 E 9280000 928 \n",
"\n",
" NOMBRE_AGENCIA NUM_PROGRAMA \\\n",
"0 Oficina del Gobernador 2075 \n",
"1 Asignaciones bajo la Custodia de la Oficina de... 1248 \n",
"2 Asignaciones bajo la Custodia de la Oficina de... 1248 \n",
"3 Junta de Planificación 1353 \n",
"4 Junta de Planificación 2042 \n",
"... ... ... \n",
"15248 Oficina de Desarrollo Socioeconómico 1632 \n",
"15249 Oficina de Desarrollo Socioeconómico 1632 \n",
"15250 Oficina de Desarrollo Socioeconómico 1632 \n",
"15251 Oficina de Desarrollo Socioeconómico 1632 \n",
"15252 Administración del Sistema de Retiro de Emplea... 4073 \n",
"\n",
" NOMBRE_PROGRAMA FONDO \\\n",
"0 Comunicación y Servicios al Ciudadano AE \n",
"1 Gerencia Gubernamental-Recursos Bajo la Custod... AE \n",
"2 Gerencia Gubernamental-Recursos Bajo la Custod... AE \n",
"3 Planificación Física AE \n",
"4 Planificación Económica y Social AE \n",
"... ... ... \n",
"15248 Desarrollo de Asuntos Comunitarios y el Tercer... RC \n",
"15249 Desarrollo de Asuntos Comunitarios y el Tercer... RC \n",
"15250 Desarrollo de Asuntos Comunitarios y el Tercer... RC \n",
"15251 Desarrollo de Asuntos Comunitarios y el Tercer... RC \n",
"15252 Administración del  Sistema de Retiro de Emple... RC \n",
"\n",
" CODIGO_CONCEPTO NUM_CUENTA \\\n",
"0 OG 2970_7 \n",
"1 D 6310 \n",
"2 D 6890 \n",
"3 AENG 6170_14 \n",
"4 AENG 6190_14 \n",
"... ... ... \n",
"15248 SC 2780 \n",
"15249 SC 2880 \n",
"15250 SP 1210_6 \n",
"15251 SP 1290 \n",
"15252 PP 7003 \n",
"\n",
" DESCRIPCION \\\n",
"0 Otros Gastos No Clasificados \n",
"1 Sentencias e Indemnizaciones \n",
"2 Otros subsidios - no clasificados \n",
"3 Donativos y Aportaciones a individuos y entida... \n",
"4 Donativos y Aportaciones a Individuos excepto ... \n",
"... ... \n",
"15248 Conservación y Reparación otros equipos - por ... \n",
"15249 Primas de fianzas de fidelidad \n",
"15250 Servicios Legales \n",
"15251 Servicios profesionales y consultivos - no cla... \n",
"15252 Pensiones del ERS \n",
"\n",
" JOINT_RESOLUTION_CATEGORY AF_2020_APR \\\n",
"0 Other operating expenses 1000 \n",
"1 Other donations and subsidies 3500 \n",
"2 Other donations and subsidies 105484 \n",
"3 Appropriations to Non-Governmental entities 45 \n",
"4 Appropriations to Non-Governmental entities 77 \n",
"... ... ... \n",
"15248 Maintenance & Repairs 1 \n",
"15249 PRIMAS 67 \n",
"15250 Legal expenses 0 \n",
"15251 Other professional service fees 54 \n",
"15252 PayGo 8681 \n",
"\n",
" AF_2021_FOMB Baseline AF_2021_Propuesto Gob \n",
"0 516 516 \n",
"1 3500 3500 \n",
"2 0 0 \n",
"3 54 61 \n",
"4 215 61 \n",
"... ... ... \n",
"15248 1 1 \n",
"15249 17 17 \n",
"15250 18 18 \n",
"15251 341 341 \n",
"15252 0 0 \n",
"\n",
"[15253 rows x 14 columns]"
]
},
"execution_count": 80,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"parsed_responses = [parse_response(response,rowCount) for response in all_responses]\n",
"df = pd.DataFrame(chain.from_iterable(parsed_responses),columns=columns)\n",
"df"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"# Errors "
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"- If too long after last page refresh, response will change to text below \n",
" - F5, Copy as cURL(bash) again and paste the new `context` value into params dictionary "
]
},
{
"cell_type": "code",
"execution_count": 34,
"metadata": {
"ExecuteTime": {
"end_time": "2020-09-09T13:09:41.376993Z",
"start_time": "2020-09-09T13:09:41.374005Z"
}
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"<?xml version=\"1.0\" encoding=\"utf-8\"?><EwaResultXml xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"><Json><![CDATA[{\"ClientUdfCallInfo\":null,\"CollaborationResult\":null,\"EditSessionHasMultipleCoauthors\":false,\"EditSessionIsDirty\":false,\"Errors\":[{\"Appearance\":1,\"Caption\":\"Continue interacting?\",\"CorrelationId\":\"2c4014f2-d542-4bff-ad4a-d07c6efdde34\",\"CorrelationIdDisplayText\":null,\"Description\":\"We noticed that you haven&#39;t been interacting with this workbook, so we paused your session.\",\"Directive\":\"Reload to get the latest version of the workbook.\",\"ErrorCause\":0,\"HelpDescription\":\"Learn how to keep a session active.\",\"HelpImageTitle\":\"\",\"HelpImageUri\":\"\",\"HelpImageWidthPx\":0,\"HelpTopicId\":\"XLWAEndUser_ExcelSessionTimeOut\",\"HideTechnicalDetailsText\":\"Sorry, something went wrong.\",\"MessageId\":-1000,\"MessageIdHash\":-2087635043,\"MessageIdName\":\"InvalidOrTimedOutSession\",\"Severity\":2,\"ShouldShowTechnicalDetails\":false,\"TechnicalDetailsText\":\"\",\"Type\":0}],\"OperationStates\":null,\"RequestId\":null,\"Result\":null,\"SecondsBeforeNextPoll\":-1,\"SessionId\":null,\"StateId\":-1,\"TransientEditSessionToken\":null,\"WorkbookMetadataResult\":null}]]></Json></EwaResultXml>\n"
]
}
],
"source": [
"print('<?xml version=\"1.0\" encoding=\"utf-8\"?><EwaResultXml xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\"><Json><![CDATA[{\"ClientUdfCallInfo\":null,\"CollaborationResult\":null,\"EditSessionHasMultipleCoauthors\":false,\"EditSessionIsDirty\":false,\"Errors\":[{\"Appearance\":1,\"Caption\":\"Continue interacting?\",\"CorrelationId\":\"2c4014f2-d542-4bff-ad4a-d07c6efdde34\",\"CorrelationIdDisplayText\":null,\"Description\":\"We noticed that you haven&#39;t been interacting with this workbook, so we paused your session.\",\"Directive\":\"Reload to get the latest version of the workbook.\",\"ErrorCause\":0,\"HelpDescription\":\"Learn how to keep a session active.\",\"HelpImageTitle\":\"\",\"HelpImageUri\":\"\",\"HelpImageWidthPx\":0,\"HelpTopicId\":\"XLWAEndUser_ExcelSessionTimeOut\",\"HideTechnicalDetailsText\":\"Sorry, something went wrong.\",\"MessageId\":-1000,\"MessageIdHash\":-2087635043,\"MessageIdName\":\"InvalidOrTimedOutSession\",\"Severity\":2,\"ShouldShowTechnicalDetails\":false,\"TechnicalDetailsText\":\"\",\"Type\":0}],\"OperationStates\":null,\"RequestId\":null,\"Result\":null,\"SecondsBeforeNextPoll\":-1,\"SessionId\":null,\"StateId\":-1,\"TransientEditSessionToken\":null,\"WorkbookMetadataResult\":null}]]></Json></EwaResultXml>')"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"In both single and multithreaded runs, varying the rowCount requested could cause the following 4 errors\n",
"\n",
"- ContentDecodingError: ('Received response with content-encoding: gzip, but failed to decode it.', error('Error -3 while decompressing data: incorrect data check'))\n",
"\n",
"- ChunkedEncodingError: ('Connection broken: IncompleteRead(27 bytes read)', IncompleteRead(27 bytes read))\n",
"- JSONDecodeError: Expecting ',' delimiter: line 1 column 723320 (char 723319)\n",
"- jupyter hangs when running executor.map (deadlock?) "
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.8.3"
},
"toc": {
"base_numbering": 1,
"nav_menu": {},
"number_sections": true,
"sideBar": true,
"skip_h1_title": false,
"title_cell": "Table of Contents",
"title_sidebar": "Contents",
"toc_cell": false,
"toc_position": {
"height": "calc(100% - 180px)",
"left": "10px",
"top": "150px",
"width": "384px"
},
"toc_section_display": true,
"toc_window_display": true
}
},
"nbformat": 4,
"nbformat_minor": 4
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment