Insert Python dataframe into SQL table

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

This article describes how to insert a pandas dataframe into a SQL database using the mssql-python driver in Python.

Prerequisites

• Azure Data Studio. To install, see Download and install Azure Data Studio.

• Follow the steps in AdventureWorks sample databases to restore the OLTP version of the AdventureWorks sample database for your version of SQL Server.

  You can verify that the database was restored correctly by querying the HumanResources.Department table:

  USE AdventureWorks;
  SELECT * FROM HumanResources.Department;
  

Install Python packages

Install mssql-python

pip install mssql-python

apk add libtool krb5-libs krb5-dev
pip install mssql-python

apt-get install -y libltdl7 libkrb5-3 libgssapi-krb5-2
pip install mssql-python

dnf install -y libtool-ltdl krb5-libs
pip install mssql-python

zypper install -y libltdl7 libkrb5-3 libgssapi-krb5-2
pip install mssql-python

zypper install -y libltdl7
pip install mssql-python

brew install openssl
pip install mssql-python

Install other packages

1. In Azure Data Studio, open a new notebook and connect to the Python 3 kernel.

2. Select Manage Packages.

   

3. In the Manage Packages pane, select the Add new tab.

4. Enter pandas, select Search, then select Install.

Create a sample CSV file

Copy the following text and save it to a file named department.csv.

DepartmentID,Name,GroupName,
1,Engineering,Research and Development,
2,Tool Design,Research and Development,
3,Sales,Sales and Marketing,
4,Marketing,Sales and Marketing,
5,Purchasing,Inventory Management,
6,Research and Development,Research and Development,
7,Production,Manufacturing,
8,Production Control,Manufacturing,
9,Human Resources,Executive General and Administration,
10,Finance,Executive General and Administration,
11,Information Services,Executive General and Administration,
12,Document Control,Quality Assurance,
13,Quality Assurance,Quality Assurance,
14,Facilities and Maintenance,Executive General and Administration,
15,Shipping and Receiving,Inventory Management,
16,Executive,Executive General and Administration

Create a new database table

1. Follow the steps in Connect to a SQL Server to connect to the AdventureWorks database.

2. Create a table named HumanResources.DepartmentTest. You use the SQL table for the dataframe insertion.

   CREATE TABLE [HumanResources].[DepartmentTest](
   [DepartmentID] [smallint] NOT NULL,
   [Name] [dbo].[Name] NOT NULL,
   [GroupName] [dbo].[Name] NOT NULL
   )
   GO
   

Load a dataframe from the CSV file

Use the Python pandas package to create a dataframe, load the CSV file, and then load the dataframe into the new SQL table, HumanResources.DepartmentTest.

1. Connect to the Python 3 kernel.

2. Paste the following code into a code cell, updating the code with the correct values for server, database, username, password, and the location of the CSV file.

   from mssql_python import connect
   import pandas as pd
   # insert data from csv file into dataframe.
   # working directory for csv file: type "pwd" in Azure Data Studio or Linux
   # working directory in Windows c:\users\username
   df = pd.read_csv("c:\\user\\username\department.csv")
   # Some other example server values are
   # server = 'localhost\sqlexpress' # for a named instance
   # server = 'myserver,port' # to specify an alternate port
   server = 'yourservername'
   database = 'AdventureWorks'
   username = 'username'
   password = 'yourpassword'
   connection_string = f'Server={server};Database={database};UID={username};PWD={password};TrustServerCertificate=yes;'
   conn = connect(connection_string)
   cursor = conn.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))
   conn.commit()
   cursor.close()
   conn.close()
   

3. Run the cell.

Confirm data in the database

Connect to the SQL kernel and AdventureWorks database and run the following SQL statement to confirm the table was successfully loaded with data from the dataframe.

SELECT count(*) from HumanResources.DepartmentTest;

Results

(No column name)
16

Next step