In the SQL Server Management Studio (SSMS), the ease of using external procedure sp_execute_external_script has been (and still will be) discussed many times. But the reason for this short blog post is the fact that, changing Python environments using Conda package/module management within Microsoft SQL Server (Services), is literally impossible. Scenarios, where you want to build a larger set of modules (packages) but are impossible to be compatible with your SQL Server or Conda, then you would need to set up a new virtual environment and start using Python from there.
Communicating with database to load the data into different python environment should not be a problem. Python Pandas module is an easy way to store dataset in a table-like format, called dataframe. Pandas is very powerful python package for handling data structures and doing data analysis.
Loading data from SQL Server to Python pandas dataframe
This underlying task is something that every data analyst, data engineer, statistician and data scientist will be using in everyday work. Extracting data from Microsoft SQL Server database using SQL query and storing it in pandas (or numpy) objects.
With following code:
## From SQL to DataFrame Pandas import pandas as pd import pyodbc sql_conn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server}; SERVER=SQLSERVER2017; DATABASE=Adventureworks; Trusted_Connection=yes') query = "SELECT [BusinessEntityID],[FirstName],[LastName], [PostalCode],[City] FROM [Sales].[vSalesPerson]" df = pd.read_sql(query, sql_conn) df.head(3)
you will get the first three rows of the result:
Make sure that you configure the SERVER and DATABASE as well as the credentials to your needs. If you are running older version of SQL Server, you will need to change the driver configuration as well.
Inserting data from Python pandas dataframe to SQL Server
Once you have the results in Python calculated, there would be case where the results would be needed to inserted back to SQL Server database. In this case, I will use already stored data in Pandas dataframe and just inserted the data back to SQL Server.
First, create a table in SQL Server for data to be stored:
USE AdventureWorks; GO DROP TABLE IF EXISTS vSalesPerson_test; GO CREATE TABLE vSalesPerson_test( [BusinessEntityID] INT ,[FirstName] VARCHAR(50) ,[LastName] VARCHAR(100))
After that, just simply run the following Python code:
connStr = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server}; SERVER=SQLSERVER2017; DATABASE=Adventureworks; Trusted_Connection=yes') cursor = connStr.cursor() for index,row in df.iterrows(): .. cursor.execute("INSERT INTO dbo.vSalesPerson_test([BusinessEntityID], [FirstName],[LastName]) values (?, ?,?)", row['BusinessEntityID'], row['FirstName'], row['LastName']) .. connStr.commit() cursor.close() connStr.close()
*Python indentation might be broken; use github file.
And the data will be inserted in SQL Server table:
As always, sample code is available at Github.
Happy coding! 🙂
[…] Tomaz Kastrun shows how to use pyodbc to interact with a SQL Server database from Pandas: […]
LikeLike
[…] Comment lire et écrire des données Microsoft SQL Server en Python utilisant des données Pandas […]
LikeLike
[…] How to read and write Microsoft SQL Server data in Python using Pandas dataframes. […]
LikeLike
[…] How to read and write Microsoft SQL Server data in Python using Pandas dataframes. […]
LikeLike
[…] How to read and write Microsoft SQL Server data in Python using Pandas dataframes. […]
LikeLike
i think we can create table also from python itself into swl server
LikeLike
excellent article, certainly the easiest and most straightforward way to interface python and SQL server. Thank you.
LikeLike
[…] https://tomaztsql.wordpress.com/2018/07/15/using-python-pandas-dataframe-to-read-and-insert-data-to-… […]
LikeLike
When i do the same thing, it says.. SQL has 0 parameter makers and 3 are supplied,..
Please advise what am i missing…. Thanks in advance
LikeLike
how to import Dataframe from python for SQL SERVER? For example… The table “DF” not exists in my DATABASE SQL.. in “R” i used de function SQLSAVE and my DATAFRAME has been created… but in python i dont know how this… somebody help-me? Sorry about my english… i´m from Brazil ;D
LikeLike
Hi. You just push the data using pandas dataframe (as a result set of your analysis) from Python to SQL Server.
LikeLike
If you have any additional questions, feel free to send me an email. Best,Tomaz
LikeLike
What I’d LOVE to know is how do you UPDATE a Microsoft SQL table from Python Pandas?
For Example I read in a MS-SQL table to a dataframe (df1) then I change one of the fields:
df1[‘SPEC_RANGE’] = ‘ ‘ ‘ , SPEC_RANGE’ = ‘Not Found’
So now df1 is changed for some records so now I want to write the UPDATES back to the database table.
How can that be done?
LikeLike
nice and clear artice – works fine !
LikeLike
Where you wrote Values =[??], what values are we to insert there. since we want to put entire dataframe
LikeLike