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.

Jon · Accepted Answer · 2019-04-01 11:11:24Z

3

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")

answered Apr 1, 2019 at 11:11

Jon

2,8141 gold badge23 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joy Wang · Accepted Answer · 2019-04-01 09:49:29Z

0

Since pyodbc cannot be installed to Azure databricks

Actually, it seems you could install pyodbc in databricks.

%sh    
apt-get -y install unixodbc-dev
/databricks/python/bin/pip install pyodbc

For more details, you could refer to this answer and this blog.

answered Apr 1, 2019 at 9:49

Joy Wang

42.4k3 gold badges38 silver badges64 bronze badges

1 Comment

sauceishere Over a year ago

@jay wang, Thanks, it worked, and additionally I needed to add '%sh curl packages.microsoft.com/keys/microsoft.asc | apt-key add - curl packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-release.list apt-get update ACCEPT_EULA=Y apt-get install msodbcsql17 apt-get -y install unixodbc-dev sudo apt-get install python3-pip -y pip3 install --upgrade pyodbc' on [link] (datathirst.net/blog/2018/10/12/…)

cromastro · Accepted Answer · 2019-10-23 23:43:44Z

0

Pigging backing on Jon ... This is what I used to write data from a Azure databricks dataframe to a Azure SQL Database:

    Hostname = "YOUR_SERVER.database.windows.net"
    Database = "YOUR_DB"
    port = 1433
    UN = 'YOUR_USERNAME'
    PW = 'YOUR_PASSWORD'
    Url = "jdbc:sqlserver://{0}:{1};database={2};user={3};password= {4}".format(Hostname, Port, Database, UN, PW)

   df.write.jdbc(Url, "schema.table", mode="append")

answered Oct 23, 2019 at 23:43

cromastro

3112 silver badges4 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related