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:

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'

[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?

[JoshuaBixby]

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.

[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-err... 

[NyigamBole]

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