topic Re: SQL error when using Python call argument in Python Questions

SQL error when using Python call argument

BobMCkay — Thu, 07 Mar 2013 21:01:08 GMT

Hi,

The following code add two arguments to the Python call. The first argument works fine but the second gives an invalid SQL error when using SELECT. The script I have attached shows the parcel string hard coded to a Parcels variable, this works OK. Can someone provide the correct string for strParcels? Note the \\ is meant to relate to a \ in the final SQL

strOwner = "Lett"
strParcels = "PARCEL_SPI = '1\\TP8994'"

retVal = Shell("cmd.exe /K S:\MID_Owners_Database_Workspace\OwnerProperty.py " & strOwner & " " & strParcels, 1)

Re: SQL error when using Python call argument

curtvprice — Thu, 07 Mar 2013 21:58:33 GMT

Hi,

The following code add two arguments to the Python call. The first argument works fine but the second gives an invalid SQL error when using SELECT. The script I have attached shows the parcel string hard coded to a Parcels variable, this works OK. Can someone provide the correct string for strParcels? Note the \\ is meant to relate to a \ in the final SQL
strOwner = "Lett" strParcels = "PARCEL_SPI = '1\\TP8994'" retVal = Shell("cmd.exe /K S:\MID_Owners_Database_Workspace\OwnerProperty.py " & strOwner & " " & strParcels, 1)

Wow, that's a tricky one because you need to pass your string arguments through VBScript/VB/VBA (whatever that is), Windows shell, and Python to your tool!

Here's my guess at it. I used literal double-quotes (""") to make sure arguments get passed to thru the Windows shell command line. I don't think you need to escape the "\" in your SQL expression because it will be read as a literal string before the Python interpreter gets a hold of it.

strOwner = """Lett""" strParcels = """PARCEL_SPI = '1\TP8994'""" retVal = Shell("cmd.exe /K " & _     "C:\Python27\ArcGIS10.1\python.exe " &_     "S:\MID_Owners_Database_Workspace\OwnerProperty.py " &_     strOwner & " " & strParcels, _     1)

This is as if you typed the command line (probably a good way to test it):

C:\> cmd.exe /K C:\Python27\ArcGIS10.1\python.exe ^ S:\MID_Owners_Database_Workspace\OwnerProperty.py "Lett" "PARCEL_SPI = '1\TP8994'"

Note cmd.exe /c may be a better choice, otherwise the shell continues to sit out there. Or you could just run Python directly:

C:\> C:\Python27\ArcGIS10.1\python.exe ^ S:\MID_Owners_Database_Workspace\OwnerProperty.py "Lett" "PARCEL_SPI = '1\TP8994'"

Re: SQL error when using Python call argument

BobMCkay — Thu, 07 Mar 2013 23:00:08 GMT

Thanks for that. I'll give it a go and get back to you. PS I'm running VBA from Access

Re: SQL error when using Python call argument

BobMCkay — Fri, 08 Mar 2013 00:21:48 GMT

Many thanks. Works well.