Connecting to SQL Server via SQL magics in Jupyter Lab

Question

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?

Habiliment

Answer 1

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]

Haemagglutinate

Answer 2

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}

Heresiarch

this should be preferred solution as it is more compact. – Washedup

Answer 3

Summary

It looks like you're trying to pass engine to %sql and assign it at the same time, which doesn't seem to be supported in either JupySQL (a currently-supported fork of ipython-sql) and ipython-sql. If you use JupySQL's version of the SqlMagic, you want to simply pass the created SQLAlchemy engine to %sql, or a static URL string (the engine is probably preferable).

JupySQL has ongoing support and maintenance at the moment, with additional features, so I recommend downloading JupySQL and using that instead (available since 2022).

See JupySQL's documentation here.

In line with the other answers, you can create a URL with SQLAlchemy's URL.create() method, specified in their documentation.

But to answer your question on how to do this correctly - there are two ways, depending on if you use ipython-sql or JupySQL:

ipython-sql

%load_ext sql

from sqlalchemy import URL

url_object = URL.create(
    "mssql+pyodbc",
    username="user",
    password="password",  # plain (unescaped) text
    host="server",
    database="db",
)

engine = sqlalchemy.create_engine(url_object)

%sql $engine.url
# or
%sql mssql+pyodbc://user:***@server/db

JupySQL

%load_ext sql

from sqlalchemy import URL
from sqlalchemy import 

url_object = URL.create(
    "mssql+pyodbc",
    username="user",
    password="password",  # plain (unescaped) text
    host="server",
    database="db",
)

engine = sqlalchemy.create_engine(url_object)

%sql engine
# or
%sql mssql+pyodbc://user:***@server/db

Greenhaw