0

i have a problemi have a sql query written that has to be passed to the python code and it has to come into an excel sheet ,but im new to this connection part of python ,i dont no can anyone please help me. this is my login credentials 

1)Db Credentials:
Hostname: INBLR-LVTEMP.IN.valtech.com
Port: 1521
SID: xstore
Username: dtv
Password: dtv

select * from itm_item;
select * from trl_sale_lineitm;
select * from xom_order;
SELECT
    LRL.AREA AS AREA,
    LRL.COUNTRY AS COUNTRY,
    LRL.CURRENCY_ID AS CURRENCY,
    LRL.RTL_LOC_ID AS STORE_CODE,
    TO_CHAR(TSL.BUSINESS_DATE, 'DD/MM/YYYY') AS BUSINESS_DATE,
    TSL.WKSTN_ID AS WKSTN_ID,
    TSL.TRANS_SEQ AS TRANS_SEQ,
    TSL.RTRANS_LINEITM_SEQ AS LINEITEM_SEQ,
    TSL.ITEM_ID AS SKU,
    ITM.DESCRIPTION AS ITM_DESC,
    TSL.QUANTITY AS QTTY,
    TSL.RETURN_FLAG AS RETURN_FLAG,
    TSL.UNIT_PRICE AS UNIT_PRICE,
    TSL.NET_AMT AS NET_AMT,
    TSL.EXTENDED_AMT AS EXTENDED_AMT,
    PT.CUST_ID AS CUST_ID,
    EMP.LOGIN_ID AS COMISSIONED_CA,
    OM.ORDER_ID AS ORDER_ID,
    CIAA.CUST_ACCT_ID AS RESERVATION_ID,
    CASE WHEN TSTL.TAX_EXEMPTION_ID is not NULL THEN 'yes' ELSE 'no' END AS IS_TAX_EXEMPTION,
    CI_CIX.EXT_INVOICE_ID AS GLOBAL_BLUE_TRN_NBR,
    TSL.CREATE_DATE AS CREATE_DATE,
    NVL(TSL.UPDATE_DATE, TSL.CREATE_DATE) AS UPDATE_DATE,
    'Retail sale' AS TRANS_TYPE,
    TSTL.TAX_AMT AS TAX_AMT,
    TSL.GROSS_AMT AS GROSS_AMT,
    TTN.NOTE,
    CONCAT(LPAD(TSL.WKSTN_ID,3,0), LPAD(TSL.TRANS_SEQ,6,0)) AS "SAP REF KEY 2"
