Colab pyodbc Install & Usage for MSSQL Server

Colab pyodbc Install & Usage for MSSQL Server

March 31st, 2023  |  Published in Uncategorized

I was working on some quick Holt-Winters projections but wanted to connect to my remote database to get some data quickly. There are a couple of tutorials out there, some of which seem good, but this was my approach and will mirror what others have done.

1. Download keys and install msodbcsql18 on machine


sudo su
curl | apt-key add -
curl > /etc/apt/sources.list.d/mssql-release.list

sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18
# optional: for bcp and sqlcmd
sudo ACCEPT_EULA=Y apt-get install -y mssql-tools18
echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc
source ~/.bashrc
# optional: for unixODBC development headers
sudo apt-get install -y unixodbc-dev

2. Install pyodbc

!pip install pyodbc

3. Setup connection

import pyodbc
db = "YOUR_DB"
user = "YOUR_USER"
global conn
conn = pyodbc.connect('DRIVER={ODBC Driver 18 for SQL Server};' +
                      'SERVER=' + server + ';' +
                      'DATABASE=' + db + ';' +
                      'UID=' + user + ';' +
                      'PWD=' + pwd + ';' +
                      'TrustServerCertificate=yes;' +

4. Query data to data frame

sql = "Select * From my_table"
df = pd.read_sql(sql, conn)

Related Posts

Starting a Ph.D. in Computer Science
Online Virtual Machine Development
Data Driven Maps Part 2: KML Choropleth Maps
Data Driven Maps Part 1: SVG Choropleth Maps
Flowing Data Charts Using Dundas/Microsoft Charts