ORA-01008: not all variables bound - but they are bound · Issue #41 · oracle/python-oracledb (original) (raw)

Traceback (most recent call last):
  File "/usr/local/lib/python3.9/wsgiref/handlers.py", line 137, in run
    self.result = application(self.environ, self.start_response)
  File "/usr/local/project_venv/lib/python3.9/site-packages/pyramid/router.py", line 270, in __call__
    response = self.execution_policy(environ, self)
  File "/usr/local/project_venv/lib/python3.9/site-packages/pyramid/router.py", line 276, in default_execution_policy
    return router.invoke_request(request)
  File "/usr/local/project_venv/lib/python3.9/site-packages/pyramid/router.py", line 245, in invoke_request
    response = handle_request(request)
  File "/usr/local/project_venv/lib/python3.9/site-packages/pyramid/tweens.py", line 43, in excview_tween
    response = _error_handler(request, exc)
  File "/usr/local/project_venv/lib/python3.9/site-packages/pyramid/tweens.py", line 17, in _error_handler
    reraise(*exc_info)
  File "/usr/local/project_venv/lib/python3.9/site-packages/pyramid/util.py", line 733, in reraise
    raise value
  File "/usr/local/project_venv/lib/python3.9/site-packages/pyramid/tweens.py", line 41, in excview_tween
    response = handler(request)
  File "/usr/local/project_venv/lib/python3.9/site-packages/pyramid/router.py", line 143, in handle_request
    response = _call_view(
  File "/usr/local/project_venv/lib/python3.9/site-packages/pyramid/view.py", line 674, in _call_view
    response = view_callable(context, request)
  File "/usr/local/project_venv/lib/python3.9/site-packages/pyramid/viewderivers.py", line 392, in viewresult_to_response
    result = view(context, request)
  File "/usr/local/project_venv/lib/python3.9/site-packages/pyramid/viewderivers.py", line 141, in _requestonly_view
    response = view(request)
  File "/opt/app/AppCardAPI/tests/test_shurgi_pyramid.py", line 27, in get_api
    promotion_data = request.oracle_dbsession.execute(
  File "<string>", line 2, in execute
  File "/usr/local/project_venv/lib/python3.9/site-packages/sqlalchemy/orm/session.py", line 1692, in execute
    result = conn._execute_20(statement, params or {}, execution_options)
  File "/usr/local/project_venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1631, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/usr/local/project_venv/lib/python3.9/site-packages/sqlalchemy/sql/elements.py", line 325, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/usr/local/project_venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1498, in _execute_clauseelement
    ret = self._execute_context(
  File "/usr/local/project_venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1862, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/project_venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 2043, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/project_venv/lib/python3.9/site-packages/sqlalchemy/util/compat.py", line 207, in raise_
    raise exception
  File "/usr/local/project_venv/lib/python3.9/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/project_venv/lib/python3.9/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/local/project_venv/lib/python3.9/site-packages/oracledb/cursor.py", line 378, in execute
    impl.execute(self)
  File "src/oracledb/impl/thin/cursor.pyx", line 122, in oracledb.thin_impl.ThinCursorImpl.execute
  File "src/oracledb/impl/thin/protocol.pyx", line 301, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 302, in oracledb.thin_impl.Protocol._process_single_message
  File "src/oracledb/impl/thin/protocol.pyx", line 295, in oracledb.thin_impl.Protocol._process_message
sqlalchemy.exc.DatabaseError: (oracledb.exceptions.DatabaseError) ORA-01008: not all variables bound

[SQL:
                SELECT description
                FROM pmt.promotions
                WHERE promotion_id = :promotion_id
            ]
[parameters: {'promotion_id': 8597}]

import sys
import oracledb
oracledb.version = "8.3.0"
sys.modules["cx_Oracle"] = oracledb
import cx_Oracle

from wsgiref.simple_server import make_server
import os
from pyramid.config import Configurator
from pyramid.response import Response
from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session, sessionmaker


def post_api(request):
    promotion_data = request.oracle_dbsession.execute(
        """      
                SELECT description
                FROM pmt.promotions
                WHERE promotion_id = :promotion_id
            """, {"promotion_id": request.matchdict["club_id"]}
    ).fetchone()
    request.oracle_dbsession.commit()
    return Response(status=204)

def get_api(request):
    promotion_data = request.oracle_dbsession.execute(
        """      
                SELECT description
                FROM pmt.promotions
                WHERE promotion_id = :promotion_id
            """, {"promotion_id": 8597}
    ).fetchone()
    request.oracle_dbsession.commit()
    return Response(status=200)


if __name__ == '__main__':
    # oracledb.init_oracle_client()
    with Configurator() as config:
        config.add_route('post_api', '/post_api/{club_id}')
        config.add_route('get_api', '/get_api')
        config.add_view(post_api, route_name='post_api')
        config.add_view(get_api, route_name='get_api')

        # Oracle connection
        engine = create_engine(
            f'oracle://{os.getenv("oracle_user")}:{os.getenv("oracle_pass")}@{os.getenv("oracle_host")}:{os.getenv("oracle_port")}/{os.getenv("oracle_db")}',
            convert_unicode=False, pool_recycle=10, pool_size=50, echo=True, echo_pool="debug"
        )
        oracle_session_factory = scoped_session(sessionmaker(bind=engine))
        config.add_request_method(
            lambda r: oracle_session_factory, "oracle_dbsession", reify=True
        )

        app = config.make_wsgi_app()
    server = make_server('0.0.0.0', 6333, app)
    print("--- start server")
    server.serve_forever()

def test_load():
    import requests
    for i in range(2):
        res_1 = requests.post("http://0.0.0.0:6333/post_api/123",)
        res_2 = requests.get("http://0.0.0.0:6333/get_api",)
        print(res_1.status_code, res_2.status_code, i)