SQL Command Magic for IPython

An IPython magic extension for executing SQL queries against Microsoft SQL Server using the sqlcmd utility.

Features

• Execute SQL queries directly from IPython/Jupyter notebooks
• Connect to Microsoft SQL Server using simple parameters or connection strings
• Execute SQL in batches separated by GO statements
• Execute external SQL files using EXECUTE_SQL_FILE
• Substitute Python variables in SQL queries
• Debug mode for troubleshooting

Installation

pip install ipython-sqlcmd

Requirements

• Python 3.7+
• IPython 7.0+
• Pandas 1.0+
• Microsoft SQL Server
• sqlcmd utility installed and available in PATH

Usage

Load the extension

%load_extsqlcmd

Set connection using simplified syntax

# Connect using just the database name (uses defaults for other parameters)%sqlcmdAdventureWorks# Connect with custom server, username and password%sqlcmdAdventureWorks--server=myserver--username=myuser--password=mypassword# Connect using password from environment variable%sqlcmdAdventureWorks--password-env=MY_SQL_PASSWORD# Connect with additional connection options%sqlcmdAdventureWorks--driver="ODBC Driver 18 for SQL Server"--no-encrypt

Set connection using full connection string (advanced)

%sqlcmd'mssql+sqlcmd:///?odbc_connect=SERVER=myserver;DATABASE=mydb;UID=myuser;PWD=mypassword'

Execute SQL

%%sqlcmdSELECTTOP10*FROMMyTableWHEREColumn1='Value'

Execute SQL with variable substitution

value="SomeValue"%%sqlcmdSELECTTOP10*FROMMyTableWHEREColumn1='$value'

Debug mode

%%sqlcmd--debugSELECTTOP10*FROMMyTable

Execute SQL from file

%%sqlcmdEXECUTE_SQL_FILE'path/to/query.sql'

Connection Parameters

When using the simplified connection syntax, the following parameters are available:

Parameter	Short Flag	Default Value	Description
--server	-s	localhost	SQL Server instance
--username	-u	sa	SQL Server username
--password	-p	None	SQL Server password
--password-env	-e	SSMS_PASSWORD	Environment variable containing password
--driver	-d	ODBC Driver 17 for SQL Server	ODBC driver name
--encrypt		True	Encrypt connection (default)
--no-encrypt			Do not encrypt connection
--trust-certificate		True	Trust server certificate (default)
--no-trust-certificate			Do not trust server certificate
--encoding	-c	utf-8	Output and SQL database encoding (e.g., utf-8, latin-1, cp1252)
--show-execution-time	-t	False	Print execution time of SQL queries

License

MIT