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'
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.
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?
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.
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?
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...
I have stumbled upon a solution which works perfectly for me. You can check the solution in the above link.