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.")
