I am struggling with the following issue: I created a arcpy script in which I connect to a database via sqlalchemy and collect some data into a pandas data frame. When I execute the tool within ArcGIS 10.3., it works perfectly fine. However, when I try to run it a second time within the same ArcGIS-session, it does not proceed, does not react to cancelling and seems to do nothing at all. Here is what I basically do: from sqlalchemy import create_engine import pandas # set up sqlalchemy engine: engine = create_engine ( 'mssql+pyodbc://[USER]:[PASSWORD]@[SERVER]/[DATABASE]?driver=SQL+Server' ) # design query query = "some SELECT query here" # read data to pandas data frame df = pandas . read_sql_query ( query , engine ) As I said, for the first run, everything is fine. When I execute it again, nothing happens, not even an error message. Another strange thing: it looks like it works fine when I execute the code from my IDE's python console, I can run it as often as I like. Seems like the problem is either in the engine setup or within ArcGIS. Any help would be great, thank you! EDIT: When I modify the script to force an error, fix this error and run it again, I get the following error message: ... File "C:\Python27\lib\site-packages\sqlalchemy\sql\selectable.py" , line 2469 , in __init__ for c in columns: AttributeError : 'tuple' object has no attribute 'lowercase'

Your questions about Python toolboxes are best answered by the documentation: What is a Python toolbox? Regarding "running out/in of process:" Running a script in process.

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

arcpy script using sqlalchemy and pandas only runs once

HendrikBernert1

I am struggling with the following issue:

I created a arcpy script in which I connect to a database via sqlalchemy and collect some data into a pandas data frame. When I execute the tool within ArcGIS 10.3., it works perfectly fine. However, when I try to run it a second time within the same ArcGIS-session, it does not proceed, does not react to cancelling and seems to do nothing at all.

Here is what I basically do:

<SPAN class="kwd">from</SPAN><SPAN class="pln"> sqlalchemy </SPAN><SPAN class="kwd">import</SPAN><SPAN class="pln"> create_engine
</SPAN><SPAN class="kwd">import</SPAN><SPAN class="pln"> pandas

</SPAN><SPAN class="com"># set up sqlalchemy engine:</SPAN><SPAN class="pln">
engine </SPAN><SPAN class="pun">=</SPAN><SPAN class="pln"> create_engine</SPAN><SPAN class="pun">(</SPAN><SPAN class="str">'mssql+pyodbc://[USER]:[PASSWORD]@[SERVER]/[DATABASE]?driver=SQL+Server'</SPAN><SPAN class="pun">)</SPAN><SPAN class="pln">
</SPAN><SPAN class="com"># design query</SPAN><SPAN class="pln">
query </SPAN><SPAN class="pun">=</SPAN><SPAN class="pln"> </SPAN><SPAN class="str">"some SELECT query here"</SPAN><SPAN class="pln">
</SPAN><SPAN class="com"># read data to pandas data frame</SPAN><SPAN class="pln">
df </SPAN><SPAN class="pun">=</SPAN><SPAN class="pln"> pandas</SPAN><SPAN class="pun">.</SPAN><SPAN class="pln">read_sql_query</SPAN><SPAN class="pun">(</SPAN><SPAN class="pln">query</SPAN><SPAN class="pun">,</SPAN><SPAN class="pln"> engine</SPAN><SPAN class="pun">)</SPAN>

As I said, for the first run, everything is fine. When I execute it again, nothing happens, not even an error message. Another strange thing: it looks like it works fine when I execute the code from my IDE's python console, I can run it as often as I like. Seems like the problem is either in the engine setup or within ArcGIS.

Any help would be great, thank you!

EDIT: When I modify the script to force an error, fix this error and run it again, I get the following error message:

...
<SPAN class="typ">File</SPAN><SPAN class="pln"> </SPAN><SPAN class="str">"C:\Python27\lib\site-packages\sqlalchemy\sql\selectable.py"</SPAN><SPAN class="pun">,</SPAN><SPAN class="pln"> line </SPAN><SPAN class="lit">2469</SPAN><SPAN class="pun">,</SPAN><SPAN class="pln"> </SPAN><SPAN class="kwd">in</SPAN><SPAN class="pln"> __init__</SPAN>
for c in columns:
<SPAN class="typ">AttributeError</SPAN><SPAN class="pun">:</SPAN><SPAN class="pln"> </SPAN><SPAN class="str">'tuple'</SPAN><SPAN class="pln"> object has no attribute </SPAN><SPAN class="str">'lowercase'</SPAN>

Find more posts tagged with

Accepted answers

All comments

JoshuaBixby

"When I execute the tool within ArcGIS 10.3..." It seems like your running the code from within an Toolbox? If so, a regular Toolbox or Python Toolbox? What if you run the code from the interactive Python window, what happens?

HendrikBernert1

Indeed, I run the script as Toolbox with some parameters to be set by the user.

When I execute the basic steps in the interactive Python console, it works fine, as it does within my IDE. Is there any difference between the script from a toolbox?

DanPatterson_Retired

what parameters are set and what parameters are not set within the tool?

Also the lowercase issue... lowercase is not a python method... lower is

>>> a = "UPPER"

>>> a.lower()

'upper'

RahelKreutz

The parameters set are the server and database from which the data shall be read, paths and some text for the map title as well as the exporting format. Nothing special there.

I honestly have no idea what the 'lowercase' issue means, it occurs somewhere whithin the pandas module when executing the SELECT-query. To me, it seems like the pandas module opens the connection, defines a cursor object and then goes through the columns provided by the SELECT-query.

For the first time, everything is perfectly fine. When I run it a second time, it seems like the cursor-object is not overwritten, but appended to the first one, which causes the object to be a tuple. I don't know I'm right here, and the worst part: I have no clue how to get rid of that problem.

