How to update row if session.add throws duplicate key error in sql alchemy ORM

Question

    engine = db_connect()
    create_table(engine)
    Session = sessionmaker(bind=engine)
    self.session = Session()

    settings = get_project_settings()
    stock_engine = create_engine(settings.get('STOCK_DATABASE_URI'), echo=True,pool_recycle=1800)
    StockSession = sessionmaker(bind=stock_engine)
    self.stock_session = StockSession()

def process_item(self, item, spider):
    sleep(0.3)
    item_table.uni_identifier = item['uni_identifier']
    
    symbols = self.stock_session.execute("SELECT * from table3")
    
    for row in symbols:
        stock_table = Trial_table()
        stock_table.stock_symbol = row["symbol"]
        stock_table.company_name = row["companyName"]
        stock_table.last_updated = arrow.utcnow().to('local').format('YYYY-MM-DD HH:mm:ss')
        
        self.session.add(stock_table)
        
        try:
            self.session.commit()
        except:
            self.session.rollback()
            raise

    self.session.add(item_table)
    try:
       self.session.commit()
    except:
       self.session.rollback()
       raise

sqlalchemy.exc.IntegrityError: (pymysql.err.IntegrityError) (1062, "Duplicate entry 'GNLN' for key 'trial_table.stock_symbol_UNIQUE'")

Above Error is generated at session.add(stock_table).What I want to achieve is if session.add(stock_table) generated duplicate key error I want to update instead of insert. How can I achieve this? Already tried using self.session.merge(stock_table). Its not working. Trial_table() is a ORM for respective table in database.

snakecharmerb · Accepted Answer · 2021-08-29 14:42:16Z

For a single-threaded application, it's simplest to trap the IntegrityErrorand try an update instead (IntegrityErrors happen when a session is committed (or flushed)).

    session.add(Trial_Table(stock_symbol=symbol, company_name=company_name))
    try:
        session.commit()
    except sa.exc.IntegrityError:
        session.rollback()
        instance = session.query(Trial_Table).filter(Trial_Table.stock_symbol == symbol).first()
        instance.company_name = company_name
        try:
            session.commit()
        except:
            session.rollback()
            raise
    except:
        session.rollback()
        raise

Ugly, but effective.

Using MySQL's "ON DUPLICATE KEY ... UPDATE" functionality would be another way. Note that the ORM functionality is experimental. It might look something like this (untested as my version of Mariadb does not support RETURNING).

from sqlalchemy import select, update

...

stmt = update(Trial_Table).where(Trial_Table.stock_symbol == symbol).values(company_name=company_name).returning(Trial_Table)
orm_stmt = select(Trial_Table).from_statement(stmt).execution_options(populate_existing=True)
session.execute(orm_stmt)
try:
    session.commit()
except:
    session.rollback()
    raise

Alternatively, you could use SQLAlchemy core syntax to perform the upsert without having to rely on support for RETURNING.

from sqlalchemy.dialects.mysql import insert
...

# Operate on the model's __table__ attribute
stmt = insert(Trial_Table.__table__).values(stock_symbol=symbol, company_name=company_name)
on_duplicate_key_stmt = stmt.on_duplicate_key_update(company_name=company_name)
session.execute(on_duplicate_key_stmt)
try:
    session.commit()
except:
    session.rollback()
    raise

Collectives™ on Stack Overflow

How to update row if session.add throws duplicate key error in sql alchemy ORM

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related