Writing Make Table Query script in ArcGIS Desktop

Question

I am trying to get the make table query running in a script. IF I simply export the script from a model, it works without a problem. However, I do have to change the variable names, as this will be part of a bigger script. As soon as I change any variable names, it won't work anymore. See example:

# Import arcpy module
import arcpy

# set outputs to be overwritten
arcpy.env.overwriteOutput = True

#WORKS
# Local variables:
AA_allDomains = "D:\\Student\\DomainsToTables\\featureDomains.gdb\\AA_allDomains"
AB_BusBar_Domains = "D:\\Student\\DomainsToTables\\featureDomains.gdb\\AB_BusBar_Domains"

QueryTable1 = "QueryTable1"
# Process: Make Query Table
inList = [AA_allDomains, AB_BusBar_Domains]
##fieldList = "AA_allDomains.Code #;AA_allDomains.Desc #;AA_allDomains.DomainName #;AB_BusBar_Domains.dataset #;AB_BusBar_Domains.featureclass #;AB_BusBar_Domains.field #;AB_BusBar_Domains.domainName #"
fieldList = [["AA_allDomains.Code", 'CODE'],["AB_BusBar_Domains.dataset", 'DATASET']]
expression = "AB_BusBar_Domains.domainName = AA_allDomains.DomainName"
arcpy.MakeQueryTable_management(inList, QueryTable1, "USE_KEY_FIELDS", "", fieldList, expression)

#DOESN'T WORK
# Local variables:
in1 = "D:\\Student\\DomainsToTables\\featureDomains.gdb\\AA_allDomains"
in2 = "D:\\Student\\DomainsToTables\\featureDomains.gdb\\AB_BusBar_Domains"

QueryTable1 = "QueryTable1"
# Process: Make Query Table
inList = [in1, in2]
##fieldList = "in1.Code #; in1.Desc #; in1.DomainName #; in2.dataset #; in2.featureclass #; in2.field #; in2.domainName #"
fieldList = [["in1.Code", 'CODE'],["in2.dataset", 'DATASET']]
expression = "in2.domainName = in1.DomainName"
arcpy.MakeQueryTable_management(inList, QueryTable1, "USE_KEY_FIELDS", "", "fieldList", expression)

I already tried to play around with different DB types, different ways of accessing the fields but nothing worked out so far.

Answer 1

In the code that works you have:

# Local variables:
AA_allDomains = "D:\\Student\\DomainsToTables\\featureDomains.gdb\\AA_allDomains"
AB_BusBar_Domains = "D:\\Student\\DomainsToTables\\featureDomains.gdb\\AB_BusBar_Domains"

fieldList = [["AA_allDomains.Code", 'CODE'],["AB_BusBar_Domains.dataset", 'DATASET']]
expression = "AB_BusBar_Domains.domainName = AA_allDomains.DomainName"

This works because your field names in fieldList abd expression are prefixed by the feature class name i.e. AA_allDomains.Code. In the example I just gave, you have the feature class (or table) name "." (dot) the field name. It just so happens that the variable in your working script is also given the feature class name.

In your code that doesn't work you have:

# Local variables:
in1 = "D:\\Student\\DomainsToTables\\featureDomains.gdb\\AA_allDomains"
in2 = "D:\\Student\\DomainsToTables\\featureDomains.gdb\\AB_BusBar_Domains"

fieldList = [["in1.Code", 'CODE'],["in2.dataset", 'DATASET']]
expression = "in2.domainName = in1.DomainName"

In this case your field names in fieldList and expression are prefixed by the variable name i.e. in1.Code instead of the feature class (or table) name.

I would recommend that you can change the variable names, but don't change any strings (text between quotes)