topic Re: Insert Cursor to Table In Memory in Python Questions

Insert Cursor to Table In Memory

ClintonCooper1 — Thu, 31 Oct 2013 19:10:22 GMT

I am trying to take a large dataset and import (export or append) it into an "in memory" table where I can then run the calculations: I need to import three fields ( SOS_VOTERID, FEAT_SEQ and YEAR_Of_BIRTH). For my code below, I am just working with 2. I believe I need to run a search cursor on my orig table, and then run an insert cursor to import the data into my new table. I am running into an error that says:

Runtime error  Traceback (most recent call last):   File "<string>", line 23, in <module> TypeError: sequence size must match size of the row

import arcpy, collections  from arcpy import env env.workspace = r"C:\Users\cc1\Desktop\NEW.gdb\WAYNE" table = "WAYNE"  table2 = arcpy.CreateTable_management("in_memory", "WAYNE") arcpy.AddField_management(table2, "SOS_VOTERID","TEXT", field_length=25) arcpy.AddField_management(table2, "FEAT_SEQ","LONG")  newList = {row[0]: row[1] for row in arcpy.da.SearchCursor(table, ["SOS_VOTERID","FEAT_SEQ"])}                                         tbl = arcpy.ListTables("*")                       for table in tbl:     fieldList = arcpy.ListFields(table)     for field in fieldList:         newList.append([table,field.name])    #  this populates the new list with table and field to directly insert to new table with arcpy.da.InsertCursor(table2, ['SOS_VOTERID', 'FEAT_SEQ']) as insert:     for f in newList:                                          insert.insertRow(f) del insert

Anyone know where I am going wrong? Thanks!

Clinton

Re: Insert Cursor to Table In Memory

ChrisSnyder — Thu, 31 Oct 2013 20:05:36 GMT

I think you would probably want some code that looked more like this:

http://forums.arcgis.com/threads/66434-A-better-way-to-run-large-Append-Merge-jobs?p=230850&viewfull=1#post230850

No need to store the data in a list or dictionary. Just read it via the search cursor and then write it directly to the in_memory table.

Nice dictionary comprehension BTW! Forgot that was supported now in v2.7... I learned something today.

Re: Insert Cursor to Table In Memory

ClintonCooper1 — Thu, 31 Oct 2013 23:14:22 GMT

I think you would probably want some code that looked more like this:

http://forums.arcgis.com/threads/66434-A-better-way-to-run-large-Append-Merge-jobs?p=230850&viewfull=1#post230850

No need to store the data in a list or dictionary. Just read it via the search cursor and then write it directly to the in_memory table.

Nice dictionary comprehension BTW! Forgot that was supported now in v2.7... I learned something today.

I kind of understand where you are coming from, but not fully. I am having issues as to where I put the line:

arcpy.CreateTable_management("in_memory", "WAYNE")

at within the code. Could you give a little bit more explanation of how to write this code? Thanks!!

Re: Insert Cursor to Table In Memory

ChrisSnyder — Thu, 31 Oct 2013 23:40:55 GMT

It'd look something like this I think - see how for each loop of the search cursor, the data (the searchRow tuple) gets fed dirtectly to the insertRow?

import arcpy inputTbl = r"C:\Users\cc1\Desktop\NEW.gdb\WAYNE" outputTbl = str(arcpy.CreateTable_management("in_memory", "WAYNE").getOutput(0)) arcpy.AddField_management(outputTbl, "SOS_VOTERID","TEXT", field_length=25) arcpy.AddField_management(outputTbl, "FEAT_SEQ","LONG") insertRows = arcpy.da.InsertCursor(outputTbl, ["SOS_VOTERID","FEAT_SEQ"]) searchRows = arcpy.da.SearchCursor(inputTbl, ["SOS_VOTERID","FEAT_SEQ"]) for searchRow in searchRows:     insertRows.insertRow(searchRow) del searchRow, searchRows, insertRows

Re: Insert Cursor to Table In Memory

ClintonCooper1 — Sat, 11 Dec 2021 16:24:32 GMT

It'd look something like this I think - see how for each loop of the search cursor, the data (the searchRow tuple) gets fed dirtectly to the insertRow?

import arcpy
inputTbl = r"C:\Users\cc1\Desktop\NEW.gdb\WAYNE"
outputTbl = str(arcpy.CreateTable_management("in_memory", "WAYNE").getOutput(0))
arcpy.AddField_management(outputTbl, "SOS_VOTERID","TEXT", field_length=25)
arcpy.AddField_management(outputTbl, "FEAT_SEQ","LONG")
insertRows = arcpy.da.InsertCursor(outputTbl, ["SOS_VOTERID","FEAT_SEQ"])
searchRows = arcpy.da.SearchCursor(inputTbl, ["SOS_VOTERID","FEAT_SEQ"])
for searchRow in searchRows:
    insertRows.insertRow(searchRow)