DanPatterson_Retired

by group, do you basically mean that you basically do an argsort, then export to csv or are you doing a query, then exporting to a csv? Yes you can. It would help if you just posted a snip of the attributes table and what you are querying for... maybe even whip in a pandas method or two so I can see what you are doing and why you are using the sqlalchemy. I have written extensively on using numpy in my blog and elsewhere so it would be easier to work from example data to narrow down my search.

HendrikBernert1

I think we're getting a little bit off-track here... using numpy instead of pandas is another topic, on which I will check out your blog to find out whether I switch to numpy or not. But for my initial issue, the problem occurs within the function

df = pandas.read_sql_query(query, engine)

where my engine setup looks like

from sqlalchemy import create_engine
engine = create_engine('mssql+pyodbc://[USER]:[PASSWORD]@[SERVER]/[DATABASE]?driver=SQL+Server')

So the basic problem is: why does the tool work fine at the first run, but does nothing on the second run? And why does pandas create a tuple-cursor-object (this is what I think is happening)?

@Dan: I will have a look at your blog and see if numpy is sufficient for my purposes. I'm basically doing a simple SELECT-query (not SELECT INTO), so I don't think the problem is within the query itself, but in the way pandas deals with connections and cursors

JamesCrandall

So the basic problem is: why does the tool work fine at the first run, but does nothing on the second run?

Have you tried closing your connections to the sql db?

RahelKreutz

I'm doing this to get rid of the connection (I'm using an engine, not the connection itself):

engine.dispose()
del engine

From my understanding, this should do the job (an engine has no particuclar method .close()).

JamesCrandall

I think you are correct. I did see reference to the notion that it's actually a pool of connections and you could issue a .close() to each/all. But also read that your .dispose() should be handling the disconnect. As a test I'd run a simple SELECT statement to see if you are getting the same strange result of only working on the first pass. If it works as expected, then perhaps the issue is at the db-level.

JamesCrandall

Also, are you deleting the pandas DataFrame?

HendrikBernert1

Ok, I created a small test-case with only the very basic function and executed it in twi different ways:

1. create a Toolbox (without any parameters set) and run it from there

2. run each single line in the ArcGIS Python Console

Results:

1. The same problem occurs as described at the top of this thread. Only difference: the script runs twice and not once, but the third run simply hangs up.

2. perfectly fine, I can do whatever I want as often as I want.

So the problem seems to come from the Toolbox and is not within the query or the database.

Here is my Toolbox-setup:

- Store relative path names

- Always run in foreground

- Run Python script in process

- Parameters: all given as string with one exception (folder), all read with arcpy.GetParameterAsText()

So nothing unusual here...

JoshuaBixby

I was able to reproduce the issue with your attached code. I figured out why I didn't generate the errors yesterday. Oddly enough, if I run the code once in the Interactive Python window in ArcMap, then I can run the script tool numerous times without errors. If I run the script tool out of process, it runs fine also. If I run it as a tool in a Python toolbox, instead of a script in a regular toolbox, it runs fine. I tried cutting out sqlalchemy and pandas and using pyodbc only, same results.

Although I can't explain what or why, everything points to a problem with in-process, regular Toolboxes. It might be worth trying running out of process or converting to a Python Toolbox.

JeffSegal

I'm having the same problem, after some debugging, I've seen that there's actually an exception being thrown.

Add a try/except block around the code (especially the create_engine statement) along with some debug prints, and print the exception.

I'm not certain, but it seems like there's a handle still alive (cached?) on subsequent runs from the toolbox.

HendrikBernert1

I did as you said, but the try-except does not raise any exceptions. I did it like this:

try:
  engine = create_engine('mssql+pyodbc://[USER]:[PASSWORD]@[SERVER]/[DATABASE]?driver=SQL+Server')
  arcpy.AddMessage('Engine created successfully')
except:
  arcpy.AddMessage('Engine not created')

try:
  df = pandas.read_sql_query(query, engine)
  arcpy.AddMessage('Dataframe created successfully')
except:
  arcpy.AddMessage('Creating dataframe failed')

On each run, I get the first message, saying that the engine has been created successfully. The script actually fails at executing the pandas.read_sql_query(), but gives no exception. It seems to be stuck within the query-execution, but I have no idea how to get any messages from there.

@joshua: what excatly do you mean by "running it out of process" and "convert to a Python Toolbox"? When I execute every single line in the Python Console of ArcGIS, everything is fine (this was my understanding of "out of process"). Is there such a thing like a special Python Toolbox? In which way is it different from a "regular" Toolbox I can create on my own and how can I convert it?

HendrikBernert1

I think I finally solved it!

This is what worked for me:

- open the Python Console in ArcGIS

- type

import myModule

- enter, then execute the Toolbox

And so it works, as often as I like (at least it seems to do so after several test runs).

With this in mind, I have another question: how do I force the script to import itself at startup?

What I do is the following:

- import everything I need from external tools

- define some global variables, most given via user input as parameters for the following functions

- define all functions I need

- run the following command:

if __name__ == '__main__':
     'EXECUTE ALL MY FUNCTIONS WITH THE PARAMETERS GIVEN BY THE USER'

So, how can I force the script to import itself when I execute it from the Toolbox?

NyigamBole

I am having a kind of similar issue with ArcGIS 10.5.

could you help me by sharing your solution in a query that I raised!! Please...

https://community.esri.com/t5/arcgis-online-questions/script-in-arcgis-runs-only-once-and-throws-error/m-p/1268901

NyigamBole

I have stumbled upon a solution which works perfectly for me. You can check the solution in the above link.

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.