topic Re: Arcpy - execute sql query in oracle error in Python Questions

Arcpy - execute sql query in oracle error

Pierre-LucBoivin — Wed, 19 Nov 2014 16:05:43 GMT

Hi,

I have to execute a sql query from an oracle table but the problem is that according to a new argument that is true, I have to make a connection to another table.

My original script that i've been using for 2 years works like a charm so I just add an if statement in the connection variable but when comes the time to populate my temp table I got this error :

I tried to put rows = cursor.fetchall() in my if statement and it doesn't work either.

The line 75 refer to the line in red

if rdc == True:

    sql = "SELECT * FROM INTERVENTIONS_GEO_UNITES_RDC" + arg1

    cursor.execute(sql)



else:

    sql = "SELECT * FROM INTERVENTIONS_GEO_UNITES WHERE " + arg1

    cursor.execute(sql)

rows = cursor.fetchall()

avec_rdc = (row for row in rows if rdc == True)

sans_rdc = (row for row in rows if rdc == False)

for row in avec_rdc:

        trow = iCur.newRow()

        trow.INCIDENT_N = row[0]

        trow.DATE_INTER = row[2]

        trow.ADRESSE_IN = row[3]

        trow.TYPE_INT = row[4]

        trow.CAS_NO = row[5]

        trow.REGION = row[6]

        trow.CATEGORIE = row[7]

        trow.SITE_KEY = row[8]

        trow.ARRONDISSE = row[9]

        trow.LISTE_UNIT = row[11]

        trow.LONGITUDE = row[12]

        trow.LATITUDE = row[13]

for row in sans_rdc:

        trow = iCur.newRow()

        trow.INCIDENT_N = row[0]

        trow.DATE_INTER = row[1]

        trow.ADRESSE_IN = row[2]

        trow.TYPE_INT = row[3]

        trow.CAS_NO = row[4]

        trow.REGION = row[5]

        trow.CATEGORIE = row[6]

        trow.SITE_KEY = row[7]

        trow.ARRONDISSE = row[8]

        trow.LISTE_UNIT = row[9]

        trow.LONGITUDE = row[10]

        trow.LATITUDE = row[11]

iCur.insertRow(trow)

thanks for your help

Re: Arcpy - execute sql query in oracle error

JamesCrandall — Wed, 19 Nov 2014 18:39:42 GMT

Total guess here, but "rows" is empty.

What is the value of "arg1"? If it's numeric, you may need to convert it to string, as well as add an extra space after the SELECT statement:

sql = "SELECT * FROM INTERVENTIONS_GEO_UNITES_RDC " + str(arg1)

Re: Arcpy - execute sql query in oracle error

JamesCrandall — Wed, 19 Nov 2014 18:48:26 GMT

Probably of more importance.....

Get rid of the "SELECT *" !!!

I am of the mind that it's just silly to have this in any production environment. I see you have the fields listed out for your cursor, it's probably even more important to have them listed in your SQL.

Re: Arcpy - execute sql query in oracle error

ChrisMathers — Wed, 19 Nov 2014 19:45:58 GMT

The syntax highlighting is having a dang fit so I did a pastbin of what I think would work. Paste #nnwkcPiRJshsKg0vgr0J at spacepaste

Its also not a good idea to use fetchall() generally. If you know you're getting a small result set its OK but because the entire result is being read into memory your can run into problems. Better to do something like what is below. Its wordier but it keeps your memory usage down.

while True:
     row = cursor.fetchone()
     if row == None:
          break
     if rdc:
          avec_rdc.append(row)
     else:
          sans_rdc.append(row)

Re: Arcpy - execute sql query in oracle error

JoshuaBixby — Wed, 19 Nov 2014 19:56:41 GMT

I agree with James Crandall‌ about SELECT * .

Are you using pyodbc? If so, fetchall() returns an empty list, even if no rows are returned, so I don't get why rows wouldn't be defined. If a successful query, even one with no results, returns a list that could instantiate rows; maybe the queries are generating an error and fetchall is returning nothing, so rows doesn't exist.

Re: Arcpy - execute sql query in oracle error

JamesCrandall — Thu, 20 Nov 2014 13:49:11 GMT

OP is probably using cx_Oracle

Re: Arcpy - execute sql query in oracle error

Pierre-LucBoivin — Thu, 20 Nov 2014 14:23:39 GMT

Hi thank for all your answers !!

It was kind of my first python script so i'm open to fix it to optimize my code.

James is right i'm using cx_Oracle for my connection.

Here what my sql query look likes

SELECT * FROM INTERVENTIONS_GEO_UNITES WHERE TYPE_INT <> '1ERREP' and DATE_INTER BETWEEN TO_DATE('01/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS')  AND TO_DATE('12/31/2011 23:59:59', 'MM/DD/YYYY HH24:MI:SS') and ARRONDISSEMENT_VILLE in ('Côte-des-Neiges--Notre-Dame-de-Grâce','Plateau-Mont-Royal','Outremont')

or if it with rdc here my query pratically the same except they add a suffix RDC at the view name.

SELECT * FROM INTERVENTIONS_GEO_UNITES_RDC WHERE TYPE_INT <> '1ERREP' and DATE_INTER BETWEEN TO_DATE('01/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS')  AND TO_DATE('12/31/2011 23:59:59', 'MM/DD/YYYY HH24:MI:SS') and ARRONDISSEMENT_VILLE in ('Côte-des-Neiges--Notre-Dame-de-Grâce','Plateau-Mont-Royal','Outremont')

What I don't understand is that i can execute the script without any problems when I don't have the condition True or False with this code

sql = "SELECT * FROM INTERVENTIONS_GEO_UNITES WHERE " + arg1
cursor.execute(sql)
rows = cursor.fetchall()

I'll try the differents options that you guys gave me and come back for the feedback of my tests

Thanks you

Re: Arcpy - execute sql query in oracle error

JamesCrandall — Thu, 20 Nov 2014 16:36:05 GMT

The difference is that you fixed the sql:

sql = "SELECT * FROM INTERVENTIONS_GEO_UNITES_RDC " + arg1

Before you had it without any space between the sql string and your arg1 value (there was no space between the RDC and the last quote):

sql = "SELECT * FROM INTERVENTIONS_GEO_UNITES_RDC" + arg1