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

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?

我正在设计一个相当复杂的数据库，并且知道我的一些查询将远远超出Django的ORM范围。有人成功地将SP和Django的ORM集成在一起吗?如果是，什么RDBMS ?您是如何实现的?

6 个解决方案

#1

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).

我们(musicpictures.com / eviscape.com)撰写了django代码片段，但并不是全部(实际上，那时甲骨文只对代码进行了测试)。

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.

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

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.

诀窍在于使它易于使用，并保持它像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

# Use it like this:

#像这样使用它:

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]

cheers, Simon.

干杯,西蒙。

#2

You have to use the connection utility in Django:

您必须在Django中使用连接实用程序:

from django.db import connection

cursor = connection.cursor()
cursor.execute("SQL STATEMENT CAN BE ANYTHING")

then you can fetch the data:

然后您可以获取数据:

cursor.fetchone()

or:

或者:

cursor.fetchall()

More info here: http://docs.djangoproject.com/en/dev/topics/db/sql/

更多信息:http://docs.djangoproject.com/en/dev/topics/db/sql/

#3

There is a good example : https://djangosnippets.org/snippets/118/

有一个很好的例子:https://djangosnippets.org/snippets/118/

from django.db import connection


cursor = connection.cursor()
ret = cursor.callproc("MY_UTIL.LOG_MESSAGE", (control_in, message_in))# calls PROCEDURE named LOG_MESSAGE which resides in MY_UTIL Package
cursor.close()

#4

If you want to look at an actual running project that uses SP, check out minibooks. A good deal of custom SQL and uses Postgres pl/pgsql for SP. I think they're going to remove the SP eventually though (justification in trac ticket 92).

如果您想查看使用SP的实际运行项目，请查看迷你书。大量的定制SQL和使用Postgres pl/pgsql为SP。我认为他们最终将会移除SP。

#5

Don't.

不喜欢。

Seriously.

认真对待。

Move the stored procedure logic into your model where it belongs.

将存储过程逻辑移动到它所属的模型中。

Putting some code in Django and some code in the database is a maintenance nightmare. I've spent too many of my 30+ years in IT trying to clean up this kind of mess.

在Django中放置一些代码，在数据库中放置一些代码是维护的噩梦。我花了30多年的时间来清理这些垃圾。

#6

I guess the improved raw sql queryset support in Django 1.2 can make this easier as you wouldn't have to roll your own make_instance type code.

我想Django 1.2中改进的原始sql queryset支持可以使这变得更容易，因为您不需要滚动自己的make_instance类型代码。

#1