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

 

 

 

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:

 

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:

 

 

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:

 

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

 

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!

Learn More

 

 

Featured Online Courses:

• AI Essentials: A Beginner’s Guide to Artificial Intelligence
• SQL Server 2022: What’s New – New and Enhanced Features
• Working with Python on Windows and SQL Server Databases
• Introduction to Azure Database for MySQL
• Boost SQL Server Database Performance with In-Memory OLTP
• Introduction to Azure SQL Database for Beginners
• Essential SQL Server Administration Tips
• SQL Server Fundamentals – SQL Database for Beginners
• Essential SQL Server Development Tips for SQL Developers
• Introduction to Computer Programming for Beginners
• .NET Programming for Beginners – Windows Forms with C#
  
• SQL Server 2019: What’s New – New and Enhanced Features
• Entity Framework: Getting Started – Complete Beginners Guide
  
• Data Management for Beginners – Main Principles
  
• A Guide on How to Start and Monetize a Successful Blog

 

Read Also:

• Python Data Access Fundamentals
• Useful Python Programming Tips
• Mastering Python & SQL Server: Best Practices
• Main Data Structures in Python
• Working with Python on Windows and SQL Server Databases (Course Preview)
• How to Run the SQL Server BULK INSERT Command from Within a Python Program
• How to Resolve: [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)
• IndentationError: expected an indented block in Python – How to Resolve it
• How to Write to a Text File from a C++ Program
• How to Establish a Simple Connection from a C# Program to SQL Server
• The timeout period elapsed prior to obtaining a connection from the pool
• Closing a C# Application (including hidden forms)
• Changing the startup form in a C# project
• Using the C# SqlParameter Object for Writing More Secure Code
• Cannot implicitly convert type ‘string’ to ‘System.Windows.Forms.DataGridViewTextBoxColumn
• Missing parentheses in call to ‘print’. did you mean print(…) – How to Resolve in Python

 

Check our online courses!

Check our eBooks!

Subscribe to our YouTube channel!

Subscribe to our newsletter and stay up to date!

 

Rate this article: (4 votes, average: 4.25 out of 5)

Loading...

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

© SQLNetHub