topic Re: how do I read a table that is in a geodatabase in Python Questions

how do I read a table that is in a geodatabase

TerrySilveus — Tue, 05 Apr 2011 22:30:10 GMT

how do I read a table that is in a geodatabase

I tried putting this into a arcpy.SearchCursor but its not working (not a feature class or shapefile). I can't read it with open because it's not a file in that sense. So how do you load a table located in a geodatabase using python code and iterate through the rows to read some value from a specific column?

Re: how do I read a table that is in a geodatabase

ChrisMathers — Wed, 06 Apr 2011 14:37:28 GMT

Can you post the code you are trying? A search cursor should be able to open a table in a GDB.

Re: how do I read a table that is in a geodatabase

TerrySilveus — Sun, 12 Dec 2021 07:37:11 GMT

This is just for my benefit... I'm trying to teach myself python. I have programming experience with other languages, but not python and not arcpy (or arcgisscripting for that matter)

import arcpy

#Initialize variables
searchDSParam = None
searchLinkParam = None
searchFieldParam = None
updateDSParam = None
updateLinkParam = None
updateFieldParam = None

#arcGIS toolbox script
#searchDSParam = arcpy.GetParameterAsText(0)
#searchLinkParam = arcpy.GetParameterAsText(1)
#searchFieldParam = arcpy.GetParameterAsText(2)
#updateDSParam = arcpy.GetParameterAsText(3)
#updateLinkParam = arcpy.GetParameterAsText(4)
#updateFieldParam = arcpy.GetParameterAsText(5)

#for testing
searchDSParam = "C:\GIS\VirtualCampus\SpatialStatsModelBuilder\ModelBuilderTutorial\CDCWonderAnalysis.gdb\HeartDiseaseSouth_1999"
searchLinkParam = "CountyCode"
searchFieldParam = "HD_Rate"
updateDSParam = "C:\GIS\VirtualCampus\SpatialStatsModelBuilder\ModelBuilderTutorial\CDCWonderAnalysis.gdb\HD_Rate_1999"
updateLinkParam = "FIPS"
updateFieldParam = "HDrate"

updateTheRow = None
updateTheRows = None
searchTheRow = None
searchTheRows = None
updateTheFields = None
searchTheField = None
searchTheFields = None
fldType = None
fldPrecision = None
fldScale = None
fldLength = None
whereClause = None
linkTypeText = False

#Dictionary to convert return value from field.type to set value using AddField
fieldTypeDictionary = {'SmallInteger': 'SHORT', 'Integer': 'LONG', 'Single': 'FLOAT', 'Double': 'DOUBLE', 'String': 'TEXT', 'Date': 'DATE'}
changed = 0
try:
    #get work done
    #add field to update dataset if necessary
    #search cursor has data to be applied to update cursor (in searchFieldParam field)
    #update cursor has field to be updated (updateFieldParam gets data from searchFieldParam)
    #link update cursor with search cursor on fields searchLinkParam/updateLinkParam

    updateTheFields = arcpy.ListFields(updateDSParam) #get a list of fields in shapefile to be updated
    searchTheFields = arcpy.ListFields(searchDSParam) #get a list of fields in the shapefile to be searched
    for searchTheField in searchTheFields:
        if searchTheField.name == searchFieldParam:
            fldType = searchTheField.type
            fldPrecision = searchTheField.precision
            fldScale = searchTheField.scale
            fldLength = searchTheField.length
        if searchTheField.name == searchLinkParam:
            if searchTheField.type == "String":
                linkTypeText = True
            
    #check to see if it has the field that will be updated, if it has it good; if not then add the field
    #NOT SURE THIS IS WORKING FOR THE CASE WHEN FIELD ALREADY EXISTS - HAVE NOT TESTED YET - BUT IT ADDS FIELD OK (Have been deleting the field for testing)
    if not updateFieldParam in updateTheFields:
        #field does not exist - need to add field
       arcpy.AddField_management(updateDSParam,updateFieldParam,fieldTypeDictionary[fldType], fldPrecision, fldScale, fldLength) #adds field to data set

    updateTheRows = arcpy.UpdateCursor(updateDSParam) #get the rows from the shapefile that will have a field updated
    
    for updateTheRow in updateTheRows:
        #loop through all the rows in the update cursor
        if linkTypeText:
            whereClause = "'" + searchLinkParam + "' = '" + updateTheRow.getValue(updateLinkParam) + "'"
        else:
            whereClause = "'" + searchLinkParam + "' = " + updateTheRow.getValue(updateLinkParam) 

        if updateTheRow.getValue(updateLinkParam) == "01001":
            print updateTheRow.getValue(updateLinkParam)

            
        # THIS SECTION NOT WORKING...SEARCH CURSOR DOESN'T LOAD DATA (EVEN WITHOUT WHERECLAUSE)            
        searchTheRows = arcpy.SearchCursor(searchDSParam,whereClause)
        for searchTheRow in searchTheRows:
            if searchTheRow.getValue(searchFieldParam) <> "":
                updateTheRow.setValue(updateFieldParam,searchTheRow.getValue(searchFieldParam))
                updateTheRows.updateRow(updateTheRow)
                changed = changed + 1
                break #exit search cursor loop - you found first row with data and updated row
            
        searchTheRows = None
    updateTheRows = None
    
