Insert Python dataframe into SQL table
Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance
This article describes how to insert a pandas dataframe into a SQL database using the pyodbc package in Python.
SQL Server Management Studio for restoring the sample database to Azure SQL Managed Instance.
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;
In Azure Data Studio, open a new notebook and connect to the Python 3 kernel.
Select Manage Packages.
In the Manage Packages pane, select the Add new tab.
For each of the following packages, enter the package name, click Search, then click Install.
- pyodbc
- pandas
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
Follow the steps in Connect to a SQL Server to connect to the AdventureWorks database.
Create a table named HumanResources.DepartmentTest. The SQL table will be used for the dataframe insertion.
CREATE TABLE [HumanResources].[DepartmentTest]( [DepartmentID] [smallint] NOT NULL, [Name] [dbo].[Name] NOT NULL, [GroupName] [dbo].[Name] NOT NULL ) GO
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.
Connect to the Python 3 kernel.
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.import pyodbc 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' 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()
Run the cell.
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