FROM
    TRL_SALE_LINEITM TSL
    LEFT JOIN TRN_TRANS TT ON 1 = 1
    AND TT.TRANS_SEQ = TSL.TRANS_SEQ
    AND TT.ORGANIZATION_ID = TSL.ORGANIZATION_ID
    AND TT.BUSINESS_DATE = TSL.BUSINESS_DATE
    AND TT.WKSTN_ID = TSL.WKSTN_ID
    AND TT.RTL_LOC_ID = TSL.RTL_LOC_ID
    LEFT JOIN TRL_RTRANS_LINEITM TRL ON 1 = 1
    AND TRL.TRANS_SEQ = TSL.TRANS_SEQ
    AND TRL.ORGANIZATION_ID = TSL.ORGANIZATION_ID
    AND TRL.BUSINESS_DATE = TSL.BUSINESS_DATE
    AND TRL.WKSTN_ID = TSL.WKSTN_ID
    AND TRL.RTL_LOC_ID = TSL.RTL_LOC_ID
    AND TRL.RTRANS_LINEITM_SEQ = TSL.RTRANS_LINEITM_SEQ
    LEFT JOIN ITM_NON_PHYS_ITEM INPI ON 1 = 1
    AND INPI.ITEM_ID = TSL.ITEM_ID
    LEFT JOIN XOM_ORDER_MOD XOM ON 1 = 1
    AND XOM.TRANS_SEQ = TSL.TRANS_SEQ
    AND XOM.ORGANIZATION_ID = TSL.ORGANIZATION_ID
    AND XOM.BUSINESS_DATE = TSL.BUSINESS_DATE
    AND XOM.WKSTN_ID = TSL.WKSTN_ID
    AND XOM.RTL_LOC_ID = TSL.RTL_LOC_ID
    AND XOM.RTRANS_LINEITM_SEQ = TSL.RTRANS_LINEITM_SEQ
    LEFT JOIN TRL_RTRANS RT ON 1 = 1
    AND RT.RTL_LOC_ID = TSL.RTL_LOC_ID
    AND RT.BUSINESS_DATE = TSL.BUSINESS_DATE
    AND RT.WKSTN_ID = TSL.WKSTN_ID
    AND RT.TRANS_SEQ = TSL.TRANS_SEQ
    LEFT JOIN CRM_PARTY PT ON 1 = 1
    AND PT.PARTY_ID = RT.CUST_PARTY_ID
    LEFT JOIN TRL_COMMISSION_MOD CM ON 1 = 1
    AND CM.RTL_LOC_ID = TSL.RTL_LOC_ID
    AND CM.BUSINESS_DATE = TSL.BUSINESS_DATE
    AND CM.WKSTN_ID = TSL.WKSTN_ID
    AND CM.TRANS_SEQ = TSL.TRANS_SEQ
    AND CM.RTRANS_LINEITM_SEQ = TSL.RTRANS_LINEITM_SEQ
    LEFT JOIN HRS_EMPLOYEE EMP ON 1 = 1
    AND EMP.PARTY_ID = CM.EMPLOYEE_PARTY_ID
    LEFT JOIN ITM_ITEM ITM ON 1 = 1
    AND ITM.ITEM_ID = TSL.ITEM_ID
    LEFT JOIN TRN_TRANS_NOTES TTN ON 1 = 1
    AND TTN.TRANS_SEQ = TSL.TRANS_SEQ
    AND TTN.ORGANIZATION_ID = TSL.ORGANIZATION_ID
    AND TTN.BUSINESS_DATE = TSL.BUSINESS_DATE
    AND TTN.WKSTN_ID = TSL.WKSTN_ID
    AND TTN.RTL_LOC_ID = TSL.RTL_LOC_ID
    AND TTN.NOTE_SEQ = 1
    LEFT JOIN (
        SELECT
            RTL_LOC_ID,
            BUSINESS_DATE,
            WKSTN_ID,
            TRANS_SEQ,
            RTRANS_LINEITM_SEQ,
            SUM(TAX_AMT) TAX_AMT,
            TAX_EXEMPTION_ID
        FROM
            TRL_SALE_TAX_LINEITM
        GROUP BY
            (
                RTL_LOC_ID,
                BUSINESS_DATE,
                WKSTN_ID,
                TRANS_SEQ,
                RTRANS_LINEITM_SEQ,
                TAX_EXEMPTION_ID
            )
    ) TSTL ON 1 = 1
    AND TSTL.TRANS_SEQ = TSL.TRANS_SEQ
    AND TSTL.BUSINESS_DATE = TSL.BUSINESS_DATE
    AND TSTL.RTL_LOC_ID = TSL.RTL_LOC_ID
    AND TSTL.WKSTN_ID = TSL.WKSTN_ID
    AND TSTL.RTRANS_LINEITM_SEQ = TSL.RTRANS_LINEITM_SEQ
    LEFT JOIN (
        SELECT
            *
        FROM
            CIVC_INVOICE_XREF CIX
            LEFT JOIN CIVC_INVOICE CI ON CIX.RTL_LOC_ID = CI.RTL_LOC_ID
            AND CIX.SEQUENCE_NBR = CI.SEQUENCE_NBR
            AND CIX.WKSTN_ID = CI.WKSTN_ID
            AND CIX.TRANS_BUSINESS_DATE = CI.BUSINESS_DATE
        WHERE
            CI.VOID_FLAG != 1
            AND CI.INVOICE_TYPE = 'TAXFREE'
    ) CI_CIX ON 1 = 1
    AND CI_CIX.TRANS_RTL_LOC_ID = TSL.RTL_LOC_ID
    AND CI_CIX.TRANS_BUSINESS_DATE = TSL.BUSINESS_DATE
    AND CI_CIX.TRANS_TRANS_SEQ = TSL.TRANS_SEQ
    AND CI_CIX.TRANS_WKSTN_ID = TSL.WKSTN_ID
    LEFT JOIN XOM_ORDER_MOD OM ON 1 = 1
    AND TSL.TRANS_SEQ = OM.TRANS_SEQ
    AND TSL.ORGANIZATION_ID = OM.ORGANIZATION_ID
    AND TSL.BUSINESS_DATE = OM.BUSINESS_DATE
    AND TSL.RTL_LOC_ID = OM.RTL_LOC_ID
    AND TSL.WKSTN_ID = OM.WKSTN_ID
    AND TSL.RTRANS_LINEITM_SEQ = OM.RTRANS_LINEITM_SEQ
    LEFT JOIN LOC_RTL_LOC LRL ON 1 = 1
    AND TSL.RTL_LOC_ID = LRL.RTL_LOC_ID
    AND LRL.ORGANIZATION_ID = TSL.ORGANIZATION_ID
    OUTER APPLY (
        SELECT
            CUST_ACCT_ID
        FROM
           CAT_CUST_ITEM_ACCT_ACTIVITY CIAA
        WHERE
            TSL.TRANS_SEQ = CIAA.TRANS_SEQ
            AND TSL.ORGANIZATION_ID = CIAA.ORGANIZATION_ID
            AND TSL.BUSINESS_DATE = CIAA.BUSINESS_DATE
            AND TSL.RTL_LOC_ID = CIAA.RTL_LOC_ID
            AND TSL.WKSTN_ID = CIAA.WKSTN_ID
            AND TSL.RTRANS_LINEITM_SEQ = CIAA.RTRANS_LINEITM_SEQ
            AND ROWNUM = 1
    ) CIAA
