How can we load data from a data frame to Azure SQL Server?

Question

I am trying, for the first time ever, to send data from a data frame in Spyder to Azure SQL Server...I think it's called Synapse. I created a small table in the database and when I run the code below, I see the results I expect to see.

import pyodbc
server = 'ryan-server.database.windows.net' 
database = 'ryan_sql_db'
username = 'UN'
password = 'PW'   
driver= '{ODBC Driver 17 for SQL Server}'

with pyodbc.connect('DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password) as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT * From Order_Table")
        row = cursor.fetchone()
        while row:
            print (str(row[0]) + " " + str(row[1]))
            row = cursor.fetchone()
            

So, the connection is fine. I guess I am just stuck on the syntax to push a data frame to SQL Server in Azure. I tested the code below.

import pyodbc
server = 'ryan-server.database.windows.net' 
database = 'ryan_sql_db'
username = 'UN'
password = 'PW'   
driver= '{ODBC Driver 17 for SQL Server}'
conn = pyodbc.connect('DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)
all_data.to_sql('health', conn, if_exists='replace', index=True)

When I run that code, I get this error.

DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': ('42S02', "[42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'sqlite_master'. (208) (SQLExecDirectW); [42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)")

Answer 1

Try to do it by importing Pandas and pyodbc.

Below are few basic steps which we follow usually:

1. Connect to SQL Server.

2. Install all your python packages in your local.

3. Load the data into CSV.

4. Later you can use below Python script to load it from dataframe.

    import pyodbc
    import pandas as pd
   
    df = pd.read_csv("c:\\user\\username\department.csv")
   
    server = 'yourservername' 
    database = 'AdventureWorks' 
    username = 'username' 
    password = 'yourpassword' 
    cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
    cursor = cnxn.cursor()
    #Insert Dataframe into SQL Server:
    for index, row in df.iterrows():
         cursor.execute("INSERT INTO HumanResources.DepartmentTest (DepartmentID,Name,GroupName) values(?,?,?)", row.DepartmentID, row.Name, row.GroupName)
    cnxn.commit()
    cursor.close() 
   

5. After completing the configs you can run below command to get the data from SQL:

    SELECT count(*) from HumanResources.DepartmentTest;
   

Refer to this official doc for detailed explanation.