Prompting User to Log in to Database in Python Addin

4396
16
10-26-2016 03:29 PM
MikeMacRae
Occasional Contributor III

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}@//random.company:1234/db1.company.com'.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.

0 Kudos
16 Replies
DanPatterson_Retired
MVP Emeritus

long shot.... since I don't use addins, but harkening back to 'the day' you could control the visible width of a text box or combo box etc but it would accept anything you typed in without truncating characters  If you can make it a couple of characters wide, then the lurker would have to really lean in to catch the password

MikeMacRae
Occasional Contributor III

Thanks Dan, everything is up for grabs at this point. I might play with that, if no other options are available!

0 Kudos
BlakeTerhune
MVP Regular Contributor

I chose to use a toolbox script tool so I could create parameter input boxes with ArcToolbox, then the user just selects a database connection that has already been created; no need to enter user/pass every time. I know it's not an add-in but maybe you can convince whomever that a script tool is just as easy.

DanPatterson_Retired
MVP Emeritus

I like that approach, much simpler... better than my next suggestion of putting a checkbox confirming the user had checked over their shoulder

0 Kudos
MikeMacRae
Occasional Contributor III

The rub of all of this isn't so much that I am using an addin over a toolbox script, it's that I cannot create complex queries on the oracle database using any existing in arcpy tools, functions or methods. I am forced to use a third party module for this (cx_Oracle) because of the bug I spoke to in my post, so database connections via ArcGIS won't work for my usage case.

0 Kudos
BlakeTerhune
MVP Regular Contributor

Did you try arcpy.ArcSDESQLExecute()?

MikeMacRae
Occasional Contributor III

Blake, I have not tried ArcSDESQLExecute until now. Thanks for pointing that out. I had no idea it existed. So, in testing it basically does the same thing that xc_Oracle does, but is limited in a number of ways. The biggest problem I have is that I can't get the field/column names of the resulting table of the query. In cx_Oracle, you can query the field.column names by looping over the description method of the cursor:

print [i[0] for i in cursor.description]

As far as I can tell, after I create the table using ArcSDESQLExecute:

db_return = db_conn.execute(sql)

When I loop over the results of the table:

for items in db_return:
    for item in items:
        print item

This will return the data populated by the query, but does not provide field headings.

Do you or anyone else know if this is possible?

0 Kudos
BlakeTerhune
MVP Regular Contributor

You're right, it does not return a formal table object, rather just a list of lists full of the row values. If you know the field names of what you're querying and just want to be able to refer to the fields in your code you could do one of the following:

  • Assign the index value of the row list to a variable named as the field name
  • Create a dictionary of dictionaries (edit: some interesting suff here)
  • Look into using the named tuple.

If you need an actual table of some kind, you could use GP tools in arcpy to create a table in memory and populate it with your query data, then save it out somewhere. Or, if you're just doing select queries, maybe you can just do a series of table views/feature layers with where clauses until you have what you need.

Apologies if I'm just completely missing the mark here because I'm not entirely sure what exactly you're trying to accomplish.

Luke_Pinner
MVP Regular Contributor

Mike, something like:

from collections import namedtuple
import arcpy

egdb = r'path\to\connection.sde'
egdb_conn = arcpy.ArcSDESQLExecute(egdb)

table = 'SCHEMA.TABLE'
fields = ('SOMEVALUE', 'SOMENAME')

sql = 'SELECT {0} FROM {1}'.format(','.join(fields), table)

egdb_return = egdb_conn.execute(sql)
row = namedtuple('row', fields)
for i in egdb_return:
    
    #With a dict
    i = dict(zip(fields,i))
    print((i['SOMEVALUE'], i['SOMENAME'])
    
    #With a named tuple
    i = row(*i)
    print((i.SOMEVALUE, i.SOMENAME))