我们是否可以跨多个oracle用户查询多个表，并将Django的Admin UI中的行制表

Basically, I have issues working with a legacy database where the tables I am using have no proper referential integrity (like no foreign keys; just tables but 'I' know they are related by some columns). So, Django's framework would not be beneficial while querying n tables across m different oracle users.

基本上，我在使用遗留数据库时遇到了一些问题，我使用的表没有适当的引用完整性(比如没有外键;只是表格，但“我”知道它们与某些列有关)。因此，Django的框架对于在m不同的oracle用户中查询n个表是没有好处的。

Something like this: select t1.col1, t2.col3, t3.col2 from user1.table1 t1, user2.table2 t2, user3.table3 t3 where t1.col1 = t2.col2 AND t2.col2 = t3.col3;

像这样:选择t1。col1,t2。col3,t3。col2 user1。表t1,user2。表二t2,user3。table3 t3 t1的地方。col1 = t2。col2和t2。col2 = t3.col3;

And now in Django's Admin UI, I wanted to display this:

现在在Django的管理UI中，我想显示以下内容:

---------------------------
| col1  |  col3  |  col2  |
---------------------------
| abcd  |  defg  |  hijk  |
---------------------------
| 1234  |  5678  |  9009  |
---------------------------

I have started on Django for its fast development only very recently. Hence, any support or docs are much appreciated.

我最近才开始关注Django的快速发展。因此，非常感谢您的支持和文档。

1 个解决方案

#1

To take advantage of Django Admin, you need to make a model first, no matter from where it fetches data. Now, since we are mapping the model to DB, you could either make the model based on a DB view, or any one of the three tables(user1.table1, user2.table2 and user3.table3):

要利用Django Admin，您需要首先创建一个模型，不管它从哪里获取数据。现在，由于我们正在将模型映射到DB，您可以基于DB视图或者三个表中的任何一个(user1)创建模型。表1,user2。表二和user3.table3):

Base on DB view:

基于数据库视图:

First, create a DB view to take col1, col2 and col3 from tables.
Also, choose a primary key for the model: it could be any (including extra) column in the DB view, as long as the model field w/ primary_key=True matches the column, here I simply choose col1.

首先，创建一个DB视图，从表中获取col1、col2和col3。另外，为模型选择一个主键:它可以是DB视图中的任何(包括额外的)列，只要模型字段w/ primary_key=True与列匹配，这里我只选择col1。

# use Sqlite to create view, Oracle could be similar
CREATE VIEW view_for_legacy_tables AS SELECT t1.col1, t2.col3, t3.col2 from user1.table1 t1, user2.table2 t2, user3.table3 t3 where t1.col1 = t2.col2 AND t2.col2 = t3.col3;

# in model
class Foo(models.Model):
    col1 = models.TextField(primary_key=True)
    col2 = models.TextField()
    col3 = models.TextField()

    class Meta:
        db_table = 'view_for_legacy_tables'

From now on, syncdb or South migrate might complain the exists, here simply ignore them.
You could bypass it by faking migration in South or move the model from models.py to other files or functions that does not loaded by models.py.

从现在开始，syncdb或South migration可能会抱怨存在的问题，这里只是忽略它们。你可以通过假装在南方迁移或者从模型中移动模型来绕过它。py到其他未被model .py加载的文件或函数。

After define corresponding ModelAdmin for the model Foo, the Foo could be displayed in changelist in Django Admin.

在为模型Foo定义相应的ModelAdmin之后，Foo可以在Django Admin中的changelist中显示。

If you want to use addview or changeview to do some modification upon the tables, you could override save method of the Foo model or customize ModelForm, or customize add_view/change_view of the ModelAdmin. The code varies on your actual usage, thus I don't provide one here.

如果您想要使用addview或changeview在表上做一些修改，您可以重写Foo模型的保存方法或自定义ModelForm，或者自定义ModelAdmin的add_view/change_view。代码根据您的实际使用而有所不同，因此我在这里不提供。

Base on table user1.table1

基于表user1.table1

This works similar as the method based on DB view. To be able to use addview and changeview, you need to customize ModelForm also.

这类似于基于DB视图的方法。要能够使用addview和changeview，还需要定制ModelForm。

Define the model

定义模型

class Foo(models.Model):
    col1 = models.TextField(primary_key=True)

    class Meta:
        db_table = 'user1.table1'

Then in admin.py

然后在admin.py

class FooAdmin(admin.ModelAdmin):
    list_display = ('col1', 'col2', 'col3')

    def queryset(self, request): # you could customize the default manager of model Foo
        qs = super(FooAdmin, self).queryset(request)
        return qs.extra(select={'col2':'user2.table2.col2', 'col3':'user3.table3.col3'},
                        tables=['user2.table2', 'user3.table3'],
                        where=['col1 = user2.table2.col2', 'user2.table2.col2'='user3.table3.col3']) # or use `raw()`, depends on your columns

    def col2(self, obj):
        return obj.col2

    def col3(self, obj):
        return obj.col3

update

更新

The usage is very uncommon, thus I've seen few documents about it. Most relative things could be options.py inside django/contrib/admin and the implementation of Model inside django/db/models/base.py.

这种用法非常少见，因此我很少看到有关它的文档。大多数相关的事情都是可以选择的。py在django/悔过书/admin中，在django/db/models/base.py中实现模型。

If you want to achieve SELECT * from t1, t2, t3 where ... like '%?%'", [var1]), the easiest way probably is to write your own view by using Admin UI, just as https://github.com/dmpayton/django-mongoadmin

如果你想在t1、t2、t3中实现SELECT *，其中……像“% ?最简单的方法可能是使用Admin UI编写自己的视图，就像https://github.com/dmpayton/django-mongoadmin一样。

If var1 of SELECT * from t1, t2, t3 where ... like '%?%'", [var1]) is determined, you could make several models for each value of var1; if its not, you have to do some dynamic way about model and admin, just ref Django dynamic model fields , which could be even harder IMO.

如果从t1、t2、t3中选择*的var1为…像“% ?确定了%'"，[var1])，可以为var1的每个值创建多个模型;如果不是，你必须对模型和管理进行一些动态处理，只是引用Django动态模型字段，这在我看来可能更加困难。

#1