Python SQL Server Integration using Pyodbc: 5 Easy Steps

on Data Ingestion, Database Management Systems, Microsoft SQL Server, Python, SQL Server, Tutorials • February 9th, 2022 • Write for Hevo

Python SQL Server: Featured Image

Today, most new developers are preferring Python over other Programming Languages due to its simple yet advanced functionalities. Application Development, Machine Learning, Artificial Intelligence, Gaming, and many more such fields are promoting the use of Python’s environment to build new products. Moreover, Businesses across the world are leveraging the rich Python Libraries to automate their daily tasks to achieve higher efficiency.  

Microsoft SQL Server is the first choice for an RDBMS (Relational Database Management System) solution in almost every company today. Its easy-to-use integrations and robust data processing have led many organizations to rely on its services for their high intent tasks. In recent times, these businesses are seeking ways to combine this RDBMS solution with Python Programming Language to further enhance its usability.

This article will introduce you to both Python and SQL Servers and list down their key features. It will also elaborate on the step-by-step approach of setting up the Python SQL Server Integration for your business. Read along to master these steps and learn about the benefits of connecting Python with your SQL Servers!

Table of Contents

Prerequisites

To set up the Python SQL Server Integration, you must have:

  • Working knowledge of Python Programming Language.
  • Microsoft SQL account.
  • Python environment setup.

What is Python?

Python SQL Server: Python Logo
Image Source

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. It is the go-to choice of developers for Website and Software Development, Automation, Data Analysis, Data Visualization, and much more. Moreover, its straightforward syntax allows Accountants, Scientists to utilize it for daily tasks. The Python Programming Language serves as the key integral tool in the field of Data Science for performing complex Statistical Calculations, creating Machine Learning Algorithms, etc. 

Python Programming Language is also renowned for its ability to generate a variety of Data Visualizations like Bar Charts, Column Charts, Pie Charts,  and 3D Charts. Furthermore, it offers a rich set of libraries that facilitates advanced Machine Learning programs in a faster and simpler manner. 

Key Features of the Python Programming Language

Python SQL Server: Python Features
Image Source

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.

You can understand more about the Python Programming Language by visiting here.

What is SQL Server?

Python SQL Server: SQL Server Logo
Image Source

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. Microsoft SQL Server facilitates a wide range of functionalities from which Transactional Processing and Data Analytics are popular among businesses today. Moreover, Microsoft SQL Server is touted to be one of the top 3 leading Database technologies along with Oracle Database and IBM DB2.

Apart from ANSI SQL, a popular Structured Query Language (SQL), Microsoft SQL Server contains its own version of SQL called the Transact SQL (T-SQL). The T-SQL supports additional features that empower you to perform operations like variable declaration, Exceptions handling, and perform other operations. Furthermore, your work on Microsoft SQL Server is simplified by its key interface tool called SQL Server Management Studio (SSMS). It automates most of the complex tasks and allows you a seamless experience.

Key Features of Microsoft SQL Server

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.

Simplify your Data Analysis with Hevo’s No-code Data Pipeline

Hevo Data, a No-code Data Pipeline helps to load data from any data source such as Microsoft SQL, SaaS applications, Cloud Storage, SDK,s, and Streaming Services and simplifies the ETL process. It supports 100+ data sources and loads the data onto the desired Data Warehouse, enriches the data, and transforms it into an analysis-ready form without writing a single line of code.

Its completely automated pipeline offers data to be delivered in real-time without any loss from source to destination. Its fault-tolerant and scalable architecture ensure that the data is handled in a secure, consistent manner with zero data loss and supports different forms of data. The solutions provided are consistent and work with different Business Intelligence (BI) tools as well.

Get Started with Hevo for Free

Check out why Hevo is the Best:

  • Secure: Hevo has a fault-tolerant architecture that ensures that the data is handled in a secure, consistent manner with zero data loss.
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
  • Minimal Learning: Hevo, with its simple and interactive UI, is extremely simple for new customers to work on and perform operations.
  • Hevo Is Built To Scale: As the number of sources and the volume of your data grows, Hevo scales horizontally, handling millions of records per minute with very little latency.
  • Incremental Data Load: Hevo allows the transfer of data that has been modified in real-time. This ensures efficient utilization of bandwidth on both ends.
  • Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
  • Live Monitoring: Hevo allows you to monitor the data flow and check where your data is at a particular point in time.
Sign up here for a 14-Day Free Trial!

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 easily set up your 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.

Step 2: Run an 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 boss’s email id.

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

Benefits of Python SQL Server Integration

Setting up the Python SQL Server Integration, it’s time to learn the advantage that it can provide to your business. Combining Python Programming Language with SQL Servers will enhance your business in the following ways: 

  • Due to its simplicity, the Python Programming Language has become the ideal choice for developers to build AI projects. Therefore, a Python SQL Server Integration will allow these 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’s 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 as compared to 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 introduced you to Python Programming Language and SQL Servers along with their unique features. It then provides a step-by-step guide using which you can set up your Python SQL server Integration seamlessly. The article also discussed the numerous benefits that combining these 2 tools can provide for your business. After reading this article, you can try and easily implement the Python SQL Server Integration for your databases.

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 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 100+ multiple 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.

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

No Code Data Pipeline For Your Data Warehouse