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