I am trying to build a python GUI Database Comparator using PySimpleGUI i missing something here?

Question

I am trying to Build a GUI DB Comparator which is divided in two layout window.

Get the DB Detail from user and Test the connection
Get a Excel file which contains the SQL Statement that will be performed on DB and using Datacompy Comparison will be displayed to the user.

Problem I am facing right now is

DB Details should be on top and the Output box should be below them but it's vice versa
While after taking the input ( DB Details ) when i click on DB Test1 button application close's itself without displaying what went wrong ( No Code error, just went off )

I am new to PySimpleGUI, might have made slilly mistakes, Please guide me through the error and statement which i need to re enter.

Here's the code of First layout window :

 import PySimpleGUI as sg
 import re
 import datacompy
 import cx_Oracle
 import pandas as pd


 def read_query(connection, query):
     cursor = connection.cursor()
        try:
           cursor.execute( query )
           header = [ x[0] for x in cursor.description]
           rows = cursor.fetchall()
           return header, rows
       finally:
           if cursor is not None:
           cursor.close()

  def createCon(uname,passw,hname,portnum,sname):

    dsn_str = cx_Oracle.makedsn(host=hname,
                            port=portnum,
                            service_name = sname)
    con = cx_Oracle.connect(user = uname,
                        password = passw,
                        dsn = dsn_str)
    if con == True:
        return con
    else:
        return 0
      


  DB_creds_one = [
     [
        sg.Text("UserName"),
        sg.In(size=(30, 2), enable_events=True, key="-uname_db1-")
     ],
     [
        sg.Text("Password"),
        sg.In(size=(30, 1), enable_events=True, key="-pword_db1-")
     ],
     [
        sg.Text("Hostname"),
        sg.In(size=(30, 1), enable_events=True, key="-hname_db1-")
     ],
     [
        sg.Text("Service Name"),
        sg.In(size=(30, 1), enable_events=True, key="-sname_db1-")
     ],
     [
        sg.Text("Port"),
        sg.In(size=(30, 1), enable_events=True, key="-port_db1-")
     ],
     [   
        sg.Button('Test Con1', key='B1')
     ],

   ]

  DB_creds_two = [
    [
        sg.Text("UserName"),
        sg.In(size=(25, 1), enable_events=True, key="-uname_db2-")
    ],
    [
        sg.Text("Password"),
        sg.In(size=(25, 1), enable_events=True, key="-pword_db2-")
    ],
    [
        sg.Text("Hostname"),
        sg.In(size=(25, 1), enable_events=True, key="-hname_db2-")
    ],
    [
        sg.Text("Service Name"),
        sg.In(size=(25, 1), enable_events=True, key="-sname_db2-")
    ],
    [
        sg.Text("Port"),
        sg.In(size=(25, 1), enable_events=True, key="-port_db2-")
    ],
    [   
        sg.Button('Test Con2',key='B2')
    ],

  ]


layoutprefile = [
    [
        sg.Column(DB_creds_one),
        sg.VSeperator(),
        sg.Column(DB_creds_two),
       [sg.Output(size=(61, 5), key='-output-')],
       [sg.Submit('Proceed'), sg.Cancel('Exit')]
    
    ]
]
 

 window = sg.Window("DB Comparator", layoutprefile)

 while True:    # The Event Loop
    event, values = window.read()
    # print(event, values)  # debug
    if event in (None, 'Exit', 'Cancel'):
         secondwindow = 0
         break
    elif event == 'B1':
         # Test DB Connection 1  return Test_DB1
         uname_d1 = window.FindElement('-uname_db1-')
         pword_d1 = window.FindElement('-pword_db1-')
         hname_d1 = window.FindElement('-hname_db1-')
         sname_d1 = window.FindElement('-sname_db1-')
         port_d1 = window.FindElement('-port_db1-')
         
         if uname_d1 and pword_d1 and hname_d1 and sname_d1 and port_d1 == "":
               print("ENter values")
         else:
               Test_DB1 = createCon(uname_d1,pword_d1,hname_d1,sname_d1,port_d1)
     elif event == 'B2':
        # Test DB Connection 2 return Test_DB2
        uname_d2 = window.FindElement('-uname_db2-')
        pword_d2 = window.FindElement('-pword_db2-')
        hname_d2 = window.FindElement('-hname_db2-')
        sname_d2 = window.FindElement('-sname_db2-')
        port_d2 = window.FindElement('-port_db2-')
        if uname_d2 and pword_d2 and hname_d2 and sname_d2 and port_d2 == "":
             print("ENter values")
        else:
             Test_DB2 = createCon(uname_d2,pword_d2,hname_d2,sname_d2,port_d2)

    if event == 'Proceed':
        if (Test_DB1 and Test_DB2 != 'True'):
            secondwindow = 0
            sg.Popup("Incorrect Database Details Please Verify the Connection Again")
        else:
            window.close()
            secondwindow = 1
            break

