topic Re: Oracle to GDB table copy (using a where clause to limit rows) in Python Questions

Oracle to GDB table copy (using a where clause to limit rows)

KendallRussell — Thu, 24 Apr 2014 14:29:13 GMT

I am having difficulty finding a way that will copy a subset of rows from a table stored in Oracle to a GDB.

Here is an example:
Source table contains the following fields - "SCHEDULE_TIME", "ADDRESS", "CITY", "STATE", "ZIP_CODE", "ROUTE_ID", "x", "y"

I am trying to extract all of the records for a route_id in one zip_code.

Here is what I have tried so far:

1. The following code ended up copying the entire table to the GDB.

 strZipCode = "72645" strRouteId = "C024"  sqlWhereStmt="\"ZIP_CODE\"='{0}' and \"ROUTE_ID\"='{1}'".format(strZipCode, strRouteId)  #MAKE A VIEW OF THE TABLE AND LIMIT THE ROWS BY THE WHERE CLAUSE arcpy.MakeTableView_management("SourceRoute_T", "SourceRoute_V", sqlWhereStmt)  #COPY THE TABLE BY USING THE VIEW CREATED ABOVE arcpy.TableToTable_conversion("SourceRoute_V", "C:\\DDT\\Work\\work.gdb", "route_72645C024_T")

2. This following code failed during the call to TableToTable_conversion with an error about the source table not having OIDs
The only change is that the where clause has been moved to the TableTable_conversion function.

 strZipCode = "72645" strRouteId = "C024"  sqlWhereStmt="\"ZIP_CODE\"='{0}' and \"ROUTE_ID\"='{1}'".format(strZipCode, strRouteId)  #MAKE A VIEW OF THE TABLE AND LIMIT THE ROWS BY THE WHERE CLAUSE arcpy.MakeTableView_management("SourceRoute_T", "SourceRoute_V")  #COPY THE TABLE BY USING THE VIEW CREATED ABOVE arcpy.TableToTable_conversion("SourceRoute_V", "C:\\DDT\\Work\\work.gdb", "route_72645C024_T", sqlWhereStmt)

Re: Oracle to GDB table copy (using a where clause to limit rows)

MathewCoyle — Thu, 24 Apr 2014 14:32:26 GMT

Can you copy in your entire code you executed?

If you have set your workspace to SDE use a staging area to save your table view.

Re: Oracle to GDB table copy (using a where clause to limit rows)

KendallRussell — Sat, 11 Dec 2021 07:09:54 GMT

Can you copy in your entire code you executed?

If you have set your workspace to SDE use a staging area to save your table view.

#IMPORTS - PYTHON
import os
#IMPORTS - THIRD PARTY
import arcpy
#IMPORTS - PROJECT

#ESRI - CHECKOUT LICENSE
arcpy.CheckOutExtension("Network")
#ESRI - Set Geoprocessing environments
arcpy.env.XYResolution = "1 Meters"
arcpy.env.XYTolerance = "1 Meters"

#LOCAL GDB
gdbWorkLoc = "D:\\arc10data\\DMS\\Scripts\\Work"
gdbWorkName= "work.gdb"
gdbWorkFull= "{0}\\{1}".format(gdbWorkLoc, gdbWorkName)

#DATABASE PIVOT TABLE
sdeDmsConn = "D:\\arc10data\\DMS\\Scripts\\DBConnections\\DAISGS-DMS.sde"
dbtPivot     = "{0}\\DMS_ADM.DPIVOT_T".format(sdeDmsConn)

