select ... for update statements gives Attribute Error

[HarshPathakhp]

select ... for update statements gives Attribute Error

[HarshPathakhp]

It seems that ibm_db_sa doesn't support with_for_update() function.
Example Usage ->
select(Users).where(Users.id == 1).with_for_update()

Please see detailed stacktrace below.

File "H:\projects\pythonProject\learn-sql-alchemy\alchemy.py", line 36, in 
    result = session.execute(stmt).scalars()
  File "C:\Developer\projects\pythonProject\learn-sql-alchemy\env\lib\site-packages\sqlalchemy\orm\session.py", line 1712, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "C:\Developer\projects\pythonProject\learn-sql-alchemy\env\lib\site-packages\sqlalchemy\engine\base.py", line 1705, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "C:\Developer\projects\pythonProject\learn-sql-alchemy\env\lib\site-packages\sqlalchemy\sql\elements.py", line 333, in _execute_on_connection
    return connection._execute_clauseelement(
  File "C:\Developer\projects\pythonProject\learn-sql-alchemy\env\lib\site-packages\sqlalchemy\engine\base.py", line 1564, in _execute_clauseelement
    compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
  File "C:\Developer\projects\pythonProject\learn-sql-alchemy\env\lib\site-packages\sqlalchemy\sql\elements.py", line 544, in _compile_w_cache
    compiled_sql = self._compiler(
  File "C:\Developer\projects\pythonProject\learn-sql-alchemy\env\lib\site-packages\sqlalchemy\sql\elements.py", line 566, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
  File "C:\Developer\projects\pythonProject\learn-sql-alchemy\env\lib\site-packages\sqlalchemy\sql\compiler.py", line 790, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File "C:\Developer\projects\pythonProject\learn-sql-alchemy\env\lib\site-packages\sqlalchemy\sql\compiler.py", line 463, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "C:\Developer\projects\pythonProject\learn-sql-alchemy\env\lib\site-packages\sqlalchemy\sql\compiler.py", line 498, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "C:\Developer\projects\pythonProject\learn-sql-alchemy\env\lib\site-packages\sqlalchemy\sql\visitors.py", line 82, in _compiler_dispatch
    return meth(self, **kw)
  File "C:\Developer\projects\pythonProject\learn-sql-alchemy\env\lib\site-packages\ibm_db_sa\base.py", line 365, in visit_select
    sql_ori = compiler.SQLCompiler.visit_select(self, select, **kwargs)
  File "C:\Developer\projects\pythonProject\learn-sql-alchemy\env\lib\site-packages\sqlalchemy\sql\compiler.py", line 3441, in visit_select
    text = self._compose_select_body(
  File "C:\Developer\projects\pythonProject\learn-sql-alchemy\env\lib\site-packages\sqlalchemy\sql\compiler.py", line 3625, in _compose_select_body
    text += self.for_update_clause(select, **kwargs)
  File "C:\Developer\projects\pythonProject\learn-sql-alchemy\env\lib\site-packages\ibm_db_sa\base.py", line 346, in for_update_clause
    if select.for_update is True:
AttributeError: 'Select' object has no attribute 'for_update'

[cdracars]

I also have encountered this issue.

[bchoudhary6415]

@HarshPathakhp and @cdracars
yes, ibm_db_sa doesn't support with_for_update() function.

by using raw SQL statement, you can achieve the same result
You can refer below example

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine("db2+ibm_db://userName:password@host:portNumber/database;")
Session = sessionmaker(bind=engine)
session = Session()
sql = "SELECT * from Users WHERE id = 1 FOR UPDATE"
result = session.execute(sql,{"id" : 1})
rows = result.fetchall()
for row in rows:
print(row)
session.commit()
session.close()

This approach gives you the ability to include a 'FOR UPDATE' clause in your query and lock the selected rows for update,
even if the 'ibm_db_sa' library does not support the 'with_for_update()' method.

[bchoudhary6415]

Hello @HarshPathakhp and @cdracars
Can you please give me update on the above issue?
Also, please share sample repo if possible.

[bchoudhary6415]

Hello @HarshPathakhp and @cdracars
Can you please give provide any update on this issue?

[cdracars]

i apoligise for not responding earlier. I ended up reworking my queries so as not to need this function. Sorry :(