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.
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