Postgres: invalid input syntax for type date

Question

I have created a database and I am trying to fetch data from it. I have a class Query and inside the class I have a function that calls a table called forecasts. The function is as follows:

def forecast(self, provider: str, zone: str='Mainland',):
    self.date_start = date_start)
    self.date_end = (date_end)
    self.df_forecasts = pd.DataFrame()
        
    fquery = """
    SELECT dp.name AS provider_name, lf.datetime_from AS date, fr.name AS run_name, lf.value AS value
    FROM load_forecasts lf
    INNER JOIN bidding_zones bz ON lf.zone_id = bz.zone_id
    INNER JOIN data_providers dp ON lf.provider_id = dp.provider_id
    INNER JOIN forecast_runs fr ON lf.run_id = fr.run_id
    WHERE bz.name = '{zone}'
    AND dp.name = '{provider}'
    AND date(lf.datetime_from) BETWEEN '{self.date_start}' AND '{self.date_end}'
    """

    df_forecasts = pd.read_sql_query(fquery, self.connection)
    return df_forecasts

In the scripts that I run I am calling the Query class giving it my inputs

query = Query(date_start, date_end)

And the function

forecast_df = query.forecast(provider='Meteologica')

I run my script in the command line in the classic way

python myscript.py '2022-11-10' '2022-11-18'

My script shows the error

sqlalchemy.exc.DataError: (psycopg2.errors.InvalidDatetimeFormat) invalid input syntax for type date: "{self.date_start}"
LINE 9:         AND date(lf.datetime_from) BETWEEN '{self.date_start...

when I use this syntax, but when I manually input the string for date_start and date_end it works.

Also you do not want an f-string, this opens the door to [SQL injection](https://xkcd.com/327/), use bind parameters. — ljmc, Jan 04 '23 at 11:22

score 0 · Answer 1 · answered Jan 05 '23 at 13:35

I cannot find a way to solve the problem with sqlalchemy, so I opened a cursor with psycopg2.

 # Returns the datetime, value and provider name and issue date of the forecasts in the load_forecasts table
    # The dates range is specified by the user when the class is called
    def forecast(self, provider: str, zone: str='Mainland',):

        # Opens a cursor to get the data
        cursor = self.connection.cursor()
        # Query to run
        query = """
        SELECT dp.name, lf.datetime_from, fr.name, lf.value, lf.issue_date
        FROM load_forecasts lf
        INNER JOIN bidding_zones bz ON lf.zone_id = bz.zone_id
        INNER JOIN data_providers dp ON lf.provider_id = dp.provider_id
        INNER JOIN forecast_runs fr ON lf.run_id = fr.run_id
        WHERE bz.name = %s
        AND dp.name = %s
        AND date(lf.datetime_from) BETWEEN %s AND %s
        """
        # Execute the query, bring the data and close the cursor
        cursor.execute(query, (zone, provider, self.date_start, self.date_end))
        self.df_forecasts = cursor.fetchall()
        cursor.close()
        return self.df_forecasts

If anyone finds the answer with sqlalchemy, I would love to see it!

Postgres: invalid input syntax for type date

1 Answers1