if secondwindow != 1:
   exit()

and one more request i am also trying to append all the error's into output window instead of using print, if anyone help me out in using .append function ? — Manish saini, May 07 '21 at 04:37
solution to sending `print` to window you can find in many GUIs in Python. For sure you can find for `tkitner` - it can be even in Python documentation. You have to create class with method `write(text)` which send `text` to your widget, and you have to assign it to `sys.stdout` - and then `print` which normally use `sys.stdout.write(text)` to send it on screen it will use your `write(text)` to send it to your widget. I'm not sure but `PySimpleGUI` may aready have widget for this and it may automatiically get all prints. Or maybe it was console in window. — furas, May 07 '21 at 06:00
maybe it close without error but because you run `break` to exit event loop. Simply use `print()` to see which part of code is executed and what you have in variables - it is called `"print debuging"` — furas, May 07 '21 at 06:06
instead of `0`, `1` in `secondwindow` you could use `False`, `True` - it will be more readable. And in `createCon` you could remove `else: return 0` and it will run automatically `return None` - and `None` is more readble then `0`. Or maybe you should use `return con` without any `if/else`. If you get `False` in `con` then `return con` will send `False` and it will be even more readable then `None` — furas, May 07 '21 at 06:09
it is wrong `uname_d1 and pword_d1 and hname_d1 and sname_d1 and port_d1 == "":` it has to be `uname_d1 == "" and pword_d1 == "" and hname_d1 == "" and sname_d1 == "" and port_d1 == "":`. It this situation you can also write it shorter with `==` instead of `and` - `uname_d1 == pword_d1 == hname_d1 == sname_d1 == port_d1 == "":`. But maybe you should use `or`. The same problem with `Test_DB1 and Test_DB2 != 'True'` - and use `True`, not string `'True'` - `Test_DB1 != True and Test_DB2 != True` or better `Test_DB1 is not True and Test_DB2 is not True` or shorter `not Test_DB1 and not Test_DB2` — furas, May 07 '21 at 06:17
is your code run? it gives me error because layout has `[ ]` in wrong place. After modification it start correctly and it even show `Output` which display text from `print()` — furas, May 07 '21 at 06:32
BTW: maybe this was your problem - you have `Output` so all error messages are send to `Output` and you don't see them . Maybe first try code without `Output` — furas, May 07 '21 at 06:33
with your code I don't see any `Output` in window - and when I set correct `[ ]` then I see `Output` below `Details` - so I don't how you could have `Output` above `Details`. Simply you use `[ ]` in wrong places. Or you run totally different code. — furas, May 07 '21 at 06:35

Jason Yang · Answer 1 · 2021-05-08T14:23:53.903

1

Your code looks not well formatted.

Some issues listed here,

wrong layout

layoutprefile = [
    [
        sg.Column(DB_creds_one),
        sg.VSeperator(),
        sg.Column(DB_creds_two),
       [sg.Output(size=(61, 5), key='-output-')],
       [sg.Submit('Proceed'), sg.Cancel('Exit')]
    
    ]
]

If you need horizontal layout

layout = [
    [sg.Button("Hello1"), sg.VerticalSeparator(), sg.Button("Hello2")],
]

or vertical layout

layout = [
    [sg.Button("Hello1")],
    [sg.HorizontalSeparator()],
    [sg.Button("Hello2")],
]

Wrong method to get value of sg.Input

uname_d1 = window.FindElement('-uname_db1-')    # It can be window['-uname_db1-']

It just gets the element of element sg.input. To get value of sg.Input

uname_d1 = values['-uname_db1-']

If both values of Test_DB1 and Test_DB2 in following logical statement will get wrong result

if (Test_DB1 and Test_DB2 != 'True'):

Maybe it should be

if not (Test_DB1 and Test_DB2):

Wrong order of arguments for function create_con when called.

#      def createCon(uname,   passw,   hname,   portnum, sname):
Test_DB1 = createCon(uname_d1,pword_d1,hname_d1,sname_d1,port_d1)

portnum may be converted into an interger before passing to function createCon

edited May 08 '21 at 14:23

answered May 07 '21 at 07:11

Jason Yang

11,284
2
9
23