del searchRow, searchRows, insertRows

Thank you so much! This worked great!

I do have one more question. To get the benefits from in memory performance, once I create a table in memory, will all the subsequent calculations/operations done on that table be in memory as well or do I need to continue to call the in memory operation? Thanks!

Re: Insert Cursor to Table In Memory

Anonymous User — Sat, 11 Dec 2021 16:24:35 GMT

outputTbl = str(arcpy.CreateTable_management("in_memory", "WAYNE").getOutput(0))

That is a nice trick.

Re: Insert Cursor to Table In Memory

ClintonCooper1 — Mon, 04 Nov 2013 21:03:42 GMT

I still have this one last question. To get the benefits from in memory performance, once I create a table in memory, will all the subsequent calculations/operations done on that table be in memory as well or do I need to continue to call the in memory operation? Thanks!

Re: Insert Cursor to Table In Memory

ChrisSnyder — Mon, 04 Nov 2013 21:41:02 GMT

It depends...

Some operations (such as AddField or CalculateField) can operate directly on the in_memory table. Other operations (such as Frequency or SummaryStatistics) create a new output table that, depending on your specifications, may be written to either the in_memory workspace or to disk.

That said, reading input from in_memory and then writting the output to in_memory would of course be the fastest option you have. Only worry is if you have enough available RAM to complete the process(es)...

Re: Insert Cursor to Table In Memory

ClintonCooper1 — Tue, 05 Nov 2013 00:10:07 GMT

I have plenty of memory, but I am not seeing any increase in performance 😞 My workflow consists of creating a new table in memory, adding the fields and indexes, appending the data in, and then performing about 7 field calculations using data cursors. I have runt he process both in memory and regular, and they are both running at around 23 hours to complete. I have seen that once your dataset gets a to a certain size, you lose your in memory performance gains. I guess that I am seeing that with my data set. Thanks for all your help!

Clinton

Re: Insert Cursor to Table In Memory

JamesCrandall — Wed, 06 Nov 2013 13:15:06 GMT

I have plenty of memory, but I am not seeing any increase in performance 😞 My workflow consists of creating a new table in memory, adding the fields and indexes, appending the data in, and then performing about 7 field calculations using data cursors. I have runt he process both in memory and regular, and they are both running at around 23 hours to complete. I have seen that once your dataset gets a to a certain size, you lose your in memory performance gains. I guess that I am seeing that with my data set. Thanks for all your help!

Clinton

This may or may not help (maybe just confuse the approach), but I wanted to mention that the in_memory is definitely a performance gainer as long as the RAM is available. The other performance gain is achieved by performing the tabular operations on non-ESRI specific objects.

So, we heavily use Pandas Data Frame objects to join/merge tabular data as well as populate new fields with math/statistic operations. We simply convert back and forth between esri and pandas objects using NumPyArrayTo..FeatureClass/Table and FeatureClass/Table..ToNumpyArray

Just something to think about as it may provide the performance you require.

Re: Insert Cursor to Table In Memory

ChrisSnyder — Sat, 11 Dec 2021 16:24:37 GMT

Another option is to just use a dictionary... this way there is no dependency on 3rd party modules. Although I admit I need to check out pandas as it seems pretty cool.

The dictionary option though is a built-in, simple, and powerful one, and can be made to be quite robust with not a lot of code. A very simple example:

valueDict = {r[0]:[r[1],r[2]] for r in arcpy.da.SearchCursor(myFC, ["OID@","ID","NAME"])}
#add/calc a new "field" that is float(OID - 1) * OID
for key in valueDict:
   valueDict[key].append(float(key -1) * key)

However, the real power comes from building RDBMS-like table structures, and being able to run complex queries (especially recursive ones) crazy fast.

Adding calcing fields via Dictionaries or Numpy Arrays (pandas) will be orders of magnitude faster than any ESRI table/tool - even in the in_memory workspace.

Clinton, how many records are you dealing with here... 23 hours seems a bit over the top.

Re: Insert Cursor to Table In Memory

JoshuaBixby — Wed, 14 May 2014 20:17:56 GMT

I know this thread is older, but I figured I would chime in because I have been running into problems with using in_memory workspaces.

When it comes to using the in_memory workspace in the interactive python window when background processing is enabled; well, all bets are off. There are numerous tools that don't actually create GPInMemoryWorkspaces when using 'in_memory' with 'Background Processing' in the interactive Python window. I primarily work in ArcGIS 10.1, but I have also noticed the same is true with 10.2.1.

If 'in_memory' doesn't mean in RAM, then performance could be much lower than expected. If using 'Background Processing' with the interactive Python windows, it pays to double check whether your 'in_memory' objects are actually in memory and not in disk-based temporary file geodatabases.