SQL Server is one of the most popular database servers used for storing relational data and running analytics workloads. As Python continues its rise as a leading data analysis language, the ability to connect Python applications to SQL Server databases is essential.

The pyodbc library provides a lightweight, robust way for Python code to connect to SQL Server databases, issue queries, and process results sets.

This article will elaborate on the step-by-step approach to Python SQL Server connection for your business using pyodbc. Read along to master these steps and learn about the benefits of Python SQL server connection!

Steps to Set Up Python SQL Server Integration using Pyodbc

This method relies on “pyodbc” library to set up the Python SQL Server Integration. The pyodbc library provides Python developers with easy access to ODBC (Open Database Connectivity) databases. Therefore, you can implement the method given in this section to set up Python ODBC integrations with any platforms such as MS Access, MySQL, IBM Db2, etc. 

The following steps will allow you to set up your Python SQL Server Integration easily:

Fasten your Python SQL Server Integration with Hevo!

Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. With integration with 150+ Data Sources (40+ free sources), we help you not only export data from sources & load data to the destinations but also transform & enrich your data, & make it analysis-ready.

Start for free now!

Get Started with Hevo for Free

Step 1: Establish the Python SQL Server Connection

The first step of setting up the Python SQL Server Integration requires you to build a connection between Python and the SQL server using the pyodbc.connect function and pass a connection string. The Python MsSQL Connection string will define the DBMS Driver, connection settings, the Server, and a specific Database.

Now, for instance, you wish to connect to server USXXX00345,67800 and a database DB02 using the SQL Server Native Client 11.0.

There are 2 ways to establish this Python SQL Server connection:

  • Approach 1 to Setup Python SQL Server Connection: You can depend on a trusted internal connection using the following code:
cnxn_str = ("Driver={SQL Server Native Client 11.0};"
            "Server=USXXX00345,67800;"
            "Database=DB02;"
            "Trusted_Connection=yes;")
cnxn = pyodbc.connect(cnxn_str)
  • Approach 2 to Setup Python SQL Server Connection: You don’t have a trusted internal connection and wish to set up the required SQL Server connection using SQL Server Management Studio (SSMS). This will require you to enter your username (say, Alex) and password(Alex123) as shown in the following code:
cnxn_str = ("Driver={SQL Server Native Client 11.0};"
            "Server=USXXX00345,67800;"
            "Database=DB02;"
            "UID=Alex;"
            "PWD=Alex123;")
cnxn = pyodbc.connect(cnxn_str)

Now, as your Python database connection is in place, you can perform SQL queries via Python.

Step 2: Run a SQL Query

Now, every query that you will perform on the SQL Server will involve a cursor initialization and query execution sequence. Moreover, any changes made inside the SQL Server must also reflect in Python (which is covered in Step3 of Python MS SQL Server Integration).

You can initialize a cursor via:

cursor = cnxn.cursor()

Now, if you wish to perform a query, call this cursor object. For example, the following query will select the top 100 rows from a SQL table name associates:

cursor.execute("SELECT TOP(100) * FROM associates")

This query will give you the desired results, however, no data will be returned to Python. To ensure that your SQL changes are reflected in Python, move on to the next step of Python SQL Server Integration.

Step 3: Extract Query Results to Python

To extract your data from SQL Server into Python, you will need the Pandas library. Pandas contain the “read_sql” function which is useful for reading data from SQL into Python. The read_sql requires a query and also the connection instance cnxn to extract the given data as follows:

data = pd.read_sql("SELECT TOP(100) * FROM associates", cnxn)

This will return a data frame consisting of the top 100 rows from your associates table.

Step 4: Apply Modifications in SQL Server

Next, if you wish to change the SQL data, you must add another step to the query execution process. This is because when you execute SQL queries, the changes are stored in a temporary space instead of directly modifying your stored data.

To make such modifications permanent, you have to commit them. For instance, if you wish to merge the firstName and lastName columns, generate a fullName column using the below code:

cursor = cnxn.cursor()
# first alter the table, adding a column
cursor.execute("ALTER TABLE associates " +
               "ADD fullName VARCHAR(20)")
# now update that column to contain firstName + lastName
cursor.execute("UPDATE associate " +
               "SET fullName = firstName + " " + lastName")

Even after executing this code, you won’t find any fullName column in your associate database. You need to commit the above changes and make them permanent via the following command:

cnxn.commit()

Step 5: Automate the Python SQL Server Functioning

