Re: [sqlalchemy] Bizzarre insert behavior: NULL constraint violation with non-null value, column changes every run.

[Mike Bayer]

On 7/29/15 4:27 AM, Ladislav Lenart wrote:
Hello.

I don't know why but the problematic version uses bytes as keys in the params
dictionary (e.g. b'batch_id') whereas the working version uses strings (e.g.
'batch_id'). I am not a Python 3 expert but I think that the two types are
distinct and thus the search for a string fails. This would also explain why the
column in the error changes - because dictionaries are nondeterministic.
Whatever key is searched for first will become the culprit.

Note however that these are just my assumptions...

I was about to say that cx_Oracle 5.2 probably has some issue with the 
fact that we're encoding strings first, where as 5.1.3 did not. But I 
looked at the source, and saw that we are not *supposed* to be encoding 
to bytes on Py3k.   So this might identify the bug on our end, that we 
aren't detecting the cx_oracle version properly (we look for version > 
(5, 0) and set "accept unicode" across the board .. why isnt this 
happening here?)


https://bitbucket.org/zzzeek/sqlalchemy/issues/3491/queryfirst-sometimes-returns-none 
is reopened to look into this.






HTH,

L.


On 28.7.2015 18:08, Bob Ternosky wrote:
That did it.

2015-07-28 12:06:16,283 INFO sqlalchemy.engine.base.Engine INSERT INTO
corp.test_table (batch_id, batch_type, scheduled_date, status, emp_id, bill_per,
label, log_file, debug_file, completed_date) VALUES (:batch_id, :batch_type,
:scheduled_date, :status, :emp_id, :bill_per, :label, :log_file, :debug_file,
:completed_date)
2015-07-28 12:06:16,283 INFO sqlalchemy.engine.base.Engine {'log_file': None,
'batch_id': 999999, 'debug_file': None, 'scheduled_date':
datetime.datetime(2015, 7, 28, 12, 6, 16, 282779), 'label': 'Testing insert',
'batch_type': 1, 'bill_per': 201501, 'status': 0, 'completed_date': None,
'emp_id': 8329}
2015-07-28 12:06:16,311 INFO sqlalchemy.engine.base.Engine COMMIT

You just saved my sanity.

Thanks a million!

And thanks to Jonathan Vanasco, your suggestions didn't work, but I learned a
few new settings.

