topic Re: Prompting User to Log in to Database in Python Addin in Python Questions

Prompting User to Log in to Database in Python Addin

MikeMacRae — Wed, 26 Oct 2016 22:29:45 GMT

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.

Re: Prompting User to Log in to Database in Python Addin

DanPatterson_Retired — Wed, 26 Oct 2016 22:55:26 GMT

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

Re: Prompting User to Log in to Database in Python Addin

BlakeTerhune — Wed, 26 Oct 2016 23:04:25 GMT

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.

Re: Prompting User to Log in to Database in Python Addin

DanPatterson_Retired — Wed, 26 Oct 2016 23:16:03 GMT

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

Re: Prompting User to Log in to Database in Python Addin

MikeMacRae — Wed, 26 Oct 2016 23:18:27 GMT

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

Re: Prompting User to Log in to Database in Python Addin

MikeMacRae — Wed, 26 Oct 2016 23:25:20 GMT

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.

Re: Prompting User to Log in to Database in Python Addin

BlakeTerhune — Wed, 26 Oct 2016 23:30:24 GMT

Did you try arcpy.ArcSDESQLExecute()?

Re: Prompting User to Log in to Database in Python Addin

DanPatterson_Retired — Thu, 27 Oct 2016 00:05:25 GMT

I don't suppose that there is anything in the Arcgis PRO conda installation that may be of use

/blogs/dan_patterson/2016/07/17/anaconda-spyder-and-arcgis-pro

or if you do you should be able to add new modules easily if they are within the full stack.... check the 3.4 link in this post Anaconda package list | Continuum Analytics: Documentation

It will just simplify patching up a 2.7 version.... you will eventually have to move anyway

Re: Prompting User to Log in to Database in Python Addin

RebeccaStrauch__GISP — Thu, 27 Oct 2016 03:58:45 GMT

Is the main need to be able to pass the credentials each time? Although this may seem off subject, read thru this thread https://community.esri.com/thread/180549 And think about reading a settings.ini file to grab the credentials and pass it as needed. I'm using this method to give admin rights for my SQL, for my purposes, without prompting or having the user/pass hard coded in the results or other log file. I keep the settings.ini file in a more secure location. I would think you could do something similar for each user with the permissions they need.

Not sure if that could be a solution or not.....I'm not an oracle person so not sure about security and access, etc.

Re: Prompting User to Log in to Database in Python Addin

MikeMacRae — Thu, 27 Oct 2016 20:16:36 GMT

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?

Re: Prompting User to Log in to Database in Python Addin

MikeMacRae — Thu, 27 Oct 2016 20:27:58 GMT

Thanks for the iidea, Rebecca. I like this idea and it might work for a personal project I am working on but in this case, I don't have that option. I work for a very large organization with a central IT and security department who's best practice manual indicates that we are not to store username and password information. This also includes exercising any option on a given GUI that allows gives you the option to do so.

Re: Prompting User to Log in to Database in Python Addin

RebeccaStrauch__GISP — Thu, 27 Oct 2016 20:44:54 GMT

Hi Mike,

Warning...make sure to always check the Results tab to make sure it isn't being stored there, which was my issue. I started looking at the stop/start AGS admin toolbox by khibma-esristaff‌ and using that type of dialog, it does store it so you can rerun it. I know he is working on a new version, and maybe he'll address this. ??

In my case it is for getting a token, and I have a static user/pass with limited network privileges and access. I have the settings file on a drive that needs increased priveldges, so only someone with admin rights can run the scripts. Of course nothing is 100% secure, so I understand if you can't work with this, especially if each user needs separate login creds. Maybe if there is a way to tap info the "current user creds" ? I haven't figured it out, but I don't manage our network/domain users. I'm always interested in alternatives too.

Re: Prompting User to Log in to Database in Python Addin

MikeMacRae — Thu, 27 Oct 2016 21:16:58 GMT

Ha, I like your train of thought! You can get the users user name by using the getuser method in the getpass module:

from getpass import getuser
getuser()

unfortunately, I'd need a lot of hacking knowledge to get someones password from the system/network in order to automate the connection to oracle.

Re: Prompting User to Log in to Database in Python Addin

DanPatterson_Retired — Thu, 27 Oct 2016 21:22:26 GMT

checkbox... did you look over your shoulder? is looking better

Re: Prompting User to Log in to Database in Python Addin

BlakeTerhune — Thu, 27 Oct 2016 22:09:11 GMT

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.

Re: Prompting User to Log in to Database in Python Addin

Luke_Pinner — Sun, 12 Dec 2021 03:05:10 GMT

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))

Re: Prompting User to Log in to Database in Python Addin

RebeccaStrauch__GISP — Fri, 28 Oct 2016 02:43:09 GMT

Here's another thought, as mentioned by others by creating a connection file. I have a connection called

"me@myDatabaseName" and it uses domain credentials, not hard coded. That way, I can put it on everyone's machine and it takes their credentials automatically.

Create ArcSDE Connection File—Help | ArcGIS for Desktop

OPERATING_SYSTEM_AUTH —Use Operating system authentication. You do not need to type a user name and password. The connection will be made with the credentials used to log in to the operating system. If the login used for the operating system is not a valid geodatabase login, the connection will fail. Also note you cannot make an ArcSDE service connection using operating system authentication to a geodatabase stored in Oracle, DB2, or Informix.

.....oops, just noticed it says not Oracle. Oh, well, I'll leave this in case it sparks another idea.