1

I am receiving this error when using an insert cursor in ArcPy.

I have identified the cause of it as a syntax error, however have not figured out the correct syntax to resolve the error.

I am using a CASE statement to convert a boolean data type to a string.

The statement works in Management studio, however there is something clearly wrong with the Python syntax which is causing the error.

I am converting the isproject from SQL Server to Oracle SDE.

The date conversion works fine; however the conversion for isproject is not working as expected.

When I debug fieldnames_string this is the output for the isproject

CASE WHEN [isproject]=1 THEN 'TRUE' WHEN [isproject]=0 THEN 'FALSE' END

What needs to be changed in this SQL statement to to avoid this error?

import arcpy
import datetime
fieldnames = [field.name for field in descrip.fields if field.name != descrip.OIDFieldName]

        #workaround for dates conversion
        fieldnames_wrapped = []
        for fieldname in fieldnames:

            if 'date' in fieldname:
                fieldnames_wrapped.append('CONVERT(VARCHAR, {0})'.format(fieldname))

            if 'isproject' in fieldname:
               fieldnames_wrapped.append("CASE WHEN [isproject]=1 THEN 'TRUE' " +\
                                       "WHEN [isproject]=0 THEN 'FALSE' END".format(fieldname))

            else:
                fieldnames_wrapped.append(fieldname)
                fieldnames_string = ','.join(fieldnames_wrapped)


        # execute sql to get all new/updated tickets
        ticket_sql = "select {0} from {1} where {2} in ({3})".format(fieldnames_string, self.ticket_table_name, self.ticket_id_field, job_id_string)
        self.logger.debug(ticket_sql)

        conn = arcpy.ArcSDESQLExecute(self.korterra_sde_path)
        results = conn.execute(ticket_sql)

        if results is None:
            raise Exception('Ticket query unsuccessful')
        elif results == True:
            # no results were returned
            raise Exception('Ticket query returned no results')

        with arcpy.da.InsertCursor(ticket_table, fieldnames) as cursor:
            for result in results:
                # remove empty strings because of date conversion error
                row = [value if isinstance(value, basestring) and len(value.strip()) != 0 else None for value in result]
                cursor.insertRow(tuple(row))

        return ticket_table
  • Could you edit your post to include which DBMS you are using? – Paul Nov 18 '16 at 18:57
1

Your SQL string has a .format(), but not a place to input it. ...unless "[isproject]" is really "{0}" in your code and you were trying to make it legible.

The following is what is giving you the syntax error:

        if 'isproject' in fieldname:
           fieldnames_wrapped.append("CASE WHEN [isproject]=1 THEN 'TRUE' " +\
                                   "WHEN [isproject]=0 THEN 'FALSE' END".format(fieldname))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.