How to Connect to SQL Server Using Python Script

Hello everyone, if you follow this blog, you will find many tutorials about Microsoft SQL Server. In the past, we learned how to install SQL Server on different operating systems. In this article, we will try to improve our skills with Python. We are going to connect to SQL Server using Python script.

Connecting to a SQL Server database using a Python script can be a useful skill for anyone working with data. In this article, we’ll walk through the steps to establish a connection to a SQL Server database using Python and the pyodbc library.

Steps to Connect to SQL Server Using Python

Step 1: Install ODBC Driver 17 for SQL Server

In this example, I am running Ubuntu Linux. We need the ODBC Driver for SQL Server to be installed on our system. In Ubuntu, use the following command (You can copy and paste the whole commands at once). If you are using another operating system, you can check the command from this page.

if ! [[ "16.04 18.04 20.04 22.04" == *"$(lsb_release -rs)"* ]];
then
    echo "Ubuntu $(lsb_release -rs) is not currently supported.";
    exit;
fi

sudo su
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -

curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list > /etc/apt/sources.list.d/mssql-release.list

exit
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql17
# optional: for bcp and sqlcmd
sudo ACCEPT_EULA=Y apt-get install -y mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
# optional: for unixODBC development headers
sudo apt-get install -y unixodbc-dev

Step 2. Install the pyodbc library

To start, we need to install the pyodbc library, which provides Python access to ODBC databases. This can be done using pip, the package installer for Python. Open your command prompt or terminal and type the following command:

pip install pyodbc

If you don’t have pip, you can install it using this command (Ubuntu)

sudo apt install python3-pip

Step 3: Connect to the SQL Server database

Once pyodbc is installed, we can use it to connect to the SQL Server database. In the Python script, we first import the pyodbc library and then establish a connection to the SQL Server database by passing the necessary parameters to the pyodbc.connect() method. Here is the common syntax:

import pyodbc

server = 'your_server_name'
database = 'your_database_name'
username = 'your_username'
password = 'your_password'

conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)

n the above code, we’re passing the server name, database name, username, and password as parameters to the pyodbc.connect() method. The DRIVER parameter specifies the ODBC driver that we’re using to connect to the SQL Server database.

Step 4: Execute SQL queries

Once the connection is established, we can execute SQL queries using the conn object that we created in step 2. Here’s an example:

cursor = conn.cursor()
cursor.execute('SELECT * FROM your_table_name')
for row in cursor:
    print(row)

In the above code, we’re creating a cursor object using the conn.cursor() method, and then executing a SQL query using the cursor.execute() method. We’re then looping through the result set using a for loop and printing each row.

Step 4: Close the connection

Finally, we need to close the connection once we’re done executing our SQL queries. This can be done using the conn.close() method. Here’s an example:

conn.close()

That’s it! You now know how to connect to a SQL Server database using Python and pyodbc. With this knowledge, you can execute SQL queries, extract data, and perform data manipulation using Python.