USE AdventureWorks;
SELECT * FROM HumanResources.Department;
Install Python packages
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
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
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
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.
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.
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)
Next steps
Plot a histogram for data exploration with Python
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for
This product
