topic Re: Load database rows into Python List or Dictionary? in Python Questions

Load database rows into Python List or Dictionary?

JamesMorrison1 — Mon, 27 Aug 2012 06:49:47 GMT

SDE 9.3.1
ArcGIS/Catalog 9.3.1
Python 2.5
Oracle 11.1.0.6.0

Using gp.CreateObject("ARCSDESQLEXECUTE", <conn_name>)

Table: T_TRANSFER

OBJECTID NUMBER(38)
FC_NAME TEXT(50)
FC_NAME_2 TEXT(50)
FC_NAME_3 TEXT(50)
TRANSFER_DATE (DATE)

SQL = SELECT * FROM T_TRANSFER ORDER BY FC_NAME

I'm a little new to Python and was wondering how you can read records from a Table in Oracle (SDE) into a List (?) or Dictionary(?) or "something-else" so I can reuse it later in my code.

Pseudo-code

[1] Load rows into Dictionary or List or "something-else"?

Later in code???

[2] Loop through rows make data count on Feature Classes named in Colum "FC_NAME"

[3] Loop through rows make delete on Feature Classes named in Colum "FC_NAME"

[4] Loop through rows make delete on Feature Classes named in Colum "FC_NAME_2"

Steps [2], [3] and [4] are ok to code - I just seem to be having issues in [1] and then making a reference to loop through rows.

Can anyone help?

Re: Load database rows into Python List or Dictionary?

KevinBell — Mon, 27 Aug 2012 15:13:46 GMT

Look up the arcmap help on search cursors. Once you have you value from the cursor you can append it to a list, or stick it in a dictionary.

Re: Load database rows into Python List or Dictionary?

SteveXu — Mon, 27 Aug 2012 16:42:13 GMT

Have you tried cx_Oracle extension? You can google on how to get cx_Oracle for python, make sure for your Oracle and python versions. Look at this http://www.oracle.com/technetwork/articles/dsl/python-091105.html. I used cx_Oracle with python, and found it is good to manipulate attribute data in oracle tables.

Re: Load database rows into Python List or Dictionary?

JamesMorrison1 — Sat, 11 Dec 2021 15:51:54 GMT

ok. This seems to work. Perhaps there are better ways?

def getFeatureClasses(schema, table, attribute, project_model): 

    dictFeatures = {}
    sql_val = "SELECT OBJECTID, " + attribute + " FROM " + schema + "." + table + " WHERE PROJECT_MODEL = '" + project_model + "' ORDER BY OBJECTID DESC"
    # read value
    print "SQL: " + sql_val  
    WriteOutLine("SQL: " + sql_val)
    
    sdeReturn = sdeConn.Execute(sql_val) 
    for row in sdeReturn:
        print row
        fID = row[0]
        dictFeatures[fID] = row
              
         
    return dictFeatures
    

    
#------------------------------------------------------------------------
# ----------------   Back up to Load ready for loading
#------------------------------------------------------------------------

error_msg = "ERROR: Back up to Load ready for loading."

try:
    
    fcsDict = getFeatureClasses("GEO", "TRANSFER", "FC_NAME", project_model)

    keyFeatures = fcsDict.keys()
    keyFeatures.sort()
    
    for key in keyFeatures:
        row = fcsDict[key]
        from_feature_class = row[1]
        data_backup(DATA_CONN_FOLDER, LOAD_DIR, FGDB_from, sSchema_to, sSDEconn_to, from_feature_class)


except OSError, (errno, strerror):
    print ERROR_STR % {'path' : error_msg, 'error': strerror }