search

kvesteri / sqlalchemy-continuum

Versioning extension for SQLAlchemy.
BSD 3-Clause "New" or "Revised" License
575 stars 127 forks source link

Datetime column in assoc table with sqlite DB does not work #282

Open AbdealiLoKo opened 2 years ago

AbdealiLoKo commented 2 years ago

When I use a datetime column with default in my assoc table in sqlite - sqlalchemy_continuum is throwing an error in append_association_operation

Reproducible example:

import datetime

from sqlalchemy import Column, ForeignKey, Integer, DateTime, Table, create_engine, func, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, configure_mappers
from sqlalchemy_continuum import make_versioned

make_versioned(user_cls=None)

Base = declarative_base()

book_author_table = Table(
    'book_author',
    Base.metadata,
    Column('book_id', Integer, ForeignKey('book.id'), primary_key=True, nullable=False),
    Column('author_id', Integer, ForeignKey('author.id'), primary_key=True, nullable=False),
    Column('created_date', DateTime, nullable=False, server_default=func.current_timestamp(), default=datetime.datetime.utcnow),
)

class Book(Base):
    __tablename__ = 'book'
    __versioned__ = {}

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String, nullable=False)
    authors = relationship('Author', secondary=book_author_table, back_populates='books')

class Author(Base):
    __tablename__ = 'author'
    __versioned__ = {}

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String, nullable=False)
    books = relationship('Book', secondary=book_author_table, back_populates='authors')

configure_mappers()
engine = create_engine('sqlite:///db.sqlite', echo=True)

# Create all tables
Base.metadata.create_all(bind=engine)

# Create a session
session = sessionmaker()
session.configure(bind=engine)
db = session()

lotr = Book(name='Lord of the rings')
tolkien = Author(name='JRR Tolkien', books=[lotr])
db.add(lotr)
db.add(tolkien)
db.commit()

To run this, you can do:

$ python -m venv venv
$ venv/bin/pip install sqlalchemy==1.4.39 sqlalchemy-continuum==1.3.12
$ venv/bin/python app.py
BEGIN (implicit)
PRAGMA main.table_info("book_author")
[raw sql] ()
PRAGMA temp.table_info("book_author")
[raw sql] ()
PRAGMA main.table_info("book")
[raw sql] ()
PRAGMA temp.table_info("book")
[raw sql] ()
PRAGMA main.table_info("author")
[raw sql] ()
PRAGMA temp.table_info("author")
[raw sql] ()
PRAGMA main.table_info("book_version")
[raw sql] ()
PRAGMA temp.table_info("book_version")
[raw sql] ()
PRAGMA main.table_info("author_version")
[raw sql] ()
PRAGMA temp.table_info("author_version")
[raw sql] ()
PRAGMA main.table_info("transaction")
[raw sql] ()
PRAGMA temp.table_info("transaction")
[raw sql] ()
PRAGMA main.table_info("book_author_version")
[raw sql] ()
PRAGMA temp.table_info("book_author_version")
[raw sql] ()

CREATE TABLE book (
        id INTEGER NOT NULL,
        name VARCHAR NOT NULL,
        PRIMARY KEY (id)
)

[no key 0.00008s] ()

CREATE TABLE author (
        id INTEGER NOT NULL,
        name VARCHAR NOT NULL,
        PRIMARY KEY (id)
)

[no key 0.00015s] ()

CREATE TABLE book_version (
        id INTEGER NOT NULL,
        name VARCHAR,
        transaction_id INTEGER NOT NULL,
        end_transaction_id INTEGER,
        operation_type SMALLINT NOT NULL,
        PRIMARY KEY (id, transaction_id)
)

[no key 0.00019s] ()
CREATE INDEX ix_book_version_transaction_id ON book_version (transaction_id)
[no key 0.00017s] ()
CREATE INDEX ix_book_version_operation_type ON book_version (operation_type)
[no key 0.00021s] ()
CREATE INDEX ix_book_version_end_transaction_id ON book_version (end_transaction_id)
[no key 0.00017s] ()

CREATE TABLE author_version (
        id INTEGER NOT NULL,
        name VARCHAR,
        transaction_id INTEGER NOT NULL,
        end_transaction_id INTEGER,
        operation_type SMALLINT NOT NULL,
        PRIMARY KEY (id, transaction_id)
)

[no key 0.00022s] ()
CREATE INDEX ix_author_version_transaction_id ON author_version (transaction_id)
[no key 0.00018s] ()
CREATE INDEX ix_author_version_end_transaction_id ON author_version (end_transaction_id)
[no key 0.00018s] ()
CREATE INDEX ix_author_version_operation_type ON author_version (operation_type)
[no key 0.00017s] ()

CREATE TABLE "transaction" (
        issued_at DATETIME,
        id INTEGER NOT NULL,
        remote_addr VARCHAR(50),
        PRIMARY KEY (id)
)

[no key 0.00015s] ()

CREATE TABLE book_author_version (
        book_id INTEGER NOT NULL,
        author_id INTEGER NOT NULL,
        created_date DATETIME DEFAULT (CURRENT_TIMESTAMP),
        transaction_id INTEGER NOT NULL,
        end_transaction_id INTEGER,
        operation_type SMALLINT NOT NULL,
        PRIMARY KEY (book_id, author_id, transaction_id)
)

[no key 0.00017s] ()
CREATE INDEX ix_book_author_version_transaction_id ON book_author_version (transaction_id)
[no key 0.00014s] ()
CREATE INDEX ix_book_author_version_operation_type ON book_author_version (operation_type)
[no key 0.00014s] ()
CREATE INDEX ix_book_author_version_end_transaction_id ON book_author_version (end_transaction_id)
[no key 0.00014s] ()

