AnsweredAssumed Answered

Using a SQL expression to check data in two databases

Question asked by simpson.ben1990 on Nov 26, 2015
Latest reply on Dec 3, 2015 by bharold-esristaff

Hi,

 

I have a toolbox script which appends data from a personal geodatabase to an Oracle database. I want to include a check to see if the data being imported from the personal gdb is not already in the Oracle database.I have a field (MODELNAME) which is in both the personal gdb table which the data is being appended from and the Oracle table where the data is being appended to. In the gdb the there is only one model name in that field. I want to check if the model name from the gdb already exists in the MODELNAME field in the Oracle database. Here is where I get stuck. I know what I want to do, I just dont know how to do it. I am assuming that I need to use a SQL statement from this. Something like "SELECT * FROM SAGIS_REACH_NETWORK AS r WHERE r.MODELNAME = SimReaches.MODELNAME".

 

I was thinking of doing a count of the number of rows where the model name in the Oracle table match the gdb table.

 

This is what I have so far:

 

model_list = [arcpy.GetParameterAsText(0)]
if model_list != None:
    # Only run script if atleast one model is selected i.e. model_list is not empty
    try:
        if arcpy.ProductInfo() == "ArcInfo" or "ArcEditor":
    
            # Parameters
            # Personal Geodatabase we are importing into
            outmodel = "Database Connections\\simcatmaster@G01L.sde" 
            # List tuples with feature classes and tables that we want to import, and their output name in Oracle
            # e.g[(in_name,out_name)]
            tables = []
            tables.append(('\\simcat\\SimReaches','\\simcatmaster.SAGIS_REACH_NETWORK'))
            today = datetime.today()
            
            # connect to oracle
            oracle_conn = pyodbc.connect("DSN=ORACLE_SIMCAT_x86")
                  
            oracle_cursor = oracle_conn.cursor()
            
            # Find the total count of models (geodatabases) selected
            modelListLen = len(model_list)
            
            # Declaring the first model position within the list of models selected (model_list)
            modelPos = 0
            for model in model_list:
                
                # Check if the model selected has already been imported to SAGIS_REACH_NETWORK
                SQL = "SELECT * FROM SAGIS_REACH_NETWORK AS r WHERE r.MODELNAME = {0}.MODELNAME;".format(tables[0])
                
                oracle_cursor.execute(SQL).fetchall()
                SQLCount = len(oracle_cursor)
                print SQLCount

 

Does anyone have any advice as to how I could do this?

 

Thanks in advance for any help,

 

Ben.

Outcomes