How do I write a pandas data frame from an Azure ML Jupyter notebook to an Azure SQL database?

Question

I'm working with some data in Azure ML, using Jupyter notebooks.

I've gathered the data from an Azure SQL database by setting the tables up as assets and then used Python to run some machine learning models and ouptut a simple pandas data frame of results.

I now need to write that results data frame back to the Azure SQL database.

I have the following code, but I don't know what to put for the driver - if someone can help, that would be great - otherwise, is there a better way of doing this?

import sqlalchemy
import pyodbc

data = [
    ['94732','John', 'Y', 'Seg1'],
    ['93045','Jane', 'N', 'Seg2']]

headers=['ID', 'name', 'Class', 'Segment']

testing_df = pd.DataFrame(data, columns=headers)
testing_df

server = 'server.windows.net '
database = 'database'
username = 'username'
password = 'pwd'
driver= '{ODBC Driver 17 for SQL Server}'

conn_str = f'DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password}'

conn = pyodbc.connect(conn_str)
table_name = 'ddo_datacove_output_test'

testing_df.to_sql(table_name, conn, index=False, if_exists='append')

Answer 1

I have the following code, but I don't know what to put for the driver - if someone can help, that would be great - otherwise, is there a better way of doing this?

Use the ODBC driver which is present in local machine and Jupyter notebook. If the driver version is 17 then you need to check whether it is present in local machine or not. To get driver versions check this document and install required ODBC accordingly.

Below code uses ODBC version 18 which is present in my local machine and as well as in jupyter notebook. To check the version of ODBC in jupyter use this command pyodbc.drivers()

import pandas as pd
import pyodbc
from sqlalchemy import create_engine

data = [
    ['94732', 'John', 'Y', 'Seg1'],
    ['93045', 'Jane', 'N', 'Seg2']
]

headers = ['ID', 'name', 'Class', 'Segment']

testing_df = pd.DataFrame(data, columns=headers)

server = '*****'
database = 'FirstDb'
username = 'admin91'
password = '*****'
driver = 'ODBC Driver 18 for SQL Server'

conn_str = f'DRIVER={{{driver}}};SERVER={server};DATABASE={database};UID={username};PWD={password}'

conn = pyodbc.connect(conn_str)

engine = create_engine(f'mssql+pyodbc:///?odbc_connect={conn_str}')

table_name = 'newTable'

testing_df.to_sql(table_name, engine, index=False, if_exists='append')

print("DataFrame successfully written to Azure SQL Database.")

Output:

DataFrame successfully written to Azure SQL Database.