TL;DR
session.query(Table).with_hint(Table, "WITH (ROWLOCK)").filter(Table.count > 0)
# SELECT * FROM Table WITH (ROWLOCK) WHERE count > 0
In SQLAlchemy, the with_for_update method is normally used when locking, but as you can see in the example below, SQL Server does not have FOR UPDATE, so this description is ignored.
from sqlalchemy.sql import select, table, literal_column
from sqlalchemy.dialects import mssql
tbl = table("MyTable")
stmt = select([tbl]).where(literal_column("hoge") > 0).with_for_update()
print(stmt.compile(dialect=mssql.dialect()))
# SELECT
# FROM [MyTable]
# WHERE hoge > :hoge_1
In SQL Server, locks are described as WITH (ROWLOCK, UDPLOCK) at the end of the FROM clause in the form of lock hints. In SQLAlchemy, you can embed a hint string as follows using a method called with_hint.
from sqlalchemy.sql import select, table, literal_column
from sqlalchemy.dialects import mssql
tbl = table("MyTable")
stmt = select([tbl]).with_hint(tbl, "WITH (ROWLOCK)").where(literal_column("count") > 0)
print(stmt.compile(dialect=mssql.dialect()))
# SELECT
# FROM [MyTable] WITH (ROWLOCK)
# WHERE count > :count_1
Congratulations
https://docs.sqlalchemy.org/en/14/core/selectable.html#sqlalchemy.sql.expression.Select.with_hint
https://docs.sqlalchemy.org/en/14/core/selectable.html#sqlalchemy.sql.expression.Select.with_for_update
https://stackoverflow.com/questions/56030825/sqlalchemy-query-api-not-working-correctly-with-hints
https://www.m3tech.blog/entry/sqlalchemy-tutorial
Recommended Posts