[Feature]: Add Support for on_conflict_do_nothing in SQLAlchemy Integration

[gdoermann]

What happened?

Currently, when using the duckdb_engine with SQLAlchemy, the on_conflict_do_nothing method is not supported, resulting in an AttributeError. This feature is essential for handling insert conflicts gracefully without raising exceptions, which is particularly useful for upsert operations and maintaining data integrity.

Additional Context:
This feature is supported in other SQLAlchemy dialects like PostgreSQL and SQLite, and adding it to DuckDB would improve its integration and usability.

DuckDB Engine Version

0.13.6

DuckDB Version

1.1.3

SQLAlchemy Version

2.0.31

Relevant log output

AttributeError: 'Insert' object has no attribute 'on_conflict_do_nothing'

Code of Conduct

• I agree to follow this project's Code of Conduct

[gdoermann]

I was able to get something similar to work in my code with:

from sqlalchemy.sql.expression import Insert
from sqlalchemy.ext.compiler import compiles


class upsert(Insert):
    def __init__(self, *args, conflict_columns=None, **kwargs):
        super().__init__(*args, **kwargs)
        self.conflict_columns = conflict_columns
        # Only upsert non-primary key columns
        if self.table.primary_key and conflict_columns:
            self.update_columns = [col for col in conflict_columns if col not in self.table.primary_key.columns]
        else:
            self.update_columns = conflict_columns


@compiles(upsert, 'duckdb')
def compile_insert_on_conflict_do_nothing(element, compiler, **kwargs):
    """
    Upsert for DuckDB

    Compile an INSERT statement with an ON CONFLICT DO NOTHING clause
    """
    stmt = compiler.visit_insert(element, **kwargs)
    if element.conflict_columns and element.update_columns:
        conflict_cols = ', '.join(element.conflict_columns)
        update_cols = ', '.join(element.update_columns)
        stmt += f" ON CONFLICT ({conflict_cols}) DO UPDATE SET {update_cols}"
    else:
        stmt += " ON CONFLICT DO NOTHING"
    return stmt

I still think it would be a great thing to add to the library.

[Mause]

Can you please provide an example of code that currently errors out?

[gdoermann]

@Mause
Here is an example of ON CONFLICT DO NOTHING:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.dialects.postgresql import insert
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session
from myproject.db import engine

# 1. Setup the engine and base
Base = declarative_base()

# 2. Define the User model
class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    email = Column(String, unique=True, nullable=False)
    name = Column(String)

# 3. Create the table (if not exists)
Base.metadata.create_all(engine)

# 4. Prepare the insert statement with ON CONFLICT DO NOTHING
def add_user(user_data):
    stmt = insert(User).values(**user_data)
    stmt = stmt.on_conflict_do_nothing(index_elements=['email'])
    
    with Session(engine) as session:
        session.execute(stmt)
        session.commit()

# 5. Example usage
if __name__ == "__main__":
    user = {'email': 'jane.doe@example.com', 'name': 'Jane Doe'}
    add_user(user)

There is also on_conflict_do_update

def add_or_update_user(user_data):
    stmt = insert(User).values(**user_data)
    # Define the update to perform on conflict
    update_dict = {
        'name': stmt.excluded.name,
        'bio': stmt.excluded.bio,
        'age': stmt.excluded.age
    }
    stmt = stmt.on_conflict_do_update(
        index_elements=['email'],
        set_=update_dict
    )

    with SessionLocal() as session:
        session.execute(stmt)
        session.commit()

[gdoermann]

I see it now. I am importing the insert directly from sqlalchemy. I need to use the postgres insert or Insert method/class. thanks for the help.