Using arcpy.makequerytable_management with where clause?

Question

I have a script that works great with selecting dates greater than "my From date" but I can't get the code to work when I want to add a less than to the sql where clause (a to date).

I had add the {0}'".format to get the original where clause to work. But can't figure out how to add the second date. I have tried AND and BETWEEN with no luck.

Any idea how to do this?

I'm stuck!

This is the code that works perfectly for the single from date:

where_time_period = "dtrepor > '{0}'".format(object_FromDate) 

table_out =  os.path.join(output_gdb, "MyRecords")
if arcpy.Exists(table_out):
    arcpy.Delete_management(table_out)
arcpy.MakeQueryTable_management(incident_table, table_out, where_clause = 
where_time_period)

update: If I run the code with just the where_time begin it works perfectly. If I try to add where_time_end it gives me all the records. I just tried to change the where_clause to this

where_time_begin = "dtrepor > '{0}'".format(object_FromDate) where_time_end = 
   "dtrepor < '{0}'".format(object_ToDate) 
   arcpy.MakeQueryTable_management(incident_table, table_out, where_clause = 
   where_time_begin and where_time_end)

instead of giving me less record it

It returns all the records –

If I try where_clause with "AND" or Between, my code won't run? where_time_begin + "AND" + where_time_end

Here's a bit more of the code

#define workspace#
arcpy.env.workspace = r"C:\Users\mandrews\Documents\ArcGIS\Default.gdb"
dbo_path=r"C:\Users\mandrews\AppData\Roaming\ESRI\Desktop10.5\ArcCatalog\Spillman_rep_OLE_D.odc"
sde_path = "C:\Users\mandrews\AppData\Roaming\ESRI\Desktop10.5\ArcCatalog\RepSpillman.sde"
#mxd_all_incidents = arcpy.mapping.MapDocument(r"C:\AlexaWetmore\monthlyAll.mxd")
mxd_all_incidents = arcpy.mapping.MapDocument(r"C:\Users\mandrews\Documents\monthlyIncidentMaps\monthlyAll.mxd")
output_dir = (r"C:\Users\mandrews\Documents\monthlyIncidentMaps")
output_gdb = (r"C:\Users\mandrews\Documents\monthlyIncidentMaps\monthlyIncidents.gdb")
#call addr temp
addr_temp = os.path.join(r"C:\Users\mandrews\Documents\ArcGIS\Default.gdb", "AdderTemp")
#copy lwmain table with this sql query for from date
incident_table = os.path.join(dbo_path,"dbo.lwmain")
offense_table = os.path.join(dbo_path,"dbo.lwoffs")
where_time_begin = "dtrepor > '{0}'".format(object_FromDate)
where_time_end = "dtrepor < '{0}'".format(object_ToDate)
table_out =  os.path.join(output_gdb, "MyRecords")
if arcpy.Exists(table_out):
    arcpy.Delete_management(table_out)
arcpy.MakeQueryTable_management(incident_table, table_out, where_clause = where_time_begin + "AND" + where_time_end)
#show count of records selected
results = arcpy.GetCount_management(table_out)
count = int(results.getOutput(0))
print (count)

Answer 1

If you make a dummy function that uses the same signature as arcpy.MakeQueryTable_management, you'll see that the and is not behaving that way you seem to think it should:

def my_MakeQueryTable_management(in_table,out_table,
           in_key_field_option=None,in_key_field=None,
           in_field=None,where_clause=None):

    print("\nMakeQueryTable_management(")
    print("\t           in_table = [{:s}],".format(str(in_table)))
    print("\t          out_table = [{:s}],".format(str(out_table)))
    print("\tin_key_field_option = [{:s}],".format(str(in_key_field_option)))
    print("\t       in_key_field = [{:s}],".format(str(in_key_field)))
    print("\t           in_field = [{:s}],".format(str(in_field)))
    print("\t       where_clause = [{:s}])".format(str(where_clause)))
    return

# Invoke both ways

where_time_begin = "dtrepor > '{0}'".format('2001-01-01 00:00:00')
where_time_end = "dtrepor < '{0}'".format('2009-12-31 23:59:59')

my_MakeQueryTable_management(
        "incident_table", "table_out",
        where_clause = where_time_begin and where_time_end)

my_MakeQueryTable_management(
        "incident_table", "table_out", 
        where_clause = where_time_begin + "AND" + where_time_end)

the result of which is:

MakeQueryTable_management(
                   in_table = [incident_table],
                  out_table = [table_out],
        in_key_field_option = [None],
               in_key_field = [None],
                   in_field = [None],
               where_clause = [dtrepor < '2009-12-31 23:59:59'])

MakeQueryTable_management(
                   in_table = [incident_table],
                  out_table = [table_out],
        in_key_field_option = [None],
               in_key_field = [None],
                   in_field = [None],
               where_clause = [dtrepor > '2001-01-01 00:00:00'ANDdtrepor < '2009-12-31 23:59:59'])

In the first example, the "and" mixes oddly with the strings, resulting in ONLY the second parameter being passed (which could result in all records being selected).

In the second, the resulting SQL string is syntactically incorrect, since spaces weren't placed around the AND (e.g., " AND ").

String math gives me hives, so I prefer to lay out my strings with the new typed form of string.format():

where_clause = "{:s} AND {:s}".format(where_time_begin,where_time_end)

or just:

where_clause = "dtrepor > '{:s}' AND dtrepor < '{:s}'".format(
        '2001-01-01 00:00:00','2009-12-31 23:59:59')