0

I'm working on a Django project where I need to execute a complex Oracle SQL query that involves multiple database links. I have already configured the database credentials for both databases in my Django settings, but I'm struggling with how to correctly execute a query that fetches data from different databases through these links.

Here's a sample of the type of query I'm trying to execute:

SELECT 
    CASE 
        WHEN ACDMH_LOC_CODE LIKE '02%' THEN 'KHOBAR'
        WHEN ACDMH_LOC_CODE LIKE '03%' THEN 'RIYADH'
        ELSE 'JEDDAH'
    END AS Region,
    ACDMH_INTF_YN,
    ACDMH_TRANSACTION_TYPE AS TRANSACTION_TYPE,
    ACDMH_SOURCE_DOC_NO AS SOURCE_DOC_NO,
    TO_CHAR(ACDMH_TRANSACTION_DATE, 'dd-MM-yyyy') AS TRANSACTION_DATE,
    ACDMH_CUSTOMER_ID AS CUSTOMER_No,
    CUSTOMER_NAME,
    TO_CHAR(ACDMH_CRE_DATE, 'dd-mm-yyyy') AS Pushed_date_WinIT,
    TO_CHAR(ACDMH_CRE_DATE, 'hh:mi:ss AM') AS Pushed_time_WinIT,
    ACDMH_INTF_ORA_REF AS ERP_REF,
    ACDMH_LOC_CODE AS LOC_CODE,
    ACDMD_ORIGINAL_INVOICE_NO AS ORIGINAL_INVOICE_NO,
    ACDMD_OLD_VALUE AS fake_PRICE,
    ACDMD_NEW_VALUE AS selling_price,
    ACDMD_TOTAL AS tran_value,
    ACDMD_TAX_AMOUNT AS TAX_AMOUNT
FROM 
    AX_CREDIT_DEBIT_MEMO_HEADERS@erpbridge
    INNER JOIN AX_CREDIT_DEBIT_MEMO_LINES@erpbridge ON ACDMH_HEADER_ID = ACDMD_HEADER_ID
    LEFT JOIN AXKSA_ORACLE_CUSTOMER ON CUSTOMER_NUMBER = ACDMH_CUSTOMER_ID
WHERE 
    [some conditions];

This query involves database links (@erpbridge) to other sources. I'm unsure how to execute such a query in Django, especially considering the database links.

I have the following questions:

How can I execute this Oracle SQL query in Django, given the use of multiple database links? Are there any specific configurations or considerations in Django for handling Oracle database links? Is using Django's raw SQL query execution the right approach for this, or is there a more efficient method? Any guidance or examples would be greatly appreciated!

1
  • Can you create a view in the DB and query that? Commented Jan 21, 2024 at 7:12

1 Answer 1

0

I have no experience with oracle database, but I have a lot of experience using SQL with MYSQL and SQL server. In django I CANNOT use raw SQL, it is absolutely not recommended for security reasons. with djando I exploit the orm with all the possible relationships using foreignkeys, in your case you can't, when a situation of this kind happens to me I use pandas. I import all the data I need with django querysets, load the querysets into Pandas dataframes. I do all the joins, where conditions etc. with the pandas dataframes and if I have big data I first use numpy to prepare the pandas dataframes. I assure you that you will get maximum efficiency and flexibility. In data manipulation, pandas write SQL, but they are bidirectional tables and work with data sets without iterating. They are the maximum efficiency in Python and therefore in Django. My advice is to solve your problem by studying pandas. I hope to be proved helpful.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.