Using Python to Automatically Update Xibo DataSets

User Stories and Project Showcase Guides
1

I spent a long time trying to figure out how to use Xibo API from a Python script so I figured I would create a guide for anyone else trying to automatically update Xibo using the API. In this example, I use the POST request for importing data from a csv file to the data set where the csv file is built from an excel sheet. I will highlight how to make changes to my code to work for any other API call.

Step 1: Registering an Application in Xibo

In order to authenticate your API calls to Xibo you will need to create a client id and client secret key. This can be done by creating a new application.
Go to Administration > Applications > Add Application. I named my Application ‘Python’ but whatever you want to name it will work. Save the Application and then edit it. Under General, check the Client Credentials box. Then under Permissions, check the All box. Save these changes. I recommend writing down your client id and client secret key because we will use them later. I tried copying and pasting the values and ran into some errors that were fixed by entering them manually.

Step 2: Getting an Access Token in Python

Create a new Python Script. Here are the imports you will need:

import requests
from requests_toolbelt.multipart.encoder import MultipartEncoder
import pandas as pd
import json
import os
import time
import mimetypes
import csv

If you get errors when running it is most likely you have not installed these.
Open a terminal and run ‘python -m pip install requests’, ‘python -m pip install requests_toolbelt’, ‘python -m pip install pandas’, and possible a few of the others using that same format if they give you errors. If you get errors referring to not having a python command then google how to setup python PATH environment variables.

In order to use API calls to update data or media on Xibo we are going to need to get an access token. This access token is obtained by authenticating yourself using the client id and client secret key we just created.
Here is the code needed for getting an access token:

def GetAccessToken():
“”"
Request an access token from the Xibo API
Uses Client ID and Client Secret to Authenticate
Return: Access Token as a String
“”"
# URL to get access token from. Xibo Server URL + /api/authorize/access_token
auth_url = “http:// YOUR_XIBO_URL /api/authorize/access_token”
# The Client Credentials from a Xibo Application you create at Administration>Applications>Add
client_id = ‘YOUR_CLIENT_ID’
client_secret = ‘YOUR_CLIENT_SECRET’
# How we plan to authenticate
auth_data = {‘grant_type’:‘client_credentials’}
# Send a Post request to xibo
auth_response = requests.request(“POST”, auth_url, data= auth_data, verify=False, allow_redirects=False, auth=(client_id, client_secret))
# Load the json to a list
response_list = json.loads(auth_response.text)
return response_list[‘access_token’]

Fill in your Xibo URL, Client Id, and Client Secret Key. This function will return the access token as a string.

Step 3: Passing Parameters to Xibo API

Now that we have an access token, we can use Xibo’s API to update data or media in Xibo. Take a look at the API documentation to determine the GET or POST request you need to make as well as the parameters it requires.
The API can be found here: https://xibo.org.uk/manual/api/

“”"""""""""""""""""""""""""""""""""""""
Send data to Xibo using API
“”"""""""""""""""""""""""""""""""""""""
#http:// XiboURL /api/ + GET/POST listed in the API
url = “http:// YOUR_XIBO_URL /api/dataset/import/2”

#Authenticate using access token
headers = { ‘Authorization’: "Bearer " + GetAccessToken() }

#Specify the path to the file that is being uploaded
filename = csvFileName # In this case I passed the csv file that was created

#Puts a timestamp on the media name for upload
basename = os.path.basename(filename)
upload_filename = f"{int(time.time())}-{basename}"
mimetype = mimetypes.guess_type(basename)

#Define all the parameters listed on the API for the desired POST
encoder = MultipartEncoder({
‘dataSetId’:“2”,
‘files’: (upload_filename, open(filename, ‘rb’), mimetype),
‘csvImport_3’: “1”,
“csvImport_4”:“2”,
“overwrite”: “1”,
“ignorefirstrow”: “0”,
})

#Determines the content type and attaches it to the header
headers.update({“Content-Type”: encoder.content_type})

#Send the post request
response = requests.post(url, headers=headers, data=encoder)
print(response.text)

In my case, I am using a POST request to a Data Set to import a csv file.
URL: The url you are going to create will be in this format: http:// YOUR_XIBO_URL /api/ + POST/GET
The POST directory listed for my case is /dataset/import/{dataSetId} so my url would be http:// xx.xxx.xxx.xxx /api/dataset/import/2
Headers: The header is just the access token we found in the previous step.
Filename: If your parameters call for a file then provide the path to that file in the filename variable. Ex: filename = ‘C:/User/me/Documents/data.csv’
Basename: The basename is just the filename without the path. Ex: data.csv
Upload filename: The upload_filename is the name of the media when in Xibo. Here I timestamp the media’s name.
Mimetype: The mimetype determines the type of file you are passing by looking at the basename.

Encoding the Parameters:
Next is the encoder, here you pass the parameters found on the Xibo API as a dictionary. Pass all of the values as strings even if it asks for integers on the API (other than files). *If you are also trying to import a csv file to a dataset then look up csvImport_ parameters as Xibo’s documentation on its is very confusing.

Add in the headers and call the post request… and that’s it! You can now successfully update Xibo data or media from your python script.

This next part is how I converted an excel sheet to csv in order to update the data set

Step 4: Converting Excel Sheet to a CSV file

“”"""""""""""""""""""""""""""""""""""""
Convert Excel Sheet Data to a CSV File
“”"""""""""""""""""""""""""""""""""""""
#Location of excel workbook to open
excel_location = r"C:\Users\MORE_PATH\YOUR_EXCEL_SHEET.xlsx"
sheetname = ‘NAME_OF_SHEET’
#Thhe columns to read from sheet
column_names = [‘COL_NAME_1’, ‘COL_NAME_2’]
#Reads the excel sheet, returns a data frame object
excel_sheet = pd.read_excel(excel_location, sheet_name = sheetname, usecols = column_names)

#Get the number of rows and columns in the data frame
num_rows = excel_sheet.shape[0]
num_cols = excel_sheet.shape[1]

#Pull data from the data frame and store it in a list
csvData = []
Had to use >> to show indents, sorry!
for row in range(0,num_rows):
>>row_list = []
>>for col in range(0, num_cols):
>>>>row_list.append(excel_sheet.iloc[row][col])
>>csvData.append(row_list)

#Write the data to a CSV file that can be sent to Xibo
csvFileName = ‘tempCSV.csv’
with open(csvFileName, ‘w’, newline = ‘’) as csvFile: #indent the next two lines
writer = csv.writer(csvFile)
writer.writerows(csvData)
csvFile.close()

First I open the excel file, which for me was a one drive file that I always kept locally. Next, you must provide which sheet you want to read from. The names of the sheets are usually listed along the bottom. In that sheet provide the column names in the first row of the sheet that you want to pull data from. The rest of it is just writing the data to a csv file that will be created in the same folder as your python script. I then set this file to the filename variable described in an earlier step.

Step 5: Automating the Python Script Execution

If you want the script to update the data set every so often automatically then take a look at how to setup windows task manager for a python script.

Hopefully this saves you some time!

  • Brandon Garrison
5 Likes