Python Connect to SQL Server

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-python

Step 2. Create a virtual environment:

python -m venv venv

Step 3. Activate the virtual environment:

venv/scripts/activate

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-dotenv

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

Step 2. Create the requirements.txt file:

pip freeze > requirements.txt

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

pymssql==2.3.0
python-dotenv==1.0.1

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=YourPassword

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}')

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, logging

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

from pymssql import connect, Connection

Third, import the load_dotenv function from the dotenv module:

from dotenv import load_dotenv

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

load_dotenv()

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')

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)

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}')

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()

How it works.

First, import logging and sys built-in modules:

import logging, sys

Second, import the create_connection function from the connect module:

from connect import create_connection

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
)

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()

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.py

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

INFO:Connected to the SQL Server database successfully.

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.

Was this tutorial helpful?