pythonflasksqlalchemyflask-sqlalchemy

How to set default datetime value with SQLAlchemy?

I'm trying to set a default server value for a created_at column in a MySQL table with the SQLAlchemy library in Python 3.13. But, whenever I start up my Flask app and the table gets created, I run into an issue where the default value gets set to a string that reads (now()) Inserting a row while leaving the created_at field empty returns a MySQL error because (now()) is not a valid datetime. Below is how my code is structured.

from db import db
from sqlalchemy import Column, DateTime, String, Integer
from sqlalchemy.sql import func

class BlogModel(db.Model):
    __tablename__ = "blogs"

id = Column(Integer, primary_key=True)
created_at = Column(DateTime(timezone=True), server_default=func.now(), nullable=False)

I've also tried the following with the same results:

created_at = Column(DateTime(timezone=True), server_default=func.current_timestamp(), nullable=False)

My requirements.txt file is as follows:

alembic==1.14.0
apispec==6.8.1
blinker==1.9.0
certifi==2024.12.14
click==8.1.8
distlib==0.3.9
filelock==3.16.1
Flask==3.1.0
Flask-JWT-Extended==4.7.1
Flask-Migrate==4.1.0
flask-smorest==0.45.0
Flask-SQLAlchemy==3.1.1
itsdangerous==2.2.0
Jinja2==3.1.5
Mako==1.3.8
MarkupSafe==3.0.2
marshmallow==3.25.1
packaging==24.2
pipenv==2024.4.0
platformdirs==4.3.6
PyJWT==2.10.1
PyMySQL==1.1.1
pytz==2024.2
setuptools==75.8.0
SQLAlchemy==2.0.37
typing_extensions==4.12.2
virtualenv==20.29.1
webargs==8.6.0
Werkzeug==3.1.3

enter image description here

Solution

  • either you use default with a python function, or you use server_default with a SQL function it seems to me (https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.Column.params.server_default)

    So:

    created_at = Column(DateTime(timezone=True), server_default=text('NOW()')), nullable=False)