[Python] Retrieving page content from Confluence REST with Python and Pandas

I want to calculate word statistics on pages in my knowledge base that runs on Confluence.

But before I do the calculations, I'd like to retrieve page data: text written on the pages.

I have a Python script that was originally made to collect commentary from pages. I am trying to adapt the script for /rest/api/content/{id} REST API that I have found with Confluence REST browser.

The original script uses API that returns result as a JSON object that when parsed with json() method returns dictionary objects.

However, the /rest/api/content/{id} API returns a result that does not contain a well formed dictionary. I receive string objects, and I cannot simply address them as array['index'] = result ['value'] to retrieve page data.

I am using JupyterLab environment to run the code.

When using Confluecne Browser and /rest/api/content/{id} API for the page 4068365, Confluence returns the following result:

{
"id": "4068365",
"type": "page",
"status": "current",
"title": "Page title",
"body": {
"view": {
"value": "<p>Some text</p>",
"representation": "storage",
"_expandable": {
"webresource": "",
"content": "/rest/api/content/4068365"
}
},
"_expandable": {
"editor": "",
"export_view": "",
"styled_view": "",
"storage": "",
"anonymous_export_view": ""
}
},
"extensions": {
"position": "none"
},

...


I'd like to obtain the value of 'value' key. However, the 'value' param is not recognized as a key because the result is formatted as a string and not as a dictionary.

Here's the code I have.


import requests
import json
import getpass
import re
import html
import pandas as pd
from datetime import datetime

# Allow HTTPS connections with self-signed cert
requests.packages.urllib3.disable_warnings()

# Create login session for Confluence
auth = ('mylogin', getpass.getpass())
s = requests.Session()
s.auth = auth
s.verify = False
s.headers = {"Content-Type": "application/json"}

# Confluence REST API URI
WIKI = 'https://example.net/wiki/rest/api/'

# Obtain text from Confluence HTML layout
def cleanhtml(raw_html):
cleanr = re.compile('<.*?>')
text = html.unescape(raw_html)
text = re.sub(cleanr, '', text)
text = text.replace(u'\xa0', u' ')
return text

# Retrieving page data
def get_data(page_id):
data = []
r = s.get(
'{}content/{}'.format(WIKI, page_id),
params = dict(
expand='body.view'
)
)
for content in r.json():
pgdata = dict()
#I can't address to value as content['value']
pgdata['text'] = cleanhtml(content['body']['view'].get('value'))
data.append(pgdata)
return data

# Pages to extract from
with open(r'C:\\Users\\Stacy\\Documents\\pages.txt') as pagesf:
pagesl = pagesf.read()
pages = pagesl.split(",\n")
print(pages)

# Preparing data frame and exporting to Excel
textdata = list()
for page in pages:
print('Handing:', page)
textdata.extend(get_data(page))

df = pd.DataFrame(
textdata,
columns = ['text']
)

df.to_excel('page_data{}.xlsx'.format(datetime.now().strftime("%Y_%m_%d_%H-%M")))



I want to collect the text from

"value": "<p>Some text</p>",


into data and store all that in a dictionary. However, I see that content contains data type and not data, so I can't reference to 'body' as to a key, because it's not a key.

Please help me retrieve page data from 'value'. What would be the right way? Thank you.

Continue reading...