使用SQLAlchemy的存储过程

Question

我该如何用sqlAlchemy调用SQL Server的存储过程？

Answer 1

在MySQL中，使用SQLAlchemy调用存储过程最简单的方法是通过Engine.raw_connection()的callproc方法。使用callproc时，你需要提供存储过程的名称和调用这个存储过程所需的参数。

def call_procedure(function_name, params):
       connection = cloudsql.Engine.raw_connection()
       try:
           cursor = connection.cursor()
           cursor.callproc(function_name, params)
           results = list(cursor.fetchall())
           cursor.close()
           connection.commit()
           return results
       finally:
           connection.close()

Answer 2

背景: 我在使用flask-sqlalchemy和MySQL，但没有使用ORM映射。通常，我会这样做：

# in the init method
_db = SqlAlchemy(app)

#... somewhere in my code ...
_db.session.execute(query)

直接调用存储过程是没有现成支持的：callproc这个方法并不是通用的，而是特定于mysql连接器的。

对于没有输出参数的存储过程，可以像平常一样执行类似下面的查询：

_db.session.execute(sqlalchemy.text("CALL my_proc(:param)"), param='something')

但如果有输出参数，事情就变得复杂了……

使用输出参数的一种方法是通过engine.raw_connection()来访问底层连接器。例如：

conn = _db.engine.raw_connection()
# do the call. The actual parameter does not matter, could be ['lala'] as well
results = conn.cursor().callproc('my_proc_with_one_out_param', [0])
conn.close()   # commit
print(results) # will print (<out param result>)

这样做很好，因为我们能够访问输出参数，但是这个连接并不是由flask会话管理的。这意味着它不会像其他受管理的查询那样被提交或中止……（如果你的存储过程有副作用，这就会有问题）。

最后，我最终这样做了：

# do the call and store the result in a local mysql variabl
# the name does not matter, as long as it is prefixed by @
_db.session.execute('CALL my_proc_with_one_out_param(@out)')
# do another query to get back the result
result = _db.session.execute('SELECT @out').fetchone()

result会是一个包含一个值的元组：就是输出参数。虽然这不是最理想的做法，但也是最安全的选择：如果在会话中其他查询失败，存储过程的调用也会被中止（回滚）。

Answer 3

引擎和连接都有一个叫做 execute() 的方法，你可以用它来执行任何 SQL 语句，Session 也是如此。比如说：

results = sess.execute('myproc ?, ?', [param1, param2])

如果你需要输出参数，可以使用 outparam() 来创建它们（如果是绑定参数，可以用 bindparam()，并设置 isoutparam=True 选项）

使用SQLAlchemy的存储过程

9 个回答

撰写回答