How to Connect to a Microsoft SQL Server Using Python and Pyodbc

Microsoft SQL Server is a relational database management system or RDBMS in short. As the name suggests, it was developed by Microsoft and is written in C/C++. It is a tool that lets its users interact with relational databases using SQL or Structured Query Language.

When working with real-world applications, we have to deal with a lot of data. The data has to be updated, created, and deleted every second, and these RDBMS are intelligent and robust enough to handle such operations quickly and securely.

Now, applications are developed using various programming languages. It means that each programming language should have a way to connect to these RDBMS and access the data stored inside the databases. These ways include ORMs or Object Relational Mapping libraries and Open Database Connectivity or ODBC packages. These are two very different things, and they both can be used to connect to databases.

A simple difference between the two is that ORM lets developers interact with databases using Python classes or models that represent database tables and managers, yet another Python class) to perform queries over the models. On the other hand, ODBC lets developers write raw or native SQL queries to interact with the database directly.

Since Python is a famous and widely-used programming language, it also has good support for ORMs and ODBCs. And, since we are talking about connecting to Microsoft SQL Server using Python and pyodbc, we will mainly learn about ODBCs, because pyodbc is an ODBC.

Connecting to a Microsoft SQL Server Using pyodbc

To connect to a Microsoft SQL Server, we first need a few details about the server: the driver name, the server name, and the database name. With the above information, a special string has to be created, which will be passed to the connect() function of the pyodbc library.

The format of the string is as follows -

Driver = { < driver_name > }
Server = <server_name >
Database = <database_name >
Trusted_Connection = yes

Using the connect() method, a connection will be established between the program and the server, and then, using that connection, SQL queries can be directly performed over the database.

Refer to the following code for the same.

import pyodbc

connection = pyodbc.connect(
    "Driver={<driver_name>};Server=<server_name>;Database=<database_name>;Trusted_Connection=yes;"
)  # Connection string
cursor = connection.cursor()
cursor.execute("SELECT * FROM <table_name>")  # Executing a query

for row in cursor:  # Looping over returned rows and printing them
    print(f"row = {row}")

One can also connect to a server using the DSN or Data Source Name, a user ID, and a password. And, the string for the same is as follows.

DSN = <dsn >
UID = <user >
PWD = <password >

For such a case, refer to the following code.

import pyodbc

connection = pyodbc.connect("DSN=<dsn>;UID=<user>;PWD=<password>")  # Connection string
cursor = connection.cursor()
cursor.execute("SELECT * FROM <table_name>")  # Executing a query

for row in cursor:  # Looping over returned rows and printing them
    print(f"row = {row}")

To learn more about drivers of Microsoft SQL Server, connections, and information needed to connect using the library, refer here. And, to learn more about the library itself, refer to the official documentation here