Microsoft SQL Server#

In this tutorial, we’ll see how to query Microsoft SQL Server from Jupyter. Optionally, you can spin up a testing server.

Tip

If you encounter issues, feel free to join our community and we’ll be happy to help!

Installing the ODBC driver#

The first step is to install the ODBC driver for SQL Server.

  • Instructions for Linux

  • Instructions for Mac

For example, if you’re on a Mac, you can install the driver with brew:

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install.sh)"
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
HOMEBREW_ACCEPT_EULA=Y brew install msodbcsql18 mssql-tools18

Starting SQL Server with Docker#

If you don’t have a SQL Server running or you want to spin up one for testing, you can do it with the official Docker image.

Important

If you’re on a Mac with Apple Silicon (e.g., M1 processor), ensure you’re running the latest Docker Desktop version. More info here.

To start the server:

%%bash
docker run -e "ACCEPT_EULA=Y" \
    -e "MSSQL_SA_PASSWORD=MyPassword!" \
    -p 1433:1433 \
    -d mcr.microsoft.com/mssql/server:2022-latest

WARNING: The requested image's platform (linux/amd64) does not match the detected host platform (linux/arm64/v8) and no specific platform was requested

32373fc0b93b9749e2457f2e3a66d3981abf871ea99bbd172c54ea83cf9b0827

Important

Ensure you set a strong password, otherwise the container will shut down silently!

Ensure that your container is running (run the command a few seconds after running the previous one to ensure it dind’t shut down silently):

%%bash
docker ps

CONTAINER ID   IMAGE                              COMMAND                  CREATED              STATUS              PORTS                              NAMES
bdfab4808021   mcr.microsoft.com/azure-sql-edge   "/opt/mssql/bin/perm…"   About a minute ago   Up About a minute   1401/tcp, 0.0.0.0:1433->1433/tcp   sql

If you have issues with the previous command, you can try with SQL Edge:

%%bash
docker run -e "ACCEPT_EULA=1" -e "MSSQL_SA_PASSWORD=MyPassword!" \
    -e "MSSQL_PID=Developer" -e "MSSQL_USER=sa" \
    -p 1433:1433 -d --name=sql mcr.microsoft.com/azure-sql-edge

bdfab4808021247a4d0aad3bbdf7dca0247c8146573a33c3f874e1f9ed847801

Ensure the server is running (wait for a few seconds before running it):

%%bash
docker ps

CONTAINER ID   IMAGE                              COMMAND                  CREATED         STATUS         PORTS                              NAMES
bdfab4808021   mcr.microsoft.com/azure-sql-edge   "/opt/mssql/bin/perm…"   3 minutes ago   Up 3 minutes   1401/tcp, 0.0.0.0:1433->1433/tcp   sql

Installing pyodbc#

pyodbc will allow us to connect to SQL Server. If you’re on macOS or Linux, you need to install unixODBC. Note that when installing the ODBC driver on macOS using brew, unixODBC is also installed.

Install pyodbc with:

pip install pyodbc

Note

If you’re on a Mac with Apple Silicon (e.g., M1 processor), you might encounter issues, if so, try thi:

pip install pyodbc=4.0.34

Verify a successful installation with:

import pyodbc

Verify that pyodbc is able to findn the SQL Server driver:

pyodbc.drivers()

['ODBC Driver 18 for SQL Server']

Tip

If the driver doesn’t appear, uninstalling pyodbc and re-installing it again might fix the problem.

If you’re on a Mac with Apple Silicon, ensure you installed pyodbc with pip, since conda might lead to issues.

Starting the connection#

To start the connection, execute the following, change the values to match your SQL Server’s configurationo:

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)

Install, load the Jupyter extension and start the connection:

%pip install jupysql --quiet

Note: you may need to restart the kernel to use updated packages.

%load_ext sql
%sql engine

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

Note

If you see the following error:

InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not found and no default driver specified (0) (SQLDriverConnect)')
(Background on this error at: https://sqlalche.me/e/14/rvf5)

It might be that you’re missing the SQL Server ODBC driver or that pyodbc cannot find it.

Run some sample queries:

%%sql
SELECT * FROM sys.databases;

*  mssql+pyodbc://sa:***@localhost:1433/master?Encrypt=yes&TrustServerCertificate=yes&driver=ODBC+Driver+18+for+SQL+Server
Done.
name database_id source_database_id owner_sid create_date compatibility_level collation_name user_access user_access_desc is_read_only is_auto_close_on is_auto_shrink_on state state_desc is_in_standby is_cleanly_shutdown is_supplemental_logging_enabled snapshot_isolation_state snapshot_isolation_state_desc is_read_committed_snapshot_on recovery_model recovery_model_desc page_verify_option page_verify_option_desc is_auto_create_stats_on is_auto_create_stats_incremental_on is_auto_update_stats_on is_auto_update_stats_async_on is_ansi_null_default_on is_ansi_nulls_on is_ansi_padding_on is_ansi_warnings_on is_arithabort_on is_concat_null_yields_null_on is_numeric_roundabort_on is_quoted_identifier_on is_recursive_triggers_on is_cursor_close_on_commit_on is_local_cursor_default is_fulltext_enabled is_trustworthy_on is_db_chaining_on is_parameterization_forced is_master_key_encrypted_by_server is_query_store_on is_published is_subscribed is_merge_published is_distributor is_sync_with_backup service_broker_guid is_broker_enabled log_reuse_wait log_reuse_wait_desc is_date_correlation_on is_cdc_enabled is_encrypted is_honor_broker_priority_on replica_id group_database_id resource_pool_id default_language_lcid default_language_name default_fulltext_language_lcid default_fulltext_language_name is_nested_triggers_on is_transform_noise_words_on two_digit_year_cutoff containment containment_desc target_recovery_time_in_seconds delayed_durability delayed_durability_desc is_memory_optimized_elevate_to_snapshot_on is_federation_member is_remote_data_archive_enabled is_mixed_page_allocation_on is_temporal_history_retention_enabled catalog_collation_type catalog_collation_type_desc physical_database_name is_result_set_caching_on is_accelerated_database_recovery_on is_tempdb_spill_to_remote_store is_stale_page_detection_on is_memory_optimized_enabled is_data_retention_enabled
master 1 None b'\x01' 2003-04-08 09:13:36.390000 150 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER False False False 0 ONLINE False False False 1 ON False 3 SIMPLE 2 CHECKSUM True False True False False False False False False False False False False False False False False True False False False False False False False False 00000000-0000-0000-0000-000000000000 False 4 ACTIVE_TRANSACTION False False False False None None None None None None None None None None 0 NONE 0 0 DISABLED False False False True True 0 DATABASE_DEFAULT master False False False False True True
tempdb 2 None b'\x01' 2023-03-03 18:30:36.683000 150 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER False False False 0 ONLINE False False False 0 OFF False 3 SIMPLE 2 CHECKSUM True False True False False False False False False False False False False False False False False True False False False False False False False False 0E7FA998-68E7-4C88-8637-96D75972D644 True 0 NOTHING False False False False None None None None None None None None None None 0 NONE 60 0 DISABLED False False False False True 0 DATABASE_DEFAULT tempdb False False False False True True
model 3 None b'\x01' 2003-04-08 09:13:36.390000 150 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER False False False 0 ONLINE False False False 0 OFF False 3 SIMPLE 2 CHECKSUM True False True False False False False False False False False False False False False False False False False False False False False False False False 00000000-0000-0000-0000-000000000000 False 0 NOTHING False False False False None None None None None None None None None None 0 NONE 60 0 DISABLED False False False True True 0 DATABASE_DEFAULT model False False False False True True
msdb 4 None b'\x01' 2023-01-25 11:15:47.897000 150 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER False False False 0 ONLINE False False False 1 ON False 3 SIMPLE 2 CHECKSUM True False True False False False False False False False False False False False False True True True False False False False False False False False DFDA93AA-A255-4F97-AFEB-F0FC5176D611 True 0 NOTHING False False False False None None None None None None None None None None 0 NONE 60 0 DISABLED False False False True True 0 DATABASE_DEFAULT msdb False False False False True True 
%%sql
CREATE TABLE languages (name VARCHAR(255), rating FLOAT, change FLOAT);
INSERT INTO languages VALUES ('Python', 14.44, 2.48);
INSERT INTO languages VALUES ('C', 13.13, 1.50);
INSERT INTO languages VALUES ('Java', 11.59, 0.40);
INSERT INTO languages VALUES ('C++', 10.00, 1.98);

*  mssql+pyodbc://sa:***@localhost:1433/master?Encrypt=yes&TrustServerCertificate=yes&driver=ODBC+Driver+18+for+SQL+Server
Done.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.

[]

%%sql
SELECT * FROM languages

*  mssql+pyodbc://sa:***@localhost:1433/master?Encrypt=yes&TrustServerCertificate=yes&driver=ODBC+Driver+18+for+SQL+Server
Done.
name rating change
Python 14.44 2.48
C 13.13 1.5
Java 11.59 0.4
C++ 10.0 1.98

Shut down the container#

%%bash
docker container ls

CONTAINER ID   IMAGE                              COMMAND                  CREATED          STATUS          PORTS                              NAMES
693c23a635a1   mcr.microsoft.com/azure-sql-edge   "/opt/mssql/bin/perm…"   48 minutes ago   Up 48 minutes   1401/tcp, 0.0.0.0:1433->1433/tcp   sql

%%bash
docker container stop sql

sql

%%bash
docker container rm sql

sql

%%bash
docker container ls

CONTAINER ID   IMAGE     COMMAND   CREATED   STATUS    PORTS     NAMES