IPython Notebook to connect to SQL Server 2019

I installed Anaconda 3 some time ago, and then I installed SQL Server 2019 without ML libraries for Python. Now I just wanted to connect from an IPython Notebook to this local named instance of SQL Server, directly.

I tried with both pyodbc (which worked from first try) and pymssql which seemed to have some issue. The error message was:

"MSSQLDatabaseException: (20009, b'DB-Lib error message 20009, severity 9:\nUnable to connect: Adaptive Server is unavailable or does not exist (localhost\\SQLSRV2019)\n')"


It looks like pymssql library needs the TCP/IP connection configuration to be enabled, and also needs SQL Server Browser service to run. So, start SQL Server 2019 Configuration Manager and then Enable TCP/IP configuration setting:

And in Windows Services.msc make sure SQL Server Browser runs:

After these changes restart SQL Server 2019 instance (don't forget this step).

The code for pyodbc:

import pyodbc conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=.\SQLSRV2019;DATABASE=db1;UID=sa;PWD=***') cursor = conn.cursor() cursor.execute("select id1, name1 from Table1") rows = cursor.fetchall() print(rows)

The code for pymssql with Pandas DataFrame:

import pymssql import pandas as pd conn = pymssql.connect(user = 'sa', password = '***', host='.\SQLSRV2019', database = 'db1') q = pd.read_sql_query('select name1, id1 from Table1', conn) conn.close() df = pd.DataFrame(q, columns=['name1','id1']) print(df)

Then let's plot the values with matplotlib:

import matplotlib.pyplot as plt
df.plot(kind='bar')

You can try these with either JupyterLab / Jupyter Notebook from Anaconda 3 or even with VS Code extension for Anaconda.