Hi Jason, i used .strip() function to get the values and they are getting printed as per whatever text box holds but i am facing another issue, whenever i am passing those uname_d1,pword_d2 .... to createCon() it's not able to make a database connection, i tried getting the exact error string it displays this " Database connection error: %s ", what am i missing here or should is change the function ? I Did passed the exact DB details in that function i was able to connect to the database problem occurs when i am trying to store them in a var and then passing the var to the function – Manish saini May 07 '21 at 12:08
1

Maybe you give wrong order of arguments, `def createCon(uname,passw,hname,portnum,sname)` vs `createCon(uname_d2,pword_d2,hname_d2,sname_d2,port_d2)`. `portnum` vs `sname` – Jason Yang May 07 '21 at 12:36
1

You should convert `portnum` into an integer if it should be an integer. – Jason Yang May 07 '21 at 13:00
Hi, i did verify the result's by printing them it's picking up the result as it is but still not able to connect to a DB and the sequence also confirmed – Manish saini May 07 '21 at 13:12
You can confirm what arguments passed by calling `print(repr(uname_D1))` before call `createCon`. If all arguments correct, then I have no answer for you about `cx_Oracle`. – Jason Yang May 07 '21 at 13:16
sure Thank you i will confirm the values by using print(repr(uname_D1)) thank you – Manish saini May 07 '21 at 13:17

score 0 · Accepted Answer · answered May 07 '21 at 07:11

I don't know how this code could work. It was giving me message that layout has wrong [] - and maybe because this I didn't have sg.Output in window. But it was still used and all error messages was send to sg.Output and you could not see error.

Correct layout

layoutprefile = [
    [ 
        sg.Column(DB_creds_one),
        sg.VSeperator(),
        sg.Column(DB_creds_two),
    ],

    [sg.Output(size=(61, 5), key='-output-')],
    [sg.Submit('Proceed'), sg.Cancel('Exit')]
]

and now it display me sg.Output at the bottom of window. And all print() and error messages are send to this widget. But it can be better to remove Output for some time to see all errors in normal console.

You have other problems

it should be with or and with == "" for all variables - to check if at least one element is empty

if uname_d1 == '' or pword_d1 == '' or hname_d1 == '' or sname_d1 == '' or port_d1 == '':

or you could check it as

if '' in (uname_d1, pword_d1, hname_d1, sname_d1, port_d1):

Eventually you can use fact that empty string is treated as False

if not all([uname_d2, pword_d2, hname_d2, sname_d2, port_d2]):

But there is other problem. Using window.FindElement('-uname_db1-') you get widget and to get text from widget you need .get() or you should use values[..]

uname_d1 = window.FindElement('-uname_db1-').get()

uname_d1 = values['-uname_db1-']

and then you can use .strip() because someone may put spaces

uname_d1 = window.FindElement('-uname_db1-').get().strip()

uname_d1 = values['-uname_db1-'].strip()

You check Test_DB1 and Test_DB2 also in wrong way. It need

(Test_DB1 != True) and (Test_DB2 != True)

or better

if (Test_DB1 is not True) and (Test_DB2 is not True):

if (Test_DB1 is False) and (Test_DB2 is False):

if (not Test_DB1) and (not Test_DB2):

You could use True/False instead of 1/0 in secondwindow.

And it can be better to set default values before while loop

secondwindow = True
Test_DB1 = False
Test_DB2 = False

if you don't assign then and you click directly Processthen you get error name 'Test_DB1' is not defined because it didn't run Test_DB1 = createCon(...) yet.

import PySimpleGUI as sg
import re
import datacompy
import cx_Oracle
import pandas as pd


def read_query(connection, query):
    cursor = connection.cursor()
    try:
       cursor.execute( query )
       header = [x[0] for x in cursor.description]
       rows = cursor.fetchall()
       return header, rows
    finally:
       if cursor is not None:
           cursor.close()

def create_con(user_name, password, host_name, port_number, service_name):

    dsn_str = cx_Oracle.makedsn(host=host_name,
                                port=port_number,
                                service_name=service_name)
    con = cx_Oracle.connect(user=user_name, password=password, dsn=dsn_str)
    
    return con
      
# --- main ---

DB_creds_one = [
     [
        sg.Text("UserName"),
        sg.In(size=(30, 2), enable_events=True, key="-uname_db1-")
     ],
     [
        sg.Text("Password"),
        sg.In(size=(30, 1), enable_events=True, key="-pword_db1-")
     ],
     [
        sg.Text("Hostname"),
        sg.In(size=(30, 1), enable_events=True, key="-hname_db1-")
     ],
     [
        sg.Text("Service Name"),
        sg.In(size=(30, 1), enable_events=True, key="-sname_db1-")
     ],
     [
        sg.Text("Port"),
        sg.In(size=(30, 1), enable_events=True, key="-port_db1-")
     ],
     [   
        sg.Button('Test Con1', key='B1')
     ],
]

