👀 Views: 23 💬 Answers: 1 📅 Created: 2025-08-07

sqlite sqlalchemy flask Python

I'm writing unit tests and I'm relatively new to this, so bear with me. I'm working on a Flask application that uses SQLAlchemy to interact with an SQLite database. I'm trying to implement an upsert operation on a table that contains a unique constraint on one of the columns. However, when I attempt to perform the upsert, I keep working with a `sqlite3.IntegrityError: UNIQUE constraint failed: my_table.unique_column` behavior. Here's a simplified version of my model: ```python from sqlalchemy import Column, Integer, String, create_engine, UniqueConstraint from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import sessionmaker Base = declarative_base() class MyTable(Base): __tablename__ = 'my_table' id = Column(Integer, primary_key=True) unique_column = Column(String, unique=True) value = Column(String) __table_args__ = (UniqueConstraint('unique_column', name='uq_unique_column'),) ``` I am using the following function to perform the upsert: ```python def upsert(session, unique_value, new_value): session.execute( "INSERT INTO my_table (unique_column, value) VALUES (:unique_value, :new_value)" " ON CONFLICT(unique_column) DO UPDATE SET value = excluded.value", {'unique_value': unique_value, 'new_value': new_value} ) session.commit() ``` When I run the `upsert` function, it works as expected if the record does not exist. However, if the record already exists in the database, I get the unique constraint violation. I've verified that the `unique_column` value I pass is indeed unique before calling the `upsert` function, but the behavior continues. I've also tried the following: 1. Checking the database to see if there are duplicate entries for that unique column. 2. Changing the SQLite version to 3.36.0, but the scenario remains. 3. Using `session.merge()` instead of a raw SQL insert, but it doesn't give me the expected behavior either. Any ideas on what's causing this scenario? I would greatly appreciate any guidance on how to resolve this, as I'm exploring trying to figure out the exact cause of the constraint violation. Has anyone else encountered this? I appreciate any insights!