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.