except:
    # Display exceptions
    if not arcpy.GetMessages() == "":
        arcpy.AddMessage(arcpy.GetMessage(2))

finally:
    #clean up everything
    if updateTheFields:
        del updateTheFields

    if searchTheFields:
        del searchTheFields

    if searchTheField:
        del searchTheField

    if searchTheRow:
        del searchTheRow

    if updateTheRow:
        del updateTheRow

    if updateTheRows:
        del updateTheRows

    if searchTheRows:
        del searchTheRows

    if searchDSParam:
        del searchDSParam

    if searchLinkParam:
        del searchLinkParam

    if updateDSParam:
        del updateDSParam

    if searchFieldParam:
        del searchFieldParam

    if updateLinkParam:
        del updateLinkParam

    if updateFieldParam:
        del updateFieldParam

    if fldLength:
        del fldLength

    if fldType:
        del fldType

    if fldPrecision:
        del fldPrecision

    if fldScale:
        del fldScale

    if whereClause:
        del whereClause

    if linkTypeText:
        del linkTypeText

    if fieldTypeDictionary:
        del fieldTypeDictionary

Re: how do I read a table that is in a geodatabase

TerrySilveus — Wed, 06 Apr 2011 21:01:01 GMT

So now I'm thinking it is my where clause... even though I'm sure I tested it without the where clause and got the same issue. I decided to test without again, and this time it looks like it did load the search cursor. So, below is what my where clause looks like as it is passes through the update cursor loop on an iteration that I know has a match... So what exactly should be surrounding the field name in the where clause for the search cursor? 'fldName' or "fldName" or !fldName! or [fldName] or something else?

This is my whereClause

"'CountyCode' = '01001'"

Re: how do I read a table that is in a geodatabase

LoganPugh — Wed, 06 Apr 2011 21:36:49 GMT

I'd suggest just writing a barebones version to make sure the actual SearchCursor part works and for other readers here to easily make sense of it. Rewriting it will probably help you figure out what went wrong the first time. Also you don't need to delete variables in Python with a few exceptions such as rows and cursors.

Re: how do I read a table that is in a geodatabase

DarrenWiens2 — Wed, 06 Apr 2011 21:43:28 GMT

I'm not totally sure, but I think the where clause switches you to SQL syntax. So, it would be:

CountryCode = '01001' <- all enclosed in double-quotes in SearchCursor

So,

whereClause = "CountryCode = '01001'"

That's really just my best guess - I'm learning, too. 🙂

Re: how do I read a table that is in a geodatabase

TerrySilveus — Wed, 06 Apr 2011 21:48:48 GMT

Thanks for the advice regarding deleting variables. I will limit that to just deleting the cursor and row variables. However, I'm not sure how much barer this code can be. It's pretty slim as it is.

