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

Wed, 29 Jul 2015 14:43:33 -0700 


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.



Please also test cx_Oracle 5.2 again with the latest master and/or 
rel_1_0 branch of SQLAlchemy - I've repaired an issue with cx_Oracle 
version detection for py3K that could fix this, will be in 1.0.9.






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 [email protected] 
<mailto:[email protected]>.
To post to this group, send email to [email protected] 
<mailto:[email protected]>.

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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.























					Reply via email to