On Tuesday, July 28, 2015 at 11:54:14 AM UTC-4, Michael Bayer wrote:

     Just curious, can you try out cx_Oracle 5.1.3?   I've seen some problems
     reported with Py3K and cx_Oracle 5.2.

     On 7/28/15 11:17 AM, Bob Ternosky wrote:
     I'm new to SQLAlchemy and have been playing with it for a week. I've got
     many SELECT based pieces working and exercising properly, but have hit a
     huge wall when trying to test inserts. Worse, what's happening makes no
     sense at all.
     This will hopefully contain the full set of information needed. Any help
     would be greatly appreciated.

     The summary: When running a simple test insert it will fail with the error:

        cx_Oracle.IntegrityError: ORA-01400: cannot insert NULL into
     ("CORP"."TEST_TABLE"."XXX")

     where XXX changes just about every time I run the insert statement - with
     identical data.

     My machine is running Linux Mint 17.2 64-bit

     Software:
     * Python 3.4.0 (with virtualenv)
     * SQLAlchemy 1.0.7
     * cx_Oracle 5.2

     Connecting to an Oracle 9i (9.2.0.6.0) database (joys of legacy systems).
     Using Oracle Instant Client 11.2.0 libraries

     An Oracle "description" of the table (the table is empty):

     > desc test_table
     BATCH_ID       NOT NULL NUMBER(10,0)
     BATCH_TYPE     NOT NULL NUMBER(2,0)
     SCHEDULED_DATE NOT NULL DATE
     STATUS         NOT NULL NUMBER(1,0)
     EMP_ID         NOT NULL NUMBER(10,0)
     BILL_PER       NOT NULL NUMBER(6,0)
     LABEL          NOT NULL VARCHAR2(128)
     LOG_FILE       NULL     VARCHAR2(256)
     DEBUG_FILE     NULL     VARCHAR2(256)
     COMPLETED_DATE NULL     DATE

     The table resides in the "CORP" schema.



     Test script named: isolated.py

     ----
     # isolated.py
     import argparse
     import datetime

     from sqlalchemy import Column, create_engine, DateTime, insert, MetaData,
     Numeric, String, Table

     # Declare insert test table
     metadata = MetaData()
     t_test_table = Table(
         'test_table', metadata,
         Column('batch_id', Numeric(9, 0, asdecimal=False), primary_key=True),
         Column('batch_type', Numeric(2, 0, asdecimal=False), nullable=False),
         Column('scheduled_date', DateTime, nullable=False),
         Column('status', Numeric(1, 0, asdecimal=False), nullable=False),
         Column('emp_id', Numeric(10, 0, asdecimal=False), nullable=False),
         Column('bill_per', Numeric(6, 0, asdecimal=False), nullable=False),
         Column('label', String(128), nullable=False),
         Column('log_file', String(256)),
         Column('debug_file', String(256)),
         Column('completed_date', DateTime),
         schema='corp'
     )

     # Oracle Credentials
     USER   = 'REDACTED'
     PASSWD = 'REDACTED'
     SID    = 'REDACTED'

     ###################
     # Main
     ###################
     parser = argparse.ArgumentParser(description = 'Test SQLAlchemy Insert')
     parser.add_argument('-c', '--cxoracle', dest = 'cxoracle', action =
     'store_true',
                         required = False, default = False, help = 'Use
     oracle+cx_oracle engine')
     parser.add_argument('-o', '--oracle', dest = 'oracle', action = 
'store_true',
                         required = False, default = False, help = 'Use oracle
     only engine')

     args = parser.parse_args()
     if not args.cxoracle and not args.oracle:
         parser.error("You must provide one of: [-c] [-o]")

     # Pick an Oracle connection method
     if args.cxoracle:
         LIBRARY = 'oracle+cx_oracle'
     else:
         LIBRARY = 'oracle'

     engine = create_engine('{}://{}:{}@{}'.format(LIBRARY, USER, PASSWD, SID),
     echo = True)
     conn = engine.connect()

     values = dict(batch_id       = 999999,
                   batch_type     = 1,
                   scheduled_date = datetime.datetime.now(),
                   status         = 0,
                   emp_id         = 8329,
                   bill_per       = 201501,
                   label          = "Testing insert",
                   log_file       = None,
                   debug_file     = None,
                   completed_date = None)

     tbl = t_test_table
     ins = tbl.insert().values(values)
     result = conn.execute(ins)
     print(result)

     # Cleanup
     conn.close()
     engine.dispose()


     -----

     I tried to isolate cx_Oracle vs basic Oracle, but no luck. If I run the
     script (with either -c or -o) I get the error message about the NULL
     violation. If I run it twice in a row, I get the same message, but the
     column changes. I've gotten batch_id, batch_type, status, emp_id,
     scheduled_date.

     I've added some debug output to SQLAlchemy's "do_execute" method in
     "python3.4/site-packages/sqlalchemy/engine/default.py:

        def do_execute(self, cursor, statement, parameters, context=None):
     +        print("*" *70)
     +       print(cursor)
     +        print(statement)
     +       print(parameters)
     +        print(context)
     +        print("*" *70)
             cursor.execute(statement, parameters)


     Here is the output of 2 consecutive runs against an empty table:


     (venv)[bash]$ python isolated.py -c
     2015-07-28 11:12:40,599 INFO sqlalchemy.engine.base.Engine b'SELECT USER
     FROM DUAL'
     2015-07-28 11:12:40,599 INFO sqlalchemy.engine.base.Engine {}
     **********************************************************************
     <cx_Oracle.Cursor on <cx_Oracle.Connection to USER@DB>>
     b'SELECT USER FROM DUAL'
     {}
     <sqlalchemy.dialects.oracle.cx_oracle.OracleExecutionContext_cx_oracle
     object at 0x7f6dda491a90>
     **********************************************************************
     2015-07-28 11:12:40,654 INFO sqlalchemy.engine.base.Engine SELECT
     CAST('test plain returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM DUAL
     2015-07-28 11:12:40,654 INFO sqlalchemy.engine.base.Engine {}
     **********************************************************************
     <cx_Oracle.Cursor on <cx_Oracle.Connection to USER@DB>>
     SELECT CAST('test plain returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM DUAL
     {}
     None
     **********************************************************************
     2015-07-28 11:12:40,708 INFO sqlalchemy.engine.base.Engine SELECT
     CAST('test unicode returns' AS NVARCHAR2(60)) AS anon_1 FROM DUAL
     2015-07-28 11:12:40,708 INFO sqlalchemy.engine.base.Engine {}
     **********************************************************************
     <cx_Oracle.Cursor on <cx_Oracle.Connection to USER@DB>>
     SELECT CAST('test unicode returns' AS NVARCHAR2(60)) AS anon_1 FROM DUAL
     {}
     None
     **********************************************************************
     2015-07-28 11:12:40,844 INFO sqlalchemy.engine.base.Engine b'INSERT INTO
     corp.test_table (batch_id, batch_type, scheduled_date, status, emp_id,
     bill_per, label, log_file, debug_file, completed_date) VALUES (:batch_id,
     :batch_type, :scheduled_date, :status, :emp_id, :bill_per, :label,
     :log_file, :debug_file, :completed_date)'
     2015-07-28 11:12:40,844 INFO sqlalchemy.engine.base.Engine {b'bill_per':
     201501, b'batch_type': 1, b'status': 0, b'scheduled_date':
     datetime.datetime(2015, 7, 28, 11, 12, 40, 843806), b'debug_file': None,
     b'log_file': None, b'label': 'Testing insert', b'completed_date': None,
     b'emp_id': 8329, b'batch_id': 999999}
     **********************************************************************
     <cx_Oracle.Cursor on <cx_Oracle.Connection to USER@DB>>
     b'INSERT INTO corp.test_table (batch_id, batch_type, scheduled_date,
     status, emp_id, bill_per, label, log_file, debug_file, completed_date)
     VALUES (:batch_id, :batch_type, :scheduled_date, :status, :emp_id,
     :bill_per, :label, :log_file, :debug_file, :completed_date)'
     {b'bill_per': 201501, b'batch_type': 1, b'status': 0, b'scheduled_date':
     datetime.datetime(2015, 7, 28, 11, 12, 40, 843806), b'debug_file': None,
     b'log_file': None, b'label': 'Testing insert', b'completed_date': None,
     b'emp_id': 8329, b'batch_id': 999999}
     <sqlalchemy.dialects.oracle.cx_oracle.OracleExecutionContext_cx_oracle
     object at 0x7f6dd188e3c8>
     **********************************************************************
     2015-07-28 11:12:40,900 INFO sqlalchemy.engine.base.Engine ROLLBACK
     Traceback (most recent call last):
       File
     
"/home/rternosky/src/git/pluto/venv/lib/python3.4/site-packages/sqlalchemy/engine/base.py",
     line 1139, in _execute_context
         context)
       File
     
"/home/rternosky/src/git/pluto/venv/lib/python3.4/site-packages/sqlalchemy/engine/default.py",
     line 456, in do_execute
         cursor.execute(statement, parameters)
     cx_Oracle.IntegrityError: ORA-01400: cannot insert NULL into
     ("CORP"."TEST_TABLE"."BATCH_ID")


     The above exception was the direct cause of the following exception:

     Traceback (most recent call last):
       File "isolated.py", line 68, in <module>
         result = conn.execute(ins)
       File
     
"/home/rternosky/src/git/pluto/venv/lib/python3.4/site-packages/sqlalchemy/engine/base.py",
     line 914, in execute
         return meth(self, multiparams, params)
       File
     
"/home/rternosky/src/git/pluto/venv/lib/python3.4/site-packages/sqlalchemy/sql/elements.py",
     line 323, in _execute_on_connection
         return connection._execute_clauseelement(self, multiparams, params)
       File
     
"/home/rternosky/src/git/pluto/venv/lib/python3.4/site-packages/sqlalchemy/engine/base.py",
     line 1010, in _execute_clauseelement
         compiled_sql, distilled_params
       File
     
"/home/rternosky/src/git/pluto/venv/lib/python3.4/site-packages/sqlalchemy/engine/base.py",
     line 1146, in _execute_context
         context)
       File
     
"/home/rternosky/src/git/pluto/venv/lib/python3.4/site-packages/sqlalchemy/engine/base.py",
     line 1341, in _handle_dbapi_exception
         exc_info
       File
     
"/home/rternosky/src/git/pluto/venv/lib/python3.4/site-packages/sqlalchemy/util/compat.py",
     line 188, in raise_from_cause
         reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
       File
     
"/home/rternosky/src/git/pluto/venv/lib/python3.4/site-packages/sqlalchemy/util/compat.py",
     line 181, in reraise
         raise value.with_traceback(tb)
       File
     
"/home/rternosky/src/git/pluto/venv/lib/python3.4/site-packages/sqlalchemy/engine/base.py",
     line 1139, in _execute_context
         context)
       File
     
