class Database:
cls.db_conn = create_engine(
URL(user='xxx', password='xxx', account='xxx',
database='xxx', schema='xxx', warehouse='xxx',
role_name='xxx', ), pool_size=4,
max_overflow=8, pool_pre_ping=True)
Session = sessionmaker(bind=cls.db_conn, autocommit=True)
cls.session_maker = scoped_session(Session)
def get_session(self):
session = Database.session_maker()
return session
def execute_query(self, query, values=None, with_result=True):
session = None
result = "No result"
try:
session = self.get_session()
output = session.execute(query, values)
if with_result:
result = output.fetchall()
session.commit()
return result
except Exception as e:
session.rollback()
raise e
finally:
self.close_session(session)
def change(self, id, name, status, data):
query = "call extraction_process(:b_id, :na, :status_1, :data_source)"
values = {'b_id': id, 'na': name, 'status_1': status, 'data_source': data}
execute_query(query, values)
when select query is used to fetch some results it works fine but to insert any values using query its throwing stored procedure execution error. hope this is the right way to create session for snowflake if not help me new to this .Tried many ways to fix it. Is it because of session creation , connection or any other ? someone pl's help me.
this is the error i'm getting
Exception while executing this query call expraction_process(:batch_id, :job_name, :job_status, :data_source_id): (snowflake.connector.errors.ProgrammingError) 090232 (25000): Stored procedure execution error: Scoped transaction started in stored procedure is incomplete and it was rolled back.
[SQL: call expraction_process(%(batch_id)s, %(job_name)s, %(job_status)s, %(data_source_id)s, )]
[parameters: {'batch_id': 1, 'job_name': 'access', 'job_status': 'processid', 'data_source_id': 1, : 'h'}]
(Background on this error at: https://sqlalche.me/e/14/f405)
Traceback (most recent call last):
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
self.dialect.do_execute(
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
cursor.execute(statement, parameters)
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/snowflake/connector/cursor.py", line 789, in execute
Error.errorhandler_wrapper(self.connection, self, error_class, errvalue)
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/snowflake/connector/errors.py", line 273, in errorhandler_wrapper
handed_over = Error.hand_to_other_handler(
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/snowflake/connector/errors.py", line 328, in hand_to_other_handler
cursor.errorhandler(connection, cursor, error_class, error_value)
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/snowflake/connector/errors.py", line 207, in default_errorhandler
raise error_class(
snowflake.connector.errors.ProgrammingError: 090232 (25000): Stored procedure execution error: Scoped transaction started in stored procedure is incomplete and it was rolled back.
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/home/ONE/extraction-process-sevices1/application/modules/db_module/database.py", line 63, in execute_query
output = session.execute(query, values)
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1712, in execute
result = conn._execute_20(statement, params or {}, execution_options)
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_20
return meth(self, args_10style, kwargs_10style, execution_options)
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 333, in _execute_on_connection
return connection._execute_clauseelement(
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement
ret = self._execute_context(
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
self._handle_dbapi_exception(
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
util.raise_(
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
raise exception
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
self.dialect.do_execute(
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
cursor.execute(statement, parameters)
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/snowflake/connector/cursor.py", line 789, in execute
Error.errorhandler_wrapper(self.connection, self, error_class, errvalue)
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/snowflake/connector/errors.py", line 273, in errorhandler_wrapper
handed_over = Error.hand_to_other_handler(
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/snowflake/connector/errors.py", line 328, in hand_to_other_handler
cursor.errorhandler(connection, cursor, error_class, error_value)
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/snowflake/connector/errors.py", line 207, in default_errorhandler
raise error_class(
sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 090232 (25000): Stored procedure execution error: Scoped transaction started in stored procedure is incomplete and it was rolled back.
[SQL: call expraction_process(%(batch_id)s, %(job_name)s, %(job_status)s, %(data_source_id)s, )]
[parameters: {'batch_id': 1, 'job_name': 'access', 'job_status': 'processid', 'data_source_id': 1, : 'h'}]
(Background on this error at: https://sqlalche.me/e/14/f405)
Traceback (most recent call last):
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
self.dialect.do_execute(
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
cursor.execute(statement, parameters)
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/snowflake/connector/cursor.py", line 789, in execute
Error.errorhandler_wrapper(self.connection, self, error_class, errvalue)
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/snowflake/connector/errors.py", line 273, in errorhandler_wrapper
handed_over = Error.hand_to_other_handler(
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/snowflake/connector/errors.py", line 328, in hand_to_other_handler
cursor.errorhandler(connection, cursor, error_class, error_value)
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/snowflake/connector/errors.py", line 207, in default_errorhandler
raise error_class(snowflake.connector.errors.ProgrammingError: 090232 (25000): Stored procedure execution error: Scoped transaction started in stored procedure is incomplete and it was rolled back.
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/home/ONE/extraction-process-sevices1/test.py", line 10, in <module>
print(ob.change(1,"access","processid",1,"h"))
File "/home/ONE/extraction-process-sevices1/application/modules/db_module/db_calls.py", line 14, in change
self.database.execute_query(query, values)
File "/home/ONE/extraction-process-sevices1/application/modules/db_module/database.py", line 75, in execute_query
raise e
File "/home/ONE/extraction-process-sevices1/application/modules/db_module/database.py", line 63, in execute_query
output = session.execute(query, values)
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 1712, in execute
result = conn._execute_20(statement, params or {}, execution_options)
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_20
return meth(self, args_10style, kwargs_10style, execution_options)
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 333, in _execute_on_connection
return connection._execute_clauseelement(
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1572, in _execute_clauseelement
ret = self._execute_context(
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
self._handle_dbapi_exception(
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
util.raise_(
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
raise exception
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
self.dialect.do_execute(
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
cursor.execute(statement, parameters)
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/snowflake/connector/cursor.py", line 789, in execute
Error.errorhandler_wrapper(self.connection, self, error_class, errvalue)
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/snowflake/connector/errors.py", line 273, in errorhandler_wrapper
handed_over = Error.hand_to_other_handler(
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/snowflake/connector/errors.py", line 328, in hand_to_other_handler
cursor.errorhandler(connection, cursor, error_class, error_value)
File "/home/ONE/extraction-process-sevices1/venv/lib/python3.10/site-packages/snowflake/connector/errors.py", line 207, in default_errorhandler
raise error_class(
sqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 090232 (25000): Stored procedure execution error: Scoped transaction started in stored procedure is incomplete and it was rolled back.
[SQL: extraction_processbatch_id)s, %(job_name)s, %(job_status)s, %(source_id)s)]
[parameters: {'b_id': 1, 'na': 'access', 'status_1': 'processid', 'data_source': 1,}]
(Background on this error at: https://sqlalche.me/e/14/f405)