eric.ness.net

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

%%sh

sudo su
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list > /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
server = "YOUR_SERVER_ADDRESS"
db = "YOUR_DB"
user = "YOUR_USER"
pwd = "YOUR_PASSWORD"
global conn
conn = pyodbc.connect('DRIVER={ODBC Driver 18 for SQL Server};' +
                      'SERVER=' + server + ';' +
                      'DATABASE=' + db + ';' +
                      'UID=' + user + ';' +
                      'PWD=' + pwd + ';' +
                      'TrustServerCertificate=yes;' +
                      'MARS_Connection=yes;')

4. Query data to data frame

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