CREATE TABLE book_author (
        book_id INTEGER NOT NULL,
        author_id INTEGER NOT NULL,
        created_date DATETIME DEFAULT (CURRENT_TIMESTAMP) NOT NULL,
        PRIMARY KEY (book_id, author_id),
        FOREIGN KEY(book_id) REFERENCES book (id),
        FOREIGN KEY(author_id) REFERENCES author (id)
)

[no key 0.00020s] ()
COMMIT
BEGIN (implicit)
INSERT INTO "transaction" (issued_at, remote_addr) VALUES (?, ?)
[generated in 0.00036s] ('2022-08-17 05:13:58.336324', None)
INSERT INTO author (name) VALUES (?)
[generated in 0.00021s] ('JRR Tolkien',)
INSERT INTO book (name) VALUES (?)
[generated in 0.00020s] ('Lord of the rings',)
INSERT INTO book_author (book_id, author_id, created_date) VALUES (?, ?, ?)
[generated in 0.00053s] (1, 1, '2022-08-17 05:13:58.342296')
ROLLBACK
Traceback (most recent call last):
  File "/home/abdealijk/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1720, in _execute_context
    dialect, self, conn, execution_options, *args, **kw
  File "/home/abdealijk/venv/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 1076, in _init_compiled
    for key in positiontup
  File "/home/abdealijk/venv/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 1076, in <listcomp>
    for key in positiontup
  File "/home/abdealijk/venv/lib/python3.7/site-packages/sqlalchemy/dialects/sqlite/base.py", line 1004, in process
    "SQLite DateTime type only accepts Python "
TypeError: SQLite DateTime type only accepts Python datetime and date objects as input.

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "app.py", line 61, in <module>
    db.commit()
  File "/home/abdealijk/venv/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1451, in commit
    self._transaction.commit(_to_root=self.future)
  File "/home/abdealijk/venv/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 829, in commit
    self._prepare_impl()
  File "/home/abdealijk/venv/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 808, in _prepare_impl
    self.session.flush()
  File "/home/abdealijk/venv/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 3383, in flush
    self._flush(objects)
  File "/home/abdealijk/venv/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 3523, in _flush
    transaction.rollback(_capture_exception=True)
  File "/home/abdealijk/venv/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 72, in __exit__
    with_traceback=exc_tb,
  File "/home/abdealijk/venv/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/home/abdealijk/venv/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 3487, in _flush
    self.dispatch.after_flush(self, flush_context)
  File "/home/abdealijk/venv/lib/python3.7/site-packages/sqlalchemy/event/attr.py", line 256, in __call__
    fn(*args, **kw)
  File "/home/abdealijk/venv/lib/python3.7/site-packages/sqlalchemy_continuum/manager.py", line 354, in after_flush
    uow.process_after_flush(session)
  File "/home/abdealijk/venv/lib/python3.7/site-packages/sqlalchemy_continuum/unit_of_work.py", line 95, in process_after_flush
    self.make_versions(session)
  File "/home/abdealijk/venv/lib/python3.7/site-packages/sqlalchemy_continuum/unit_of_work.py", line 308, in make_versions
    self.create_association_versions(session)
  File "/home/abdealijk/venv/lib/python3.7/site-packages/sqlalchemy_continuum/unit_of_work.py", line 295, in create_association_versions
    session.execute(stmt)
  File "/home/abdealijk/venv/lib/python3.7/site-packages/sqlalchemy/orm/session.py", line 1712, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/home/abdealijk/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1631, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/home/abdealijk/venv/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 333, in _execute_on_connection
    self, multiparams, params, execution_options
  File "/home/abdealijk/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1508, in _execute_clauseelement
    cache_hit=cache_hit,
  File "/home/abdealijk/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1726, in _execute_context
    e, util.text_type(statement), parameters, None, None
  File "/home/abdealijk/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 2044, in _handle_dbapi_exception
    sqlalchemy_exception, with_traceback=exc_info[2], from_=e
  File "/home/abdealijk/venv/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/home/abdealijk/venv/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1720, in _execute_context
    dialect, self, conn, execution_options, *args, **kw
  File "/home/abdealijk/venv/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 1076, in _init_compiled
    for key in positiontup
  File "/home/abdealijk/venv/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 1076, in <listcomp>
    for key in positiontup
  File "/home/abdealijk/venv/lib/python3.7/site-packages/sqlalchemy/dialects/sqlite/base.py", line 1004, in process
    "SQLite DateTime type only accepts Python "
sqlalchemy.exc.StatementError: (builtins.TypeError) SQLite DateTime type only accepts Python datetime and date objects as input.
[SQL: INSERT INTO book_author_version (book_id, author_id, created_date, transaction_id, operation_type) VALUES (?, ?, ?, ?, ?)]
indiVar0508 commented 2 years ago

Hi @AbdealiJK ,

Thanks for code to reproduce the error, was able to generate traceback locally, the issue seems to be happening when Sqlalchemy event triggers a callable(@track_association_operations L434 Manager.py) at before_flush event and values returned as part of param tuple contain string ISOformat datetime value,

have raised PR #283 as a fix which seems to work with provided code and testcases also seem to be unaltered, some feedback is required from maintainers of this project in regards for using a third party dependency and code context flow for exception handling if needed.

waiting for workflow approval from maintainers to verify any unseen issue/concerns.

Thanks