it creates a field (this part works)
it loads an update cursor (this part works)
it loads a search cursor (this part doesn't work) -when I use a where clause, but it works without the where clause, so there is an issue with my where clause. I now think I just need to know how to wrap the field name.

Re: how do I read a table that is in a geodatabase

TerrySilveus — Wed, 06 Apr 2011 22:35:58 GMT

on another note... is there any way in python that I can just write an sql statement and use that instead or somehow pass an entire sql statement to the cursor?

Re: how do I read a table that is in a geodatabase

TerrySilveus — Wed, 06 Apr 2011 22:55:52 GMT

I'm not totally sure, but I think the where clause switches you to SQL syntax. So, it would be:

CountryCode = '01001' <- all enclosed in double-quotes in SearchCursor

So,

whereClause = "CountryCode = '01001'"

That's really just my best guess - I'm learning, too. 🙂

That's it. You apparently don't need to wrap the field name for the where clause. Thank you.

Re: how do I read a table that is in a geodatabase

MarkWiygul — Sun, 12 Dec 2021 07:37:14 GMT

Hi there. I'm learning from this thread. I'm understanding that the where clause is an SQL line, correct? If that's the case and you need to provide a quoted expression as a parameter, and if a variable representing that expression is not quated, then you could use the...
repr() function with the variable as an argument to return extra quotes into the parameter as follows..
Thanks for posting your tests. Let me know if you have any insight on what I'm figuring...

 
Python 2.5
IDLE 1.2.1  
>>>help(gp.searchcursor)           # gp = arcgisscripting.create(9.3) 
Help on built-in function searchcursor:

searchcursor(...)
    searchcursor(<dataset>, {where_clause}, {spatial_reference}, {fields}, {sort_fields}) -> object
    Returns a search cursor object against the given table/feature class.

>>> a = "anyStatement = '01001'"
>>> a
"anyStatement = '01001'"
>>> print a
anyStatement = '01001'
>>> print repr(a)
"anyStatement = '01001'"
>>>

Re: how do I read a table that is in a geodatabase

TerrySilveus — Sat, 09 Apr 2011 05:51:04 GMT

Hi there. I'm learning from this thread. I'm understanding that the where clause is an SQL line, correct? If that's the case and you need to provide a quoted expression as a parameter, and if a variable representing that expression is not quated, then you could use the...
repr() function with the variable as an argument to return extra quotes into the parameter as follows..
Thanks for posting your tests. Let me know if you have any insight on what I'm figuring...

The where clause is only a partial sql statement and while that does work and I got my answer regarding why my where clause didn't work, thanks everyone. I asked if anyone knew how to use python with a full sql statement. I'm not sure in you code what are asking with anystatemt. For the whereclause in sql is typically fieldname = value. You will need to put the value in quotes if it is a string, but not if it is a number; I'm not sure how to encase a date in python. If your value is a variable you need to build the whereclause by concatenating field and value. In my case I had two variables to build my where clause. One was for the name of the field, the other was the value of the data to be selected. In my case the data value was of string type so I had to encased it with quotes the value was '01001' The results I showed was after building the where clause, but it didn't work because I also encased the field name in quotes. I did that because I that's how the field calculator does it. I also tried encasing it in ! bangs, but that didn't work either. I read somewhere that python required field names be encased in the ! symbol, but I guess that's only in the field calculator. If someone has any comments on when the field name should be enclosed in a pair of symbols and what determines which symbol to use I would really like to know. I've seen it with single and double quotes, bangs, and brackets. What is the determination on when to use what? Also, for future reference, in python strings can be denoted with either single or double quotes, numbers are not wrapped with anything, but what about dates. In VBA it was a # pound sign, what is it in python?

Re: how do I read a table that is in a geodatabase

MathewCoyle — Sun, 12 Dec 2021 07:37:17 GMT

This is a cursor I use to find dates.

fc = # Feature Class with date field
field = "DateField"
rows = arcpy.SearchCursor(fc, field+" = date '2011-02-22'")
...

Using times I think would be something like. I haven't had the opportunity to test this though.

rows = arcpy.SearchCursor(fc, field+" = date '2011-02-22 00:00:00'")

For HH:MM:SS.

In terms of when to use what brackets etc, this link has some good info on syntax using different data types.
http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//00s500000033000000.htm