SQLAlchemy 2.0 ORM Model DateTime Insertion

Question

I am having some real trouble getting a created_date column working with SQLAlchemy 2.0 with the ORM model. The best answer so far I've found is at this comment: https://stackoverflow.com/a/33532154 however I haven't been able to make that function work. In my (simplified) models.py file I have:

import datetime
from sqlalchemy import Integer, String, DateTime
from sqlalchemy.sql import func
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column

class Base(DeclarativeBase):
    pass

class MyTable(Base):
    __tablename__ = "my_table"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String, nullable=False)
    created_date: Mapped[datetime.datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())

So far, so good, thinks I. In the simplified engine.py I have:

from sqlalchemy import create_engine
from sqlalchemy import select
from sqlalchemy.orm import Session

import models

def add_entry(engine, name_str):
    this_row = models.MyTable()
    this_row.name = name_str
    with Session(engine) as session:
        session.add(this_row)
        session.commit()

If I'm understanding correctly, the default value for the created_date to be a SQL function, and SQLAlchemy maps now() to SQLite3's datetime(). With the engine set to echo=True, I get the following result when it tries to run this insert command (Please note, this is data from the non-simplified form but it's still pretty simple, had 3 strings instead of the one I described)

2023-02-06 09:47:07,080 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-02-06 09:47:07,080 INFO sqlalchemy.engine.Engine INSERT INTO coaches (d_name, bb2_name, bb3_name) VALUES (?, ?, ?) RETURNING id, created_date
2023-02-06 09:47:07,081 INFO sqlalchemy.engine.Engine [generated in 0.00016s] ('andy#1111', 'AndyAnderson', 'Killer Andy')
2023-02-06 09:47:07,081 INFO sqlalchemy.engine.Engine ROLLBACK

This causes an exception when it gets to the time function: IntegrityError: NOT NULL constraint failed: coaches.created_date

Some additional data (I have been using the rich library which produces an enormous amount of debug information so I'm trying to get the best bits:

│ ╭─────────────────────────────────────────── locals ───────────────────────────────────────────╮ │
│ │    exc_tb = <traceback object at 0x00000108BD2565C0>                                         │ │
│ │  exc_type = <class 'sqlalchemy.exc.IntegrityError'>                                          │ │
│ │ exc_value = IntegrityError('(sqlite3.IntegrityError) NOT NULL constraint failed:             │ │
│ │             coaches.created_date')                                                           │ │
│ │      self = <sqlalchemy.util.langhelpers.safe_reraise object at 0x00000108BD1B79A0>          │ │
│ │ traceback = None                                                                             │ │
│ │     type_ = None                                                                             │ │
│ │     value = None                                                                             │ │
│ ╰──────────────────────────────────────────────────────────────────────────────────────────────╯

In any event, I feel like I've gotten the wrong end of the stick on the way to make a table column automatically execute a SQL command with the func call. Any notions on this one? I haven't found any direct example in the SQLAlchemy 2.0 docs, and aside from the pretty awesome comment to a similar question, I haven't found any working solutions.

Thanks for considering!

I implemented a SQLAlchemy 2.0 mapped_column with a server_default of func.now() expecting the column to automatically fill during an INSERT operation. During the insert operation, SQLAlchemy threw an exception claiming the column NOT NULLABLE constraint was violated -- thus it was not automatically filling.

Answer 1

Posting an answer to my own question to note what actually did work (actual problem still exists, but a simplified variation does work just dandy the way I expect it to.)

import datetime

from sqlalchemy import Integer, String, DateTime
from sqlalchemy import create_engine
from sqlalchemy.sql import func
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import Session


class Base(DeclarativeBase):
    pass


class MyTable(Base):
    __tablename__ = "my_table"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String)
    created_date: Mapped[datetime.datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now()
    )


def initialize_engine(filename):
    return create_engine(f"sqlite+pysqlite:///{filename}", echo=True)


def initialize_tables(engine):
    Base.metadata.create_all(engine)


def add_row(engine, name):
    this_row = MyTable(name=name)
    print(this_row)
    with Session(engine) as session:
        session.add(this_row)
        session.commit()


my_file = "test.db"

my_engine = initialize_engine(my_file)
initialize_tables(my_engine)

add_row(my_engine, "Dave")

This produces the result:

python datetest.py
2023-02-06 11:02:41,157 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-02-06 11:02:41,158 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("my_table")
2023-02-06 11:02:41,158 INFO sqlalchemy.engine.Engine [raw sql] ()
2023-02-06 11:02:41,158 INFO sqlalchemy.engine.Engine COMMIT
<__main__.MyTable object at 0x000002CC767ECD50>
2023-02-06 11:02:41,159 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-02-06 11:02:41,160 INFO sqlalchemy.engine.Engine INSERT INTO my_table (name) VALUES (?) RETURNING id, created_date
2023-02-06 11:02:41,160 INFO sqlalchemy.engine.Engine [generated in 0.00020s] ('Dave',)
2023-02-06 11:02:41,171 INFO sqlalchemy.engine.Engine COMMIT

The schema in the correctly working database reads:

sqlite> .schema my_table
CREATE TABLE my_table (
        id INTEGER NOT NULL,
        name VARCHAR NOT NULL,
        created_date DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL,
        PRIMARY KEY (id)
);

So... all I have to do is figure out why my original code isn't doing the simple variation!