AnsweredAssumed Answered

Prompting User to Log in to Database in Python Addin

Question asked by BrokenLegMike on Oct 26, 2016
Latest reply on Oct 27, 2016 by rastrauch

I have built a python addin where a user selects a bunch of polygon features in ArcMap which then queries an Oracle database and provides a spreasheet of the results.


This works well but the final peice of the puzzle is I need to be able to prompt my users to enter their credentials for the oracle database login so that the script can query the database.


For the record, I am using the python module:

import cx_Oracle

to connect to the database using the syntax:

db = cx_Oracle.connect('{0}/{1}@//'.format(username, password)

to login. To explain, I am passing the username and password into the connection string via the .format() method. Which ever way I design a login prompt, the script will grab the users credentials and pass them into the connection string.


In the python addin help documentation, I do not see a function or property anywhere to pop up a dialogue that will accept some sort of textual input. For example, a text box with 2 entry fields. One for the username and one for the password and then a button to accept the credentials. Or some other option that will allow this.


Couple answers to questions you may have:


Q: Why don't you set the environment workspace to the oracle database connection which gives an automatic dialogue to prompt for credential:

(i.e arcpy.env.workspace = r"Database Connections\oracleDB.sde")

A: This works fine, however, after logging into the database, I need to query it. Arcpy offers the 'Make Query Layer' tool to build and compile a query in Oracle. After working with this tool for a week or so, it turns out it has a bug which populates incorrect query results (see bug number below). ArcGIS and arcpy do not offer any other option to submit complex queries to Oracle sde connection via python. This is why I choose to use cx_Oracle for this portion of my script. That and it executes much much faster!

BUG-000090452 : The Make Query Layer tool does not respect the Unique fields variable in Model builder, or the oid_fields keyword in Python arcpy.MakeQueryLayer_management() command.


Q: Why don't you use some third party GUI builder like Tkinter?

A: Any use of Tkinter in ArcGIS (Map, Catalogue, etc) will cause it to crash. From what I've read, ArcGIS will not support third party GUI builders. Also, I did test a tkinter widget this and it crashed everytime when using in ArcMap. outside of ArcMap, it worked fine.


Q: Why not just use a combobox as a textual input for a user name and/or password?

A: The problem I have with this is, when the user types into the combobox, I can't mask the characters the user is entering for the password (i.e. when the user types into the box, only asterix appears). I don't want to expose their password to a lurking employee looking over their shoulders.


Anyways, what I am looking for is a long shot I think. if anyone can throw an idea or 2 out there, I'm willing to test it.