Skip to main content
Question

How to read SDE layer in python startup script?

  • January 7, 2014
  • 1 reply
  • 64 views

andrew_r
Contributor
Forum|alt.badge.img
I am having problems reading an SDE feature class in python using the 'SDE30' reader type. Has anyone had success with this reader?  I have tried inputing the parameters several ways and it appears to connect to the database, however it does not actually read any records.  At the end of the python section of the workbench logfile it says 0 records read...but no error.  For testing I have planted some errors so if the script was reading the individual records it woudl raise specific exceptions.

 

 

In order to figure out the parameter syntax I'm going off another post from one of my coworkers Logan at...

 

http://fmepedia.safe.com/AnswersQuestionDetail?id=906a0000000cldqAAA

 

 

Mine is a little different since he used the geodatabase SDE reader and I'm using the basic one named 'SDE30'  .  I'm feeding in the connection parameters from published parameters in my workspace.  He used an .sde connection file.

 

 

Below is my code.  Basically I want to check the input features to make sure they all have unique ID's and they don't have nulls in a few required fields before the workspace is allowed to run.

 

 

Thanks for any help you can provide,

 

 

Andrew

 

 

import fmeobjects

 

import collections

 

 

 

def countNullsAndDuplicatesInField(dataset, unique_id_field_name, reader_format, parameters):

 

    id_nulls = 0

 

    created_by_nulls = 0

 

    created_date_nulls = 0

 

    fieldvaluecounts = collections.defaultdict(int)

 

    logger.logMessageString("Andrew 1")

 

    reader = fmeobjects.FMEUniversalReader(reader_format, False)

 

    logger.logMessageString("Andrew 2")

 

    ##reader = fmeobjects.FMEReader(reader_format, False)

 

    reader.open(dataset, parameters)

 

    logger.logMessageString("Andrew 3")

 

    feature = reader.read()

 

    logger.logMessageString("Andrew 4")

 

    while feature != None:

 

        logger.logMessageString("Andrew 5")

 

        id_fieldvalue = feature.getAttribute(id_field_name)

 

        if id_fieldvalue == 87984: raise Exception, 'Andrew you found drainage_id 87984'

 

        created_by_fieldvalue = feature.getAttribute('CREATED_BY')

 

        created_date_fieldvalue = feature.getAttribute('CREATED_DATE')

 

        

 

        if id_fieldvalue != None:

 

            fieldvaluecounts[fieldvalue] += 1

 

        else:

 

            id_nulls += 1

 

            

 

        if created_by_fieldvalue == None:

 

            created_by_nulls += 1

 

        

 

        if created_date_fieldvalue == None:

 

            created_date_nulls += 1

 

        feature = reader.read()

 

    reader.close()

 

    duplicate_rows = sum((v - 1 for v in fieldvaluecounts.itervalues() if v > 1))

 

    return id_nulls, duplicate_rows, created_by_nulls, created_date_nulls

 

        

 

