Compare GUID type field to Identical GUID string in SQL Where Query

LeviCecil — Thu, 04 Mar 2021 19:11:07 GMT

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])

Re: Compare GUID type field to Identical GUID string in SQL Where Query

Anonymous User — Thu, 04 Mar 2021 19:25:38 GMT

strings need to be surrounded by ' for sql.

try

where_clause = """WHERE GlobalID = '{}'""".format(REL_GUID)

Re: Compare GUID type field to Identical GUID string in SQL Where Query

LeviCecil — Thu, 04 Mar 2021 19:35:28 GMT

Perfect, thanks!!

topic Re: Compare GUID type field to Identical GUID string in SQL Where Query in Python Questions

Compare GUID type field to Identical GUID string in SQL Where Query

Re: Compare GUID type field to Identical GUID string in SQL Where Query

Re: Compare GUID type field to Identical GUID string in SQL Where Query