Updating 2 tables related by 3 ID fields

[DavinShokes1]

I'm having a little trouble setting up this script. Here are the bullet points.

• 2 Tables have the same three fields. [SiteID], [EasyID], [FeatureID]. For simplicity, lets pretend these are the only fields (besides OID).
• [FeatureID] is a unique integer for each feature created in Table 1.
• [SiteID] is a non-unique integer and groups features by location.
• [EasyID] is a string (typically a number) unique within the same [SiteID], but not unique in the field.
• Table 1 - The master table. New features have all three ID fields populated.
• Table 2 - New features always have [SiteID], sometimes [EasyID], and never a [FeatureID].
• Table 2 - Features with [SiteID] and [EasyID], and no [FeatureID] may have a matching [SiteID] and [EasyID] in Table 1. If so, update [FeatureID] in Table 2.
• Table 2 - Features missing [EasyID] need to be assigned the next number for that [SiteID] in either Table 1 or 2. If current EasyID's for a site are '1', '3', '4A', '6-7','S', and '55', new features would be '2', '4', '5', '6', '7', '8'....etc.
• Table 2 - New features given a [FeatureID] when inserted into Table 1. The features are then updated in Table 2.
• Table 1 - Finally, any features with a [SiteID] in Table 2, but the [FeatureID] and [EasyID] are not in Table 2, are inserted into Table 2
• At the end of the script both tables should match.

I've looked through using model builder with no luck. With python dictionaries and search cursors i'm getting stuck trying to join against [SiteID] and [EasyID] at the same time. I am also don't know how to return the dictionaries with just integer EasyID's and loop through updating the next smallest integer.

Here is what I've got so far. Much of it stems from what I read from Richard Fairhurst's post Turbo Charging Data Manipulation with Python Cursors and Dictionaries

import arcpy

#Tables
T1 = r"C:\Python\Scratch.gdb\Table1"
T2 = r"C:\Python\Scratch.gdb\Table2"
fields = ["FeatureID", "SiteID", "EasyID"]

# Get FeatureID dictionaries for each Table
T1Dict = {r[0]:(r[0:]) for r in arcpy.da.SearchCursor(T1, fields)}
T2Dict = {r[0]:(r[0:]) for r in arcpy.da.SearchCursor(T2, fields)}

# Get SiteID+EasyID dictionaries for each Table
T1ConcatDict = {str(r[1]) + "," + str(r[2]):(r[0]) for r in arcpy.da.SearchCursor(T1, fields)}
T2ConcatDict = {str(r[1]) + "," + str(r[2]):(r[0]) for r in arcpy.da.SearchCursor(T2, fields)}

#First, If T2.FeatureID is Null but T2.EasyID and T2.SiteID are in T1, Update T2.FeatureID
with arcpy.da.UpdateCursor(T2, fields) as updateRows:  
    for updateRow in updateRows:  
        # store the Join value by combining 3 field values of the row being updated in a keyValue variable  
        keyValue = str(updateRow[1]) + "," + str(updateRow[2])  
        # verify that the keyValue is in the Dictionary  
        if keyValue in T1ConcatDict & updateRow[0] is None & updateRow[1] is not None:  
            # transfer the value stored under the keyValue from the dictionary to the updated field: FeatureID.  
            updateRow[0] = T1ConcatDict[keyValue][0]  
            updateRows.updateRow(updateRow)  

#Rebuild Dictionary if it is needed again
T2ConcatDict = {str(r[1]) + "," + str(r[2]):(r[0]) for r in arcpy.da.SearchCursor(T2, fields)}
T2Dict = {r[0]:(r[0:]) for r in arcpy.da.SearchCursor(T2, fields)}


'''
#Get Max(EasyID) within SiteID
NumberList = []
for value in T1Dict[2]:
    try:
        NumberList.append(int(value))
    except ValueError:
        continue
T1EasyNumberDict = [s[2] for s in T1Dict[2] if s.isdigit()]
T1MaxEasyDict = max(T1EasyNumberDict)
'''


