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

224
2
Jump to solution
03-04-2021 11:08 AM
LeviCecil
Occasional Contributor III

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

 

 

 

0 Kudos
1 Solution

Accepted Solutions
JeffK
by
Occasional Contributor III

strings need to be surrounded by ' for sql.

try

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

 

View solution in original post

2 Replies
JeffK
by
Occasional Contributor III

strings need to be surrounded by ' for sql.

try

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

 

View solution in original post

LeviCecil
Occasional Contributor III

Perfect, thanks!! 

0 Kudos