Python application trying to insert into oracle
Question: I have a simple tkinter application I built to put in the amount of hours I
worked. for some reason I keep getting this error "DatabaseError: ORA-01036:
illegal variable name/number" any help will be greatly appreciated!

here is my SnowTime.py

    
    
    import os 
    import sys
    import Tkinter
    import tkMessageBox
    import cx_Oracle 
    from datetime import date, datetime, timedelta
    import time
    
    
    def insertBcl():
         connection = cx_Oracle.connect('user', 'password', cx_Oracle.makedsn('xxx.xxx.xxx.xxx', 'port', 'sid'))
         cursor = connection.cursor()
         cursor.execute("SELECT MAX(TRANSACTION_ID) FROM LABOR")
         for result in cursor:
    
             r = reduce(lambda rst, d: rst * 10 + d, (result))
    
             f = r + 1
         #cursor.close()
         #connection.close()
    
    
         hours = e1.get()
         today = datetime.now().date().strftime("%Y-%m-%d")
    
         SNOWJ = "SNOWJ"
         I = "I"
         OH = "OH"
         W = "W"
         null = "null"
         zero = 0
         N = "N"
         SNOW = "SNOW"
    
    
    
         add_time =("INSERT INTO TEST " 
                        "(EMPLOYEE_ID, TRANSACTION_DATE, TRANSACTION_ID, TYPE, INDIRECT_ID, HOURS_WORKED, GOOD_QTY, BAD_QTY, SHIFT_ID, SETUP_COMPLETED, PASS_FAIL, CREATE_DATE, REASON, USER_ID, OPEN_OP)"
                         "VALUES (%(EMPLOYEE_ID)s, %(TRANSACTION_DATE)s, %(TRANSACTION_ID)s, %(TYPE)s, %(INDIRECT_ID)s, %(HOURS_WORKED)s, %(GOOD_QTY)s, %(BAD_QTY)s, %(SHIFT_ID)s, %(SETUP_COMPLETED)s, %(PASS_FAIL)s, %(CREATE_DATE)s, %(REASON)s, %(USER_ID)s, %(OPEN_OP)s)")
    
         data_time = { 'EMPLOYEE_ID': SNOWJ,
                      'TRANSACTION_DATE': today, 
                      'TRANSACTION_ID': f,
                      'TYPE': I,
                      'INDIRECT_ID': OH,
                      'HOURS_WORKED': hours,
                      'GOOD_QTY': zero,
                      'BAD_QTY': zero,
                      'SHIFT_ID': null,
                      'SETUP_COMPLETED': N,
                      'PASS_FAIL': N,
                      'CREATE_DATE': today,
                      'REASON': null,
                      'USER_ID': SNOW,
                      'OPEN_OP': null,
        }    
    
    
         print add_time
    
         print data_time
    
         cursor.execute(add_time, data_time)
    
        connection.commit()
        cursor.close()  
        connection.close()
    

here is what add_time looks like when printed

    
    
     INSERT INTO TEST (EMPLOYEE_ID, TRANSACTION_DATE, TRANSACTION_ID, TYPE, INDIRECT_ID, HOURS_WORKED, GOOD_QTY, BAD_QTY, SHIFT_ID, SETUP_COMPLETED, PASS_FAIL, CREATE_DATE, REASON, USER_ID, OPEN_OP)VALUES (%(EMPLOYEE_ID)s, %(TRANSACTION_DATE)s, %(TRANSACTION_ID)s, %(TYPE)s, %(INDIRECT_ID)s, %(HOURS_WORKED)s, %(GOOD_QTY)s, %(BAD_QTY)s, %(SHIFT_ID)s, %(SETUP_COMPLETED)s, %(PASS_FAIL)s, %(CREATE_DATE)s, %(REASON)s, %(USER_ID)s, %(OPEN_OP)s)
    

here is what data_time looks like print

    
    
    {'CREATE_DATE': '2015-11-13', 'OPEN_OP': 'null', 'HOURS_WORKED': '4', 'REASON': 'null', 'TRANSACTION_DATE': '2015-11-13', 'INDIRECT_ID': 'OH', 'TRANSACTION_ID': 12816058, 'PASS_FAIL': 'N', 'USER_ID': 'SNOW', 'EMPLOYEE_ID': 'SNOWJ', 'SHIFT_ID': 'null', 'SETUP_COMPLETED': 'N', 'GOOD_QTY': 0, 'BAD_QTY': 0, 'TYPE': 'I'}
    

here is my table structure

    
    
    column_name                     data_type 
    
    EMPLOYEE_ID                     VARCHAR2(15 BYTE)            
    TRANSACTION_DATE                DATE               
    TRANSACTION_ID                  NUMBER(10,0)          
    TYPE                            CHAR(1 BYTE)           
    INDIRECT_ID                     VARCHAR2(15 BYTE)              
    HOURS_WORKED                    NUMBER(7,2)               
    GOOD_QTY                        NUMBER(14,4)           
    BAD_QTY                         NUMBER(14,4)        
    SHIFT_ID                        VARCHAR2(15 BYTE)               
    SETUP_COMPLETED                 CHAR(1 BYTE)            
    PASS_FAIL                       CHAR(1 BYTE)         
    CREATE_DATE                     DATE            
    REASON                          VARCHAR2(50 BYTE)              
    USER_ID                         VARCHAR2(15 BYTE)       
    OPEN_OP                         CHAR(1 BYTE)         
    


Answer: In Oracle odd things can happen when you use reserved key words.

I see you have a table column called TYPE which is a key word. You could try
the double quote trick like this

    
    
     INSERT INTO TEST (EMPLOYEE_ID, TRANSACTION_DATE, TRANSACTION_ID, "TYPE", ... 
    
    %(TYPE)s, %(INDIRECT_ID)s, %(HOURS_WORKED)s, %(GOOD_QTY)s, %(BAD_QTY)s, %(SHIFT_ID)s, %(SETUP_COMPLETED)s, %(PASS_FAIL)s, %(CREATE_DATE)s, %(REASON)s, %(USER_ID)s, %(OPEN_OP)s)
    

If you still get odd errors I suggest renaming the column TYPE to something
that is not a key word.

