Finding multiple tables and setting them to a variable

[MikePowell]

There is something that just isn't right with this and I can figure it out. Here is the code:

   import sys, string, os, cx_Oracle, arcpy, datetime

   LRName = "RSID_"+num
   LRName1 = "RSID"+num
   last_year = str(now.year -1)        
   Two_years = str(now.year -2)

   orcl = cx_Oracle.connect('xxxxx/xxxxx@xxx')
   curs = orcl.cursor()
   sqllr1 = "select * from cat where table_name like '" +str(LRName1)+"%'"
   sqllr2 = "select * from cat where table_name like '" +str(LRName)+"%'"
   curs.execute(sqllr1)

   tables = curs.fetchall()
   for table in tables:
      if last_year in table:
         LastRun_Table = table
         break
      elif Two_years in table:
         LastRun = table
         break
      else:
         curs.execute(sqllr2)
         tables1 = curs.fetchall()
         for table1 in tables1:
            if last_year in table1:
               LastRun_Table = table1
               break
            elif Two_years in table1:
               LastRun_Table = table1
               break
            else:
               LastRun_Table = ""

   curs.close()
   orcl.close()

Basically I am trying to look for certain tables from previous years that match with what I am running now, set that table name to a variable that I will use later. This runs through but I am pretty sure that it is not going through the "for" statement. If I put in "fetchone" instead of "fetchall", it will go through the script once and than I get this for an error:

TypeError: 'NoneType' object is not iterable

This is just a small part of a large script that I am trying to get to work. The large script loops through several feature classes and within each feature class it does this portion (plus other things). I am kind of confused on the ".fetch" thing but it seem to be the only way that I could put the table name into a variable. I would think that there is an easier way to do this but I don't know. Any help would appreciated. Thanks in advance.

[JoshuaChisholm]

Hello Mike,

I'm not too great with Oracle, but it looks like sqllr2 is getting ignored. Maybe this means you are not fetching the data you want and the for loop is running on an empty list. Would it be better to get the results from both sql statements combined?

   ...
   sqllr1 = "select * from cat where table_name like '" +str(LRName1)+"%'"
   sqllr2 = "select * from cat where table_name like '" +str(LRName)+"%'"
   sql=sqllr1+" UNION "+sqllr2
   curs.execute(sql)

   tables = curs.fetchall()
   ...

Let me know!

[MikePowell]

That works, and is probably a little better coding than what I had (didn't know about the "UNION"). I have print statements everywhere just to follow along and make sure the variables are what they are suppose to be. I printed tables and this is what I get:

[('RSID122M_IDS_3_2014', 'TABLE'), ('RSID_122M_IDS_9_2012', 'TABLE')]

This is correct (not sure what "TABLE" is but the rest of it is fine), but it didn't assign the table name to the variable "LastRun_Table" and that is what I need. I have print statements for LastRun_Table and nothing came up. Any ideas? Am I going about this all wrong?

[MikePowell]

Ok, I think I figured it out. After doing more research I found that when you use .fetchone(), .fetchmany(), or .fetchall() it returns tuple of tuple which I needed to convert to a list. I don't know if I saying it correctly or if I understand it exactly or not but it made some sense and I was able to get what I was looking for. Here is what I have now:

  import sys, string, os, cx_Oracle, arcpy, datetime

   LRName = "RSID_"+num
   LRName1 = "RSID"+num
   last_year = str(now.year -1)        
   Two_years = str(now.year -2)

   orcl = cx_Oracle.connect('xxxxx/xxxxx@xxx')
   curs = orcl.cursor()
   sqllr1 = "select * from cat where table_name like '" +str(LRName1)+"%'"
   sqllr2 = "select * from cat where table_name like '" +str(LRName)+"%'"
   sqllr = sqllr1+" UNION "+sqllr2
   curs.execute(sqllr)

   tables = [item[0] for item in curs.fetchall()]
   for table in tables:
      if last_year in table:
         LastRun_Table = table
         break
      elif Two_years in table:
         LastRun = table
         break
      else:
         LastRun_Table = ""

   curs.close()
   orcl.close()

Thank for the help and insight.

[JoshuaChisholm]

Hello Mike,

"UNION" is a sql command (more info here). I am happy you were able to figure it out! Nice work.

Just one more comment. In the lines "if last_year in table:" and "elif Two_years in table:", you are tested to see if the a year is anywhere in the title. It looks like your tables end with the year, but also have numbers throughout the title. I'm just worried that you might get a 'false positive' if you happen to have a table like "RSID_12013M_IDS_9_2008". I'm not sure if this is possible for your data, but it might be safer to use the lines "if table.endswith(last_year)" and "elif table.endswith(Two_years)" instead.

Cheers!

[MikePowell]

Good point. I don't that will ever come up with this data but should try to count all issues. Appreciate all the help. Thanks again