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 Step 1. Create a project directory such as Step 2. Create a virtual environment: Step 3. Activate the virtual environment: We’ll use two packages; Step 1. Install It’ll install the Step 2. Create the It’ll generate the Note that your package versions may be higher than the ones above. Step 1. Create a new file The Step 2. Create a new Python module In the If the database connection is established successfully, the How it works. First, import Second, import the Third, import the Fourth, load environment variables from the Fifth, read the environment variables including Sixth, connect to the SQL Server by calling the Seventh, display an error in the Step 3. Create the How it works. First, import Second, import the Third, configure the Fourth, connect to the SQL Server by calling the If the connection is established successfully, we log an informational message and close the database connection by calling the Step 4. Open your terminal and run the following command to execute the If you see the following in your terminal, you’re successfully connected to the SQL Server: Download the project source code Please note that we’ll reuse the BookStore
database. To create it, you can follow Setting up a virtual environmentsqlserver-python
:mkdir sqlserver-python
cd sqlserver-python
Code language: plaintext (plaintext)python -m venv venv
Code language: plaintext (plaintext)venv/scripts/activate
Code language: plaintext (plaintext)Installing necessary packages
pymssql
– for connecting to the SQL Server.python-dotenv
for working with .env
file that stores database connection parameters.pymssql
and python-dotenv
packages using the following pip
command:pip install pymssql python-dotenv
Code language: plaintext (plaintext)pymssql
and python-dotenv
packages.requirements.txt
file:pip freeze > requirements.txt
Code language: plaintext (plaintext)requirements.txt
file that includes all dependencies of the project:pymssql==2.3.0
python-dotenv==1.0.1
Code language: plaintext (plaintext)Connecting to the SQL Server
.env
in the project directory that includes SQL Server connection parameters:DB_SERVER=localhost
DB_NAME=BookStore
DB_USER=joe
DB_PASSWORD=YourPassword
Code language: Python (python).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.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)connect.py
file, we define a create_connnection()
function that loads database connection parameters from the .env
file and connects to the SQL Server.create_connection()
function returns a Connection
object. Otherwise, it returns None
.os
module for getting configuration parameters from the env and logging package for logging purposes:import os, logging
Code language: Python (python)connect
function and Connection
object from the pymssql
package:from pymssql import connect, Connection
Code language: Python (python)load_dotenv
function from the dotenv
module:from dotenv import load_dotenv
Code language: Python (python).env
file by calling the load_dotenv()
function:load_dotenv()
Code language: Python (python)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)connect()
function with the corresponding database parameters:connect(server=db_server, user=db_user, password=db_password, database=db_name)
Code language: Python (python)except
block if an error occurs during the connection:logging.error(f'Error connecting to the SQL Server database: {e}')
Code language: Python (python)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)logging
and sys
built-in modules:import logging, sys
Code language: Python (python)create_connection
function from the connect
module:from connect import create_connection
Code language: Python (python)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)create_connection()
function:conn = create_connection()
if conn:
logging.info('Connected to the SQL Server database successfully.')
conn.close()
Code language: Python (python)close()
method of the Connection
object.main.py
file:python main.py
Code language: Python (python)INFO:Connected to the SQL Server database successfully.
Code language: Python (python)Download the project source code
Summary
.env
file to store the database configuration.python-dotenv
package to read variable environments stored in the .env
file.connect()
function of the pymssql
package to connect to the SQL Server.create_connection()
function in the next tutorials for connecting to the SQL Server.