Python update runs successfully, but nothing is updating

JustinNettleton1

I am working on a realtime GIS python script I was given. I am working on simplifying it and just want to update features from a SQL view to a Feature service. The script runs successfully, but nothing updates. Ideally I just want to update the AGO feature layer only when a unit status changes in the SQL view. But a beginner at this and can't get it to work right. Any guidance would be appreciated. I know I could just do a truncate and append but at the speed I would want for this to update, I was having issues where the dashboard this feeds would update during the truncate and no data would be shown. Plus would like to use this else where in the future if possible on other projects.

Thanks, Justin!

import pyodbc
import os
from arcgis.gis import GIS
import arcgis
import sys
import arcgis.features



#lists for Units used during the update process
agol_units = []
current_units = []
unit_numbers = []

##select id,Unit,Status,Type,Juridiction,Division,LastUpdate
# SQL Query being passed through the ODBC connection
SQL_QUERY = """
select id,Unit,Status,StatusCategory,StatusRank,OOSReason,StatusChange,Type,Juridiction,Division,LastUpdate,Longitude,Latitude
from Reports_Database.dbo.Avail_Units_T
order by id

"""
print ('Query Successful')

def ODBC_connect():
    ##SERVER = 'CADGIS.heartlandcad.net\CADGIS\SQLEXPRESS' # Server FQDN
    SERVER = 'CADGIS\SQLEXPRESS' # Server FQDN
    DATABASE = 'Reports_Database' # Database Name
    ##Connection string to pass in pyodbc connection request. Using Trusted_Connect=yes to have SQL Server Native Client ODBC driver use Windows Authentication of the account running the script
    ## Using the Windows Authentication of the account running the script allows for the use of a Managed Service Account to run the scheduled python script
    connectionString = f'DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={SERVER};DATABASE={DATABASE};Trusted_Connection=yes'
    conn = pyodbc.connect(connectionString)
    cursor = conn.cursor()
    cursor.execute(SQL_QUERY) 
    records = cursor.fetchall()
    for r in records:
        record = [r.id,r.Unit,r.Status,r.StatusCategory,r.StatusRank,r.OOSReason,r.StatusChange,r.Type,r.Juridiction,r.Division,r.LastUpdate,r.Longitude,r.Latitude]
        current_units.append(record)
        unit_numbers.append(r.id)
        
print ('SQL Connection Successful')

def Portal_push():
    #user = os.getenv('justin.nettleton') # System Environmental Variable Name for Username
   
    #portal = os.getenv('https://www.arcgis.com') # System Environmental Variable Name for Portal URL
    #gis = GIS(portal,user,password,use_gen_token=True)
    gis = GIS("https://www.arcgis.com", "justin.nettleton", "******")
    token = gis._con.token

    agol_item = gis.content.get('c0011a3f169b45ca89f3142218fb4f3d') # ArcGIS Online Item ID
    agoLayer = agol_item.layers[0]
    agoFSet = agoLayer.query()#(where = '1=1')
    ago_list = agoFSet.features



    #Retrieving the unit numbers that are existing in the ArcGIS Online Feature Service
    for exsting_unit in ago_list:
        agol_units.append(exsting_unit)
    
   

    # Updating units
    for call in current_units:
        if call in agol_units: 
           #unit_feature = [f for f in ago_list if f.attributes['id'] == call][0]
            unit_dict = {"attributes":
                        {"id":call[0],
                        "Unit":call[1],
                        "Status":call[2],
                        "StatusCategory":call[3],
                        "StatusRank":call[4],
                        "OOSReason":call[5],
                        "StatusChange":call[6],
                        "Type":call[7],
                        "Juridiction":call[8].upper(),
                        "Division":call[9],
                        "LastUpdate":call[10]},
                        "geometry":
                        {"Longitude": call[11], "Latitude": call[12]}}
            agoLayer.edit_attributes(updates=[unit_dict])


            

if __name__ == '__main__':
    ODBC_connect()
    Portal_push()
    
print ('Update Complete')

gis_bCapell

Hi @JustinNettleton1 ,

Can you confirm that your condition on line 66 in OP:

if call in agol_units:

ever evaluates to True?

From your code the structure of each element in 'current_units' is a list built from your SQL Server data, but that should not match any element in agol_units because those are dictionaries. So I would think that condition never evaluates to True.

I think these things will move you in the right direction...

First restructure the record variable (line 37) to be a dictionary that matches the structure of your AGOL feature attributes found by calling existing_unit.features on (line 60) so that you are comparing apples to apples on line 66. You will also then be able to retrieve values by key to assign to the feature before editing the feature layer.

Next lines 65-82 really need to loop through the agol_units at some point so you can edit them with agol_unit.attributes["field"] = record["field"] (if setup appropriately as mentioned above). Then you can pass the agol_unit edit to the feature layer like this: agoLayer.edit_attributes(updates=[agol_unit ]). The FeatureLayer.edit_attributes method 'updates' parameter accepts a list of Feature objects, not dictionaries (see link above).

View solution in original post

JohnEvans6

You may want to remove the password in def Portal_push(). Without a response message it's hard to tell anything. On this line, add in:

resp = agoLayer.edit_attributes(updates=[unit_dict])
Print (resp)

I am going to guess the update you are sending is too large and needs to be split into multiple updates. [EDIT]: Eh maybe not...you're only updating one feature at a time. Maybe a bad data type so it rejects making the update.

MikeVolz

Try throwing in some error handling to try to capture information with that.

JustinNettleton1

Here is how incorporated what you suggested and the error I am getting now.

# Updating units
    for call in current_units:
        if call in agol_units: 
           #unit_feature = [f for f in ago_list if f.attributes['id'] == call][0]
            unit_dict = {"attributes":
                        {"id":call[0],
                        "Unit":call[1],
                        "Status":call[2],
                        "StatusCategory":call[3],
                        "StatusRank":call[4],
                        "OOSReason":call[5],
                        "StatusChange":call[6],
                        "Type":call[7],
                        "Juridiction":call[8].upper(),
                        "Division":call[9],
                        "LastUpdate":call[10]},
                        "geometry":
                        {"x": call[11], "y": call[12]}}
            #agoLayer.edit_attributes(updates=[unit_dict])
            resp = agoLayer.edit_attributes(updates=[unit_dict]) 
print (resp)

---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
In  [20]:
Line 84:    print (resp)

NameError: name 'resp' is not defined
---------------------------------------------------------------------------

JakeSkinner

@JustinNettleton1 instead of Longitude/Latitude for your geometry, try x/y. Ex:

unit_dict = {"attributes":
                        {"id":call[0],
                        "Unit":call[1],
                        "Status":call[2],
                        "StatusCategory":call[3],
                        "StatusRank":call[4],
                        "OOSReason":call[5],
                        "StatusChange":call[6],
                        "Type":call[7],
                        "Juridiction":call[8].upper(),
                        "Division":call[9],
                        "LastUpdate":call[10]},
                        "geometry":
                        {"x": call[11], "y": call[12]}}

JustinNettleton1

I change to x and y and still nothing in the table updated. Latitude and Longitude are the names of my geometry fields, but not sure that it matters.

Justin

gis_bCapell