How to write current date into Postgres with Python?

Question

I cannot write date into Postgres column with type 'date'.

I use this code:

import datetime
import pytz

now = datetime.datetime.now(pytz.timezone("Europe/Moscow"))
sql = "INSERT INTO questions (date) VALUES (%s, %s)" \
                      % (datetime.date(now.year, now.month, now.day))
cursor.execute(sql)

Also, I tried this:

sql = "INSERT INTO questions (date) VALUES (%s, %s)" \
                      % (datetime.datetime.now())
cursor.execute(sql)

But I have the same error:

psycopg2.errors.DatatypeMismatch: ╬╪╚┴╩└:  ёЄюысхЎ "date" шьххЄ Єшя date, р т√Ёрцхэшх - integer
LINE 1: INSERT INTO questions (id, date) VALUES (10, 2020-12-04)
                                                     ^
HINT:  ╧хЁхяш°шЄх т√Ёрцхэшх шыш яЁхюсЁрчєщЄх хую Єшя.

Can you help me, what's the problem?

Do not insert your values into the sql string. (There's good reasons for never doing this; google sql injection...) Try instead: cursor.execute(sql, (datetime.datetime.now(), (datetime.datetime.now())) — paul
– paul, Commented Dec 4, 2020 at 18:45
Please check the docs psycopg.org/docs/usage.html#passing-parameters-to-sql-queries. As paul comments, you should not use string formatting/interpolation to create SQL queries unless you understand all the rules around quoting variables. Chances are postgres thinks 2020-12-04 is an arithmetic expression. — snakecharmerb
– snakecharmerb, Commented Dec 4, 2020 at 18:48

ishak O. · Accepted Answer · 2020-12-04 20:15:04Z

1

Date type is actually a custom type of the String. So date value should be string.

Try this (which added 2 ' characters.):

sql = "INSERT INTO questions (id, date) VALUES (%s, '%s')" % (datetime.datetime.now())

Note: Check the paul's comment also!

edited Dec 4, 2020 at 20:15

answered Dec 4, 2020 at 18:57

ishak O.

1901 gold badge3 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Henry Over a year ago

See the comments on the answer about not inserting values directly into SQL strings. This can be a very severe security vulnerability.

Collectives™ on Stack Overflow

How to write current date into Postgres with Python?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related