How can I connect to an Azure SQL db from a Databricks notebook?

[yopbibo]

I know how to do it with spark, and read/write tables (like https://docs.microsoft.com/en-gb/azure/databricks/data/data-sources/sql-databases#python-example )

But this time, I need to only update a field of a specific row in a table. I do not think I can do that with spark jdbc (mode for append or overwrite)

Then, I tried to use jaydebeapi and pymssql, but no success.

jaydebeapi would: Class com.microsoft.sqlserver.jdbc.SQLServerDriver is not found

Also tried to use pyodbc, but failed to install mssql odbc driver.

what is the best practice for that usecase?

[yopbibo]

thanks for the link.

I am maybe wrong, but they describe how to connect with spark. They do not provide a connection engine that we could use directly (like with pyodbc) or an engine that we could use in pandas, for example.

[yopbibo]

thanks!

but here again, it goes through the spark interface.

Therefore, comes with spark limits. For example, when write, we can "overwrite" or "append".

What if we want to, only, update an existing row?

Could load all the table, update it in spark, and write it back. But if the table is large, and we need to do that in a very short time?

[yopbibo]

sure!

pip install jaydebeapi pymssql
 
import jaydebeapi
import pymssql
 
pymssql.connect(server='xxxxxxxxxxxxxxxxx.database.windows.net',
                       user='sqladmin@xxxxxxxxxxxxxxxxx',
                       password='1234',
                       database="yyyyyyyyyyyyyyyyy",
                       port='1433',
                      )
 
pymssql.connect(server='xxxxxxxxxxxxxxxxx.database.windows.net',
                       user='sqladmin',
                       password='1234',
                       database="yyyyyyyyyyyyyyyyy",
                       port='1433',
                      )
 
jaydebeapi.connect('com.microsoft.sqlserver.jdbc.SQLServerDriver',
                   'jdbc:sqlserver://xxxxxxxxxxxxxxxxx.database.windows.net:1433;database=yyyyyyyyyyyyyyyyy',
                   ['sqladmin', '1234'])

bellow, outputs:

OperationalError: (20009, b'DB-Lib error message 20009, severity 9:\nUnable to connect: Adaptive Server is unavailable or does not exist (xxxxxxxxxxx.database.windows.net)\nNet-Lib error during Connection refused (111)\nDB-Lib error message 20009, severity 9:\nUnable to connect: Adaptive Server is unavailable or does not exist (xxxxxxxxxxxxx.database.windows.net)\nNet-Lib error during Connection refused (111)\n')

same for the second call.

and last, for jaydebeapi

TypeError: Class com.microsoft.sqlserver.jdbc.SQLServerDriver is not found

[Abhiram_B]

Hi @yopbibo  - Did you find the answer that you were looking for? i.e. update individual rows in db tables?