Skip to content

Microsoft SQL Server

Python

In the example below, we show how to set up a connection to a Microsoft SQL Server data source using Python. We demonstrate how to read data from a Microsoft SQL Server table to a Pandas DataFrame and vice versa. To do this, we need Python 3.7 or newer and install the following packages:

We recommend using a virtual environment and adding the packages to a requirements.txt file. In this file, you can add the following:

pyodbc         # tested with version 4.0.31
SQLAlchemy     # tested with version 1.4.21
pandas         # tested with version 1.3.0
python-dotenv  # tested with version 0.18.0

Read from Microsoft SQL Server table

In the following example code, we first set up a connection to Microsoft SQL Server using SQLAlchemy. With pandas, we read a table from Microsoft SQL Server to a Pandas DataFrame.

import os
from sqlalchemy import create_engine
import pandas as pd

# More about dotenv in the section `Configure dotenv`
from dotenv import find_dotenv, load_dotenv
load_dotenv(find_dotenv())

engine = create_engine(
    "mssql+pyodbc://{user}:{password}@{host}:{port}/{database}" \
    "?driver=ODBC+Driver+17+for+SQL+Server".format(
        user=os.getenv("DB_USER"),
        password=os.getenv("DB_PASSWORD"),
        host=os.getenv("DB_HOST"),
        port=os.getenv("DB_PORT"),
        database=os.getenv("DB_DATABASE"),
    )
)

df = pd.read_sql_table(table_name, engine)

Info

For Python, there are many packages that you can use to set up a connection to Microsoft SQL Server. The example we show here is to demonstrate a method you could use with Pandas and how you could apply it in AskAnna. The AskAnna platform is flexible. For example, if you don't use Pandas you could setup the connection directly using pyodbc. With AskAnna you could use other packages to set up a connection as well.

Write to Microsoft SQL Server table

We reuse almost to full setup as with reading the data from Microsoft SQL Server. Instead of reading data, we first create a Pandas DataFrame and then write the data to Microsoft SQL Server.

import os
from sqlalchemy import create_engine
import pandas as pd

# More about dotenv in the section `Configure dotenv`
from dotenv import find_dotenv, load_dotenv
load_dotenv(find_dotenv())

engine = create_engine(
    "mssql+pyodbc://{user}:{password}@{host}:{port}/{database}".format(
        user=os.getenv("DB_USER"),
        password=os.getenv("DB_PASSWORD"),
        host=os.getenv("DB_HOST"),
        port=os.getenv("DB_PORT"),
        database=os.getenv("DB_DATABASE"),
    )
)

df = pd.DataFrame({'example' : ['value 1', 'value 2', 'value 3']})
df.to_sql(table_name, engine)

Configure dotenv

In the above examples, we used:

from dotenv import find_dotenv, load_dotenv
load_dotenv(find_dotenv())

These two lines make it possible to develop your Python code locally, while you can also run the same code in AskAnna. When you add project variables, these variables will become available as environment variables in the run environment.

Locally, you can add a file .env and when you run the Python code locally, the environment variables are loaded from this file. Read more about this on the project page of python-dotenv.

To run the above example, you need a .env file with:

DB_HOST={host}
DB_PORT={port}
DB_USER={user}
DB_PASSWORD={password}
DB_DATABASE={database}

Add AskAnna project variables

To run the above examples as a job in AskAnna, you should add project variables. On the project page, go to the tab variables. Here you can create new variables. To run the above example, you should add variables with names and corresponding values:

  1. DB_HOST
  2. DB_PORT
  3. DB_USER
  4. DB_PASSWORD
  5. DB_DATABASE

Warning

Make sure that at least the variable DB_PASSWORD is set to masked. You don't want to expose this value.