topic Re: arcpy script using sqlalchemy and pandas only runs once in Python Questions

arcpy script using sqlalchemy and pandas only runs once

HendrikBernert1 — Sat, 11 Dec 2021 13:26:41 GMT

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'

Re: arcpy script using sqlalchemy and pandas only runs once

JoshuaBixby — Mon, 08 Aug 2016 13:45:24 GMT

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

Re: arcpy script using sqlalchemy and pandas only runs once

HendrikBernert1 — Mon, 08 Aug 2016 13:58:58 GMT

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?

Re: arcpy script using sqlalchemy and pandas only runs once

DanPatterson_Retired — Mon, 08 Aug 2016 15:55:39 GMT

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'

Re: arcpy script using sqlalchemy and pandas only runs once

RahelKreutz — Mon, 08 Aug 2016 19:44:19 GMT

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.

Re: arcpy script using sqlalchemy and pandas only runs once

DanPatterson_Retired — Mon, 08 Aug 2016 20:05:56 GMT

And you have added a simple 'del' to the scripts to delete the objects prior to completion? I would assume that would be automatic on script completion, but I use numpy rather than pandas, so I can't comment on how it handles objects during script completion... some of it is a black box, but you would have to go through the github code to see what is going on with any particular function/module pandas/pandas at master · pydata/pandas · GitHub

Re: arcpy script using sqlalchemy and pandas only runs once

HendrikBernert1 — Tue, 09 Aug 2016 06:15:41 GMT

Yes, I delete all objects that matter in that case, specifically the data frame after exporting the data and the engine after the data is retrieved. From my understanding, pandas as well as sqlalchemy are taking care of committing, rollback, close and everything else, but obviously, I missed something at some point.

But since you mention numpy: what I basically do is, I select data into the pandas data frame and do everything else there, which means I group the data based on a columnn specified by the user and export the resulting table as a csv-file. Can you do the same thing with numpy? I specifically worry about the grouping...

Re: arcpy script using sqlalchemy and pandas only runs once

DanPatterson_Retired — Tue, 09 Aug 2016 06:50:42 GMT

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.

Re: arcpy script using sqlalchemy and pandas only runs once

HendrikBernert1 — Sat, 11 Dec 2021 13:26:44 GMT

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

Re: arcpy script using sqlalchemy and pandas only runs once

JamesCrandall — Tue, 09 Aug 2016 14:15:11 GMT

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?

Re: arcpy script using sqlalchemy and pandas only runs once

RahelKreutz — Sat, 11 Dec 2021 13:26:47 GMT

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

Re: arcpy script using sqlalchemy and pandas only runs once

JamesCrandall — Tue, 09 Aug 2016 15:24:35 GMT

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.

Re: arcpy script using sqlalchemy and pandas only runs once

JamesCrandall — Tue, 09 Aug 2016 15:29:25 GMT

Also, are you deleting the pandas DataFrame?

Re: arcpy script using sqlalchemy and pandas only runs once

JoshuaBixby — Tue, 09 Aug 2016 21:50:32 GMT

What are your script properties? For example, with the General tab, are "Store relative path names..." and "Always run in foreground" checked? On the Source tab, is "Run Python script in process" checked?

In ArcMap's Geoprocessing Options dialog, is "Background Processing" enabled? Have you installed 64-bit Background Geoprocessing?

I ginned up a little script modeled after yours. Since I don't know what your code does once it generates the Panda dataframe, I just dump some of the dataframe content as a message using arcpy.AddMessage() . No matter how often I run it, I get the results I expect dumped as a message.

Without knowing a bit more of the script settings and what is being done with the Panda dataframe, I am all out of ideas.

Re: arcpy script using sqlalchemy and pandas only runs once

HendrikBernert1 — Wed, 10 Aug 2016 06:25:30 GMT

Yes, those options are checked within the toolbox settings as well as in the Geoprocessing Options. With simple print-debugging (print some message after each step), I came to the conclusion that the error occurs within the aforementioned function

df = pandas.read_sql_query(query, engine)

Deleting the pandas data frame as well as the engine which connects to the database don't do the job, so maybe you're right and the error occurs within the db. But if so, why does it work from the console as often as I like?

Maybe one more piece of information (since this is an internal company project, unfortunately I can't be too specific here): I run the database connection and querying from a function which I will attach here. As you can see there, I'm also trying to check whether the engine already exists, but the result is still the same. I'm out of ideas...

Re: arcpy script using sqlalchemy and pandas only runs once

HendrikBernert1 — Wed, 10 Aug 2016 06:28:02 GMT

Yes, I do delete everything I assign (at least I hope I did not oversee something). I'll create a simpler SELECT testcase and get back to you as soon as I can

Re: arcpy script using sqlalchemy and pandas only runs once

HendrikBernert1 — Wed, 10 Aug 2016 08:45:07 GMT

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

Re: arcpy script using sqlalchemy and pandas only runs once

DanPatterson_Retired — Wed, 10 Aug 2016 08:49:17 GMT

and there are no lock files being created by arcmap then? (confirm through windows explorer) If arcmap is open are you using a separate IDE or arcmap's? If you are always using a separate python ide and the only difference is whether arcmap is open, then the culprit is arcmap and tracking that down needs viewing what is going on with the operating system.

Re: arcpy script using sqlalchemy and pandas only runs once

HendrikBernert1 — Wed, 10 Aug 2016 09:48:40 GMT

As far as I can see, there are no lock files created. I'm using an external IDE, but it does not matter whether I have it open or not.

Little step forward: now it runs twice and crashes when executed a third time... extremely strange

Re: arcpy script using sqlalchemy and pandas only runs once

JoshuaBixby — Wed, 10 Aug 2016 15:41:10 GMT

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.