How to Connect Python to SQL Server Using pyodbc?

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.

Steps to Set Up Python SQL Server Integration using Pyodbc

This method relies on “pyodbc” library to set up the Python SQL Server Integration.

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.

Pro Tip: Load your data from 150+ sources to SQL Server in real-time without writing any code.

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.

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.

What is Python?

Python is a versatile general-purpose Programming Language. Its small learning curve coupled with its robustness has made it one of the most popular Programming Languages ​​today.

The following features are responsible for Python Programming Language’s popularity today:

• Beginner Friendly: The Python Programming Language offers a hassle-free environment for developers. Its straightforward workflow is suitable for everyone and entry-level coders are drawn to it. Moreover, you can use and distribute its open-source codes for commercial purposes free of cost.
• Robust Applications: Its simple syntax operates on natural human-readable language making it the go-to choice of projects on Python Programming Language, which is faster as compared to other Programming Languages. Furthermore, its versatile nature makes it the ideal choice for Web Development and Machine Learning projects.
• Large Communities: Due to Python’s immense popularity, a huge active community of programmers is available online that contributes to this language’s modules and libraries. Moreover, this vast support community is ready to help in case you or any other coder gets stuck in a programming issue. You can easily get suggestions and solutions by posting your issue on these community pages.

What is SQL Server?

Microsoft SQL Server is a widely used Relational Database Management System (RDBMS) designed to provide data storage and retrieval facility to users as per their requirements.

The key features of Microsoft SQL Server are as follows:

• Accelerated Data Recovery: Microsoft SQL Server has an exceptional recovery and rollback mechanism that automatically switches on during a server crash. The process of database recovery leverages Transaction Logs which drastically decreases the delay time.
• Advanced Encryption: Considering that businesses store classified information in databases, ensuring privacy is essential for Database Providers. Since 2016, Microsoft SQL Server relies on a new Encryption Technology known as AlwaysEncrypted that offers transparent Column Encryption without providing Database Administrators any access to Decryption Keys.
• Intelligent Query Processing: Microsoft SQL Server hosts an in-built Query Optimizer that operates on an execution plan. This plan has been optimized to provide performance enhancements in terms of Dynamic Memory Grants, Batch Mode, Table Variable Deferred Compilation, etc.
• Advanced Analytics: You can easily leverage the data stored in Microsoft SQL Server to implement a comprehensive analysis as per your requirements. Moreover, you can make use of Microsoft SQL Server Analysis Service (SSAS) which to excel at Data Mining tasks.

Conclusion

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.

FAQ on Python to SQL Server