
This article provides a step-by-step tutorial of connecting to Azure SQL Server using Python on Linux OS.
After creating an Azure SQL Database/Server, you can find the server name on the overview page.

Azure SQL Server uses ODBC (Open Database Connectivity) as the driver.
A database driver is a computer program that implements a protocol (ODBC or JDBC) for a database connection.
Let me explain it in plain language. Do you still remember the time when we purchased hardware or software, it would come with a disk of its driver, and you had to install that driver before using the application? Well, you can think of the database as the application, and the database driver is essentially the driver that enables us to access the database, or DBMS(Database Management System). Different database systems (Postgresql, Mysql, SQL Server, Oracle,…, etc) have different drivers, mostly either ODBC or JDBC.
Pyodbc
is an open-source python package that makes accessing ODBC databases easy. Some use pymssql
, but pyodbc
is the most popular one.
Let’s get our hands dirty! Firstly, import the required packages. We use sqlalchemy
, which is a popular python SQL toolkit, here to create the connection and use urllib
to create the connection string.
import os
import pyodbc
import sqlalchemy as sa
from sqlalchemy import create_engine
import urllib (Python 2.7)
from urllib.parse import quote_plus (Python 3)
Note that the quote_plus, which we will be using to generate the connection string, is different in Python 2.7 and Python 3. The first step of setting up the connection is to declare the environment variables. We use os.getenv
to specify the variables and credentials so they are in a safe hand.
server = os.getenv('SERVER_NAME')
database = os.getenv('DB_NAME')
username = os.getenv('USERNAME')
password = os.getenv('PASSWORD')
port = os.getenv('PORT')
The default port of Azure Sql Server is 1433. We need one more variable, driver. In order to find the right driver, run the following command lines in your terminal (make sure you have pyodbc
installed):
$ odbcinst -j
unixODBC 2.3.4
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/jamesho/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
$ cat /etc/odbcinst.ini
[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.0
UsageCount=1
The information of the driver is stored in odbcinst.ini
file. Set the driver to the name of your driver.
driver = '{ODBC Driver 13 for SQL Server}'
Next, we’re going to set up the connection string. There are 2 ways to define the connection string, one is using quote_plus under urllib
package to parse the string, the other is using sqlalchemy’s URL format.
# Using urllib
odbc_str =
'DRIVER='+driver+';SERVER='+server+';PORT='+port+';DATABASE='+database+';UID='+username+';PWD='+password
connect_str = 'mssql+pyodbc:///?odbc_connect='+quote_plus(odbc_str)
#Using sa URL format
sa_url = f"mssql+pyodbc://{username}:{password}@{server}:{port}/{database}?driver={driver}"
A full list of connection string of different databases can be found here.
Finally, create an engine and pass the string to the engine. Use the execute function of the engine to run your query, which should be passed as a string too.
engine = create_engine(connect_str / sa_url)
print(engine.execute('''
YOUR SQL QUERY
''').fetchall())
Full script:
import os
import pyodbc
import sqlalchemy as sa
from sqlalchemy import create_engine
import urllib (Python 2.7)
from urllib.parse import quote_plus (Python 3)
server = os.getenv('SERVER_NAME')
database = os.getenv('DB_NAME')
username = os.getenv('USERNAME')
password = os.getenv('PASSWORD')
port = os.getenv('PORT',default=1433)
driver = '{ODBC Driver 13 for SQL Server}'
#connect using parsed URL
odbc_str = 'DRIVER='+driver+';SERVER='+server+';PORT='+port+';DATABASE='+database+';UID='+username+';PWD='+ password
connect_str = 'mssql+pyodbc:///?odbc_connect=' + quote_plus(odbc_str)
#connect with sa url format
sa_url = f"mssql+pyodbc://{username}:{password}@{server}:{port}/{database}?driver={driver}"
engine = create_engine(connect_str/sa_url)
print(engine.execute('''
YOUR SQL QUERY
''').fetchall())
Go to your terminal, export the environment variables like, and run this python script.
$export SERVER_NAME =
$export DB_NAME =
$export USERNAME =
$export PASSWORD =
$export PORT =
$python {your script name}.py
You’ve successfully connected to your Azure SQL Database and you can interact with it using sqlalchemy.