5

I am trying to connect to our remote sql server db via Jupyter Labs using the SQL magics. I can connect with a traditional connection string, but the SQL magics doesnt seem to work (which means I am doing something wrong). Below is the working pyodbc connection:

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=Server;'
                      'Database=DB;'
                      'Trusted_Connection=yes;')

But when I try to connect with the magics I get an error that says no connection string supplied

%load_ext sql
%sql engine = create_engine("mssql+pyodbc://user:password@server/db")

Ideally, I would like to connect with Magics using a DSN (I tried this also and didnt work):

%load_ext sql
%sql engine = create_engine("mssql+pyodbc://DSN;Trusted_Connection = Yes")

Any help on getting a connection working?

cowboy
  • 613
  • 5
  • 20

2 Answers2

6

Try this from a Jupyter cell:

import urllib
params = urllib.parse.quote_plus("DRIVER={SQL SERVER};SERVER=Server;DATABASE=DB;TRUSTED_CONNECTION=YES")
"mssql+pyodbc:///?odbc_connect=%s" % params

Then run the cell and copy/paste the encoded string that is returned from its evaluation into the magic.

%%sql mssql+pyodbc:///?odbc_connect=DRIVER%3D%7BSQL+SERVER%7D%3BSERVER%3DServer%3BDATABASE%3DDB%3BTRUSTED_CONNECTION%3DYES
   [your SQL code here]
jdc
  • 1,255
  • 12
  • 15
4

A programmatic way without copy pasting is to use curled braces:

connection_str = "DRIVER={SQL SERVER};SERVER=Server;DATABASE=DB;TRUSTED_CONNECTION=YES"
connection_str_quoted = urllib.parse.quote_plus(connection_str)
connection_uri = 'mssql+pyodbc:///?odbc_connect={}'.format(connection_str_quoted)

# this is how you would connect in sqlalchemy
import sqlalchemy
conn = sqlalchemy.create_engine(connection_uri)

%sql {connection_uri}
Dima Lituiev
  • 12,544
  • 10
  • 41
  • 58