I have a Python script that exports sde attachments, photos submitted through Survey123. I need to rename the resulting JPGs with the site, room and time they were taken. The initial file name only has the relative global ID from the attachment table that relates to the global ID of the room feature in the survey table. Comparing these is how I get the room data for renaming the JPG file. It works just fine, but we have nearly 15,000 rooms over 100 sites, so I want to speed up the cursor search with a SQL query.
The problem is, the Global ID field from the survey table is a GUID type field. The identical relative global ID field in the attachment table is a string. The Global ID field is only cast to string inside the cursor GUID = item[0]. The SQL where clause is defined outside the cursor, where Global ID refers to the GUID type field. I've tried converting the relative ID string field to a UUID type, but this hasn't worked. I tried setting the where clause as an empty string and then defining it inside the search cursor loop. I keep getting this error: for item in cursor:
RuntimeError: Underlying DBMS error [[Microsoft][ODBC Driver 13 for SQL Server]Syntax error, permission violation, or other nonspecific error] [PPSTest.DBO.CustodianInspection]
Here is the relative section of my script:
##Step 2 -Loop through each photo and get time taken from metadata
for jpg in os.listdir(temp_dir):
filepath = temp_dir + "//" + jpg
date_time = (get_date_taken(filepath))
date_time_obj = datetime.datetime.strptime(date_time, '%Y:%m:%d %H:%M:%S')
date_string = date_time_obj.strftime("%b %d %Y")
time_string = date_time_obj.strftime("%I_%M_%S%p")
REL_GUID = jpg[5:43]
##Step 3 -Compare REL_GUID in photo filename to GUID in custodian inspection table to grab site name and room number
where_clause = "WHERE GlobalID = " + REL_GUID
with da.SearchCursor(inspection_table, ['GlobalID', 'SiteName', 'RoomNumber','Comments','appa_score'],where_clause) as cursor:
for item in cursor:
GUID = item[0]
SiteName = item[1]
RoomNum = item[2]
comments = item[3]
appaScore = str(item[4])
Solved! Go to Solution.
strings need to be surrounded by ' for sql.
try
where_clause = """WHERE GlobalID = '{}'""".format(REL_GUID)
strings need to be surrounded by ' for sql.
try
where_clause = """WHERE GlobalID = '{}'""".format(REL_GUID)
Perfect, thanks!!