How to Connect to SQL Server from Python

Summary: in this tutorial, you’ll learn how to connect to SQL Server databases from Python.

Creating a new database in SQL Server

We’ll use the BookStore database. To create it, you can follow this tutorial.

Setting up a virtual environment

Step 1. Create a project directory such as sqlserver-python:

mkdir sqlserver-python
cd sqlserver-pythonCode language: plaintext (plaintext)

Step 2. Create a virtual environment:

python -m venv venvCode language: plaintext (plaintext)

Step 3. Activate the virtual environment:

venv/scripts/activateCode language: plaintext (plaintext)

Installing necessary packages

We’ll use two packages;

• pymssql – for connecting to the SQL Server.
• python-dotenv for working with .env file that stores database connection parameters.

Step 1. Install pymssql and python-dotenv packages using the following pip command:

pip install pymssql python-dotenvCode language: plaintext (plaintext)

It’ll install the pymssql and python-dotenv packages.

Step 2. Create the requirements.txt file:

pip freeze > requirements.txtCode language: plaintext (plaintext)

It’ll generate the requirements.txt file that includes all dependencies of the project:

pymssql==2.3.0
python-dotenv==1.0.1Code language: plaintext (plaintext)

Note that your package versions may be higher than the ones above.

Connecting to the SQL Server

Step 1. Create a new file .env in the project directory that includes SQL Server connection parameters:

DB_SERVER=localhost
DB_NAME=BookStore
DB_USER=joe
DB_PASSWORD=YourPasswordCode language: Python (python)

The .env file contains four important database parameters:

• DB_SERVER – specifies the SQL Server database server (localhost).
• DB_NAME – indicates the database name on the SQL Server you want to connect to. It is BookStore in this case.
• DB_USER – provides the user account that has sufficient permissions to connect to the SQL Server.
• DB_PASSWORD – specify the password for the database user.

Step 2. Create a new Python module connect.py with the following code:

import os, logging
from pymssql import connect, Connection
from dotenv import load_dotenv


def create_connection() -> Connection | None:
    # read data from .env file
    load_dotenv()
    db_server = os.getenv('DB_SERVER')
    db_user = os.getenv('DB_USER')
    db_password = os.getenv('DB_PASSWORD')
    db_name = os.getenv('DB_NAME')

    # connect to the SQL Server
    try:
        return connect(server=db_server, user=db_user, password=db_password, database=db_name)
    except Exception as e:
        logging.error(f'Error connecting to the SQL Server database: {e}')
Code language: Python (python)

In the connect.py file, we define a create_connnection() function that loads database connection parameters from the .env file and connects to the SQL Server.

If the database connection is established successfully, the create_connection() function returns a Connection object. Otherwise, it returns None.

How it works.

First, import os module for getting configuration parameters from the env and logging package for logging purposes:

import os, loggingCode language: Python (python)

Second, import the connect function and Connection object from the pymssql package:

from pymssql import connect, ConnectionCode language: Python (python)

Third, import the load_dotenv function from the dotenv module:

from dotenv import load_dotenvCode language: Python (python)

Fourth, load environment variables from the .env file by calling the load_dotenv() function:

load_dotenv()Code language: Python (python)

Fifth, read the environment variables including DB_SERVER, DB_USER, DB_PASSWORD, and DB_NAME using the os.getenv() function:

db_server = os.getenv('DB_SERVER')
db_user = os.getenv('DB_USER')
db_password = os.getenv('DB_PASSWORD')
db_name = os.getenv('DB_NAME')       Code language: Python (python)

Sixth, connect to the SQL Server by calling the connect() function with the corresponding database parameters:

connect(server=db_server, user=db_user, password=db_password, database=db_name)Code language: Python (python)

Seventh, display an error in the except block if an error occurs during the connection:

logging.error(f'Error connecting to the SQL Server database: {e}')Code language: Python (python)

Step 3. Create the main.py file and call the create_connection function to connect to the SQL Server:

import logging, sys
from connect import create_connection

# config logging to console
logging.basicConfig(
    stream=sys.stdout, 
    encoding='utf-8', 
    format='%(levelname)s:%(message)s',
    level=logging.DEBUG
)

# create a database connection
conn = create_connection()
if conn:
    logging.info('Connected to the SQL Server database successfully.')
    conn.close()Code language: Python (python)

How it works.

First, import logging and sys built-in modules:

import logging, sysCode language: Python (python)

Second, import the create_connection function from the connect module:

from connect import create_connectionCode language: Python (python)

Third, configure the logging module to log information to the standard output, which is the terminal in this case:

# config logging to console
logging.basicConfig(
    stream=sys.stdout, 
    encoding='utf-8', 
    format='%(levelname)s:%(message)s',
    level=logging.DEBUG
)Code language: Python (python)

Fourth, connect to the SQL Server by calling the create_connection() function:

conn = create_connection()
if conn:
    logging.info('Connected to the SQL Server database successfully.')
    conn.close()Code language: Python (python)

If the connection is established successfully, we log an informational message and close the database connection by calling the close() method of the Connection object.

Step 4. Open your terminal and run the following command to execute the main.py file:

python main.pyCode language: Python (python)

If you see the following in your terminal, you’re successfully connected to the SQL Server:

INFO:Connected to the SQL Server database successfully.Code language: Python (python)

Download the project source code

Download the project source code

Summary

• Use the .env file to store the database configuration.
• Use the python-dotenv package to read variable environments stored in the .env file.
• Call the connect() function of the pymssql package to connect to the SQL Server.

Please note that we’ll reuse the create_connection() function in the next tutorials for connecting to the SQL Server.