psycopg2 SyntaxError with time or date

I have a code that automatically identifies for what table, which columns and what the values to parse into sql insert statement. It works well with all of my tables, except only one. This is a structure of the model of this table:

class HourlyForecast(Model, db.Model):
__tablename__ = 'hourly_forecast'
id = db.Column(db.Integer, primary_key=True, nullable=False)
city_id = db.Column(db.Integer, db.ForeignKey('city.id'), nullable=False)
weather_status_id = db.Column(db.Integer, db.ForeignKey('weather_status.id'), nullable=False)
temperature = db.Column(db.Integer, nullable=False)
wind_speed = db.Column(db.Float(decimal_return_scale=2), nullable=False)
pressure = db.Column(db.Float(decimal_return_scale=2), nullable=False)
humidity = db.Column(db.Float(decimal_return_scale=2),  nullable=False)
precipitation = db.Column(db.Integer, nullable=False)
time = db.Column(db.Integer, nullable=False)
current_time = db.Column(db.Time, nullable=False)
date = db.Column(db.Date, nullable=False)

I got SyntaxError only with this table. My insertion statement:

columns = kwargs.keys()
values = [kwargs[column] for column in columns]
insert_statement = f'insert into {table}(%s) values %s'
obj = cur.mogrify(insert_statement, (AsIs(','.join(columns)), tuple(values)))

This is an error:

psycopg2.errors.SyntaxError: ПОМИЛКА:  синтаксична помилка в або поблизу "current_time"
LINE 1: ...e,wind_speed,pressure,humidity,precipitation,time,current_ti...

It’s says in Ukrainian syntax error at or near "current_time". And this values were parsed into the hourly_weather table:

[5, 1, 16, 2, 1018, 61, 4, 8, '08:34', datetime.date(2022, 6, 16)]

Where current_time is before the last one value.

And yes, I use both psycopg2 and sqlalchemy. There are some reasons for that.

Answer

I’m not sure I follow all that is going on here (actually, I’m sure that I don’t…) but I think that

Your issue is that current_time is a keyword in PostgreSQL, and
that you should probably use the SQL building features of psycopg2 to handle the necessary quoting for you.

Advertisement

Answer