DB_creds_two = [
    [
        sg.Text("UserName"),
        sg.In(size=(25, 1), enable_events=True, key="-uname_db2-")
    ],
    [
        sg.Text("Password"),
        sg.In(size=(25, 1), enable_events=True, key="-pword_db2-")
    ],
    [
        sg.Text("Hostname"),
        sg.In(size=(25, 1), enable_events=True, key="-hname_db2-")
    ],
    [
        sg.Text("Service Name"),
        sg.In(size=(25, 1), enable_events=True, key="-sname_db2-")
    ],
    [
        sg.Text("Port"),
        sg.In(size=(25, 1), enable_events=True, key="-port_db2-")
    ],
    [   
        sg.Button('Test Con2',key='B2')
    ],

]


layoutprefile = [
    [ 
        sg.Column(DB_creds_one),
        sg.VSeperator(),
        sg.Column(DB_creds_two),
    ],
    [sg.Output(size=(61, 5), key='-output-')],
    [sg.Submit('Proceed'), sg.Cancel('Exit')]
]
 
window = sg.Window("DB Comparator", layoutprefile)

# default values at start
secondwindow = True
Test_DB1 = False
Test_DB2 = False

while True:    # The Event Loop
    event, values = window.read()
    # print(event, values)  # debug
    if event in (None, 'Exit', 'Cancel'):
        secondwindow = False
        break
    elif event == 'B1':
        # Test DB Connection 1  return Test_DB1
        uname_d1 = values['-uname_db1-'].strip()
        pword_d1 = values['-pword_db1-'].strip()
        hname_d1 = values['-hname_db1-'].strip()
        sname_d1 = values['-sname_db1-'].strip()
        port_d1  = values['-port_db1-'].strip()

        print()
        
        #if uname_d1 == '' or pword_d1 == '' or hname_d1 == '' or sname_d1 == '' and port_d1 == '':
        #if not all([uname_d1, pword_d1, hname_d1, sname_d1, port_d1]):
        if '' in (uname_d1, pword_d1, hname_d1, sname_d1, port_d1):
            print("Enter values")
        else:
            Test_DB1 = create_con(uname_d1, pword_d1, hname_d1, sname_d1, port_d1)
            
    elif event == 'B2':
        # Test DB Connection 2 return Test_DB2
        uname_d2 = values['-uname_db2-'].strip()
        pword_d2 = values['-pword_db2-'].strip()
        hname_d2 = values['-hname_db2-'].strip()
        sname_d2 = values['-sname_db2-'].strip()
        port_d2  = values['-port_db2-'].strip()
        
        #if uname_d2 == '' or pword_d2 == '' or hname_d2 == '' or sname_d2 == '' and port_d1 == '':
        #if not all([uname_d2, pword_d2, hname_d2, sname_d2, port_d2]):
        if '' in (uname_d2, pword_d2, hname_d2, sname_d2, port_d2):
            print("Enter values")
        else:
            Test_DB2 = create_con(uname_d2, pword_d2, hname_d2, sname_d2, port_d2)

    if event == 'Proceed':
        if not Test_DB1 and not Test_DB2:
            sg.Popup("Incorrect Database Details Please Verify the Connection Again")
        else:
            window.close()
            break

#    if not secondwindow:
exit()

Hi @furas, after using .strip() function the values are getting printed as per whatever text box holds but i am facing another issue, whenever i am passing those uname_d1,pword_d2 .... to createCon() it's not able to make a database connection, i tried getting the exact error string it displays this " Database connection error: %s ", what am i missing here or should is change the function ? I Did passed the exact DB details in that function i was able to connect to the database problem occurs when i am trying to store them in a var and then passing the var to the function — Manish saini, May 07 '21 at 11:32
if you have new problem then you should create new question on new page. Or at least you could add it to current question - it will be more readable. I don't have cx_Oracle so I can't check it but first I would use `print(...)` `print(type(...))` to see values in variables. Maybe you have wrong values in variables. Maybe you used wrong variables. I hope you don't use space in database configuration because `.strip()` removes it from values. — furas, May 07 '21 at 12:58
sure i will put up a new question for the same, and i did verified the values in those var they are the expected values but still not able to connect — Manish saini, May 07 '21 at 13:16
maybe @JasonYang has right and you have to convert `port numer` to `integer` - widgets give all as text (even numbers) but database may need number instead of text. — furas, May 07 '21 at 13:26

I am trying to build a python GUI Database Comparator using PySimpleGUI i missing something here?

2 Answers2