看起来SqlAlchemy似乎不支持调用存储过程.有没有人找到适用于SQL Server的解决方法?
样品程序:
CREATE PROCEDURE list_lock_set @name varchar (5), @requester varchar(30)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO list_lock (name, requester, acquired) values (@name, @requester, GETDATE())
RETURN 0
END
GO
这有效:
import pyodbc
dbh = pyodbc.connect(driver=''{SQL Server}'', server=srv, database=db, uid=uid, pwd=pwd)
dbc = dbh.cursor()
dbc.execute("list_lock_set ?, ?", ['bbc', 'pyodbc'])
dbc.commit()
这不会产生错误,但也不起作用:
from sqlalchemy import create_engine
engine = create_engine('mssql+pyodbc://usr:passw@srv/db?driver=SQL Server', echo=True)
engine.execute("list_lock_set ?, ?", ['bbc', 'sqlalchemy'])
谢谢.
编辑:似乎最好的解决方案是从引擎中删除pyodbc游标:
cursor = engine.raw_connection().cursor()
cursor.execute("list_lock_set ?, ?", ['bbc', 'using cursor'])
cursor.commit()
我也可以获得pyodbc连接:
engine.raw_connection().connection
并设置autocommit = True,但这可能会干扰引擎逻辑.非常感谢@Batman.