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

Question

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 , textfrom sqlalchemy.orm import sessionmakerfrom 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_enginefrom sqlalchemy.orm import sessionmakerfrom sqlalchemy import outparamfrom 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调用存储过程正常获取出参呢？

Answer 1

用outparam方法：

from sqlalchemy import create_enginefrom sqlalchemy.orm import sessionmakerfrom sqlalchemy.sql import outparamfrom 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)