if __name__ == "__main__":

 

    dataset = 'sde'

 

    reader_format = 'SDE30'

 

    instance_name = FME_MacroValues['SOURCE_DB_INSTANCE']

 

    username = FME_MacroValues['SOURCE_DB_USERNAME']

 

    password = FME_MacroValues['SOURCE_DB_PASSWORD']

 

    version = 'SDE.DEFAULT'

 

    table_name = FME_MacroValues['SOURCE_DB_TABLE_NAME']

 

    unique_id_field_name = FME_MacroValues['SOURCE_UPDATE_ID']

 

    

 

    logger = fmeobjects.FMELogFile()

 

    ##logger.logMessageString("Hey Andrew B")

 

    

 

    #Use this dialog to dtermine parameters syntax

 

    ##dialog = fmeobjects.FMEDialog()

 

    ##directives = dialog.sourcePrompt("SDE30", "sde")

 

    ##raise Exception('start directives: ' + ", ".join(directives[2]))

 

 

    parameters = ['SERVER',dataset,'USERID',username,'PASSWORD',password,'INSTANCE',instance_name,'VERSION_NAME',version,'TABLELIST',table_name]

 

    ##parameters = ['RUNTIME_MACROS', 'CONNECTION','Parameters','SERVER','server','USERID',username,'PASSWORD',password,'INSTANCE',instance_name,'VERSION_NAME',version,'REMOVE_TABLE_QUALIFIER','NO','TABLELIST',table_name,'WHERE','','SDE30_EXPOSE_FORMAT_ATTRS','','USE_SEARCH_ENVELOPE','NO','SEARCH_ENVELOPE_MINX',0,'SEARCH_ENVELOPE_MINY',0,'SEARCH_ENVELOPE_MAXX',0,'SEARCH_ENVELOPE_MAXY',0,'CLIP_TO_ENVELOPE','NO','_MERGE_SCHEMAS','YES', 'META_MACROS', 'SourceCONNECTION','Parameters','SourceSERVER','server','SourceUSERID',username,'SourcePASSWORD',password,'SourceINSTANCE',instance_name,'SourceVERSION_NAME',version,'SourceREMOVE_TABLE_QUALIFIER','NO','SourceWHERE','','SourceSDE30_EXPOSE_FORMAT_ATTRS','','SourceUSE_SEARCH_ENVELOPE','NO','SourceSEARCH_ENVELOPE_MINX',0,'SourceSEARCH_ENVELOPE_MINY',0,'SourceSEARCH_ENVELOPE_MAXX',0,'SourceSEARCH_ENVELOPE_MAXY',0,'SourceCLIP_TO_ENVELOPE','NO', 'METAFILE', 'SDE30', 'COORDSYS','' , 'IDLIST', table_name]

 

    ##parameters = directives[2]

 

    

 

    id_nulls, id_dups, created_by_nulls, created_date_nulls = countNullsAndDuplicatesInField(dataset, unique_id_field_name, reader_format, parameters)

 

    if id_nulls > 0 or id_dups > 0 or created_by_nulls > 0 or created_date_nulls > 0:

 

        error_list = []

 

        if id_nulls > 0:

 

            error_list.append('Null IDs found')

 

        if id_dups > 0:

 

            error_list.append('Duplicate Ids found')

 

        if created_by_nulls > 0:

 

            error_list.append('Null CREATED_BY field values found')

 

        if created_date_nulls > 0:

 

            error_list.append('Null CREATED_DATE field values found')

 

        error_message = ", ".join(error_list)

 

        raise Exception, error_message

 

 

 

 

 

 

Here's what the logfile shows during the python execution

 

 

2014-01-07 10:18:06| 0.4| 0.0|INFORM|FME_BEGIN_PYTHON: evaluating python script from string...

2014-01-07 10:18:06| 0.4| 0.0|INFORM|Andrew 1

2014-01-07 10:18:06| 0.4| 0.0|INFORM|Andrew 2

2014-01-07 10:18:06| 0.6| 0.2|INFORM|Making a direct connection using instance 'sde:oracle11g:giswpddev' and userId = 'WPDR_ADMIN'

2014-01-07 10:18:08| 0.6| 0.0|INFORM|Direct connection made using instance 'sde:oracle11g:giswpddev' and userId = 'WPDR_ADMIN'

2014-01-07 10:18:08| 0.6| 0.0|INFORM|ArcSDE release: '10.2'. Underlying database: '10.2 geodatabase(Oracle)'

2014-01-07 10:18:08| 0.6| 0.0|INFORM|ArcSDE Reader: Connecting to version 'SDE.DEFAULT'

2014-01-07 10:18:08| 0.7| 0.0|INFORM|Andrew 3

2014-01-07 10:18:08| 0.7| 0.0|INFORM|Using ArcSDE Reader $Revision: 89811 $ ( $Date: 2012-06-14 10:14:37 -0700 (Thu, 14 Jun 2012) $ ) to read Spatial Database Engine dataset `sde'

2014-01-07 10:18:08| 0.7| 0.0|STATS |READER_PIPELINE R_1::f_0(TestFactory): Tested 0 input feature(s) -- 0 feature(s) passed and 0 feature(s) failed

2014-01-07 10:18:08| 0.7| 0.0|STATS |READER_PIPELINE R_1::f_1(TeeFactory): Cloned 0 input feature(s) into 0 output feature(s)

2014-01-07 10:18:08| 0.7| 0.0|INFORM|Andrew 4

2014-01-07 10:18:08| 0.7| 0.0|INFORM|FME_BEGIN_PYTHON: python script execution complete.

1 reply

david_r
Celebrity
  • January 8, 2014
Hi,

 

 

one tip when using the FMEUniversalReader/Writer is to use the FMEDialog object to better control the parameters used. Example:

 

 

dialog = FMEDialog()

 

directives = dialog.sourcePrompt("SDE30", "sde")

 

print "The directives are:", directives

 

reader = FMEUniversalReader("SDE30", False, directives[2])

 

 

You can then specify the parameters interactively in the dialog, just as you would in the Workbench. You can then later hardcode these values into your application, if you want.

 

 

David

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings