Using postgres "time with timezone" with sqlalchemy

Question

I use postgres and sqlalchemy with python 3.7

I've got a column with type "time with timezone" and try to insert a new line.

TAB = Table("tab", METADATA,
           # ...

           Column('created', DateTime(timezone=True), default=datetime.now)
           )

Now I try to insert a new line. I got this message:

(psycopg2.ProgrammingError) column "created" is of type time with time zone but expression is of type timestamp without time zone

Has anybody an idea what I have to do?

Thanks!

Maybe datetime.utcnow(), or as in this answer: stackoverflow.com/a/33532154/5781248 — J.J. Hakala
– J.J. Hakala, Commented Feb 21, 2019 at 0:30

benvc · Accepted Answer · 2019-08-06 16:13:48Z

13

The primary issue seems to be that you are mapping a SQLALchemy DateTime column to a PostgreSQL time with time zone column. You should look into Time instead.

If you meant to use a PostgreSQL timestamp with time zone column, then you still need to rethink your default setting since datetime.now is not time zone aware. There are a few ways to handle this, but a simple one is to use SQLAlchemy's func.now() instead. For example:

from sqlalchemy import Column, DateTime, Integer, MetaData, Table
from sqlalchemy.sql import func

metadata = MetaData()

example = Table('example', metadata,
    Column('id', Integer, primary_key=True),
    Column('date', DateTime(timezone=True), default=func.now())
    )

edited Aug 6, 2019 at 16:13

answered Feb 21, 2019 at 6:00

benvc

15.3k4 gold badges39 silver badges57 bronze badges

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

Collectives™ on Stack Overflow

Using postgres "time with timezone" with sqlalchemy

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related