CODEWITHSUNDEEP
python-3.xsqlalchemy
drake1994
drake1994

Reputation: 99

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

    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.

Upvotes: 0

Views: 1441

Answers (1)

snakecharmerb
snakecharmerb

Reputation: 55933

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

Upvotes: 2

Related Questions