WHERE 1=1
    AND TRL.VOID_FLAG = 0
    AND TT.POST_VOID_FLAG = 0
    AND TT.TRANS_STATCODE = 'COMPLETE'
    AND TSL.BUSINESS_DATE BETWEEN TO_DATE('01-DEC-2018', 'DD-MON-YYYY')
    AND TO_DATE(TO_DATE('31-DEC-2018', 'DD-MON-YYYY'))
   AND TSL.RTL_LOC_ID IN (SELECT RTL_LOC_ID FROM LOC_RTL_LOC where country='US')
    --AND TSL.WKSTN_ID >= ?
    --AND TSL.WKSTN_ID <= ?
    AND TSL.ITEM_ID not in (select ITEM_ID from ITM_NON_PHYS_ITEM where NON_PHYS_ITEM_TYPCODE in ('DEPOSIT_PAYMENT', 'VOUCHER', 'GIFT_REFUND','ORDER_ACCOUNT_CREDIT'))
    AND (TSL.SALE_LINEITM_TYPCODE IN ('SALE', 'SEND_SALE') OR TSL.SALE_LINEITM_TYPCODE IN ('ORDER') AND TSL.RETURN_FLAG = 1)
    order by create_date;

import pymssql
import pandas as pd

server = '1521'
username = 'dtv'
password = 'dtv'
database = 'yourdatabase'
table_name = 'xom_order'
conn = pymssql.connect(host=1521,user=dtv,password=dtv,database=database)
dat = pd.read_sql("select * from itm_item,conn)

i have tried different ways to do this still im not able please can anyone help me out,so i have tried this method but im not able to execute correctly .

"I expect the output to be as follows when u run that sql query in python code the results what u get has to be placed in an excel sheet automatically"

python-3.x

Improve this question
6
  • You might be interested in this stackoverflow.com/help/how-to-ask Commented Apr 23, 2019 at 4:55
  • @ap please help us Commented Apr 23, 2019 at 5:00
  • use cb.bind('<<ComboboxSelected>>', on_select) to run your own function on_select when you change selection - and in this function you may have to has to change values in other Combobox. Simple example: combobox-get-selection Commented Apr 23, 2019 at 5:21
  • Your code is confusing. You have several IntVar that you are trying to set Strings to. All your *Var except for v1 and v2 are actually unused. It is unclear what you are trying to do or show. Commented Apr 23, 2019 at 5:26
  • in new code you need only one main window Tk() and only one mainloop(). In my code I used name root because it is popular name in all examples and tutorial but you use window - so use only window in code and remove root. Commented Apr 23, 2019 at 14:03

1 Answer 1

Reset to default
1

It uses cb1.bind('<<ComboboxSelected>>', on_select) to execute function on_select when you change country in first Combobox. And this function changes values in second Combobox.

from tkinter import *
from tkinter.ttk import Combobox

data = {
    "EMEA": [1105,1106],
    "ASIA": [3565,2445, 126464, 1145454],
    "AMERICA": [56464 ,5679, 55346],
}    


def on_select(event):
    selecte_value = event.widget.get()
    if selecte_value in data:
        cb2['values'] = data[selecte_value]
    else:
        cb2['values'] = all_values

country = list(data.keys())        
all_values = sorted(sum(data.values(), []))

window = Tk() # only one main window `Tk()`, to create second window use `Toplevel()`

cb1 = Combobox(window, values=country)
cb1.bind('<<ComboboxSelected>>', on_select)
#cb1.bind('<Return>', on_select) # pressed ENTER
cb1.pack()

cb2 = Combobox(window, values=all_values)
cb2.pack()

window.mainloop() # only one main loop, even if you have many windows

EDIT: code with Button which gets values from Combobox. It also keep it in global variables so you have these values even after closing window.

from tkinter import *
from tkinter.ttk import Combobox

# -- functions ---

def on_select(event):
    selecte_value = event.widget.get()
    if selecte_value in data:
        cb2['values'] = data[selecte_value]

def on_quit():
    # keep values in global variables 
    # to have these values even after closing window (after mainloop)
    global selected_country
    global selected_value

    selected_country = cb1.get()
    selected_value = cb2.get()

    print('--- before closing window ---')
    print('cb1:', selected_country)
    print('cb2:', selected_value)
    #print('cb1:', cb1.get())
    #print('cb2:', cb2.get())

    window.destroy() # destroy works better then quit, in some system quit doesn't close main window

# -- data --

data = {
    "BE": [1201,1202,1203],
    "CH": [2501,2502,2503,2504,2506,2507,2508,2505],
    "DE": [1501,1502,1503,1504,1505,1506,1507,1508,1509],
    "ES": [2801,2803,2804,2805,2806,2808,2810],
    "AU": [6114,6116,6122,6124,6126,6128,6130,6132,6108,6112,6140],
    "CN": [5102,5127,5128,5129,5131,5132,5134,5135,5138,5139,5142,5143,5144,5103,5106,5107,5108,5109,5110,5111,5112,5113,5114,5115,5116,5117,5118,5120,5121,5123,5125,5126,5147,5148,5149,5150,5145,5146,5155],
    "CA": [3002,3143,3004,3005,3008,3010],
    "GB": [1101,1102,1103,1104,1105,1106,1107,1108,1109,1110,1111,1112,1114],
    "HK": [5431,5447,5408,5411,5428,5417,5418,5420],
    "IE": [1150],
    "IT": [2707,2712],
    "JP": [4146,4150,4154,4145,4132,4135,4136,4138,4140,4141,4142,4144,4160,4166,4169,4170,4171,4172,4174,4176,4180,4181,4183,4186,4106,4111,4112,4113,4116,4120,4130],
    "LU": [1206],
    "MO": [5523,5533,5534,5536,5538,5539],
    "NL": [1221,1222,1223],
    "RO": [2606],
    "SG": [6407,6408,6409,6410,6411],
}

country = list(data.keys())
all_values = sorted(sum(data.values(), []))

# -- main ---

window = Tk()
window.geometry("300x250+10+10")

frame = Frame(window)
frame.pack(side="left")

cb1 = Combobox(frame, values=country)
cb1.bind('<<ComboboxSelected>>', on_select)
#cb1.bind('<Return>', on_select) # if someone will write text in Combobox and press ENTER

cb1.pack()

cb2 = Combobox(frame, values=all_values)
cb2.pack()

button = Button(window, text="SUBMIT", fg="red", command=on_quit)
button.pack(side="left")

window.mainloop()

# access selected values after closing window
print('--- after closing window ---')
print('cb1:', selected_country)
print('cb2:', selected_value)
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

i didnt get that return y u have called instead can u make it a submit button in the same piece of code

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.