如何使用Django从Oracle 11g中的不同架构访问表? [英] How to access tables from a different schema in oracle 11g using django?

问题描述

我在oracle 11g中有一个名为mi_abc的用户.用户在数据库中没有任何表，但可以访问另一个模式sch_abc中的所有表.当我从mi_abc的sch_abc模式运行来自sqldeveloper的普通选择查询时，它工作得很好，但是当我使用django时，总是出现错误:-

I have a user named mi_abc in oracle 11g. The user do not have any table in the database but has access to all the tables in another schema sch_abc. When I run a normal select query from sqldeveloper on the sch_abc schema from mi_abc, it works perfectly fine, but when I use django, I am always getting the error:-

django.db.utils.DatabaseError:ORA-00942:表或视图不存在

我试图设置db_table = sch_abc.tableName，还设置了db_table = tableName，但是两者都给了我同样的错误.有什么线索可以解决这个问题吗?

I tried to set the db_table = sch_abc.tableName and also set db_table = tableName but both gives me the same error. Any clue how to resolve this?

跟踪:-

Traceback (most recent call last):
  File "C:\xxx\xxx\xxx\xxx\xxx\xxx\xxxx\lib\site-packages\django\core\handlers\exception.py", line 41, in inner
    response = get_response(request)
  File "C:\xxx\xxx\xxxx\xxxx\xxxx\Python\Python37\lib\site-packages\django\utils\deprecation.py", line 142, in __call__
    response = self.process_response(request, response)
  File "C:\xxxx\xxxx\xxx\xxx\xxx\Python\Python37\lib\site-packages\django\contrib\sessions\middleware.py", line 58, in process_response
    request.session.save()
  File "C:\xxx\xxxx\xxxx\xxxx\xxxxx\Python\Python37\lib\site-packages\django\contrib\sessions\backends\db.py", line 81, in save
    return self.create()
  File "C:\xxxx\xxxxx\xxxx\xxxx\xxxxx\Python\Python37\lib\site-packages\django\contrib\sessions\backends\db.py", line 50, in create
    self._session_key = self._get_new_session_key()
  File "C:\xxxx\xxxxx\xxxxx\xxxxx\xxxx\Python\Python37\lib\site-packages\django\contrib\sessions\backends\base.py", line 164, in _get_new_session_key
    if not self.exists(session_key):
  File "C:\xxx\xxx\xxx\xxx\xxx\Python\Python37\lib\site-packages\django\contrib\sessions\backends\db.py", line 46, in exists
    return self.model.objects.filter(session_key=session_key).exists()
  File "C:\xxx\xxx\xxx\xxx\xxx\Python\Python37\lib\site-packages\django\db\models\query.py", line 673, in exists
    return self.query.has_results(using=self.db)
  File "C:\xxx\xxx\xxx\xxx\xxx\Python\Python37\lib\site-packages\django\db\models\sql\query.py", line 517, in has_results
    return compiler.has_results()
  File "C:\xxx\xxx\xxx\xxx\xxx\Python\Python37\lib\site-packages\django\db\models\sql\compiler.py", line 858, in has_results
    return bool(self.execute_sql(SINGLE))
  File "C:\xxx\xxx\xxx\xxx\xxx\Python\Python37\lib\site-packages\django\db\models\sql\compiler.py", line 899, in execute_sql
    raise original_exception
  File "C:\xxx\xxx\xxx\xxx\xxx\Python\Python37\lib\site-packages\django\db\models\sql\compiler.py", line 889, in execute_sql
    cursor.execute(sql, params)
  File "C:\xxx\xxx\xxx\xxx\xxx\Python\Python37\lib\site-packages\django\db\backends\utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "C:\xxx\xxx\xxx\xxx\xxx\Python\Python37\lib\site-packages\django\db\backends\utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "C:\xxx\xxx\xxx\xxx\xxx\Python\Python37\lib\site-packages\django\db\utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "C:\xxx\xxx\xxx\xxx\xxx\Python\Python37\lib\site-packages\django\utils\six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "C:\xxx\xxx\xxx\xxx\xxx\Python\Python37\lib\site-packages\django\db\backends\utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "C:\xxx\xxx\xxx\xxx\xxx\Python\Python37\lib\site-packages\django\db\backends\oracle\base.py", line 497, in execute
    return self.cursor.execute(query, self._param_generator(params))
django.db.utils.DatabaseError: ORA-00942: table or view does not exist

推荐答案

好，我解决了这个问题，让我告诉你在django中没有直接的方法.我的应用程序存在的问题是，我正在使用django的身份验证功能以及会话处理功能.所有这些表都是由django在初始迁移时直接创建的.因此，在models.py文件中不存在它们，您只需添加模式名称并要求您的应用程序连接到该模式的表即可.

Well, I resolved the issue and let me tell you there is no straight way to do it in django. The problem with my application was, I was using the authetication features of django and also session handling. All these tables are created by django directly on the initial migration. So, there is no existence of them in the models.py file that you can simply append the schema name and ask your application to connect to the table of that schema.

我最后要做的是，我为实际上包含这些表的其他模式的所有表创建了私有同义词.如果执行此操作，则无需在Django代码中进行任何更改.您的应用程序将仅能正常工作，因为oracle会进行实际连接到正确表的工作.您将仅在应用程序中调用该表，就好像它是您自己的一样.这样，当django查找django_session，auth_user等表时，它像往常一样简单地查询它，并且oracle将其重定向到另一个模式中存在的实际表.

What I ended up doing is, I created private synonyms to all the tables of the other schema which actually contained those tables. If you do this, you don't have to change anything in your django code. Your application will simply work because oracle will do the dirty work of actually connecting to the proper table. You will merely call the table in your application as if its your own. In this way when django looks for tables like django_session, auth_user etc, it simply queries it like it always does and oracle redirects it to the actual tables present in another schema.

希望这对将来会遇到此问题的人们有所帮助.

Hope this helps people who face this issue in the future.

这篇关于如何使用Django从Oracle 11g中的不同架构访问表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！