Select to view content in your preferred language

InsertCursor works but error with UpdateCursor

500
2
Jump to solution
09-07-2023 02:32 PM
TSWCD_GIS
New Contributor III

Hello,

I've been working on a script that is working in all ways except for an error being thrown with an UpdateCursor, stating the following: 

Traceback (most recent call last):
File "D:\Scripts\Insightly\UpdateInsightlyProjects.py", line 130, in <module>
for row in update_cursor:
RuntimeError: Function or procedure does not exist [Function or procedure does not exist] [tswcdediting.sde.InsightlyProjects]

Below is the code, I'm assuming it has something to do with the where clause but I've run into a wall and can't figure out what I've done wrong. Any suggestions would be appreciated. Thank you!

 

import requests
import json
import arcpy
from datetime import datetime, timedelta

# Set the date range for the last day
current_date = datetime.now()
last_day = current_date - timedelta(days=1)
url_formatted_date = last_day.strftime("%Y-%m-%dT00:00:00.000Z")

url = f"https://xxxxxxxxx/v3.1/Projects/Search?updated_after_utc={url_formatted_date}"
headers = {
    "Authorization": "Basic xxxxxxxxxxxxxxxxxx",
    "Accept": "application/json",
}

# Send API request
response = requests.get(url, headers=headers)
data = response.json()



# Point to SDE table and fields
sde_connection = r'D:\xxxxx\xxxxx.sde'
arcpy.env.workspace = sde_connection
sdetable = "xxxxxxxx"
tablefields = ["project_id",
               "xxxxx",
               "xxxx",
               "project_name",
               "contract_acres",
               "calc_acres",
               "program",
               "manager",
               "enroll_date",
               "expire_date",
               "active",
               "duration",
               "cib_paid",
               "waterright",
               "subbasin",
               "contractor"
               ]

# Start an edit session.
edit = arcpy.da.Editor(sde_connection)

# Edit session is started without an undo/redo stack for versioned data
#  (for second argument, use False for unversioned data)
edit.startEditing(False, True)

# Start an edit operation
edit.startOperation()

# Getting data and setting values
# Have everything start as "None" so null or missing values don't cause error)
# Extract standard fields first
# Populates new project_IDs and updates ones that exist already

for item in data:
    project_id = item.get("PROJECT_ID", None)
    project_name = item.get("PROJECT_NAME", None)
    project_category = item.get("CATEGORY_ID", None)
    manager_id = item.get("RESPONSIBLE_USER_ID", None)
    date_updated_utc = datetime.strptime(item.get("DATE_UPDATED_UTC"), "%Y-%m-%d %H:%M:%S")
    date_created_utc = datetime.strptime(item.get("DATE_CREATED_UTC"), "%Y-%m-%d %H:%M:%S")

# Extract custom fields from CUSTOMFIELDS array
    custom_fields = item.get("CUSTOMFIELDS", [])
    accounting_code = None
    contract_acres = None
    xxxxxx = None
    managed_acres = None
    enroll_date = None
    expire_date = None
    active = None
    duration = None
    cibpaid = None
    waterright = None
    subwatershed = None
    contractor = None
    for field in custom_fields:
        field_name = field.get("FIELD_NAME")
        field_value = field.get("FIELD_VALUE")
        if field_name == "Accounting_Code__c":
            accounting_code = field_value
        elif field_name == "Contract_Acres__c":
            contract_acres = field_value
        elif field_name == "xxxxxxx__c":
            xxxxxx = field_value
        elif field_name == "Managed_Acres__c":
            managed_acres = field_value
        elif field_name == "Enrollment_Date__c":
            enroll_date = field_value
        elif field_name == "Expiration_Date__c":
            expire_date = field_value
            duration = int(expire_date[:4]) - int(enroll_date[:4])
            if datetime.strptime(field_value, "%Y-%m-%d %H:%M:%S") > datetime.now():
                active = "Yes"
            else:
                active = "No"
        elif field_name == "CIB_Paid__c":
            cibpaid = field_value
        elif field_name == "Water_Right__c":
            waterright = field_value
        elif field_name == "Subwatershed_Name__c":
            subwatershed = field_value
        elif field_name == "Contractor__c":
            contractor = field_value

# Insert a new row with the extracted values
    if date_created_utc > last_day:
        with arcpy.da.InsertCursor(sdetable, tablefields) as insert_cursor:
            row = [project_id, accounting_code, xxxxxx, project_name, contract_acres, managed_acres, project_category, manager_id, enroll_date, expire_date, active, duration, cibpaid, waterright, subwatershed, contractor]
            insert_cursor.insertRow(row)

    elif date_updated_utc > last_day and date_created_utc < last_day:
            # Update the corresponding records in the SDE table
        with arcpy.da.UpdateCursor(sdetable, tablefields, where_clause=f"project_id = {project_id}") as update_cursor:
            for row in update_cursor:
                    # Update the values in the row
                row = [project_id, accounting_code, xxxxxx, project_name, contract_acres, managed_acres, project_category, manager_id, enroll_date, expire_date, active, duration, cibpaid, waterright, subwatershed, contractor]
                update_cursor.updateRow(row)

# Stop the edit operation.
edit.stopOperation()

# Stop the edit session and save the changes
edit.stopEditing(True)

print("Values inserted into ArcSDE table.")
0 Kudos
1 Solution

Accepted Solutions
RhettZufelt
MVP Notable Contributor

Is project_id a text field?  If so, maybe:

f"project_id = '{project_id}'"

R_

View solution in original post

2 Replies
RhettZufelt
MVP Notable Contributor

Is project_id a text field?  If so, maybe:

f"project_id = '{project_id}'"

R_

TSWCD_GIS
New Contributor III

Its always frustrating when I overlook the simplest of fixes, but that did it!

Thank you so much Rhett!

0 Kudos