Trouble Passing Dates to SQL Query in Python Script from Flask App

[MikhaylaB]

I'm building a test Flask app where the end user can select the sql parameters that are passed to a python script. I've had no problems passing queries for text fields, but the date range is giving me trouble. I've tried implementing strftime() but I must have used it incorrectly (AttributeError: 'str' object has no attribute 'strftime').

When I remove strftime() I get the following error: "RuntimeError: An invalid SQL statement was used". The user is selecting a mm/dd/yyyy from the calendar popup for date_before and date_after.  I'm not experienced with this stuff and am just cobbling things together to see what works. I'll include snippets of my app.py and export.py files if anyone has suggestions

Updated to include the html tags

@app.route('/', methods=['POST'])
def export_data_route():
    type_query = request.form['type_query']
    leader_query = request.form['leader_query']
    assigned_query = request.form['assigned_query']
    date_after = request.form['date_after']
    date_before = request.form['date_before']

    export_data(type_query, leader_query, assigned_query, date_after, date_before)

def export_data(type_query, leader_query, assigned_query, date_before, date_after):
    # Fetch data using arcpy and convert to pandas DataFrame
    workorders_fields = [field.name for field in arcpy.ListFields(f'{gdb_path}\\{workorders_fc}')]
    staff_fields = [field.name for field in arcpy.ListFields(f'{gdb_path}\\{staff_rt}')]
    print("Converting to pandas DataFrame")

    # Fetch data from both tables
    workorders_data = []
    with arcpy.da.SearchCursor(f'{gdb_path}\\{workorders_fc}', workorders_fields, where_clause=f"Type = '{type_query}' AND teamLeader = '{leader_query}' AND teamAssigned = '{assigned_query}' AND dateReceived <= '{date_before}' AND dateReceived >= '{date_after}'") as cursor:
        for row in cursor:
            workorders_data.append(row)

      <label for="date_before">Date Before:</label>
      <input type="date" id="date_before" name="date_before"><br><br>
      <label for="date_after">Date After:</label>
      <input type="date" id="date_after" name="date_after"><br><br>

[BlakeTerhune]

You might need to use Convert() in the SQL expression to explicitly specify the value is a date and not a string. I also reorganized a little bit for readability.

# Fetch data from both tables
workorders_data = []
and_expressions = [
    f"Type = '{type_query}'",
    f"teamLeader = '{leader_query}'",
    f"teamAssigned = '{assigned_query}'",
    f"dateReceived <= Convert(datetime, '{date_before}')",
    f"dateReceived >= Convert(datetime, '{date_after}')"
]
expression = " AND ".join(and_expressions)
with arcpy.da.SearchCursor(f'{gdb_path}\\{workorders_fc}', workorders_fields, where_clause=expression) as cursor:
    for row in cursor:
        workorders_data.append(row)

[gis_bCapell]

@MikhaylaB Can you share an example of the string objects held by the date_before and date_after variables? (I see you specified mm/dd/yyyy which is what I used in the test below, but just want to confirm what exactly is held in the variable. Also, I'm not sure how the app is setup but depending on the contents of request.form you may need to handle Null values when building the SQL Query.)

Here is a quick test to show how the Select By Attributes window handles an input string in the format MM/DD/YYYY (top left window). ArcGIS Pro converts it into YYYY-MM-DD HH:MM:SS SQL string if the field is a Date type (top blue arrow) or into YYYY-MM-DD SQL string if the field is a Date Only type (bottom blue arrow). You will need to mimic this conversion based on the structure of the strings in your date_x variables and the field type of the attribute table you are trying to query with your where_clause.

[MikhaylaB]

The end users are entering dates as 06/03/2024. I've added Convert() to transform it into 2024-06-03

RuntimeError: An invalid SQL statement was used. [SELECT OBJECTID,Shape,Type,WorkNumID,teamLeader,teamAssigned,dateReceived,status FROM pytest WHERE Type = 'Horticulture' AND teamLeader = 'Harry' AND teamAssigned = 'car' AND dateReceived >= Convert(datetime,'2024-04-01') AND dateReceived <= Convert(datetime,'2024-06-03')]

[MikhaylaB]

And the below is my date formatting in the field:

[gis_bCapell]

Hi @MikhaylaB ,

Based on what I saw in the test. It looks like you need to add '00:00:00' to your SQL query after getting the YYYY-MM-DD so that the query contains YYYY-MM-DD HH:MM:SS format even though it is only showing the year, month and day in the Number Format window. So try adding 00:00:00 following the close of Convert() and before the " on lines 7 an 8 above.

[MikhaylaB]

Thanks, I'll give it a shot!