RuntimeError: Value type is incompatible with the field type

[DanielleKuchler]

Hi all. I am very new to using Python Notebooks in ArcGIS Pro. I am trying to fill one row of a table in ArcGIS Pro with data that I extracted from the USGS website (using their Web Service URL Generation tool). I have been able to request the data from the url, but am running into issues when it comes to adding it into the table within my geodatabase. The current field type I have for the field "date_time" is "DATE", so I am not sure what is going wrong.

The error I get after running the last line of code is: "RuntimeError: The value type is incompatible with the field type [date_time]."

Any advice or suggestions would be appreciated!

#create a new table
#set arcpy workspace to your geodatabase
arcpy.env.workspace="C:\\My GIS Projects\\Lake Levels USGS\\Lake Levels USGS.gdb"

#set variable outpath
outpath=arcpy.env.workspace

#create new, empty table
arcpy.CreateTable_management(outpath, "Riffe_Lake_Level")

#create fields in empty table
arcpy.AddField_management("Riffe_Lake_Level", "site_name", "STRING")
arcpy.AddField_management("Riffe_Lake_Level", "date_time", "DATE")
arcpy.AddField_management("Riffe_Lake_Level", "station_id", "LONG")
arcpy.AddField_management("Riffe_Lake_Level", "agency_code", "STRING")
arcpy.AddField_management("Riffe_Lake_Level", "waterelev", "DOUBLE")

#use first table as a template for a second table
arcpy.CreateTable_management(outpath, "Alder_Lake_Level", "Riffe_Lake_Level")

#edit table to allow editing of field values
editrows = arcpy.da.InsertCursor("Riffe_Lake_Level", ["site_name", "date_time", "station_id", "agency_code", "waterelev"])
editrows.fields

#import USGS water elevation data
import urllib.parse
import urllib.request
import json
import requests

usgs_water_api = "https://waterservices.usgs.gov/nwis/dv/?format=json&sites=14234800&siteStatus=all"
api_response = requests.get(usgs_water_api)

water_data = api_response.json()

#define values for fields
for i in data:
        site_name = water_data["value"]["timeSeries"][0]["sourceInfo"]["siteName"]
        date_time = water_data["value"]["timeSeries"][0]["values"][0]["value"][0]["dateTime"]
        station_id = water_data["value"]["timeSeries"][0]["sourceInfo"]["siteCode"][0]["value"]
        agency_code = water_data["value"]["timeSeries"][0]["sourceInfo"]["siteCode"][0]["agencyCode"]
        waterelev = water_data["value"]["timeSeries"][0]["values"][0]["value"][0]["value"]
        row = [site_name, date_time, station_id, agency_code, waterelev]
        print(row)
#populate row in data table
        editrows.insertRow(row)

[DanPatterson · Accepted solution]

check the source types, a date may look like one, but it could be string/text

[DanielleKuchler]

Switching source type to field worked! I'm glad it wasn't a more complicated issue. Thank you!

[CodyPatterson]

Hey @DanielleKuchler 

For testing purposes, you may want to attempt to plug this right before your for loop. This will print out a small report of what each date time is, you may also want to check to see if it is a certain index where this happens. If even one of the date_time variables are incorrect, it will stop the execution when it is reached.

for i in data:
        date_time = water_data["value"]["timeSeries"][0]["values"][0]["value"][0]["dateTime"]
        print(f"Date Time of {date_time} is of type {type(date_time)}")

 You may also try manually inputting a row using the edit cursor, with different types of date time format, here is a link to many date/time formats: https://pro.arcgis.com/en/pro-app/latest/help/mapping/time/supported-field-formats.htm

Cody

[JoshuaBixby]

If it is the datetime field causing the issue, then share an example or two of the datetimes from the web service and save community members the effort of having to go retrieve it themselves to help you.

Retrieving the JSON, it looks like the web service is returning datetime as a string in the following format:  "2024-08-21T13:08:33.583Z".  That looks to be an ISO8601 compliant time format string, but currently the Data Access cursors cannot parse it (I have logged a defect already).  So, you are going to have to manipulate the time string into a simpler format that can be parsed.

[DanielleKuchler]

Thank you for the insight and for taking the time to retrieve the source data! Apologies for not putting those examples here. Changing the field type to string allowed the data to be retrieved for the time being. 

[Luke_Pinner]

I'm not sure what this loop is for, you haven't defined `data` anywhere, the `water_data` variable is a dict not a list/tuple and you don't use the `i` inside the loop.

#define values for fields
for i in data:

Try converting the `date_time` string to a `datetime` object.

import requests
from datetime import datetime
usgs_water_api = "https://waterservices.usgs.gov/nwis/dv/?format=json&sites=14234800&siteStatus=all"
api_response = requests.get(usgs_water_api)
water_data = api_response.json()

date_time = water_data["value"]["timeSeries"][0]["values"][0]["value"][0]["dateTime"]
date_time = datetime.fromisoformat(date_time)

[DanielleKuchler]

Thank you for your help with the "for loop" comment, you're right, I didn't actually need that as part of my code for this scenario. 

[DanielleKuchler]

For anyone curious, this is what ended up working:

#create a new table
#set arcpy workspace to your geodatabase
arcpy.env.workspace="C:\\My GIS Projects\\Lake Levels USGS\\Lake Levels USGS.gdb"

#set variable outpath
outpath=arcpy.env.workspace

# Define the name of the new table
table_name = 'RiffeLakeElev'

#create new, empty table
arcpy.CreateTable_management(outpath, "Riffe_Lake_Level")

#create fields in empty table
arcpy.AddField_management("Riffe_Lake_Level", "site_name", "STRING")
arcpy.AddField_management("Riffe_Lake_Level", "date_time", "STRING")
arcpy.AddField_management("Riffe_Lake_Level", "station_id", "STRING")
arcpy.AddField_management("Riffe_Lake_Level", "agency_code", "STRING")
arcpy.AddField_management("Riffe_Lake_Level", "waterelev", "DOUBLE")

#edit table to allow editing of field values
editrows = arcpy.da.InsertCursor("Riffe_Lake_Level", ["site_name", "date_time", "station_id", "agency_code", "waterelev"])
editrows.fields

#import USGS water elevation data
import urllib.parse
import urllib.request
import json
import requests

usgs_water_api = "https://waterservices.usgs.gov/nwis/dv/?format=json&sites=14234800&siteStatus=all"
api_response = requests.get(usgs_water_api, verify=False)

water_data = api_response.json()  

#define values for fields

site_name = water_data["value"]["timeSeries"][0]["sourceInfo"]["siteName"]
date_time = water_data["value"]["timeSeries"][0]["values"][0]["value"][0]["dateTime"]
station_id = water_data["value"]["timeSeries"][0]["sourceInfo"]["siteCode"][0]["value"]
agency_code = water_data["value"]["timeSeries"][0]["sourceInfo"]["siteCode"][0]["agencyCode"]
waterelev = water_data["value"]["timeSeries"][0]["values"][0]["value"][0]["value"]

row = [site_name, date_time, station_id, agency_code, waterelev]
print(row)

#populate row in data table
editrows.insertRow(row)

#finish editing
del editrows