How to Connect to SQL Server Databases from a Python Program

In this article, we are going to see, step by step, via an example, how we can connect to SQL Server from a Python program using an ODBC connection and the pyodbc module.

For this article’s example, I will be using Visual Studio Code, with the MS Python extension.

Prior to start reading the article, we strongly recommend that you enroll to our online course “Working with Python on Windows and SQL Server Databases“. This course, will help you learn how to access and work with SQL Server databases, directly from your Python programs, by teaching you how to perform all the major database development operations from within your Python code.

Great, lets proceed with our example.

 

Step 1: Create a Python Script in Visual Studio Code

After installing Python, pyodbc, Visual Studio Code and the MS Python extension, we create a new python script in Visual Studio code. In this example, I’m creating the Python script “ConnectSQL.py” which we save into c:\test

 

How to Connect to SQL Server Databases from a Python Program

 

 

Step 2: Import pyodbc in your Python Script

The next step, is to import pyodbc in your Python script using the below command:

import pyodbc

 

Step 3: Set the Connection String

Now it’s time to set our connection string. For this example, I will be connecting to a local named instance of SQL Server, named “SQL2K19“, using a trusted connection, that is Windows Authentication. Also, the initial database to connect to, is “SampleDB“.

Therefore, I will be setting up my connection string, using the ODBC Driver for SQL Server as below:

# Trusted Connection to Named Instance
connection = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=.\SQL2K19;DATABASE=SampleDB;Trusted_Connection=yes;')

Here’s a screenshot of our code so far:

How to Connect to SQL Server Databases from a Python Program

 

Step 4: Create a Cursor Object from our Connection and Execute the SQL Command

The next step after setting up our connection to the database, is to create a cursor object and execute the SQL query.

In this example, I’m calling the SQL Server global system variable @@VERSION that returns version information about the SQL Server instance.

cursor=connection.cursor()
cursor.execute("SELECT @@VERSION as version")

Here’s how our code looks like now:

How to Connect to SQL Server Databases from a Python Program

 

 

Step 5: Retrieve the Query Results from the Cursor

Then, via a while loop and repetitive calls to the cursor.fetchone() method, you retrieve the query results from the cursor:

while 1:
    row = cursor.fetchone()
    if not row:
        break
    print(row.version)

 

Let’s see a new screenshot of our code in Visual Studio:

 

Step 6: Close the Cursor and the Connection

Finally, you close the cursor and the connection, in order not to leave an open connection to your SQL Server database.\

To this end, you call the below 2 commands:

cursor.close()
connection.close()

 

Let’s see the code:

How to Connect to SQL Server Databases from a Python Program

 

And let’s see the outcome of the code execution:

How to Connect to SQL Server Databases from a Python Program

 

Step 7: This Example’s Full Code

Below, you can find this example’s full code.

import pyodbc

# Trusted Connection to Named Instance
connection = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=.\SQL2K19;DATABASE=SampleDB;Trusted_Connection=yes;')

cursor=connection.cursor()
cursor.execute("SELECT @@VERSION as version")

while 1:
    row = cursor.fetchone()
    if not row:
        break
    print(row.version)

cursor.close()
connection.close()

 

Step 8: Learn More about SQL Data Access from Python

Enroll to our online course  “Working with Python on Windows and SQL Server Databases” with an exclusive discount, and get started with Python data access programming for SQL Server databases, fast and easy!

Working with Python on Windows and SQL Server Databases - Online Course
(Lifetime Access, Q&A, Certificate of Completion, downloadable resources and more!)

Learn More

 

 

Featured Online Courses:

 

Read Also:

 

Check our online courses!

Check our eBooks!

Subscribe to our YouTube channel!

Subscribe to our newsletter and stay up to date!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (4 votes, average: 4.25 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub