Unable to perform inner join of two SQL tables from python, getting communication link error/ connection write error

Question

I am deploying my model results in the database. I have read the data from SQL using pyodbc connection. Ran ML model in python and have predicted results 'ColA, ColB, ColC'. The main table already has these columns with values as NULL.

Main table:

|Number| ColA   | ColB   | ColC |
|:---- |:------:| :-----:|-----:|
| 123  |  NULL  | NULL   |NULL  |
| 456  | NULL   | NUL    |NULL  |

After model run, I have saved the predicted values in temp table.

Temp table:

|Number| ColA   | ColB   | ColC  |
|:---- |:------:| :-----:|-----: |
| 123  | India  |2-Jan-21|10:00AM|
| 456  | USA    |2-Jan-21|12:00AM|

Now I need to update the values of 'ColA, ColB, ColC' in Main table based on Number.

I tried below queries:

statement = '''
UPDATE Maintable
SET
     ColA= u.ColA,  
     ColB= u.ColB,
     ColC= u.ColC,
FROM Maintable AS t
INNER JOIN Temptable u ON t.Number=u.Number;
'''

This gives below errors:

OperationalError: ('08S01', '[08S01] [Microsoft][ODBC SQL Server Driver]Communication link failure (0) (SQLExecDirectW)')

Sometimes this error:

Error: ('01000', '[01000] [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionWrite (send()). (10054) (SQLExecDirectW); [01000] [Microsoft][ODBC SQL Server Driver][Shared Memory]General network error. Check your network documentation. (11)')

And if it runs successfully without errors, when I do select * from Maintable, the job runs for long and unable to view the table. Have to forcefully kill the job.

How to overcome this error? is there any other way to update the SQL data from python?

question from:https://stackoverflow.com/questions/66059758/unable-to-perform-inner-join-of-two-sql-tables-from-python-getting-communicatio

Answer 1

Waitting for answers