#Second, If T2.FeatureID and T2.EasyID are Null, Update T2.EasyID with next smallest number (as string) in either T1 or T2 for the specific SiteID
with arcpy.da.UpdateCursor(T2, fields) as updateRows:  
    for updateRow in updateRows:  
        # store the Join value by combining 3 field values of the row being updated in a keyValue variable  
        keyValue = str(updateRow[1]) + "," + str(updateRow[2])  
        # verify that the keyValue is in the Dictionary  
        if keyValue in T1Dict & updateRow[0] is None & updateRow[1] is None:  
            # transfer the value stored under the keyValue from the dictionary to the updated field.  
            # Perhaps retrieve max int occurs here?
  updateRow[2] = max(T1Dict[keyValue][2], T2Dict[keyValue[2]) 
            updateRows.updateRow(updateRow)  


#Third, Insert into T1 if T2.SiteID is not null and T2.EasyID is not null


#Forth, Update T2.FeatureID with T1.FeatureID from previous insert where T2.SiteID=T1.SiteID and T2.EasyID=T1.EasyID


#Lastly, Insert any T1 features into T2 where T1.EasyID not in (Select EasyID from T2 where T2.SiteID = T1.SiteID) and T1.FeatureID  not in (Select FeatureID from T2)

Thank you for any advise.

Edit: Here is a sample of sites with 2 explanatory columns

T1.FeatureID	T2.FeatureID	T1.SiteID	T2.SiteID	T1.EasyID	T2.EasyID	Status	Result
358589	358589	136238	136238	1	1	Existing T1 and T2 Feature	No Change
358590	358590	136238	136238	2	2	Existing T1 and T2 Feature	No Change
358594	358594	136238	136238	4	4	Existing T1 and T2 Feature	No Change
652538	652538	136238	136238	5	5	Existing T1 and T2 Feature	No Change
486028	486028	136238	136238	8	8	Existing T1 and T2 Feature	No Change
486029	486029	136238	136238	9	9	Existing T1 and T2 Feature	No Change
525300	525300	136238	136238	34	34	Existing T1 and T2 Feature	No Change
574802	574802	136238	136238	998	998	Existing T1 and T2 Feature	No Change
670911		136238		300		New T1 Feature	(Step 6) Inserted Into T2
493840		136238		9996		New T1 Feature	(Step 6) Inserted Into T2
493839		136238		9997		New T1 Feature	(Step 6) Inserted Into T2
493831		136238		9999		New T1 Feature	(Step 6) Inserted Into T2
696019		136238		105-106		New T1 Feature	(Step 6) Inserted Into T2
696037		136238		9999N		New T1 Feature	(Step 6) Inserted Into T2
696014		136238		Area1		New T1 Feature	(Step 6) Inserted Into T2
670910		136238		N		New T1 Feature	(Step 6) Inserted Into T2
580636		136238		N30c		New T1 Feature	(Step 6) Inserted Into T2
360401		136401		AC		Exiting T1 Feature	(Skip) Not Inserted since no T2.SiteID match
360402		136401		SP		Exiting T1 Feature	(Skip) Not Inserted since no T2.SiteID match
360510		136427		Area 1		Exiting T1 Feature	(Skip) Not Inserted since no T2.SiteID match
362653		136635		15		Exiting T1 Feature	(Skip) Not Inserted since no T2.SiteID match
362943	362943	136698	136698	1	1	Existing T1 and T2 Feature	No Change
362944	362944	136698	136698	2	2	Existing T1 and T2 Feature	No Change
362945	362945	136698	136698	3	3	Existing T1 and T2 Feature	No Change
362946	362946	136698	136698	4	4	Existing T1 and T2 Feature	No Change
362947	362947	136698	136698	5	5	Existing T1 and T2 Feature	No Change
362950	362950	136698	136698	11C	11C	Existing T1 and T2 Feature	No Change
362948	362948	136698	136698	8		New T2 Feature, Exists in T1	(Step 5) Update T2.EasyID
362949	362949	136698	136698	9		New T2 Feature, Exists in T1	(Step 5) Update T2.EasyID
362951		136698	136698	15	15	New T2 Feature, Exists in T1	(Step 1) Update T2.FeatureID
362954		136698	136698	16	16	New T2 Feature, Exists in T1	(Step 1) Update T2.FeatureID
362955		136698	136698	17	17	New T2 Feature, Exists in T1	(Step 1) Update T2.FeatureID
362956		136698	136698	18	18	New T2 Feature, Exists in T1	(Step 1) Update T2.FeatureID
362957		136698	136698	19	19	New T2 Feature, Exists in T1	(Step 1) Update T2.FeatureID
			136698		20	New T2 Feature	(Step 3,4) Insterted into T1, Update T2.FeatureID
			136698		21	New T2 Feature	(Step 3,4) Insterted into T1, Update T2.FeatureID
			136698		22	New T2 Feature	(Step 3,4) Insterted into T1, Update T2.FeatureID
			136698		25	New T2 Feature	(Step 3,4) Insterted into T1, Update T2.FeatureID
			136698			New T2 Feature	(Step 2,3,4) Get next lowest integer for T2.EasyID -> 6
			136698			New T2 Feature	(Step 2,3,4) Get next lowest integer for T2.EasyID -> 7
			136698			New T2 Feature	(Step 2,3,4) Get next lowest integer for T2.EasyID -> 10
			136698			New T2 Feature	(Step 2,3,4) Get next lowest integer for T2.EasyID -> 11

[DavinShokes1]

I've made some headway. My script is successfully insertings new rows into sql server with pyodbc. However, I've got an encoding issue when updating the hosted layer. The sql table varchar columns and I assume that hosted layers are nvarchar. The call to update the hosted layer succeeds but the text column doesn't actually update. Integer fields do.

Here is my code I've kept the actual column names so the SQL Server table fields are:

SiteID-->CustomerID, FeatureID-->PropertyID, and EasyID--> Location

The hosted layer fields are:

SiteID-->CustomerID, FeatureID-->PropertyID_WO, and EasyID--> TAG

I've match the sequence to prevent name issues. I'm don't know how to go further without figuring out how to update TAG with Location.

# -*- coding: utf-8 -*-
# ---------------------------------------------------------------------------
# 


import sys, os, datetime, arcpy, pyodbc, time, uuid


def hms_string(sec_elapsed):
    h = int(sec_elapsed / (60 * 60))
    m = int((sec_elapsed % (60 * 60)) / 60)
    s = sec_elapsed % 60.
    return "{}:{:>02}:{:>05.2f}".format(h, m, s)
# End hms_string


def timedif(end_datetime, start_datetime):
    seconds_elapsed = (end_datetime - start_datetime).total_seconds()
    return hms_string(seconds_elapsed)
# End timedif
start_time = datetime.datetime.now()
print "# Script start: {}".format(start_time)


from arcpy import env, da
from arcrest.security import AGOLTokenSecurityHandler
from arcrest.agol import FeatureService
from arcrest.agol import FeatureLayer
from arcrest.common.filters import LayerDefinitionFilter
from datetime import timedelta


print "# Imports loaded."


# Set Geoprocessing environments
Scratch_gdb = r'C:\Python\scratch.gdb'
arcpy.env.scratchWorkspace = Scratch_gdb
arcpy.env.workspace = Scratch_gdb




Local_Table = r'C:\Users\dkshokes\AppData\Roaming\ESRI\Desktop10.3\ArcCatalog\RTC03.sde\Rainbow.dbo.Property'
Output_Table_1 = "T1"
Expression_1 = r'"CustomerID" = 166602 AND "Location" is not Null'
Online_Trees = r'http://services.arcgis.com/###/arcgis/rest/services/###/FeatureServer/0'
username = "####"
password = "####"
proxy_port = None
proxy_url = None
agolSH = AGOLTokenSecurityHandler(username=username, password=password)
Output_Table_2 = "T2"
Expression_2 = '"CustomerID" = 166602'
fields_1 = ['CustomerID', 'Location', 'PropertyID', 'OID@', 'Description', 'CBH', 'PropertyQuantity', 'PropertyItemID', 'DateEntered', 'LastChangeDate', 'Special_Note']
fields_2 = ['CustomerID', 'TAG', 'PropertyID_WO', 'OID@', 'COMMON', 'DBH', 'QTY', 'PropertyItemID', 'CreationDate', 'EditDate', 'Notes', 'Creator', 'Editor', 'PruneYear', 'ProposalStatus', 'SoilYear']
print "\n# Online variables loaded.  "


# Local variables:
T1 = Scratch_gdb + "\\" + Output_Table_1
print "# T1 Table: {}".format(T1)
print "# T1 Fields: {}".format(fields_1)
T2 = Scratch_gdb + "\\" + Output_Table_2
print "# T2 Table: {}".format(T2)
print "# T2 Fields: {}".format(fields_2)
FieldMap_1 = r"PropertyID \"PropertyID\" true true false 50 Long 0 10 ,First,#," + Local_Table + ",PropertyID,-1,-1;CustomerID \"CustomerID\" true true false 4 Long 0 10 ,First,#," + Local_Table + ",CustomerID,-1,-1;Location \"Location\" true true false 7 Text 0 0 ,First,#," + Local_Table + ",Location,-1,-1;PropertyItemID \"PropertyItemID\" true true false 4 Long 0 10 ,First,#," + Local_Table + ",PropertyItemID,-1,-1;Description \"Description\" true true false 255 Text 0 0 ,First,#," + Local_Table + ",Description,-1,-1;PropertyQuantity \"PropertyQuantity\" true true false 4 Long 0 10 ,First,#," + Local_Table + ",PropertyQuantity,-1,-1;CBH \"CBH\" true true false 8 Double 1 18 ,First,#," + Local_Table + ",CBH,-1,-1;Special_Note \"Special_Note\" true true false 255 Text 0 0 ,First,#," + Local_Table + ",Special_Note,-1,-1;Status \"Status\" true true false 10 Text 0 0 ,First,#," + Local_Table + ",Status,-1,-1;DateEntered \"DateEntered\" true true false 36 Date 0 0 ,First,#," + Local_Table + ",DateEntered,-1,-1;PropertyGroup \"PropertyGroup\" true true false 2 Short 0 1 ,First,#," + Local_Table + ",PropertyGroup,-1,-1;Removed \"Removed\" true true false 2 Short 0 1 ,First,#," + Local_Table + ",Removed,-1,-1;LastChangeDate \"LastChangeDate\" true true false 36 Date 0 0 ,First,#," + Local_Table + ",LastChangeDate,-1,-1"


if arcpy.Exists(T1):
    arcpy.Delete_management(T1)
    print "Previous T1 Found so it was deleted."
if arcpy.Exists(T2):
    arcpy.Delete_management(T2)
    print "Previous T2 Found so it was deleted."


#print "\n# Local variables loaded...Making temporary business table."
arcpy.TableToTable_conversion(Local_Table, Scratch_gdb, Output_Table_1, Expression_1, FieldMap_1, "")
#print "# Successfully made temporary business table."


#print "\n# Querying Feature Layer and Making Temporary Feature Class."
fl = FeatureLayer(Online_Trees, securityHandler=agolSH, initialize=False,proxy_url=proxy_url, proxy_port=proxy_port)
print fl.query(where=Expression_2, out_fields='*', returnGeometry=True, returnIDsOnly=False, returnCountOnly=False, returnFeatureClass=True, out_fc=Output_Table_2)
print "\n# Ready to compare." + "  Time elapsed: {}".format(timedif(datetime.datetime.now(), start_time))


print "\n# Intialize T1 as a concatenated dictionary"
T1Dict = {}
# Initialize a list to hold any concatenated key duplicates found
T1KeyDups = []
# Open a search cursor and iterate rows
with arcpy.da.SearchCursor(T1, fields_1) as searchRows:
    for searchRow in searchRows:
        # Build a composite key value from 2 fields: CustomerID;TAG
        keyValue = '{};{}'.format(searchRow[0], searchRow[1]).decode('utf-8')
        if not keyValue in T1Dict and searchRow[1] is not None:
            # Key not in dictionary.  Add Key pointing to a list of a list of field values
            T1Dict[keyValue] = [list(searchRow[0:])]
            #print "T1Dict keyValue: {}".format(keyValue)
        elif searchRow[1] is not None:
            # Key in dictionary.  Append a list of field values to the list the Key points to
            T1KeyDups.append(keyValue)
            T1Dict[keyValue].append(list(searchRow[0:]))
        #print T1Dict[keyValue][0:]
del searchRows, searchRow


##print "\n# Sample of how to access the keys, record count, and record values of the dictionary"
##for keyValue in T1Dict.keys():
##    for i in range(0, len(T1Dict[keyValue])):
##        print "T1Dict Key CustomerID;TAG is {} Record:{} of {} PropertyID_WO:{} ObjectID:{}.".format(keyValue, i+1, len(T1Dict[keyValue]), T1Dict[keyValue][0], T1Dict[keyValue][1])


if len(T1KeyDups) > 0:
    print "T1KeyDups keys found! {}".format(T1KeyDups)
    # fix T1 here
else:
    print ("No Duplicates!")
print "\n# T1Dict Complete:" + "  Time elapsed: {}".format(timedif(datetime.datetime.now(), start_time))


print "\n# Get the list of TAGs associated with each CustomerID in a dictionary for T1"
T1CustomerIDDict = {}
with arcpy.da.SearchCursor(T1, fields_1) as searchRows:
    for searchRow in searchRows:
        keyValue = searchRow[0]
        if not keyValue in T1CustomerIDDict:
            # Key not in dictionary.  Add Key pointing to a list of a list of field values
            T1CustomerIDDict[keyValue] = [searchRow[0:]]            
        else:
            # Append a list of field values to the list the Key points to
            T1CustomerIDDict[keyValue].append(searchRow[0:])
        #print T1CustomerIDDict[keyValue]
del searchRows, searchRow
print "\n# T1CustomerIDDict Complete"


print "\n# Get the list of TAGs associated with each CustomerID in a dictionary for T2"
T2CustomerIDDict = {}
with arcpy.da.SearchCursor(T2, fields_2) as searchRows:
    for searchRow in searchRows:
        keyValue = searchRow[0]
        if not keyValue in T2CustomerIDDict:
            T2CustomerIDDict[keyValue] = [searchRow[0:]]
        else:
            T2CustomerIDDict[keyValue].append(searchRow[0:])
del searchRows, searchRow
print "\n# T2CustomerIDDict Complete"


print "\n# Creating CustomerIDDict"
CustomerIDDict = {}
for keyValue in T2CustomerIDDict.keys():
    intList = []
    for TAG in T2CustomerIDDict[keyValue]:
        try:
            if TAG[1]:
                TAG = int(TAG[1])
                intList.append(TAG)
        except:
            print "Error with T2.TAG"
    if keyValue in T1CustomerIDDict:
        for TAG in T1CustomerIDDict[keyValue]:
            try:
                if TAG[1]:
                    TAG = int(TAG[1])
                    intList.append(TAG)
            except:
                print "Error with T1.TAG"          
    #print "\nnumeric T2.TAGs: {}".format(set(intList))
    #print "\nnumeric T1.TAGs: {}".format(set(intList))
    # remove already used numbers out of the numbers from 1 to 9999
    # and get a sorted list for use with an update cursor.
    CustomerIDDict[keyValue] = sorted(set(range(1, 200)) - set(intList))
print "\n# CustomerIDDict Complete"


print "\n# Inserting and Updating" + "  Time elapsed: {}".format(timedif(datetime.datetime.now(), start_time))
### This cursor sucessfully inserts into sql server a new row with an unused TAG and attributes from the hosted feature.
### It then updates the hosted layer fields propertyID_WO and TAG. 
##  propertyID_WO succeeds but TAG is blank
#
print "#  Working with pyodbc sql stuff"
cnxn_SQL = pyodbc.connect(Trusted_Connection='yes', Driver = '{SQL Server Native Client 10.0}', Server = '###', database = '###')
cursor_SQL = cnxn_SQL.cursor() 
with arcpy.da.UpdateCursor(T2, fields_2) as updateRows:
     for updateRow in updateRows:            
        # if T2.Row has TAG and CustomerID but no PropertyID compare and look for match in T1
        if (updateRow[2] == None) and not (updateRow[1] == None):
            print "1st if TAG {} PropertID {}".format(updateRow[1],updateRow[2])
            for valu in T1CustomerIDDict[updateRow[0]]:
                if valu[1] == updateRow[1]:
                    try:
                        updateRow[2] = valu[2]
                        print "# Updating PropertyID_WO for TAG:{} hosted layer with {}".format(updateRow[1],updateRow[2])
                        fl.calculate(where="OBJECTID={} AND CustomerID={} AND TAG={}".format(updateRow[3], updateRow[0], updateRow[1]), calcExpression={"field" : "PropertyID_WO", "value" : "{}".format(updateRow[2])})
                        print "# Success"
                    except:
                        print "# Failed Hosted Update of PropertyID"
        if (updateRow[1] == None) and (updateRow[2] == None):
            print "3rd if TAG {} PropertID {}".format(updateRow[1],updateRow[2])
            newPropID = None
            print CustomerIDDict[updateRow[0]][0]
            # Set templist to show available integer TAGs for 1 CustomerID at a time.
            templist = CustomerIDDict[updateRow[0]]
            # Set Null tag to first available integer
            print updateRow[1:3]
            updateRow[1] = templist[0]
            # Insert to local sql server table, update online from sql results, then Remove used tag.
            print updateRow[1:3]
            try:
                # Get ItemID required by insert trigger
                propitemid = cursor_SQL.execute("SELECT [PropertyItemID] from [TREESPECIESRELATIONSHIPTABLE] Where [COMMON__GIS_name_] = ?",updateRow[4]).fetchone()[0]
                # Insert New trees into T1
                cursor_SQL.execute("IF NOT EXISTS (SELECT 1 FROM Property WHERE CustomerID = ? and Location = ?) INSERT INTO Property(CustomerID, Location, Description, CBH, PropertyQuantity, PropertyItemID, DateEntered, LastChangeDate, Special_Note) values (?,?,?,?,?,?,?,?,?)", updateRow[0], updateRow[0], updateRow[0],updateRow[1],updateRow[4],updateRow[5],updateRow[6],propitemid,updateRow[8],updateRow[9],updateRow[10])
                cnxn_SQL.commit()
                # Grab to primary key for updating hosted layer
                newPropID = cursor_SQL.execute("Select PropertyID From Property where CustomerID=? AND Location=?", updateRow[0], updateRow[1]).fetchone()[0]
                print "Finished Insert for New PropertyID {}".format(newPropID)
                cnxn_SQL.commit()
                updateRow[2] = newPropID
                templist.remove(updateRow[1])
                print updateRow[1:3]
            except:
                print "# Failed SQL Server Insert"
            try:
                print "# Updating TAG and PropertyID_WO for hosted layer"
                print fl.calculate(where="OBJECTID={} AND CustomerID={}".format(updateRow[3], updateRow[0]), calcExpression={"field" : "TAG", "value" : "{}".format(updateRow[1]),"field" : "PropertyID_WO", "value" : "{}".format(updateRow[2])})
            except:
                print "# Failed Hosted Update of TAG and PropertyID"
        updateRows.updateRow(updateRow)
del updateRows, updateRow
cnxn_SQL.close()         
#
##
###
###
print "Finished!" + "  Time elapsed: {}".format(timedif(datetime.datetime.now(), start_time))

[RichardFairhurst]

I am not clear which part of the code is causing the problem and which field name is for an integer and which is for a string.  Generally to use Python to write to a string field you have to enclose non-strings in the str() method.  For converting strings to integers you must always test that the string is actually an integer and use Null (or some other default value for blank numbers) when the strings is not numeric.  For example, " " is not an integer and will fail if you try to convert it to an integer.  Potentially Null will also cause a problem.

Anyway, any time type conversion is involved in a script it is easy to produce errors and unexpected results until you include enough logic to handle both the values that can convert and the values that can't convert at each conversion step.  To confirm for yourself what is failing you should include a print statement in your try except block to show you the actual value read from the source field that you are trying to write to the field that is rejecting the value or producing bad results.

[DavinShokes1]

The last try at line 230 is where it is succeeding the REST call, but the layer's field values stay null. A few lines prior, updateRow[1] gets an integer from the dictionary CustomerIDDict.

            # Set Null tag to first available integer 

            updateRow[1] = templist[0] 

Then updateRow[1] gets put into...

              print fl.calculate(where="OBJECTID={} AND CustomerID={}".format(updateRow[3], updateRow[0]), calcExpression={"field" : "TAG", "value" : "{}".format(updateRow[1]),"field" : "PropertyID_WO", "value" : "{}".format(updateRow[2])}) 

I'm guessing it turns into: calcExpression={"field" : "TAG", "value" : "4",....},but I'm not certain.

Also, at line  97 added a decode and a typeerror went away. I can't tell if this decode causes issues in the dictionary later on though.

             # Build a composite key value from 2 fields: CustomerID;TAG 

             keyValue = '{};{}'.format(searchRow[0], searchRow[1]).decode('utf-8')

[RichardFairhurst]

Actually, line 230 is a print statement.  Are you sure it is executing?  You should break that whole expression down to multiple prints statements so you are certain exactly what arguments are being passed to the query and calculation.  For example, you could try:

print "OBJECTID={} AND CustomerID={}".format(updateRow[3], updateRow[0])

print "{}".format(updateRow[1])

print "{}".format(updateRow[2])

Then, if all of those inputs makes sense, execute the fl.calculate outside of a print operation.

There are some details to pay attention to.  If CustomerID is numeric and the value makes sense, then the SQL should be fine.  But if CustomerID is a string value then the SQL should fail, since the value is not within quotes.  For a string CustomerID you would need:  "OBJECTID={} AND CustomerID='{}'".format(updateRow[3], updateRow[0])

[DavinShokes1]

I figured it out. I was trying to update 2 different fields at once and field calculator understandably only takes one. Its crazy how much time I looked up information on unicode stuff only to find it was a misuse of the parameter.

Can't thank you enough Richard. I should be able to fumble through the rest of the conditionals.