SQLAlchmey调用存储过程获取出参报错ORA-00936: missing expression，该如何解决？

Z时代
2024-02-25
分类：IT

Oracle存储过程代码如下：

create procedure test_procedure(
id_in in number,
del_num out number
) as 
begin
  select count(*) into del_num from test t where t.id = id_in ;
  delete from test t where t.id = id_in ;
  commit;
end;
/

python调用代码如下：

from sqlalchemy import create_engine , text
from sqlalchemy.orm import sessionmaker
from configs import url
# 创建数据库连接
engine = create_engine(url)
# 创建会话
Session = sessionmaker(bind=engine)
session = Session()
# 调用存储过程
result = session.execute(text("CALL test_procedure(:param1, @out_param)"),
                         {'param1': 3})
# 获取输出参数的值
out_param_value = session.execute("SELECT @out_param").scalar()
# 打印输出参数的值print(out_param_value)

执行python报错信息：
DatabaseError: (cx_Oracle.DatabaseError) ORA-00936: missing expression

咨询了GPT给了如下的代码：

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import outparam
from configs import url
# 创建数据库连接
engine = create_engine(url)
# 创建会话
Session = sessionmaker(bind=engine)
session = Session()
# 调用存储过程
result = session.execute("BEGIN test_procedure(:alarmid, :del_num); END;",
                         {'alarmid': 123, 'del_num': outparam.Parameter(int)})
# 获取输出参数的值
out_param_value = result.fetchone()['del_num']
# 打印输出参数的值print(out_param_value)

结果报错：
AttributeError: 'function' object has no attribute 'Parameter'

所以究竟怎么才能通过SQLAlchmey调用存储过程正常获取出参呢？

回答：

用outparam方法：

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.sql import outparam
from configs import url
# 创建数据库连接
engine = create_engine(url)
# 创建会话
Session = sessionmaker(bind=engine)
session = Session()
# 调用存储过程
result = session.execute(
    "BEGIN test_procedure(:param1, :out_param); END;",
    {'param1': 123, 'out_param': outparam('out_param', int)}
)
# 获取输出参数的值
out_param_value = result.out_parameters['out_param']
# 打印输出参数的值print(out_param_value)