def main():
    #ESTABLISH ZIP AND ROUTE TO WORK WITH
    strZipCode = "38111"
    strCridId  = "C061"
    strZipCrid = strZipCode + strCridId

    #OUTPUT GDB TABLE
    tblRouteSingleName = "Route_{0}_T".format(strZipCrid)
    tblRouteSingleFull = "{0}\\{1}".format(gdbWorkFull, tblRouteSingleName)

    #OUTPUT VIEW OF PIVOT TABLE
    viewPivot = "PivotView"

    #CREATE OUTPUT GDB
    if not os.path.exists(gdbWorkFull):
        arcpy.CreateFileGDB_management(gdbWorkLoc, gdbWorkName)

    #BUILD WHERE CLAUSE TO LIMIT ROWS
    sqlWhere="\"ZIP_CODE\"='{0}' and \"CRID_ID\"='{1}'".format(strZipCode, strCridId)

    #DELETE GDB TABLE IF EXISTS
    if arcpy.Exists(tblRouteSingleFull):
        arcpy.Delete_management(tblRouteSingleFull)

    r1=arcpy.MakeTableView_management(dbtPivot, viewPivot, sqlWhere)
    r2=arcpy.TableToTable_conversion(viewPivot, gdbWorkFull, tblRouteSingleName)


if __name__ == '__main__':
    main()

Re: Oracle to GDB table copy (using a where clause to limit rows)

MathewCoyle — Sat, 11 Dec 2021 07:09:56 GMT

This is the code that exports the entire table? Nothing looks glaringly wrong. What version of ArcGIS are you using?

The first thing I would try is removing the maketableview completely. Use the query parameter for the tabletotable function instead to see if you get any different results. I would also change your query to something like this. Also verify that your query is valid and returns the expected results using ArcMap.

    sqlWhere = "{0} = '{1}' AND {2} = '{3}'".format(
        arcpy.AddFieldDelimiters(dbtPivot, 'ZIP_CODE'), strZipCode,
        arcpy.AddFieldDelimiters(dbtPivot, 'CRID_ID'), strCridId)

I just have a few other minor suggestions.
You shouldn't need the r1= and r2= parts. Also try setting your workspace to your output GDB. And, os.path.join is a better way of joining paths, yours seem like they should be fine and you would be getting some different errors if that was the issue. But it is something to keep in mind.

Re: Oracle to GDB table copy (using a where clause to limit rows)

KendallRussell — Sat, 11 Dec 2021 07:09:59 GMT

Yes, this is the code that ends up exporting the entire table.

Here is what I have done so far and the results:
1. Added AddFieldDelimiters() to the build of the where clause. This built the where clause differently - no double quotes around the field names.

Result: No difference, the entire table was exported to the GDB.

Note: I ran these steps in ArcMAP - MakeTableView_management() with the where clause then TableToTable_conversion() and it did the same thing. The entire table was exported to the GDB.

2. I moved the where clause from the MakeTableView_management() to the TableToTable_conversion() function.

    arcpy.MakeTableView_management(dbtPivot, viewPivot)
    arcpy.TableToTable_conversion(viewPivot, gdbWorkFull, tblRouteSingleName, sqlWhere)

Result: Execution Error
"ExecuteError: ERROR 000339: Input DMS_ADM.DPIVOT_T does not have OIDs
Failed to execute (TableToTable)"

Note: same results when ran in ArcMap

3. I removed MakeTableView() (I left in the AddFieldDelimiters()) and I just called TableToTable_conversion using the name of the
table in Oracle as the input.

#    arcpy.MakeTableView_management(dbtPivot, viewPivot)
    arcpy.TableToTable_conversion(dbtPivot, gdbWorkFull, tblRouteSingleName, sqlWhere)

Result: Execution Error
"ExecuteError: ERROR 000339: Input DMS_ADM.DPIVOT_T does not have OIDs
Failed to execute (TableToTable)"

Note: Same results when ran in ArcMap

Re: Oracle to GDB table copy (using a where clause to limit rows)

KendallRussell — Thu, 24 Apr 2014 17:47:28 GMT

I think that I found a solution.

1. Used MakeQueryTable_management() to make a view to the table with a where clause that limits the rows.
2. Use TableToTable_conversion() to copy to a table inside the GDB.

This worked in both ArcMap and a stand-alone script.

I appreciate you taking the time to help.