Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

Good morning all, I have a script here that takes data from a cross-tabulated non-spatial dataset where monthly values are stored in a single table row for a given year and linearizes those data so that each monthly value is a new record in a new table. I'm using arcpy to create a search cursor to grab the cross-tabulated data and an insert cursor to write the linearized data to a new table. Here is a screenshot of the original table (obviously there are other ancillary fields as well):

enter image description here

The script:

import arcpy, os

months = {'JAN':1,'FEB':2,'MAR':3,'APR':4,'MAY':5,'JUN':6,'JUL':7,'AUG':8,
          'SEP':9,'OCT':10,'NOV':11,'DEC':12}
ignore = ['Arbitrary_count','TOTAL_MGY','JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC']

ws = r'D:\Data\Users\jbellino\Project\faswam\data\water_use\SC\FromTomAb\SC_WELLS_data_jcb.mdb'
arcpy.env.workspace = ws
arcpy.env.overwriteOutput = True
tbl = 'ORIGINAL_DHEC_WELL DATA'
itbl = 'monthly_dhec_well_data'

fields = arcpy.ListFields(tbl)

rows = arcpy.SearchCursor(os.path.join(ws,tbl))
irows = arcpy.InsertCursor(os.path.join(ws,itbl))
for row in rows:
    for month in months:
        #--for each row in the original table, and for each month stored in that row, 
        # create a new record in 'itbl'
        irow = irows.newRow()
        for field in fields:
            if field.name == month:
                #--if the field name refers to a month abbreviation it contains data
                # first convert the month abbreviation to month number
                irow.cn_mo = months[month]
                try:
                    # then grab the data in the field and process it into the appropriate 
                    # fields of the new table in the correct units
                    irow.cn_qnty_mo_va = row.getValue(field.name)*1000000
                    irow.cn_qnty_mo_va_mega = row.getValue(field.name)
                except:
                    #--skip null values
                    pass
            elif field.name not in ignore:
                #--if the field name is not a month abbreviation, just copy the data
                # to the new table
                irow.setValue(field.name,row.getValue(field.name))
        irows.insertRow(irow)
del irows

Now, the problem is that the script will process every row in the original table, however not all rows are transferred into the new linearized table. The number of records that do make it is seemingly random and it has happened with a number of different source and destination tables. My work around has been to create/delete my insert cursor inside the for month in months: loop, but this can really bog things down with large datasets. Even then the script always "misses" the last record in the original table and I have to append the monthly values to the new table manually.

Any ideas of what may be going on here? AdvTHANKSance.

share|improve this question
    
I see you are using personal geodb, but perhaps it is related to this bug re: insert cursors w/ sde versions (maybe the issue is wider than the reported bug)? support.esri.com/en/bugs/nimbus/TklNMDc5NDk2 –  Jay Cummins May 1 '12 at 14:57
    
It's certainly possible... –  Jason May 1 '12 at 15:14
    
Thank you so much, I spent the entire day yesterday trying to figure out why this was not working! –  user17731 May 1 '13 at 13:25
add comment

2 Answers

While @Jason's answer is correct. I'm going to explain why del irow,irows is necessary for proper operation.

The reason for using del irow,irows is when a cursor is created, a lock on that file is imposed, so that other process or thread can't read or write that data. When rows = arcpy.SearchCursor(os.path.join(ws,tbl)) is executed a shared lock is held on that shape file and when irows = arcpy.InsertCursor(os.path.join(ws,itbl)) is executed an exclusive lock is held on that file. When processes are holding locks on any file other process can't read or write on that file. So after processing cursors the the lock should be removed so another process can use locked files. del irow,irows statement unlinks irow and irows between variable and actual object. When the link between variable and object is cut off, object is deallocated from memory by python garbage collector. As there's no object is holding the lock, other processes can hold another lock.

share|improve this answer
    
Thanks for adding that. Though I still don't understand why an insert cursor would write, say, 80% of the rows and then just not write the last 20% if the irow and irows variables/objects aren't deleted at the end of the script. –  Jason May 30 '12 at 14:33
    
Because you aren't releasing the lock. The lock is still on your feature set. When at first time you are inserting you are getting feature class unlocked. But after inserting, if you don't release the lock, then for next iteration you will not get the lock. Hence the insertion operation fails. –  user May 31 '12 at 1:48
    
I've also noticed there are some bugs either in ArcGIS or arcpy. Sometimes it just shows some weird values –  user May 31 '12 at 3:44
add comment
up vote 3 down vote accepted

I had initially neglected to delete my irow object at the end of the script; after updating my code the insert cursor appears to be adding all rows (even the last one!) the way it should be. Here is the final code:

import arcpy, os

months = {'JAN':1,'FEB':2,'MAR':3,'APR':4,'MAY':5,'JUN':6,'JUL':7,'AUG':8,
          'SEP':9,'OCT':10,'NOV':11,'DEC':12}
ignore = ['Arbitrary_count','TOTAL_MGY','JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC']

ws = r'D:\Data\Users\jbellino\Project\faswam\data\water_use\SC\FromTomAb\SC_WELLS_data_jcb.mdb'
arcpy.env.workspace = ws
arcpy.env.overwriteOutput = True
tbl = 'ORIGINAL_DHEC_WELL DATA'
itbl = 'monthly_dhec_well_data'

fields = arcpy.ListFields(tbl)

rows = arcpy.SearchCursor(os.path.join(ws,tbl))
irows = arcpy.InsertCursor(os.path.join(ws,itbl))
for row in rows:
    for month in months:
        #--for each row in the original table, and for each month stored in that row, 
        # create a new record in 'itbl'
        irow = irows.newRow()
        for field in fields:
            if field.name == month:
                #--if the field name refers to a month abbreviation it contains data
                # first convert the month abbreviation to month number
                irow.cn_mo = months[month]
                try:
                    # then grab the data in the field and process it into the appropriate 
                    # fields of the new table in the correct units
                    irow.cn_qnty_mo_va = row.getValue(field.name)*1000000
                    irow.cn_qnty_mo_va_mega = row.getValue(field.name)
                except:
                    #--skip null values
                    pass
            elif field.name not in ignore:
                #--if the field name is not a month abbreviation, just copy the data
                # to the new table
                irow.setValue(field.name,row.getValue(field.name))
        irows.insertRow(irow)
del irow,irows
del row,rows
share|improve this answer
    
Ok, so this was a pretty silly mistake on my part, however I'm going to leave this post up so that others who might be banging their head on a similar problem won't have to re-ask this question again. If anyone has other thoughts as to the usefulness of this post, lets discuss it rather than ghosting in and down-voting. –  Jason May 7 '12 at 18:20
    
Why you are writing del irow,irows this statements? What is purpose of this statement? –  user May 30 '12 at 9:23
1  
@crucifiedsoul, I have run in to problems writing rows of data if the irow and irows objects are not deleted from memory. I'm sure someone else can explain why this is the case, but if you don't delete these objects the cursor will process all rows, but only write a portion of them. –  Jason May 30 '12 at 13:37
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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