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...
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.
