在 Django 的 ORM 中访问存储过程的最佳方法是什么

我们(musicpictures.com/eviscape.com)编写了django代码片段，但并不是全部(实际上，该代码当时只在Oracle上测试过).

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

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

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]

#下面是一些助手函数:

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]    

干杯，西蒙.