[PYTHON] I tried logging the amount of power generated by solar power generation. (4) Real-time monitoring with Salesforce

[I tried logging the amount of power generated by solar power generation. Table of contents]

Real-time monitoring with Salesforse

Up to the last time, it has become possible to log the current and voltage values measured by INA260 with Raspberry Pi, but since it is a big deal, I would like to upload it on the Internet so that I can check the amount of power generation from anywhere.

I think there are various options for that method, but this time I used Salesforce's Developer Edition.

● Benefits of using Salesforce

Must be PaaS (Platform as a Service) Cloud services can be broadly divided into three types: IaaS (Infrastracture as a Service), PaaS (Platform as a Service), and SaaS (Software as a Service). Salesforce belongs to PaaS. PaaS does not require environment construction like IaaS, and has a higher degree of freedom than SaaS, and rich functions can be freely and easily customized.
Developer Edition is free Salesforce basically requires you to purchase a paid license, but the Developer Edition is full-featured and available for free. However, there are the following restrictions.

・ The number of users that can be created is 2. ・ Available data storage 5MByte ・ Available file storage 20MByte

For more information, see [Salesforce Features and Edition Assignments-Salesforce Help] (https://help.salesforce.com/articleView?id=overview_limits_general.htm&type=5) "See Data Storage and File Storage Allocation.

Data storage 5M may seem small, but BigObject can store up to 1 million records. This will be explained later.

● Salesforce Developer Edition registration

Salesforce Developer Edition can be used by registering as a user from the following site. Please note that there may be a time lag between registration and receipt of the email.

Salesforce Developers User Registration

● Login to Salesforce

To log in to Salesforce, go to the following site.

Salesforce Login (https://login.salesforce.com/?locale=jp)

Enter the registered user name and password to log in. At the first login, after the guidance, the following screen (service settings) is basically displayed.

There are many menus, and you may not know what to do, but here we will set it according to the following procedure.

Create a custom object
OAuth settings and API activation
Input test data via SOAP API
Create a report
Creating a dashboard

● Create custom objects

Salesforce has standard and custom objects. These correspond to database tables.

Standard object: An object prepared for applications such as CRM from the beginning. Customizable. Custom Objects: Objects that are completely user-definable.

The following is the procedure for creating a custom object.

Click the gear icon at the top right of the screen. Select "Settings".
From the menu on the left side of the screen Platform Tools-Select "Objects and Items"-"Object Manager"
Click Create on the right side of the screen and select Custom Object from the drop-down menu.

Create a custom object

Set the name of the custom object and the API access name. After saving, the API access name of the custom object will have "__c" at the end. Here, the settings are as follows.

Create a custom tab

If you check "Launch new custom tab wizard after saving custom object" in 4, "New custom tab screen" is automatically displayed after saving the custom object. If you forget to check it, Click the "New" button from Settings-Platform Tools- "User Interface"-"Tabs" to launch the new custom tab creation screen.

Here, select the tab style and click "Next". After that, there are settings for the user to display and the application to be displayed, but keep the defaults and proceed with "Next" to save.

Create custom items

Again, from Settings-Platform Tools- "Objects and Items"-"Object Manager", select the custom object you just created.

Select "Items and Relations" from the left menu.

Click the "New" button at the top right of the screen.

Select the data type of the item. Here, as an example, the item of current value is created, so select "Numeric value" and click "Next".

Set the item name, number of digits, etc. and click "Next".

Leave the default item-level security settings and click Next.

You can leave the defaults for adding to the page layout. Click "Save" or "Save & New".

Follow the same procedure as above to add the necessary items.

● OAuth settings and API activation

Use the REST API or SOAP API to upload measurements from the Raspberry Pi to Salesforce. To enable it, you need to check "Enable API" in the user profile.

Display the user list in "Settings"-"Users". Open the profile of the user used for DB access and press the "Edit" button. Then, check "Enable API" and save.

Also, when accessing the API from the outside, a security token is required in addition to the password. If you don't know your security token, you can reissue it by following the steps below.

Click the character icon at the top right of the screen and click "Settings".
Select "Reset My Security Token" from the left menu.
Press the "Reset Security Token" button.

When the above is executed, an email containing the security token will be sent to the registered email address.

Next, create a "connected application" to configure OAuth settings. It can be created from "Settings"-"Applications"-"Connected applications".

This is described in detail on the following site, so I referred to it.

I tried accessing Salesforce REST API from Python using simple_salesforce

Get Salesforce data using REST API (https://qiita.com/nsuhara/items/19cf8ec89b88fb3deb39)

● Input test data via REST API

From here, it will be the work on the Raspberry Pi side. I modified the source code of the previous article and added code to upload measurements to a custom Salesforce object. Use the Salesforce REAST API to access Salesforce.

Salesforce REST API Developer's Guide (https://developer.salesforce.com/docs/atlas.ja-jp.api_rest.meta/api_rest/quickstart.htm)

There seem to be several libraries for python in Salesforce, but here we use simple_salesforce. The site I introduced earlier describes the procedure for installing the library, so I referred to it.

I tried accessing Salesforce REST API from Python using simple_salesforce

import serial
import struct
import datetime as dt
import json
from simple_salesforce import Salesforce

USERNAME = "username"
PASSWORD = 'password'
SECURITY_TOKEN = "Security token"

use_port = "/dev/ttyACM0"

_serial = serial.Serial(use_port)
_serial.baudrate = 115200
_serial.parity = serial.PARITY_NONE
_serial.bytesize = serial.EIGHTBITS
_serial.stopbits = serial.STOPBITS_ONE
_serial.timeout = 15

def Login():
    sf = Salesforce(username=USERNAME, password=PASSWORD,
                    security_token=SECURITY_TOKEN)
    return sf

sf = Login()
try:                                                                                                                        while True:
        rx = _serial.readline()
        #rx = rx.strip()
        st = "{0},{1}".format(dt.datetime.now(), rx)
        print(st)
        with open('/home/pi/current_{}.log'.format(dt.date.today()), mode="a") as f:
            f.write(st)

        datas = st.split(",")
        if len(datas)<3:
            continue

        t1 = datas[0][0:19]                                                                                                     t2 = t1.replace(" ","T") + "Z"                                                                                          print("{0},{1}".format(t1,t2))
        try:
            for i in range(1,len(datas)):
                datas[i] = datas[i].replace("b'","").replace("\\r\\n'","").strip()

            if len(datas[1])==0 or len(datas[2])==0 or len(datas[1])>10 or len(datas[2])>10 :
                continue
            if datas[1][:1].isdigit()==False or datas[2][:1].isdigit()==False:
                continue

            res = sf.GeneratorLog__c.create({'Name': t1, #The Name item is a character string type and the date and time are set.
                                             'MeasDate__c': t2, #Date and time(Datetime type)
                                             'Current__c': float(datas[1]), #Current value
                                             'Voltage__c': float(datas[2]), #Voltage value
                                             'Temperature__c': 20.00}) #Since the temperature will be measured in the future, only the items were created, but a fixed value is set.
            if res.get('error'):
                raise Exception(response.get('error_description'))

            print(json.dumps(res, indent=4))

        except Exception as e:
                sf = Login() #If you keep logging in, an exception will occur due to a timeout, so log in again and try again.
                res = sf.GeneratorLog__c.create({'Name': t1,
                                                 'MeasDate__c': t2,
                                                 'Current__c': float(datas[1]),
                                                 'Voltage__c': float(datas[2]),
　                                               'Temperature__c': 20.00})
                print(json.dumps(res, indent=4))

except Exception as ex:
    print(ex)
finally:
    _serial.close()

If you connect a solar panel or a test battery to the INA260 and execute it, you can see that the data has been uploaded from the Salesforce screen.

By the way, you can select the tab to display by clicking the pen mark at the right end of the tab display field in the Lightning Experience application to display the created custom tab. Please note that you want to customize the tab display in the Classic application. By pressing the application selection button at the left end of the tab display field, tab selection was possible even from "Show All". (Recently, due to the transition to Lightning Experience, you may not be able to do what you were able to do before, so be careful)

Any application will do, so add the following tab. ・ Power generation log (created custom tab) ・ Report (explained below) ・ Dashboard (explained below)

● Creating a report

Logging data is not fun just by storing it. After all, the tendency is not well understood unless it is graphed. Create a report for graphing.

Select the Report tab and click "Create New Report" at the top of the screen. At this time, click the button that is not Classic.

On the "Select Report Type" screen, existing objects are listed. Select the custom object (power generation log) you created.

from here ・ Display items ・ Search conditions ・ Grouping And prepare the data to make a graph.

For the graph I want to draw, I want to display the time on the X-axis and the current on the Y-axis for each day. Therefore, a current is added to the display item. Enter "current" in the search field under "column" on the left side of the screen, or open "item" on the left side of the screen and select "current". Then, you can see that "current" is added to the table in the center of the screen.

However, the "Add Graph" button at the top of the screen remains disabled. It is necessary to group them to display the graph. Therefore, we will display the average current every 10 minutes for each day.

Here, return to "Service settings"-"Objects and items" and add a custom item to the custom object (power generation log).

item name	Data type	Formula	Remarks
Day	Formula	DATEVALUE( LEFT(Name,10))
Minute time	Formula	VALUE(MID(Name,12,2) & "." & MID(Name,15,1))	Set time and tens of minutes

By the way, in the Name item, the measurement date and time is entered as a character string type as "yyyy-MM-dd HH: mm: ss".

Immediately add the created custom item to the report. Added "Day" and "Minute" in "Group Rows".

Also, if you click "Current" in "Column", the summary menu will be displayed, so select the average.

Finally, the "Add Graph" button is enabled. Click "Add Graph".

Click the gear button at the top right of the graph.

Display graph: Line Title: Current measurement X-axis: minutes and hours Series: day Y-axis: current average

If you can confirm that the graph is displayed normally on the screen, save it.

Similarly, you may want to report on voltage and power.

● Creating a dashboard

Add to the dashboard to make the report easier to read.

Select the Dashboard tab and click "New Dashboard" at the top of the screen. Enter the name of the dashboard and click "Create". When you press the "+ Component" button at the top of the screen, a list of reports will be displayed, so select the report you want to display. Next, a form similar to the graph creation of the report is displayed, so set it in the same way as the graph creation of the report.

Click the "Add" button to add the graph component on the screen. If you add and save the reports you want to display in the same way, it will look like this.

Now you can log in to Salesforce and access your dashboard from anywhere to monitor your power generation in real time. However, there is one caveat. Please note that the data will not be updated unless you press the update button at the top right of the screen.

[I tried logging the amount of power generated by solar power generation. Table of contents]