I am performing an insert statment through python SQLALCHEMY. created a base model for a database table. In Oracle, I've created a sequence like shown below
CREATE SEQUENCE XXBRIM.XXBRIM_HEADER_INTER_ID_SEQUENCE START WITH 60001 MAXVALUE 9999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 10000 NOORDER NOKEEP GLOBAL; Used the above sequence in Trigger shown below
CREATE OR REPLACE TRIGGER XXBRIM.XXBRIM_HEADER_ID_INCREAMENT BEFORE INSERT OR UPDATE ON XXBRIM.XXBRIM_SUBHEADER_INTERMEDIA_T FOR EACH ROW BEGIN insert into dummy_table (col1) values(1); IF :NEW.ID_PK IS NULL THEN SELECT XXBRIM_header_inter_id_SEQUENCE.NEXTVAL INTO :NEW.ID_PK FROM DUAL; END IF; END; In python, using SQLAlchemy created a base model and used the sequence. part of the code is give below:
class HeaderIntermediate(Base): __tablename__ = 'XXBRIM_SUBHEADER_INTERMEDIA_T' ID_PK = Column(Integer, Sequence('XXBRIM.XXBRIM_header_inter_id_SEQUENCE')) tried to insert using the below code in python:
session.execute( HeaderIntermediate.__table__.insert(), [new_ls] (new_ls is a dictionary with column name is key and value to be inserted as value)
I get the below error:
nsert exec had an exception: (cx_Oracle.DatabaseError) ORA-02289: sequence does not exist [SQL: INSERT INTO "XXBRIM_SUBHEADER_INTERMEDIA_T" ("ID_PK") VALUES ("XXBRIM.XXBRIM.XXBRIM_HEADER_ID_INCREAMENT".nextval) RETURNING "XXBRIM_SUBHEADER_INTERMEDIA_T"."ID_PK" INTO :ret_0] [parameters: {'ret_0': <cx_Oracle.Var of type DB_TYPE_VARCHAR with value [[], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], []]>}] (Background on this error at: http://sqlalche.me/e/14/4xp6) Traceback (most recent call last): File "/Users/risav/.conda/envs/json_parser_ver1/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1770, in _execute_context self.dialect.do_execute( File "/Users/risav/.conda/envs/json_parser_ver1/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute cursor.execute(statement, parameters) cx_Oracle.DatabaseError: ORA-02289: sequence does not exist The above exception was the direct cause of the following exception: Traceback (most recent call last): File "/Users/risav/Desktop/BRIM_INTEGRATION/json_parser_final_version_1/engine/runtime/parse_json_version1.py", line 105, in process_to_db sentence = batch_insert(table_name, row_deduplicate,session) File "/Users/risav/Desktop/BRIM_INTEGRATION/json_parser_final_version_1/engine/runtime/db_utils.py", line 72, in batch_insert session.execute( File "/Users/risav/.conda/envs/json_parser_ver1/lib/python3.8/site-packages/sqlalchemy/orm/session.py", line 1689, in execute result = conn._execute_20(statement, params or {}, execution_options) File "/Users/risav/.conda/envs/json_parser_ver1/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1582, in _execute_20 return meth(self, args_10style, kwargs_10style, execution_options) File "/Users/risav/.conda/envs/json_parser_ver1/lib/python3.8/site-packages/sqlalchemy/sql/elements.py", line 323, in _execute_on_connection return connection._execute_clauseelement( File "/Users/risav/.conda/envs/json_parser_ver1/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1451, in _execute_clauseelement ret = self._execute_context( File "/Users/risav/.conda/envs/json_parser_ver1/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1813, in _execute_context self._handle_dbapi_exception( File "/Users/risav/.conda/envs/json_parser_ver1/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1994, in _handle_dbapi_exception util.raise_( File "/Users/risav/.conda/envs/json_parser_ver1/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 207, in raise_ raise exception File "/Users/risav/.conda/envs/json_parser_ver1/lib/python3.8/site-packages/sqlalchemy/engine/base.py", line 1770, in _execute_context self.dialect.do_execute( File "/Users/risav/.conda/envs/json_parser_ver1/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 717, in do_execute cursor.execute(statement, parameters) sqlalchemy.exc.DatabaseError: (cx_Oracle.DatabaseError) ORA-02289: sequence does not exist [SQL: INSERT INTO "XXBRIM_SUBHEADER_INTERMEDIA_T" ("ID_PK") VALUES ("XXBRIM.XXBRIM.XXBRIM_HEADER_ID_INCREAMENT".nextval) RETURNING "XXBRIM_SUBHEADER_INTERMEDIA_T"."ID_PK" INTO :ret_0] [parameters: {'ret_0': <cx_Oracle.Var of type DB_TYPE_VARCHAR with value [[], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], [], []]>}] (Background on this error at: http://sqlalche.me/e/14/4xp6) [10102] But I created and commited the sequence also. Dont know what I am missing
