How to use dsn.ini file for odbc connection?

Question

I am trying to connect to the msserver using odbc connection using ipython-sql magic in jupyter notebook.

I was wondering how to use dsn.ini file to load the configuration and connect to the server.

References:

• https://github.com/catherinedevlin/ipython-sql

My dsn.ini file

[DB_Practice]
username=sa
password=myrealpassword
host=myoriginalservername
drivername=ODBC Driver 17 for SQL Server
database=Practice

Error

%load_ext sql
%config SqlMagic.dsn_filename='./dsn.ini'
%sql --section DB_Practice


Environment variable $DATABASE_URL not set, and no connect string given.
Connection info needed in SQLAlchemy format, example:
               postgresql://username:password@hostname/dbname
               or an existing connection: dict_keys([])

Question

How to connect to msserver using odbc and using dsn.ini file?

Answer 1

It's a bit old but thought I'll post an answer in case anyone else is getting this issue.

The order I got it to work was the following: First I've tried to get it working without a dsn, just to make sure there's connectivity and I can actually query my DB. You'd have to install the pyodbc driver: pip install pyodbc

And can pass a sqlalchemy engine:

from sqlalchemy import create_engine
from sqlalchemy.engine import URL

connection_url = URL.create(
    "mssql+pyodbc",
    username="sa",
    password="MyPassword!",
    host="localhost",
    port=1433,
    database="master",
    query={
        "driver": "ODBC Driver 18 for SQL Server",
        "Encrypt": "yes",
        "TrustServerCertificate": "yes",
    },
)
engine = create_engine(connection_url)

Then, run a connection directly via the notebook, by passing that engine:

%load_ext sql
%sql engine

This is following that connection guide.

Here it's documented how to connect using DSN.ini.

So once you were able to set the engine, just consume the dsn file into it, set up the right parameter in your ini file and consume it as follows:

%config SqlMagic.dsn_filename='./dsn.ini'
%sql --section DB_CONFIG_1