"/home/rternosky/src/git/pluto/venv/lib/python3.4/site-packages/sqlalchemy/engine/default.py",
     line 456, in do_execute
         cursor.execute(statement, parameters)
     sqlalchemy.exc.IntegrityError: (cx_Oracle.IntegrityError) ORA-01400:
     cannot insert NULL into ("CORP"."TEST_TABLE"."BATCH_ID")
      [SQL: b'INSERT INTO corp.test_table (batch_id, batch_type,
     scheduled_date, status, emp_id, bill_per, label, log_file, debug_file,
     completed_date) VALUES (:batch_id, :batch_type, :scheduled_date, :status,
     :emp_id, :bill_per, :label, :log_file, :debug_file, :completed_date)']
     [parameters: {b'bill_per': 201501, b'batch_type': 1, b'status': 0,
     b'scheduled_date': datetime.datetime(2015, 7, 28, 11, 12, 40, 843806),
     b'debug_file': None, b'log_file': None, b'label': 'Testing insert',
     b'completed_date': None, b'emp_id': 8329, b'batch_id': 999999}]



     (venv)[bash]$ python isolated.py -c
     2015-07-28 11:12:43,349 INFO sqlalchemy.engine.base.Engine b'SELECT USER
     FROM DUAL'
     2015-07-28 11:12:43,350 INFO sqlalchemy.engine.base.Engine {}
     **********************************************************************
     <cx_Oracle.Cursor on <cx_Oracle.Connection to USER@DB>>
     b'SELECT USER FROM DUAL'
     {}
     <sqlalchemy.dialects.oracle.cx_oracle.OracleExecutionContext_cx_oracle
     object at 0x7f726fa76ac8>
     **********************************************************************
     2015-07-28 11:12:43,405 INFO sqlalchemy.engine.base.Engine SELECT
     CAST('test plain returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM DUAL
     2015-07-28 11:12:43,405 INFO sqlalchemy.engine.base.Engine {}
     **********************************************************************
     <cx_Oracle.Cursor on <cx_Oracle.Connection to USER@DB>>
     SELECT CAST('test plain returns' AS VARCHAR(60 CHAR)) AS anon_1 FROM DUAL
     {}
     None
     **********************************************************************
     2015-07-28 11:12:43,459 INFO sqlalchemy.engine.base.Engine SELECT
     CAST('test unicode returns' AS NVARCHAR2(60)) AS anon_1 FROM DUAL
     2015-07-28 11:12:43,459 INFO sqlalchemy.engine.base.Engine {}
     **********************************************************************
     <cx_Oracle.Cursor on <cx_Oracle.Connection to USER@DB>>
     SELECT CAST('test unicode returns' AS NVARCHAR2(60)) AS anon_1 FROM DUAL
     {}
     None
     **********************************************************************
     2015-07-28 11:12:43,614 INFO sqlalchemy.engine.base.Engine b'INSERT INTO
     corp.test_table (batch_id, batch_type, scheduled_date, status, emp_id,
     bill_per, label, log_file, debug_file, completed_date) VALUES (:batch_id,
     :batch_type, :scheduled_date, :status, :emp_id, :bill_per, :label,
     :log_file, :debug_file, :completed_date)'
     2015-07-28 11:12:43,614 INFO sqlalchemy.engine.base.Engine {b'log_file':
     None, b'status': 0, b'batch_type': 1, b'completed_date': None, b'emp_id':
     8329, b'scheduled_date': datetime.datetime(2015, 7, 28, 11, 12, 43,
     613744), b'label': 'Testing insert', b'debug_file': None, b'bill_per':
     201501, b'batch_id': 999999}
     **********************************************************************
     <cx_Oracle.Cursor on <cx_Oracle.Connection to USER@DB>>
     b'INSERT INTO corp.test_table (batch_id, batch_type, scheduled_date,
     status, emp_id, bill_per, label, log_file, debug_file, completed_date)
     VALUES (:batch_id, :batch_type, :scheduled_date, :status, :emp_id,
     :bill_per, :label, :log_file, :debug_file, :completed_date)'
     {b'log_file': None, b'status': 0, b'batch_type': 1, b'completed_date':
     None, b'emp_id': 8329, b'scheduled_date': datetime.datetime(2015, 7, 28,
     11, 12, 43, 613744), b'label': 'Testing insert', b'debug_file': None,
     b'bill_per': 201501, b'batch_id': 999999}
     <sqlalchemy.dialects.oracle.cx_oracle.OracleExecutionContext_cx_oracle
     object at 0x7f7266e733c8>
     **********************************************************************
     2015-07-28 11:12:43,669 INFO sqlalchemy.engine.base.Engine ROLLBACK
     Traceback (most recent call last):
       File
     
