How to INSERT INTO Azure SQL database from Azure Databricks in Python

Question

Since pyodbc cannot be installed to Azure databricks, I am trying to use jdbc to insert data into Azure SQL database by Python, but I can find sample code for that.

jdbcHostname = "xxxxxxx.database.windows.net"
jdbcDatabase = "yyyyyy"
jdbcPort = 1433
#jdbcUrl = "jdbc:sqlserver://{0}:{1};database={2};user={3};password={4}".format(jdbcHostname, jdbcPort, jdbcDatabase, username, password)

jdbcUrl = "jdbc:sqlserver://{0}:{1};database={2}".format(jdbcHostname, jdbcPort, jdbcDatabase)
connectionProperties = {
  "user" : jdbcUsername,
  "password" : jdbcPassword,
  "driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver"
}

pushdown_query = "(INSERT INTO test (a, b) VALUES ('val_a', 'val_b')) insert_test" 

Please advise how to write insertion code in Python. Thanks.

Answer 1

If I may add, you should also be able to use a Spark data frame to insert to Azure SQL. Just use the connection string you get from Azure SQL.

connectionString = "<Azure SQL Connection string>"

data = spark.createDataFrame([(val_a, val_b)], ["a", "b"])

data.write.jdbc(connectionString, "<TableName>", mode="append")