2

I'm creating a table using SQLAclhemy. The table includes a unique index, and I would like the table to be setup to ignore attempts to insert a row that is a duplicate of an existing index value. This is what my code looks like:

import os

import sqlalchemy
from sqlalchemy import (Column, String, Integer, Float, DateTime, Index)

from sqlalchemy.ext.declarative import declarative_base

db_user = 'USERNAME'
db_password = 'PASSWORD'
address = '000.000.0.0' 
port = '1111'
database = 'my_db'


engine = sqlalchemy.create_engine(
        f"mariadb+mariadbconnector://{db_user}:{db_password}@{address}:{port}/{database}")

Base = declarative_base()

class Pet(Base):
    __tablename__ = 'pets'
    id = Column(Integer, primary_key=True)
    name = Column(String(8))
    weight = Column(Float)
    breed = Column(Float)
    birthday = Column(DateTime)
    __table_args__ = (Index('breed_bd', "breed", "birthday", unique=True), )

Base.metadata.create_all(engine)
Session = sqlalchemy.orm.sessionmaker()
Session.configure(bind=engine)
session = Session()

I've seen that in straight sql, you can do things like

CREATE TABLE dbo.foo (bar int PRIMARY KEY WITH (IGNORE_DUP_KEY = ON))

or

CREATE UNIQUE INDEX UNQ_CustomerMemo ON CustomerMemo (MemoID, CustomerID)
    WITH (IGNORE_DUP_KEY = ON);

I'm wondering what I should change/add in my code to accomplish something similar.

jpobst
  • 3,491
  • 2
  • 25
  • 24
  • 3
    It seems to me that `WITH (IGNORE_DUP_KEY...` is not supported by MariaDB (it looks like a Microsoft SQL-Server feature) so it isn't possible. You may be able to use `INSERT IGNORE...` or `ON DUPLICATE KEY UPDATE` as described the answers [here](https://stackoverflow.com/q/46654221/5320906). – snakecharmerb Aug 14 '22 at 06:53
  • @snakecharmerb good catch. I was trying to avoid an `INSERT IGNORE` because I'd prefer to insert my data using `df.to_sql()` (which doesn't have an ignore option), but I think you've convinced me `INSERT IGNORE` is my best option. I've just tried it out using SQLAlchemy's `insert()` and it's working (with just a few extra lines of code). Thank you. – jpobst Aug 14 '22 at 13:15

0 Answers0