Python Connect MSSQL: Step-by-Step Guide for Data Analysis

As a data analyst, you’re constantly on the lookout for the most efficient ways to solve data challenges. Often, that means combining the strengths of multiple tools that excel in different areas. In this case, it’s Python and SQL. This combination allows you to leverage Microsoft SQL Server for robust database management and Python for data analysis and data visualization.

While setting up this integration might seem a bit complex at first, the results are well worth the effort. Python connect to MSSQL opens up possibilities for advanced analytics, data exploration, and even application development.

If you’ve ever wondered whether it’s possible to Python connect MSSQL or struggled to make it work, this guide is for you. In this blog post, we’ll take you through a guide on how to connect Python to Microsoft SQL Server using the Pyodbc library.

Prerequisites

To connect to your SQL Server with Python, ensure you have the following:

Python installed on your device
SQL installed on your device
An IDE installed or Jupyter Notebook

Install the Pyodbc library

The first step is to install the Pyodbc library. To do this, open your terminal (or Jupyter Notebook) and type this command:

pip install pyodbc

This would work seamlessly on Windows. However, on macOS, it requires some additional setup. You’ll need to install unixODBC, which you can easily do using Homebrew:

brew install unixodbc
export LDFLAGS="-L/opt/homebrew/Cellar/unixodbc/[your version]/lib"
export CPPFLAGS="-I/opt/homebrew/Cellar/unixodbc/[your version]/include"

Python MSSQL: Python connect to SQL Server

After installing the pyodbc library, the next step is to configure your SQL Server connection. You’ll need to specify key parameters such as the server name, database name, username, password, and driver version, all of which can be obtained from your Azure SQL Portal.

Once you have these details, you can proceed to define your connection string as shown below:

connection_string = ( "DRIVER={driver};" 
"SERVER=your_server_name;"
 "DATABASE=your_database_name;"
 "UID=username;"                
 "PWD=password" )

Replace the placeholder data with actual values for your driver version, server name, database name, username, and password.

If you’re using Jupyter Notebook, your connection string would look like this:

connection_string = ("Driver={driver};"
  SERVER=your_server_name;"
 "DATABASE=your_database_name;"
"Trusted_Connection=yes;")

This includes an additional Trusted_Connection parameter that gives permission to connect.

Next, let’s establish the connection using Pyodbc’s built-in connect method:

Connection = pyodbc.connect(connection_string)

This would work, but won’t return any data since we’ve not actually written any SQL queries to pull data from the connected database.

Write and execute queries

To execute Python in SQL Server, we use a cursor object. Cursors provide a way to manage and control how queries are executed and how results are fetched from the database. Here’s how you can initialize a cursor to connect MSSQL with Python:

Cursor = Connection.cursor()

Next, proceed to execute the SQL query:

Cursor.execute("Select * from department")

MSSQL Python Connection: Create tables

If you already have existing tables in your database, you can run the SQL query to fetch data from the database. If you do not, you’d have to create tables, or the run command will return an empty array. Let’s create a table of departments for a business:

create_table_query = '''
CREATE TABLE department (
   id INT PRIMARY KEY,
 department_name VARCHAR(255),
 department_code VARCHAR(255),
 department_head VARCHAR(255),
 department_location VARCHAR(255)
)
'''Cursor.execute(create_table_query)
connection.commit()

This creates a department table with all the specified fields.

Next, let’s insert data into it. While we could manually add each record using the INSERT INTO ... VALUES … SQL statement, that approach can be time-consuming. A more efficient method is to load the department data directly from a CSV file:

with open('department.csv', 'r') as f:
   reader = csv.reader(f)
   department_list = list(reader)

This code opens the department.csv file in read mode. Python’s built-in csv.reader() helps to read each row of the CSV. Lastly, we convert all the rows into a list called department_list.

Next, let’s define the SQL insert query:

insert_multiple_data_query = '''
INSERT INTO customers (id, first_name, last_name, email, UID, sign_up_date)
VALUES (?, ?, ?, ?, ?, ?)
'''
Cursor.executemany(insert_multiple_data_query, department_list)
Connection.commit()

This uses the SQL INSERT statement with placeholders (?) for parameterized queries. These placeholders are filled by data from each row of your CSV file. It then executes the SQL query for each row in department_list. The executemany() method allows you to insert all the data into the database at once, rather than looping through each row manually. Finally, the commit() method helps to save all the changes and apply them to the database.

Protip: Using ? prevents SQL injection and ensures safer query execution.

Now that you have created a table, you can then run queries successfully.

Run the SQL query

Let’s run our SQL query and get a list of all the department data. Here:

department_query = 'SELECT * FROM Department'

cursor.execute(department_query)
department = cursor.fetchall()

print(department)

#output

{"id": 1, "department_name": "Product Management", "department_code": "Operations", "department_head": "Jacobo Crumpe", "department_location": "463 Maple Alley"}
{"id": 2, "department_name": "Marketing", "department_code": "Marketing", "department_head": "Ax Berkeley", "department_location": "86 Shasta Terrace"}
{"id": 3, "department_name": "Engineering", "department_code": "IT", "department_head": "Letitia Davison", "department_location": "92799 Tomscot Plaza"}
{"id": 4, "department_name": "Sales", "department_code": "IT", "department_head": "Cordula Vicary", "department_location": "4 Orin Street"}
{"id": 5, "department_name": "Research and Development", "department_code": "Marketing", "department_head": "Skipp Vurley", "department_location": "47 Pawling Place"}

Finally, make sure you close the connection once you’re done to free up resources:

Connection.close()

Conclusion: Python and MSSQL connection

In this blog post, we walked through a step-by-step guide on how to Python connect MSSQL and enhance your data analysis workflow.

If you’re looking for a more seamless way to connect to your databases while leveraging Python for advanced analytics and visualization, you might find Quadratic useful.

Quadratic is an AI-powered data analytics platform that connects directly to multiple databases, raw data sources, and APIs, eliminating the hassle of complex setup. This allows you to focus less on configuration and more on generating meaningful insights with Python and its built-in AI assistant. Try Quadratic for free today.

Python Connect MSSQL: Step-by-step guide for data analysis