SQLAlchemy Continuum tries to add empty rows to the database

[ErikvdVen]

SQLAlchemy Continuum tries to add an empty row to the database, when I try to update an existing record:

uow.process_before_flush(session)
  File "/project/.venv/lib/python3.11/site-packages/sqlalchemy_continuum/unit_of_work.py", line 71, in process_before_flush
    self.create_transaction(session)
  File "/project/.venv/lib/python3.11/site-packages/sqlalchemy_continuum/unit_of_work.py", line 121, in create_transaction

.....

sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1048, "Column 'active' cannot be null")
[SQL: INSERT INTO transactions (account_id, user_id, date, offset_iban, offset_name, offset_bic, final_party, amount, currency, description, hash, correction_transaction_id, correction_type, active) VALUES (%(account_id)s, %(user_id)s, %(date)s, %(offset_iban)s, %(offset_name)s, %(offset_bic)s, %(final_party)s, %(amount)s, %(currency)s, %(description)s, %(hash)s, %(correction_transaction_id)s, %(correction_type)s, %(active)s) RETURNING transactions.id, transactions.created_on, transactions.updated_on]
[parameters: {'account_id': None, 'user_id': None, 'date': None, 'offset_iban': None, 'offset_name': None, 'offset_bic': None, 'final_party': None, 'amount': None, 'currency': None, 'description': None, 'hash': None, 'correction_transaction_id': None, 'correction_type': None, 'active': None}]

This is the code:

transaction = db.query(Transaction).filter(Transaction.id == transaction_id).first()
transaction.active = False
db.commit()

I've changed my models, so the active column can't hold a default value anymore, so now it fails. If I enable the active column to have a default value, then it updates the existing record, but it also adds an empty row to both the version table as well as the actual table which contains the transactions. I've no clue why this is happening. This is my database.py:

from sqlalchemy import URL, create_engine
from sqlalchemy.orm import declarative_base, sessionmaker
from config import Settings
from sqlalchemy_continuum import make_versioned

settings = Settings()
make_versioned(options={
    'transaction_column_name': 'tx_id',
    'end_transaction_column_name': 'end_tx_id'
})

mysql_db = {
    "drivername": "mysql+pymysql",
    "username": username,
    "password": .password,
    "host": host,
    "database": name,
    "port": port,
    "query": None,
}

engine = create_engine(URL.create(**mysql_db))
Base = declarative_base()
Session = sessionmaker(engine, autoflush=False)

def get_db():
    db = Session()
    try:
        yield db
    finally:
        db.close()

I use declarative_base() instead of configure_mappers. Tables are created and rows are inserted in the version tables, so that should be all good. The only issue is the empty rows which are added when updating an existing row. The version table gets two rows inserted: one with the update that happened and an empty row telling an insert happened. At first, I thought the inserts were an issue, but then I figured actual empty rows were inserted in the transactions table.

Now I get an error because I refuse the active column of having a default value. If I change my model by telling the active column can contain a default value, empty rows are inserted.

Any thoughts?

[ErikvdVen]

I think there is a conflict with my own transactionS table and the transaction table of SQLAlchemy Continuum, is there a way to tell the package to use a different table name or whateveer? Or is it best to change my own table name?

I get the same error over and over again, no matter which model tries to insert something into the database. Same insert on the same table with the same error.

[ErikvdVen]

Fixed it by migrating my own table "transactions" (including constraints, foreign keys etc) to "bank_transactions". Had to use configure_mappers() function anyways, though I thought I wouldn't. I'm calling that function now inside the __init__.py, of the models folder, after importing all classes. Thought that would be the best place.

make_versioned(user_cls=None) is called on the database.py (wondering what user_cls is for, though).

Seems to work fine now.