The above steps discussed how you can modify your SQL data and extract it to Python or, alternatively, you can first extract the data to Python and then perform the manipulations. Once this setup is ready, you can use the Python SQL Server Integration to automate a multitude of tasks. 

For example, you may need to perform daily reporting which involves querying the newest batch of data within the SQL server, calculating basic statistics, and sending the results via email to the management. You can automate this lengthy process by leveraging the Python SQL Server Integration as follows:

# imports for SQL data part
import pyodbc
from datetime import datetime, timedelta
import pandas as pd

# imports for sending email
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
import smtplib

date = datetime.today() - timedelta(days=7)  # get the date 7 days ago

date = date.strftime("%Y-%m-%d")  # convert to format yyyy-mm-dd

cnxn = pyodbc.connect(cnxn_str)  # initialise connection (assume we have already defined cnxn_str)

# build up our query string
query = ("SELECT * FROM associates "
         f"WHERE joinDate > '{date}'")

# execute the query and read to a dataframe in Python
data = pd.read_sql(query, cnxn)

del cnxn  # close the connection

# make a few calculations
mean_payment = data['payment'].mean()
std_payment = data['payment'].std()

# get max payment and product details
max_vals = data[['product', 'payment']].sort_values(by=['payment'], ascending=False).iloc[0]

# write an email message
txt = (f"Customer reporting for period {date} - {datetime.today().strftime('%Y-%m-%d')}.nn"
       f"Mean payment amounts received: {mean_payment}n"
       f"Standard deviation of payment amounts: {std_payments}n"
       f"Highest payment amount of {max_vals['payment']} "
       f"received from {max_vals['product']} product.")

# we will built the message using the email library and send using smtplib
msg = MIMEMultipart()
msg['Subject'] = "Automated customer report"  # set email subject
msg.attach(MIMEText(txt))  # add text contents
       
# we will send via outlook, first we initialise connection to mail server
smtp = smtplib.SMTP('smtp-mail.outlook.com', '587')
smtp.ehlo()  # say hello to the server
smtp.starttls()  # we will communicate using TLS encryption
       
# login to outlook server, using generic email and password
smtp.login('Alex@outlook.com', 'Alex123')
       
# send email to our boss
smtp.sendmail('Alex@outlook.com', 'boss@outlook.com', msg.as_string())
       
# finally, disconnect from the mail server
smtp.quit()

This code will now extract the prior week’s data, calculate your key metrics, and send a summary on your stakeholder’s email ID.

That’s it! Your Python SQL Server Integration is in place and is working successfully.

Benefits of Python SQL Server Integration

Combining Python Programming Language with SQL Servers will enhance your business in the following ways: 

  • A Python SQL Server Integration allows AI developers to leverage the SQL Server’s functionality directly from their Python code. This will make it much easier for these developers to embed data analytics, AI functionality and machine learning features into an existing application’s code. 
  • The Python SQL Server connection provides a significant edge for development work. Other languages like R, when integrated with SQL Server, will enhance only the analytical aspect of programming. However, Python when used along with SQL Servers, allows developers to perform real-time data streaming and operations in a much easier way.
  • The Python SQL Server integration setup provides faster data processing than using a network-based system over TCP, HTTP, etc. This is because it utilizes local platform resources including the file system, GPU, etc. for building real-time integrations and relies on universal data formats such as JSON and XML.

Conclusion

The article provides a step-by-step guide to set up your Python SQL server Integration seamlessly. The article also discussed the numerous benefits that combining these two tools can provide for your business. After reading this article, you can try and easily implement the Python SQL Server Integration for your databases.

Take a deeper dive into Python and its abilities with the following essentials:

Visit our Website to Explore Hevo

Now, to run SQL queries or perform Data Analytics on your SQL Server data, you first need to export this data to a Data Warehouse. This will require you to custom-code complex scripts to develop the ETL processes. Hevo Data is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs.

It can automate your data transfer process, hence allowing you to focus on other aspects of your business like Analytics, Customer Management, etc. This platform allows you to transfer data from sources like Microsoft SQL to Cloud-based Data Warehouses like Amazon Redshift, Snowflake, Google BigQuery, etc. It will provide you with a hassle-free experience and make your work life much easier.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. Check out our Hevo Pricing page for more details.

Share your understanding of the Python SQL Server Integration function in the comments below!

mm
Former Research Analyst, Hevo Data

Abhinav is a data science enthusiast who loves data analysis and writing technical content. He has authored numerous articles covering a wide array of subjects in data integration and infrastructure.

No Code Data Pipeline For Your Data Warehouse