When an exception occurs, the error object is not deleted from pygrametl._alltables, causing the correct code to fail to execute during subsequent submissions

[qianxuanyon]

Hi

I'm sorry I ran into a problem again

When it reported an error, I changed the value of the measures parameter After modifying the code, it stays at the old error

1. First execution

   
   import time
   import cx_Oracle
   import pygrametl
   from pygrametl.datasources import SQLSource,CSVSource
   from pygrametl.tables import BatchFactTable,Dimension,BulkFactTable,tempfile

print(time.ctime()) dw_conn = pygrametl.ConnectionWrapper(connection=conn) sour = SQLSource(connection = conn, query = 'SELECT ID,A,B FROM test_x1 where rownum<10')

test_y1

tag_table = BatchFactTable(name = 'test_y1',keyrefs=[],measures = ['ID','A','B'],batchsize=50000,targetconnection = dw_conn)

for row in sour: tag_table.insert(row) dw_conn.commit()

Sun Sep 20 18:49:45 2020

DatabaseError Traceback (most recent call last)

in 17 tag_table.insert(row) 18 ---> 19 dw_conn.commit() /opt/anaconda3/lib/python3.7/site-packages/pygrametl/__init__.py in commit(self) 898 def commit(self): 899 """Commit the transaction.""" --> 900 endload() 901 self.__connection.commit() 902 /opt/anaconda3/lib/python3.7/site-packages/pygrametl/__init__.py in endload() 406 method = getattr(t, 'endload', None) 407 if callable(method): --> 408 method() 409 410 _today = None /opt/anaconda3/lib/python3.7/site-packages/pygrametl/tables.py in endload(self) 1888 def endload(self): 1889 """Finalize the load.""" -> 1890 self.__insertnow() 1891 1892 def __insertmultirow(self): /opt/anaconda3/lib/python3.7/site-packages/pygrametl/tables.py in __insertexecutemany(self) 1899 def __insertexecutemany(self): 1900 if self.__batch: -> 1901 self.targetconnection.executemany(self.insertsql, self.__batch) 1902 self.__batch = [] 1903 /opt/anaconda3/lib/python3.7/site-packages/pygrametl/__init__.py in executemany(self, stmt, params, translate) 698 newparams = params 699 --> 700 self.__cursor.executemany(newstmt, newparams) 701 702 def _translate2pyformat(self, stmt, row=None): DatabaseError: ORA-00904: "B": invalid identifier ``` 2. Execute after modification ```python import time import cx_Oracle import pygrametl from pygrametl.datasources import SQLSource,CSVSource from pygrametl.tables import BatchFactTable,Dimension,BulkFactTable,tempfile print(time.ctime()) dw_conn = pygrametl.ConnectionWrapper(connection=conn) sour = SQLSource(connection = conn, query = 'SELECT ID,A,B FROM test_x1 where rownum<10') # test_y1 tag_table = BatchFactTable(name = 'test_y1',keyrefs=[],measures = ['ID','A'],batchsize=50000,targetconnection = dw_conn) for row in sour: tag_table.insert(row) dw_conn.commit() Sun Sep 20 18:50:49 2020 --------------------------------------------------------------------------- DatabaseError Traceback (most recent call last) in 14 for row in sour: 15 tag_table.insert(row) ---> 16 dw_conn.commit() /opt/anaconda3/lib/python3.7/site-packages/pygrametl/__init__.py in commit(self) 898 def commit(self): 899 """Commit the transaction.""" --> 900 endload() 901 self.__connection.commit() 902 /opt/anaconda3/lib/python3.7/site-packages/pygrametl/__init__.py in endload() 406 method = getattr(t, 'endload', None) 407 if callable(method): --> 408 method() 409 410 _today = None /opt/anaconda3/lib/python3.7/site-packages/pygrametl/tables.py in endload(self) 1888 def endload(self): 1889 """Finalize the load.""" -> 1890 self.__insertnow() 1891 1892 def __insertmultirow(self): /opt/anaconda3/lib/python3.7/site-packages/pygrametl/tables.py in __insertexecutemany(self) 1899 def __insertexecutemany(self): 1900 if self.__batch: -> 1901 self.targetconnection.executemany(self.insertsql, self.__batch) 1902 self.__batch = [] 1903 /opt/anaconda3/lib/python3.7/site-packages/pygrametl/__init__.py in executemany(self, stmt, params, translate) 698 newparams = params 699 --> 700 self.__cursor.executemany(newstmt, newparams) 701 702 def _translate2pyformat(self, stmt, row=None): DatabaseError: ORA-00904: "B": invalid identifier ```

[qianxuanyon]

When an exception occurs, the correct code cannot run

[qianxuanyon]

Resolved

Execute after an exception

pygrametl._alltables.remove(tag_table)

[chrthomsen]

Hello,

Good that you resolved the problem! I think it will be vary hard to make a general way for deciding when an exception should lead to that a table is removed from pygrametl._alltables. Thus, I think it has to handled manually as you do in your example.

Best regards, Christian