在 Django 的 ORM 中访问存储过程的最佳方式是什么 [英] What is the best way to access stored procedures in Django's ORM

问题描述

我正在设计一个相当复杂的数据库，并且知道我的一些查询将远远超出 Django ORM 的范围.有没有人成功地将 SP 与 Django 的 ORM 集成?如果是这样，什么 RDBMS 以及您是如何做到的?

I am designing a fairly complex database, and know that some of my queries will be far outside the scope of Django's ORM. Has anyone integrated SP's with Django's ORM successfully? If so, what RDBMS and how did you do it?

推荐答案

我们 (musicpictures.com/eviscape.com) 写了那个 django 片段，但它不是整个故事(实际上那个代码当时只在 Oracle 上测试过).

We (musicpictures.com / eviscape.com) wrote that django snippet but its not the whole story (actually that code was only tested on Oracle at that time).

当您想重用经过验证和测试的 SP 代码时，或者在一次 SP 调用比多次调用数据库更快的情况下，或者在安全性需要对数据库进行适度访问的情况下，或者在查询非常复杂的情况下，存储过程是有意义的/多重步骤.我们针对 Oracle 和 Postgres 数据库使用混合模型/SP 方法.

Stored procedures make sense when you want to reuse tried and tested SP code or where one SP call will be faster than multiple calls to the database - or where security requires moderated access to the database - or where the queries are very complicated / multistep. We're using a hybrid model/SP approach against both Oracle and Postgres databases.

诀窍是让它易于使用并保持django"的风格.我们使用 make_instance 函数，它获取游标的结果并创建从游标填充的模型的实例.这很好，因为游标可能会返回其他字段.然后你可以在你的代码/模板中使用这些实例，就像普通的 django 模型对象一样.

The trick is to make it easy to use and keep it "django" like. We use a make_instance function which takes the result of cursor and creates instances of a model populated from the cursor. This is nice because the cursor might return additional fields. Then you can use those instances in your code / templates much like normal django model objects.

def make_instance(instance, values):
    '''
    Copied from eviscape.com

    generates an instance for dict data coming from an sp

    expects:
        instance - empty instance of the model to generate
        values -   dictionary from a stored procedure with keys that are named like the
                   model's attributes
    use like:
        evis = InstanceGenerator(Evis(), evis_dict_from_SP)

    >>> make_instance(Evis(), {'evi_id': '007', 'evi_subject': 'J. Bond, Architect'})
    <Evis: J. Bond, Architect>

    '''
    attributes = filter(lambda x: not x.startswith('_'), instance.__dict__.keys())

    for a in attributes:
        try:
            # field names from oracle sp are UPPER CASE
            # we want to put PIC_ID in pic_id etc.
            setattr(instance, a, values[a.upper()])
            del values[a.upper()]
        except:
            pass

    #add any values that are not in the model as well
    for v in values.keys():
        setattr(instance, v, values[v])
        #print 'setting %s to %s' % (v, values[v])

    return instance

# 像这样使用它:

pictures = [make_instance(Pictures(), item) for item in picture_dict]

# 下面是一些辅助函数:

# And here are some helper functions:

def call_an_sp(self, var):
    cursor = connection.cursor()
    cursor.callproc("fn_sp_name", (var,))
    return self.fn_generic(cursor)


def fn_generic(self, cursor):
    msg = cursor.fetchone()[0]
    cursor.execute('FETCH ALL IN "%s"' % msg)
    thing = create_dict_from_cursor(cursor)
    cursor.close()
    return thing

def create_dict_from_cursor(cursor):
    rows = cursor.fetchall()
    # DEBUG settings (used to) affect what gets returned. 
    if DEBUG:
        desc = [item[0] for item in cursor.cursor.description]
    else:
        desc = [item[0] for item in cursor.description]
    return [dict(zip(desc, item)) for item in rows]    

干杯，西蒙.

这篇关于在 Django 的 ORM 中访问存储过程的最佳方式是什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！