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)

• Mary Andrews: Note: this is just a small part of the code but I think you get the idea!
• Vince: Please edit the question to specify the database in the data source. Different databases use different mechanisms to process date/timestamp fields; you likely need to cast the string to the right type.
• Vince: Please use the Question body to add details, not comments. Your and syntax appears to be in error, thought it's difficult to tell. You should probably include the between usage in the body as well.
• Mary Andrews: I'm using an OLE DB connection to a SQL database

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')

• Mary Andrews: Thanks Vince, THIS WORKED! What I would like to understand is why when I set a variable as a string I then have to convert it to an object and then declare it as a sting in the sql statement. Can anyone recommend a book or website to help me understand this?
• Vince: You're basically asking about how Python operates. It's very similar to how most other programming languages operate, and attempting Boolean operations on strings is one of the places where the results can seem bizarre if you don't sit down and study the grammar of the parser. Discussion of how the Python parser operates is certainly off-topic in GIS SE.
• Mary Andrews: Should anyone in the future be looking to understand this here is a website that has helped me! esriaustraliatechblog.wordpress.com/2012/02/27/…