"/home/rternosky/src/git/pluto/venv/lib/python3.4/site-packages/sqlalchemy/engine/base.py",
     line 1139, in _execute_context
         context)
       File
     
"/home/rternosky/src/git/pluto/venv/lib/python3.4/site-packages/sqlalchemy/engine/default.py",
     line 456, in do_execute
         cursor.execute(statement, parameters)
     cx_Oracle.IntegrityError: ORA-01400: cannot insert NULL into
     ("CORP"."TEST_TABLE"."BILL_PER")


     The above exception was the direct cause of the following exception:

     Traceback (most recent call last):
       File "isolated.py", line 68, in <module>
         result = conn.execute(ins)
       File
     
"/home/rternosky/src/git/pluto/venv/lib/python3.4/site-packages/sqlalchemy/engine/base.py",
     line 914, in execute
         return meth(self, multiparams, params)
       File
     
"/home/rternosky/src/git/pluto/venv/lib/python3.4/site-packages/sqlalchemy/sql/elements.py",
     line 323, in _execute_on_connection
         return connection._execute_clauseelement(self, multiparams, params)
       File
     
"/home/rternosky/src/git/pluto/venv/lib/python3.4/site-packages/sqlalchemy/engine/base.py",
     line 1010, in _execute_clauseelement
         compiled_sql, distilled_params
       File
     
