pyodbc.OperationalError TCP Provider: Error code 0x2746 & TCP Provider: Error code 0x20 (32)

Question

Hello I have an issue using pyodbc, i am trying to make an update in a, SQL DB. I have a test set in which the update is supposed to be called 3 times.

the first time is runnnig without issue without any problem, then the second one i have this error message :

pyodbc.OperationalError: ('08S01', '[08S01] [Microsoft][ODBC Driver 13 for SQL Server]TCP Provider: Error code 0x2746 (10054) (SQLExecDirectW)')

and on the third one i have this message :

pyodbc.OperationalError: ('08S01', '[08S01] [Microsoft][ODBC Driver 13 for SQL Server]TCP Provider: Error code 0x20 (32) (SQLExecDirectW)')

i tried to search those error codes and it seems related to database connectivity but i am pretty sure that my DB is alright and well connected to my program.

here is how i update :

def depreciate_hi_by_id_data_file(self,id_data_file):
    str_query = f"""UPDATE HMOQTHEI SET HEI_IS_DEPRECIATED = 1 WHERE HEI_HEALTH_INDICATOR_ID IN (SELECT HMOQTHEI.HEI_HEALTH_INDICATOR_ID FROM HMOQTDAF INNER JOIN HMOQTHEI ON HMOQTDAF.DAF_DATA_FILE_ID = HMOQTHEI.HEI_DATA_FILE_ID WHERE (HMOQTHEI.HEI_IS_DEPRECIATED = 0 AND HMOQTDAF.DAF_FILE_NAME = '{id_data_file}' ))"""
    self.logger.info('MARKING HI FOR DEPRECIATION')
    try:
        self.database.execute_update_query(str_query)
        self.logger.info('HI SUCCESSFULLY DEPRECIATED')
    except Exception as e:
        self.logger.exception('HI DEPRECIATION FAILED')

def execute_update_query(self, str_query: str):
    self.logger.debug(f'DEBUG | Execute query : {str_query}')
    cursor = self.connection.cursor()
    cursor.execute(str_query)
    self.connection.commit()
    cursor.close()

The thing i find weird is that for 3 calls to this functions i have 3 different behaviours. I also tried to run my sql request in a zeppelin note book and it works :

%python

from pandas import read_sql_query
from pyodbc import connect

#-HMO011-HMO011-HMO011-HMO011-HMO011-HMO011-HMO011-HMO011
str_driver = "xxxx"
str_server = "xxxx"
str_database = "xxxx"
str_username = "xxxx"
str_password = "xxxx"


str_connection = 'DRIVER=' + str_driver + ';SERVER=' + str_server + ';DATABASE=' + str_database + ';UID=' + str_username + ';MARS_Connection=Yes' + ';PWD=' + str_password
connection = connect(str_connection)
cursor = connection.cursor()
id_data_file = "001_306_53_20201201102042.json"
str_query = f"""UPDATE HMOQTHEI SET HEI_IS_DEPRECIATED = 0 WHERE HEI_HEALTH_INDICATOR_ID IN (SELECT HMOQTHEI.HEI_HEALTH_INDICATOR_ID FROM HMOQTDAF INNER JOIN HMOQTHEI ON HMOQTDAF.DAF_DATA_FILE_ID = HMOQTHEI.HEI_DATA_FILE_ID WHERE (HMOQTHEI.HEI_IS_DEPRECIATED = 1 AND HMOQTDAF.DAF_FILE_NAME = '{id_data_file}' ))"""

cursor.execute(str_query)
connection.commit()
cursor.close()
connection.close()

Answer 1

Ok so i've finally found what was wrong : i had declared my DBMANAGER in the attribute of a class which launch the function in multiprocess so, the 3 request were in conflict with each other, so i've redefined my DBMANAGER within each process and now everything is fine

CONCLUSION : Beware of multiprocessed access to database