python - SQLAlchemy occasionally erroneously returns an empty result -

sqlalchemy v1.0.6  cx_oracle v5.2 

we've been having issue on our production code little while , narrowed down data coming sqlalchemy.

running same query multiple times return empty result. under conditions, can return empty result every time code executed. despite fact data in database hasn't changed @ , pure sql versions of same query running directly on cx_oracle return correct result.

here declarative code sqlalchemy:

class database:     def __init__(self, service_name, database, username, password):         """         service_name (str): service name defined in tnsnames.ora.         database (str): database within chosen service.         """         self.engine = create_engine(             r'oracle+cx_oracle://{username}:{password}@{service_name}'.format(username=username, password=password,                                                                               service_name=service_name),             case_sensitive=false)         self.session_maker = sessionmaker(bind=self.engine, autoflush=false, autocommit=false)          # database name must injected every table definition; why tables must procedurally generated.         self.base = declarative_base()  # base class database tables         self.build_tables(database)      def make_session(self):         """create read-only session database."""         def readonly_abort():             raise exception('writing prohibited; db read-only')          session = self.session_maker()         session.flush = readonly_abort         return session      def build_tables(self, database):         class lot(self.base):             __tablename__ = 'lot'             __table_args__ = {'schema': database}             lot_key = column(integer, primary_key=true)             lot_id = column(string, name='lot_id')          self.lot = lot 

and here test code:

def sqlalchemy_test():     db = dp_orm.database(service_name, database)     session = db.make_session()     cursor = session.query(db.lot)     results = cursor.first()     if results none:         raise exception  def cx_oracle_test():     import cx_oracle     import set_environment_variables     conn = cx_oracle.connection(username, password, service_name)     cursor = conn.cursor()      c = cursor.execute('select * {}.lot rownum <= 1'.format(database))     results = list(c)     if len(results) != 1:         raise exception 

the first function, sqlalchemy_test, error 50% of time. second function, cx_oracle_test, has not yet errored. here's what's interesting: problem disappears if introduce pause several seconds between cursor = session.query(db.lot) , results = cursor.first(). looks sort of timing issue.

any clue what's going on here?

edit: i've simplified code necessary create error. here is:

from sqlalchemy import create_engine, column, integer, string sqlalchemy.ext.declarative import declarative_base sqlalchemy.orm import sessionmaker  # fix environment variables import os try:     del os.environ['oracle_home'] except keyerror:     pass os.environ['tns_admin'] = r'c:\product\11.1.0\client_1\network\admin' os.environ['path'] = r'c:\product\11.1.0\client_1\bin;' + os.environ['path']  engine = create_engine(r'oracle+cx_oracle://{username}:{password}@{service_name}'.format(username='user', password='password', service_name='service')) session_maker = sessionmaker(bind=engine) base_class = declarative_base()  class lot(base_class):     __tablename__ = 'lot'     __table_args__ = {'schema': 'schema_name'}     lot_key = column(integer, primary_key=true)     lot_id = column(string)  session = session_maker() cursor = session.query(lot) result = cursor.first() if result none:     raise exception 

answer found: problem cx_oracle 5.2. reinstalling cx_oracle 5.1.3 resolved issue.