在第二个数据库上的Django调用存储过程 [英] Django Call Stored Procedure on Second Database

问题描述

我正在尝试在多数据库Django安装上调用存储过程，但没有运气获得结果.存储过程(位于辅助数据库上)在Django中始终返回一个空数组，但是在mysql客户端中执行时确实会出现预期的结果.

I'm trying to call a stored procedure on a multi-db Django installation, but am not having any luck getting results. The stored procedure (which is on the secondary database) always returns an empty array in Django, but the expected result does appear when executed in a mysql client.

我的 view.py 文件 从SomeDBModel导入模型 从django.db导入连接

My view.py file from SomeDBModel import models from django.db import connection

def index(request, someid):
    #Some related django-style query that works here 

    loc = getLocationPath(someid, 1)
    print(loc)

def getLocationPath(id, someval):
    cursor = connection.cursor()
    cursor.callproc("SomeDB.spGetLocationPath", [id, someval])
    results = cursor.fetchall()
    cursor.close()
    return results

我也尝试过:

from SomeDBModel import models
from django.db import connections

def index(request, someid):
    #Some related Django-style query that works here

    loc = getLocationPath(someid, 1)
    print(loc)

def getLocationPath(id, someval):
    cursor = connections["SomeDB"].cursor()
    cursor.callproc("spGetLocationPath", [id, someval])
    results = cursor.fetchall()
    cursor.close()
    return results

每次打印出结果，我都会得到:

Each time I print out the results, I get:

[]

应检索的数据示例:

{
    Path: '/some/path/', 
    LocalPath: 'S:\Some\local\Path', 
    Folder: 'SomeFolderName', 
    Code: 'SomeCode'
}

我还尝试过的一件事是打印 cursor.callproc 的结果.我得到:

One thing I also tried was to print the result of cursor.callproc. I get:

(id, someval)

此外，打印 cursor._exected 的结果将得出:

Also, printing the result of cursor._executed gives:

b'SELECT @_SomeDB.spGetLocationPath_arg1, @_SomeDB.spGetLocationPath_arg2'

似乎完全没有要运行的存储过程的引用.我什至尝试过将此作为最后的手段:

Which seems to not have any reference to the stored procedure I want to run at all. I have even tried this as a last resort:

cursor.execute("CALL spGetLocationPath("+str(id)+","+str(someval)+")")

但是我遇到关于需要 multi = True 的错误，但是将其放入execute()函数似乎并不像某些网站所建议的那样起作用，而且我不知道其他地方放在Django中.

but I get an error about needing multi=True, but putting it in the execute() function doesn't seem to work like some sites have suggested, and I don't know where else to put it in Django.

所以...我错过了什么主意?如何使存储过程正常工作?

So...any ideas what I missed? How can I get stored procedures to work?

推荐答案

我采取了以下步骤:

1. 将存储过程转储结果制作到临时表中，以便将结果集展平为单个结果集.这摆脱了multi=True
的需要
2. 此外，我确保IP地址的用户可以访问数据库本身中的调用存储过程.

3. 最后，我继续研究 callproc 函数.最终，另一个站点上的某个人提出了以下有效的代码:

1. Made my stored procedure dump results into a temporary table so as to flatten the result set to a single result set. This got rid of the need for multi=True
2. In addition, I made sure the user at my IP address had access to call stored procedures in the database itself.

3. Finally, I continued to research the callproc function. Eventually someone on another site suggested the following code, which worked:

cur = connections["SomeDB"].cursor()
cur.callproc("spGetLocationPath", [id, someval])
res = next(cur.stored_results()).fetchall()
cur.close()

这篇关于在第二个数据库上的Django调用存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！