"/home/rternosky/src/git/pluto/venv/lib/python3.4/site-packages/sqlalchemy/engine/base.py",
     line 1146, in _execute_context
         context)
       File
     
"/home/rternosky/src/git/pluto/venv/lib/python3.4/site-packages/sqlalchemy/engine/base.py",
     line 1341, in _handle_dbapi_exception
         exc_info
       File
     
"/home/rternosky/src/git/pluto/venv/lib/python3.4/site-packages/sqlalchemy/util/compat.py",
     line 188, in raise_from_cause
         reraise(type(exception), exception, tb=exc_tb, cause=exc_value)
       File
     
"/home/rternosky/src/git/pluto/venv/lib/python3.4/site-packages/sqlalchemy/util/compat.py",
     line 181, in reraise
         raise value.with_traceback(tb)
       File
     
"/home/rternosky/src/git/pluto/venv/lib/python3.4/site-packages/sqlalchemy/engine/base.py",
     line 1139, in _execute_context
         context)
       File
     
"/home/rternosky/src/git/pluto/venv/lib/python3.4/site-packages/sqlalchemy/engine/default.py",
     line 456, in do_execute
         cursor.execute(statement, parameters)
     sqlalchemy.exc.IntegrityError: (cx_Oracle.IntegrityError) ORA-01400:
     cannot insert NULL into ("CORP"."TEST_TABLE"."BILL_PER")
      [SQL: b'INSERT INTO corp.test_table (batch_id, batch_type,
     scheduled_date, status, emp_id, bill_per, label, log_file, debug_file,
     completed_date) VALUES (:batch_id, :batch_type, :scheduled_date, :status,
     :emp_id, :bill_per, :label, :log_file, :debug_file, :completed_date)']
     [parameters: {b'log_file': None, b'status': 0, b'batch_type': 1,
     b'completed_date': None, b'emp_id': 8329, b'scheduled_date':
     datetime.datetime(2015, 7, 28, 11, 12, 43, 613744), b'label': 'Testing
     insert', b'debug_file': None, b'bill_per': 201501, b'batch_id': 999999}]


     --
     You received this message because you are subscribed to the Google Groups
     "sqlalchemy" group.
     To unsubscribe from this group and stop receiving emails from it, send an
     email to sqlalchemy+...@googlegroups.com <javascript:>.
     To post to this group, send email to sqlal...@googlegroups.com 
<javascript:>.
     Visit this group at http://groups.google.com/group/sqlalchemy.
     For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.